Ruby连接使用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 endend

测试代码如下：

db = sqlserver.newdb.opendb.query("select player from players where team = 'reds';")field_names = db.fieldsplayers = db.datadb.closedb = 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

• 0
• 0
• 0
• 0
• 0