require 'win32ole'class SqlServer # This class manages database connection and queries attr_accessor :connection, :data, :fields def initialize @connection = nil @data = nil end def open # Open ADO connection to the SQL Server database connection_string = "Provider=SQLOLEDB.1;
" connection_string << "Persist Security Info=False;
" connection_string << "User ID=USER_ID;
" connection_string << "password=PASSWORD;
" connection_string << "Initial Catalog=DATABASE;
" connection_string << "Data Source=IP_ADDRESS;
" connection_string << "Network Library=dbmssocn" @connection = WIN32OLE.new('ADODB.Connection') @connection.Open(connection_string) end def query(sql) # Create an instance of an ADO Recordset recordset = WIN32OLE.new('ADODB.Recordset') # Open the recordset, using an SQL statement and the # existing ADO connection recordset.Open(sql, @connection) # Create and populate an array of field names @fields = [] recordset.Fields.each do |field| @fields << field.Name end begin # Move to the first record/row, if any exist recordset.MoveFirst # Grab all records @data = recordset.GetRows rescue @data = [] end recordset.Close # An ADO Recordset's GetRows method returns an array # of columns, so we'll use the transpose method to # convert it to an array of rows @data = @data.transpose end def close @connection.Close endend
测试代码如下：
db = SqlServer.newdb.opendb.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';
")field_names = db.fieldsplayers = db.datadb.close
db = SqlServer.new('localhost', 'sa', 'SOMEPASSWORD') db.open('Northwind') db.query("SELECT * from Customers;
") puts field_names = db.fields cust = db.data puts cust.size puts cust[0].inspect db.close
抄到的别人版本的：
MSSQL require "dbi" require "win32ole" WIN32OLE.codepage = WIN32OLE::CP_UTF8 require 'iconv' Re_cn=/[x7f-xff]/ class MssqlDb attr_accessor :mdb, :connection, :data, :fields def initialize(host,mdb,user,pass) @host= host @mdb=@database= mdb @username= user @password= pass @connection = nil @data = nil @fields = nil end def open connection_string = "Provider=SQLOLEDB.1;
User ID=@username;
password=@password;
Data Source=@host,1433;
Initial Catalog=@mdb" @connection = WIN32OLE.new('ADODB.Connection') @connection.Open(connection_string) @password='' end def query(sql) recordset = WIN32OLE.new('ADODB.Recordset') recordset.Open(sql, @connection) @fields = [] recordset.Fields.each do |field| @fields << field.Name end begin @data = recordset.GetRows.transpose rescue @data = [] end recordset.Close end def queryGB(sql) if sql=~ Re_cn sql = utf8_to_gb(sql) end recordset = WIN32OLE.new('ADODB.Recordset') recordset.Open(sql, @connection) @fields = [] recordset.Fields.each do |field| @fields << field.Name end begin @data = recordset.GetRows.transpose rescue @data = [] end recordset.Close end def execute(sql) @connection.Execute(sql) end def executeGB(sql) if sql=~ Re_cn sql = utf8_to_gb(sql) end @connection.Execute(sql) end def close @connection.Close end def utf8_to_gb(s) p 'conv to gb18030' Iconv.conv("GB18030//IGNORE","UTF-8//IGNORE",s) end def gb_to_utf8(s) p 'conv to utf8' Iconv.conv("UTF-8//IGNORE","GB18030//IGNORE",s) end end ACCESS require "win32ole" class AccessDb attr_accessor :mdb, :connection, :data, :fields def initialize(mdb=nil) @mdb = mdb @connection = nil @data = nil @fields = nil end def open connection_string = 'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=' connection_string << @mdb @connection = WIN32OLE.new('ADODB.Connection') @connection.Open(connection_string) p 'access open ok.' end def query(sql) recordset = WIN32OLE.new('ADODB.Recordset') recordset.Open(sql, @connection) @fields = [] recordset.Fields.each do |field| @fields << field.Name end begin @data = recordset.GetRows.transpose rescue @data = [] end recordset.Close end def execute(sql) @connection.Execute(sql) end def close @connection.Close end end
