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