Ruby connection uses the SQL server database code instance in windows, rubysql

Ruby connection uses sql server database code example under windows, rubysql

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
Copied version of others:

 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