From: jamesvtoomey@... (James Toomey)
Date: 2003-01-29T08:43:54+09:00
Subject: Re: Connecting to a MS SQL database...

"Dwayne Smurdon @ DNA Media Pro" <smurdon@dnamediapro.com> wrote in message news:<13926B416A401249B9561EEA179834034B52@dna-serv.corp.dnamediapro.com>...
> Where can I find information about connecting to a database?  In particular
> I have an MS SQL database and I am using a DSN -- but would also like to
> know how to use DSN-less connections.
> 
> Is this part of an existing module?  OLE?  I've googled a lot but have not
> seen much.
> 
> -Dwayne
> 
> --

This code should work. You'll probably find more examples if you
search for "VB", "SQLServer", and "DSN-less connection", rather than
including Ruby in your search terms. Once you find an example,
translate it from VB to Ruby (which isn't too hard, since win32ole
allows you to use the methods and properties with pretty much the same
syntax).

require 'win32ole'
adoConn = WIN32OLE.new('ADODB.Connection')
adoRS = WIN32OLE.new("ADODB.Recordset")
#For a DSN connection, use this string
adoConn.ConnectionString =
"DSN=PutDSNNameHere;UID=PutUserNameHere;PWD=PutPasswordHere;"
#For a DSN-less connection, use this string
adoConn.ConnectionString = "driver={SQL Server};
Server=PutServerNameHere; Database=PutDatabaseNameHere;
UID=PutUserNameHere; PWD=PutPasswordHere"
adoConn.Open
# The 1 specifies the "Keyset" cursor, and the 3 specifies Optimistic
locking
adoRS.Open "SELECT * From TableName", adoConn, 1, 3
# This loops through the records and writes out the contents of each
field.
1.upto(adoRS.RecordCount) do
 |i|
 puts i
 puts adoRS.Fields("FieldName").Value
 adoRS.MoveNext
end

adoConn.Close