Ruby串連使用windows下sql server資料庫代碼執行個體

Ruby串連使用windows下sql server資料庫代碼執行個體

 　　這篇文章主要介紹了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