Ruby connection using Windows SQL Server database Code instance

This article mainly introduces the Ruby connection using Windows SQL Server database code instance, this article directly gives the implementation code, and gives two implementations and Access database implementation code, the need for friends can refer to the

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

The test code is as follows:

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

Copy to the other version of:

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