這篇文章主要介紹了Ruby連接使用windows下sql server數據庫代碼實例,本文直接給出實現代碼,而且給出了兩種實現和access數據庫的實現代碼,需要的朋友可以參考下
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 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測試代碼如下：
?
1 2 3 4 5 6 db = SqlServer.new db.open db.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';") field_names = db.fields players = db.data db.close?
1 2 3 4 5 6 7 8 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抄到的別人版本的：
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 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
