windows下sql server连接

史蒂夫和斯凯

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
end
end


测试代码如下：

db = SqlServer.new
db.open
db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")
field_names = db.fields
players = db.data
db.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