Forum: Ruby on Rails dsn-less odbc connections possible?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
F12532c1224ff2ab2260e853258deddb?d=identicon&s=25 neongrau __ (neongrau)
on 2007-04-23 20:30
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?
2f46d76f0e5db4dc318b03be07ebaac4?d=identicon&s=25 Tom Ward (Guest)
on 2007-04-24 10:24
(Received via mailing list)
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
F12532c1224ff2ab2260e853258deddb?d=identicon&s=25 neongrau __ (neongrau)
on 2007-04-24 11:47
Tom Ward 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:in
`connect'
        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:in
`send'
        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:in
`connection='
        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:in
`connection'

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)
2f46d76f0e5db4dc318b03be07ebaac4?d=identicon&s=25 Tom Ward (Guest)
on 2007-04-24 11:58
(Received via mailing list)
What dsn parameter is used in the call ODBC::connect(dsn, username,
password)?

Tom
F12532c1224ff2ab2260e853258deddb?d=identicon&s=25 neongrau __ (neongrau)
on 2007-04-24 12:23
Tom Ward 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
O:0x2886ba0>>

i guess i'll better drop odbc totally and just support the default
connection adapters and system dsn. :(
F12532c1224ff2ab2260e853258deddb?d=identicon&s=25 neongrau __ (neongrau)
on 2007-04-24 13:09
i just hacked in the relevant code logic from odbc.rb into the
odbc_adapter.rb and now it seems to work :D


        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
This topic is locked and can not be replied to.