Did you know? DZone has great portals for Python, Cloud, NoSQL, and HTML5!

DZone Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

Snippets Manager

Snippets has posted 5886 posts at DZone. View Full User Profile

Accessing SQL Server 2005 From Ruby

04.26.2007

        The following is improved version of the code created by David Mullet, from
http://rubyonwindows.blogspot.com/2007/03/ruby-ado-and-sqlserver.html

require 'win32ole'

class SqlServer
    # This class manages database connection and queries
    attr_accessor :connection, :data, :fields
	attr_writer :username, :password

    def initialize(host, username = 'sa', password='')
        @connection = nil
        @data = nil
	@host = host
	@username = username
	@password = password
    end

    def open(database)
        # Open ADO connection to the SQL Server database
        connection_string =  "Provider=SQLOLEDB.1;"
        connection_string << "Persist Security Info=False;"
        connection_string << "User ID=#{@username};"
        connection_string << "password=#{@password};"
        connection_string << "Initial Catalog=#{database};"
        connection_string << "Data Source=#{@host};"
        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


How to use it:
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

Tags:

Ruby
sqlserver

Comments

Hoorure Net replied on Wed, 2008/01/09 - 2:45pm

Doeasn't work: F:/gitlocalrepo/QT/LDAP_Sync/sql_server2:40:in `method_missing': Open (WIN32OLERuntimeError) OLE error code:80004005 in Microsoft OLE DB Provider for SQL Server [DBNETLIB][ConnectionOpen (Connect()).]Specified SQL server not found. HRESULT error code:0x80020009 Exception occurred. from F:/gitlocalrepo/QT/LDAP_Sync/sql_server2:40:in `open' from F:/gitlocalrepo/QT/LDAP_Sync/sql_server2:79 from -e:1:in `load' from -e:1 Process finished with exit code 1

Snippets Manager replied on Thu, 2009/02/12 - 4:38pm

This was really helpful! FYI, if you want to use Windows authentication, assuming that the id running the Ruby script is the one you want to pass on, modify the connection string as follows:


			connection_string =  "Provider=SQLOLEDB.1;"
			connection_string << "Persist Security Info=False;"
			connection_string << "Trusted_Connection=yes;"
			connection_string << "Initial Catalog=#{database};"
			connection_string << "Data Source=#{@host};"
			connection_string << "Network Library=dbmssocn"
			@connection = WIN32OLE.new('ADODB.Connection')
			@connection.Open(connection_string)