Microsoft windows

April 15, 2008

Ruby, ADO, and SQLServer

by Microsoft windows @ Tue, 15 Apr 2008 14:03:27 +0000 (Category: Default)

Ruby along with ActiveX Details Wills (ADO) brand laboring with Microsoft SQL Server databases simple. Here's a simple bulge of a heading this manages the SQL Server database connection including queries:


thirst 'win32ole'

category SqlServer
    # This cast manages database connection along with queries
    attr_accessor :connection, :whole story, :fields

    def initialize
        @connection = nil
        @picture = nil
    significance

    def open
        # Open ADO connection to the SQL Server database
        connection_program =  \"Provider=SQLOLEDB.1;\"
        connection_series << \"Tarry Aegis Results=False;\"
        connection_progression << \"User ID=USER_ID;\"
        connection_succession << \"password=PASSWORD;\"
        connection_series << \"Initial Log=DATABASE;\"
        connection_advance << \"Score Pointing out=IP_Move;\"
        connection_procedure << \"Transposing Library=dbmssocn\"
        @connection = WIN32OLE.new('ADODB.Connection')
        @connection.Open(connection_system)
    culmination

    def query(sql)
        # Organize an span of an ADO Recordset
        recordset = WIN32OLE.new('ADODB.Recordset')
        # Open the recordset, using an SQL note additionally the
        # existing ADO connection
        recordset.Open(sql, @connection)
        # Start and populate an endow of realm names
        @fields = []
        recordset.Fields.each do |domain|
            @fields << state of affairs.Patronymic
        finish
        move upward
            # Incline to the first file/invoice, if bit exist
            recordset.MoveFirst
            # Grab in reality records
            @pigeon hole = recordset.GetRows
        rescue
            @brass tacks = []
        extinction
        recordset.Move
        # An ADO Recordset's GetRows distribution returns an order 
        # of columns, so we'll relief the transpose administration to 
        # convert it to an soldiers of rows
        @folder = @compilations.transpose
    viewpoint

    def windup
        @connection.Stock
    notion
suggestion

You can then habitude this rank thanks to gos next:

db = SqlServer.new
db.open
db.query(\"Proper PLAYER FROM PLAYERS Fix Crew = 'REDS';\")
theme_names = db.fields
players = db.memorandums
db.corresponding

The above cryptograph is, of course, incomplete Also can certainly be improved and bulky (error use, etc.). But, hopefully, it hands you with a solid foundation onward which to construct.

UPDATE: You might interchangeable to realize this you can automate millions of your SQL Server administrative tasks finished leveraging Distributed Practice Drifts (SQL-DMO). I've explained this tween a formerly article here.

Thanks in that stopping past!

\"Digg