Require ' Win32ole ' class SQL Server # 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 S
QL 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 A instance 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. The Name end begin # Move to the ' Record/row ', if any exist the recordset. MoveFirst # Grab All records @data = Recordset. GetRows Rescue @data = [] End recordset.
Close # a ADO Recordset ' s GetRows method returns a array # of columns, so we'll use the transpose # convert it to a array of rows @data = @data. Transpose End def close @connection.
Close 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
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,:d ata,: Fields def initialize (host,mdb,user,pass) @hos t= host @mdb = @database = mdb @username = user @password = Pass @connection = Nil @data = Nil @fields = Nil End def open connection_string = "provider=sqloledb.1;" The User id= @username;p assword= @password;D ata 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. The 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. The 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 ACCESS require "Win32ole" class Accessdb Attr_accessor:mdb,: Connection,:d ata,: Fields def Initializ E (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. The 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