Ruby连接使用windows下sql server数据库代码实例

Windows  /  管理员 发布于 4年前   261

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  endend

测试代码如下：

db = SqlServer.newdb.opendb.query("SELECT PLAYER FROM PLAYERS WHERE TEAM = 'REDS';")field_names = db.fieldsplayers = db.datadb.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 

抄到的别人版本的：

 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