Ruby连接使用windows下sql server数据库代码实例
require'win32ole'
classSqlServer
#Thisclassmanagesdatabaseconnectionandqueries
attr_accessor:connection,:data,:fields
definitialize
@connection=nil
@data=nil
end
defopen
#OpenADOconnectiontotheSQLServerdatabase
connection_string="Provider=SQLOLEDB.1;"
connection_string<<"PersistSecurityInfo=False;"
connection_string<<"UserID=USER_ID;"
connection_string<<"password=PASSWORD;"
connection_string<<"InitialCatalog=DATABASE;"
connection_string<<"DataSource=IP_ADDRESS;"
connection_string<<"NetworkLibrary=dbmssocn"
@connection=WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)
end
defquery(sql)
#CreateaninstanceofanADORecordset
recordset=WIN32OLE.new('ADODB.Recordset')
#Opentherecordset,usinganSQLstatementandthe
#existingADOconnection
recordset.Open(sql,@connection)
#Createandpopulateanarrayoffieldnames
@fields=[]
recordset.Fields.eachdo|field|
@fields<<field.Name
end
begin
#Movetothefirstrecord/row,ifanyexist
recordset.MoveFirst
#Graballrecords
@data=recordset.GetRows
rescue
@data=[]
end
recordset.Close
#AnADORecordset'sGetRowsmethodreturnsanarray
#ofcolumns,sowe'llusethetransposemethodto
#convertittoanarrayofrows
@data=@data.transpose
end
defclose
@connection.Close
end
end
测试代码如下：
db=SqlServer.new
db.open
db.query("SELECTPLAYERFROMPLAYERSWHERETEAM='REDS';")
field_names=db.fields
players=db.data
db.close
db=SqlServer.new('localhost','sa','SOMEPASSWORD')
db.open('Northwind')
db.query("SELECT*fromCustomers;")
putsfield_names=db.fields
cust=db.data
putscust.size
putscust[0].inspect
db.close
抄到的别人版本的：
MSSQL
require"dbi"
require"win32ole"
WIN32OLE.codepage=WIN32OLE::CP_UTF8
require'iconv'
Re_cn=/[\x7f-\xff]/
classMssqlDb
attr_accessor:mdb,:connection,:data,:fields
definitialize(host,mdb,user,pass)
@host=host
@mdb=@database=mdb
@username=user
@password=pass
@connection=nil
@data=nil
@fields=nil
end
defopen
connection_string="Provider=SQLOLEDB.1;UserID=@username;password=@password;DataSource=@host,1433;InitialCatalog=@mdb"
@connection=WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)
@password=''
end
defquery(sql)
recordset=WIN32OLE.new('ADODB.Recordset')
recordset.Open(sql,@connection)
@fields=[]
recordset.Fields.eachdo|field|
@fields<<field.Name
end
begin
@data=recordset.GetRows.transpose
rescue
@data=[]
end
recordset.Close
end
defqueryGB(sql)
ifsql=~Re_cn
sql=utf8_to_gb(sql)
end
recordset=WIN32OLE.new('ADODB.Recordset')
recordset.Open(sql,@connection)
@fields=[]
recordset.Fields.eachdo|field|
@fields<<field.Name
end
begin
@data=recordset.GetRows.transpose
rescue
@data=[]
end
recordset.Close
end
defexecute(sql)
@connection.Execute(sql)
end
defexecuteGB(sql)
ifsql=~Re_cn
sql=utf8_to_gb(sql)
end
@connection.Execute(sql)
end
defclose
@connection.Close
end
defutf8_to_gb(s)
p'convtogb18030'
Iconv.conv("GB18030//IGNORE","UTF-8//IGNORE",s)
end
defgb_to_utf8(s)
p'convtoutf8'
Iconv.conv("UTF-8//IGNORE","GB18030//IGNORE",s)
end
end
ACCESS
require"win32ole"
classAccessDb
attr_accessor:mdb,:connection,:data,:fields
definitialize(mdb=nil)
@mdb=mdb
@connection=nil
@data=nil
@fields=nil
end
defopen
connection_string='Provider=Microsoft.Jet.OLEDB.4.0;DataSource='
connection_string<<@mdb
@connection=WIN32OLE.new('ADODB.Connection')
@connection.Open(connection_string)
p'accessopenok.'
end
defquery(sql)
recordset=WIN32OLE.new('ADODB.Recordset')
recordset.Open(sql,@connection)
@fields=[]
recordset.Fields.eachdo|field|
@fields<<field.Name
end
begin
@data=recordset.GetRows.transpose
rescue
@data=[]
end
recordset.Close
end
defexecute(sql)
@connection.Execute(sql)
end
defclose
@connection.Close
end
end