DBI with SQL Server

Can someone give me a little help with DBI when connecting to SQL
Server? I've dug around a bit, but can't get a simple select statement
to work. Here's what I'm trying:

require 'dbi'
db = DBI.connect("DBI:ODBC:driver={SQL
Server};Server=kint;Database=MyJournal;UID=test;PWD=test")
sql = "SELECT * from UserInfo"
db.select_all(sql) do |row|
row.each_value do |value|
puts value
end
end
db.disconnect

I get the following error:

c:/tools/ruby/lib/ruby/site_ruby/1.8/DBD/ODBC/ODBC.rb:78:in `connect':
S1090 (0) [Microsoft][ODBC Driver Manager] Invalid string or buffer
length (DBI::DatabaseError)
from c:/tools/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:550:in `connect'
from c:/tools/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:367:in `connect'
from sql.rb:2

I'm using the one-click windows installation (ruby 1.8.1 (2004-01-27)
[i386-mswin32]).

Thanks,
Eli

Eli Tucker wrote:

Can someone give me a little help with DBI when connecting to SQL
Server? I've dug around a bit, but can't get a simple select statement
to work. Here's what I'm trying:

Get the latest rubydbi distribution. Extract the ADO.rb file. Copy it to X:\Ruby\lib\ruby\site_ruby\1.8\DBD\ADO\ADO.rb Then you can do things like this:

# Make sure the "dsn =" stuff is all on one line; it may wrap in this email
dsn= 'DBI:ADO:Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=test;User Id=sa;Password=password;'

DBI.connect(dsn) do |conn|
    conn.select_all("SELECT * FROM foo") do |row|
        puts row[:Id]
    end
end

Of course this assumes you are on a Windows box. ADO.rb depends on Win32OLE, which (I would assume) only works on Windows.

···

--
She drove a Plymouth Satellite
Faster than the Speed of Light...

http://www.joeygibson.com/blog

Atlanta Ruby User Group http://www.AtlRUG.org

Joey,

Thanks for the tip. This worked great.

- Eli