Dsn-less odbc connections possible?


#1

hi all!

i’m currently working on a rails app that needs to access other
databases via user defined ODBC connections.

i’m using the latest rails-odbc gem on rails 1.2.2 (windows).

while DSN driven connections seem to work fine it’s a bit inefficient
because of the need to define the DSN’s on the server and then add them
in the rails app.
so i was hoping to use connection strings directly. but whenever i try
putting in a connection string the connection fails with the error
“Invalid string or buffer length”

an example string i was using:

Driver={SQL
Server};Host=myhostname;Database=mydbname;Uid=myusername;Pwd=mypassword

any ideas how to get this working?


#2

I use a dastabase.yml file like this:

development:
adapter: sqlserver
mode: ODBC
dsn: Driver={SQL Server};Server=MyMachine;Database=my_database

As far as I know, it should work with most dsn strings.

Tom


#3

Tom W. wrote:

I use a dastabase.yml file like this:

development:
adapter: sqlserver
mode: ODBC
dsn: Driver={SQL Server};Server=MyMachine;Database=my_database

As far as I know, it should work with most dsn strings.

Tom

this only seem to work with the sqlserver adapter in odbc mode.
but i need to use the rails-odbc adapter (to support all odbc databases
including oracle).

i have this in a (dummy) model
establish_connection(:adapter => ‘odbc’,
:dsn => connection_options.dsn,
:username => connection_options.username,
:password => connection_options.password,
:trace => connection_options.trace,
:emulate_booleans =>
connection_options.emulate_booleans,
:convert_numeric_literals =>
connection_options.convert_numeric_literals)

but as soon as i use a connection string instead of a system dsn it ends
with this error:

ODBC::Error: S1090 (0) [Microsoft][ODBC Driver Manager] Invalid string
or buffer length
from
c:/ruby/lib/ruby/gems/1.8/gems/odbc-rails-1.4/lib/active_record/connection_adapters/odbc_adapter.rb:48:in
initialize' from c:/ruby/lib/ruby/gems/1.8/gems/odbc-rails-1.4/lib/active_record/connection_adapters/odbc_adapter.rb:48:inconnect’
from
c:/ruby/lib/ruby/gems/1.8/gems/odbc-rails-1.4/lib/active_record/connection_adapters/odbc_adapter.rb:48:in
odbc_connection' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:267:insend’
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:267:in
connection_without_query_cache=' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/query_cache.rb:54:inconnection=’
from
c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:235:in
retrieve_connection' from c:/ruby/lib/ruby/gems/1.8/gems/activerecord-1.15.2/lib/active_record/connection_adapters/abstract/connection_specification.rb:78:inconnection’

the error causing line (48) in odbc_adapter.rb is:
conn = ODBC::connect(dsn, username, password)

i can’t get that to connect properly and i still couldn’t figure out
what code is used for that “ODBC::connect” it can’t be the connect
method from odbc.rb because that would work :S (i manually verified that
it splits the dsn if it’s a connecton string)


#4

What dsn parameter is used in the call ODBC::connect(dsn, username,
password)?

Tom


#5

Tom W. wrote:

What dsn parameter is used in the call ODBC::connect(dsn, username,
password)?

Tom

as DSN for example
“Driver={SQL
Server};Server=myhostname;Database=mydatabase;Uid=foo;Pwd=bar;”

(makes no difference if user credentials are passed directly or within
the connection string or if i write DBI:ODBC:Driver={SQL Server})

and the connection string is correct since i can successfully connect
with it using DBI ->

db=DBI.connect(“DBI:ODBC:Driver={SQL
Server};Server=myhostname;Database=mydatabase
;Uid=foo;Pwd=bar;”)
=> #<DBI::DatabaseHandle:0x47242ec @trace_mode=2,
@handle=#<DBI::DBD::ODBC::Data
base:0x4723900 @attr={}, @handle=#ODBC::Database:0x4723fcc>,
@trace_output=#>

i guess i’ll better drop odbc totally and just support the default
connection adapters and system dsn. :frowning:


#6

i just hacked in the relevant code logic from odbc.rb into the
odbc_adapter.rb and now it seems to work :smiley:

    driver_attrs = dsn.split(';')
    if driver_attrs.length.eql?(1) # try system dsn
      conn = ODBC::connect(dsn, username, password)
    else # try connection string
      driver = ::ODBC::Driver.new
      driver.name = 'DSN-less'
      driver_attrs.each do |param|
        attr = param.split('=')
        driver.attrs[attr[0]] = attr[1] if attr.length.eql?(2)
      end
      conn = ::ODBC::Database.new.drvconnect(driver)
    end
    conn.autocommit = true

i need to test this a bit and then i’ll ask the maintainer to include
this into the next release