DBI and MSAccess

ruby-talk
1

I want to access a MSAccess database with Ruby. I’m new to databases and
Ruby, so my question is quite basic.

When I try to connect to the database with DBI a error message shows up. I
have no idea if there is something wrong with my code or my system
configuration. Perhaps someone can provide a working code fragment?

Environment:
Windows ME, MSAccess not installed
Ruby 173-7
A mbd file

Code:
require ‘dbi’

dbq = "dbq=C:/Benutzer/Test.mdb"
dsn = “driver=Microsoft Access Driver(*.mdb);#{dbq}”

DBI.connect(“DBI:ODBC:#{dsn}”,"","")

Error:
C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/DBD/ODBC/ODBC.rb:71:in connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:inconnect’
from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:322:in `connect’

···

Rasmus

2

I want to access a MSAccess database with Ruby. I’m new to databases and
Ruby, so my question is quite basic.

When I try to connect to the database with DBI a error message shows up. I
have no idea if there is something wrong with my code or my system
configuration. Perhaps someone can provide a working code fragment?

Environment:
Windows ME, MSAccess not installed
Ruby 173-7
A mbd file

[snip]

I’ve never used DBI (though I plan to!), so I may
be wrong here. If so, someone will correct me.

My impression is that DBI only interfaces with
the existing database software, i.e., you would
have to have Access installed in order for this
to work.

Hal

···

----- Original Message -----
From: “Rasmus Debitsch” Debitsch@T-Online.de
Newsgroups: comp.lang.ruby
To: “ruby-talk ML” ruby-talk@ruby-lang.org
Sent: Wednesday, March 12, 2003 2:16 PM
Subject: DBI and MSAccess

3

Rasmus Debitsch wrote:

I want to access a MSAccess database with Ruby. I’m new to databases and
Ruby, so my question is quite basic.

When I try to connect to the database with DBI a error message shows up. I
have no idea if there is something wrong with my code or my system
configuration. Perhaps someone can provide a working code fragment?

Environment:
Windows ME, MSAccess not installed
Ruby 173-7
A mbd file

Code:
require ‘dbi’

dbq = “dbq=C:/Benutzer/Test.mdb”
dsn = “driver=Microsoft Access Driver(*.mdb);#{dbq}”

DBI.connect(“DBI:ODBC:#{dsn}”,“”,“”)

Error:
C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/DBD/ODBC/ODBC.rb:71:in connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:in connect’
from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:322:in `connect’

Rasmus

You can use the rubyinstaller @ rubyinstaller.sf.net (version 1.6.8) is
good. It contains the ODBC DBD/DBI drivers. Then all you need is to
install the MDAC package from microsoft (although you might already have

and ODBC driver). On Windows 98 go into the control panel and look for
ODBC datasources. There should be a tab that says ‘drivers’. Check for
a MS Access driver. You can then select the System tab, hit new, choose
MS Access, then select the MDB file you have on your drive. Next create
a test script to use the DBI:ODBC. Here’s some code I swiped myself:

require ‘dbi’

begin
# connect to the MySQL server
dbh = DBI.connect(“DBI:ODBC:lli”, “root”, “”)
# get server version string and display it
row = dbh.select_one(“SELECT VERSION()”)
puts "Server version: " + row[0]
dbh.select_all(“select * from types”) do |row|
puts row[0].to_s + " " + row[1]
end
rescue DBI::DatabaseError => e
puts “An error occurred”
print "Error code: ", e.err, “\n”
print "Error message: ", e.errstr, “\n”
ensure
# disconnect from server
dbh.disconnect if dbh
end

Replace ‘lli’ with whatever you named your ODBC datasource. Replace
‘root’ with a userid (with MS Access you probably won’t need one).

You can find more info on the ruby DBI @ http://ruby-dbi.sourceforge.net

Good skill to you!

Greg Brondo

4

I want to access a MSAccess database with Ruby. I’m new to databases and
Ruby, so my question is quite basic.

When I try to connect to the database with DBI a error message shows up. I
have no idea if there is something wrong with my code or my system
configuration. Perhaps someone can provide a working code fragment?

Environment:
Windows ME, MSAccess not installed

You do not have to have Access installed, just the MDAC that includes
ODBC/ADO, depending on how you connect.

I access Access (ha) through win32ole → ADO – so I can’t help you with the
DBI specifics.

Error:
C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/DBD/ODBC/ODBC.rb:71:in connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:in connect’
from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:322:in `connect’

Also, if you have the ability to post an English ODBC error msg, I might be
able to help more.

Chris
http://clabs.org

5

Thanks to all trying to help me.

Here is what I figured out:

  • The ODBC module of DBD can’t handle DSN-less connection.
  • I solved the problen by two methods:
    (1) Create a DSN and using the DSN in the connection
    (2) Changing the connect method in ODBC.rb to

def connect(dbname, user, auth, attr)
if dbname.split(‘;’).length > 1
# DSNless connection
drv = ::ODBC::Driver.new;
drv.name = ‘DSNless’
dbname.split(‘;’).each do |param|
pv = param.split(‘=’);
if pv.length < 2
next
end
drv.attrs[pv[0]] = pv[1]
end
db = ::ODBC::Database.new
handle = db.drvconnect(drv)
return Database.new(handle, attr)
else
# DSN given
handle = ::ODBC.connect(dbname, user, auth)
return Database.new(handle, attr)
end
rescue ODBCErr => err
raise DBI::DatabaseError.new(err.message)
end
end

and using the DSN-less connection

db=DBI.connect(‘DBI:ODBC:driver=Microsoft Access Driver (*.mdb);
dbq=path_of_my_database.mdb’)

in my program.

Rasmus

“Rasmus Debitsch” Debitsch@T-Online.de wrote in message news:b4o3eg$kpn$04$1@news.t-online.com…

···

I want to access a MSAccess database with Ruby. I’m new to databases and
Ruby, so my question is quite basic.

When I try to connect to the database with DBI a error message shows up. I
have no idea if there is something wrong with my code or my system
configuration. Perhaps someone can provide a working code fragment?

Environment:
Windows ME, MSAccess not installed
Ruby 173-7
A mbd file

Code:
require ‘dbi’

dbq = “dbq=C:/Benutzer/Test.mdb”
dsn = “driver=Microsoft Access Driver(*.mdb);#{dbq}”

DBI.connect(“DBI:ODBC:#{dsn}”,“”,“”)

Error:
C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/DBD/ODBC/ODBC.rb:71:in connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:in connect’
from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:322:in `connect’

Rasmus

6

Chris Morris wrote:

I want to access a MSAccess database with Ruby. I’m new to databases and
Ruby, so my question is quite basic.

When I try to connect to the database with DBI a error message shows up. I
have no idea if there is something wrong with my code or my system
configuration. Perhaps someone can provide a working code fragment?

Environment:
Windows ME, MSAccess not installed

You do not have to have Access installed, just the MDAC that includes
ODBC/ADO, depending on how you connect.

I access Access (ha) through win32ole → ADO – so I can’t help you with the
DBI specifics.

Error:
C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/DBD/ODBC/ODBC.rb:71:in connect': S1090 (0) [Microsoft][ODBC Driver Manager] Ungültige Zeichenfolgen- oder Pufferlänge. (DBI::DatabaseError) from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:496:in connect’
from C:/PROGRAMME/RUBY/lib/ruby/site_ruby/1.7/dbi/dbi.rb:322:in `connect’

Also, if you have the ability to post an English ODBC error msg, I might be
able to help more.

In English:

“Invalid character string or buffer length”

Seems like an syntax error in the DSN or something alike (perhaps the path name?).

Regards,

Michael

7

Thank you for the answer.

The version with system DSN works. Now I have a base for my experiments.

I found some hints in the net. Some people point out, that the string of a
DSN less connection must not longer than 32 characters for some odbc
implemetation. Other problems may be the right escaping of quotes or
slashes.
I will try the perl or tcl version and go back to ruby than.

···

–
Rasmus

8

“Hal E. Fulton” hal9000@hypermetrics.com wrote in message
news:08e901c2e8d5$ca49afa0$0300a8c0@austin.rr.com…

From: “Rasmus Debitsch” Debitsch@T-Online.de
Newsgroups: comp.lang.ruby
To: “ruby-talk ML” ruby-talk@ruby-lang.org
Sent: Wednesday, March 12, 2003 2:16 PM
Subject: DBI and MSAccess

I want to access a MSAccess database with Ruby. I’m new to databases and
Ruby, so my question is quite basic.

When I try to connect to the database with DBI a error message shows up.
I
have no idea if there is something wrong with my code or my system
configuration. Perhaps someone can provide a working code fragment?

Environment:
Windows ME, MSAccess not installed
Ruby 173-7
A mbd file

[snip]

I’ve never used DBI (though I plan to!), so I may
be wrong here. If so, someone will correct me.

My impression is that DBI only interfaces with
the existing database software, i.e., you would
have to have Access installed in order for this
to work.

Hal

No, Access does not need to be installed. You can access a *.mdb database
through ODBC directly. For example Word and Excell can access such
databases. VB or Delphi and other tools can, too.
ODBC is much more than just an API; if you use Microsoft’s MDAC you can even
do SQL joins of two databases that are created by very different engines
like FoxPro( .dbf ) and Access (.mdb). This means ODBC itself PARSES SQL!
(That’s why there are options for ‘direct’ SQL, i.e.: give it to the
database without parsing)
No wonder ODBC can be such a temperamental thing…

Peter

···

----- Original Message -----

9

A quick follow-up to this ealier question, which I am now facing:

Since Access is not required to be install in order to use DBI to
access the database, can I then:

(1) Move the Access .mdb file to a Linux box
(2) Use DBI to access the .mdb file on Linux

I am not very familiar with ODBC. It certainly appears to be required
to access MS databases on Windows, but how about on Linux? Does the
Ruby DBI module include an ODBC driver for Linux?

Thanks,

Arthur

debitsch@t-online.de (Rasmus) wrote in message news:1289054f.0303200823.31d51e5@posting.google.com…

···

Thanks to all trying to help me.

Here is what I figured out:

  • The ODBC module of DBD can’t handle DSN-less connection.
  • I solved the problen by two methods:
    (1) Create a DSN and using the DSN in the connection
    (2) Changing the connect method in ODBC.rb to

def connect(dbname, user, auth, attr)
if dbname.split(‘;’).length > 1
# DSNless connection
drv = ::ODBC::Driver.new;
drv.name = ‘DSNless’
dbname.split(‘;’).each do |param|
pv = param.split(‘=’);
if pv.length < 2
next
end
drv.attrs[pv[0]] = pv[1]
end
db = ::ODBC::Database.new
handle = db.drvconnect(drv)
return Database.new(handle, attr)
else
# DSN given
handle = ::ODBC.connect(dbname, user, auth)
return Database.new(handle, attr)
end
rescue ODBCErr => err
raise DBI::DatabaseError.new(err.message)
end
end

and using the DSN-less connection

db=DBI.connect(‘DBI:ODBC:driver=Microsoft Access Driver (*.mdb);
dbq=path_of_my_database.mdb’)

in my program.

Rasmus

“Rasmus Debitsch” Debitsch@T-Online.de wrote in message news:b4o3eg$kpn$04$1@news.t-online.com…

I want to access a MSAccess database with Ruby. I’m new to databases and
Ruby, so my question is quite basic.

10

“Michael Neumann” uu9r@stud.uni-karlsruhe.de wrote in message
news:20030313085950.GA4441@rz.uni-karlsruhe.de…

“Invalid character string or buffer length”

Seems like an syntax error in the DSN or something alike (perhaps the path
name?).

I just wonder what ODBC is doing in the connection string. I haven’t worked
with DBI, so this might be the standard way. But I would have thought you’d
connect to Access directly i.e. such that DBI uses ADO internally, or better
yet, DAO.

Mikkel

11

My impression is that DBI only interfaces with
the existing database software, i.e., you would
have to have Access installed in order for this
to work.

Hal

No, Access does not need to be installed. You can access a *.mdb database
through ODBC directly. For example Word and Excell can access such
databases. VB or Delphi and other tools can, too.
ODBC is much more than just an API; if you use Microsoft’s MDAC you can
even
do SQL joins of two databases that are created by very different engines
like FoxPro( .dbf ) and Access (.mdb). This means ODBC itself PARSES
SQL!
(That’s why there are options for ‘direct’ SQL, i.e.: give it to the
database without parsing)
No wonder ODBC can be such a temperamental thing…

That’s nice to know. Thanks…

Hal

···

----- Original Message -----
From: “Peter Frey” peterfrey@sympatico.ca
Newsgroups: comp.lang.ruby
To: “ruby-talk ML” ruby-talk@ruby-lang.org
Sent: Friday, March 14, 2003 2:42 PM
Subject: Re: DBI and MSAccess