Using DBI and MySQL gems

oweinh · October 13, 2008, 11:43pm

Quick question:

I thought using rubygems that to use the dbi and mysql to access a MySQL
database from Ruby I would only have to do the following:

gem ‘mysql’
gem ‘dbi’

and then I could do whatever, eg:

dbh = DBI.connect(‘DBI:Mysql:money’, ‘mysqluser’, ‘pass3’)

HOWEVER, what I have to do is:

gem ‘mysql’
gem ‘dbi’
require ‘dbi’

and THEN the DBI.connect syntax works… does this indicate some
problem with my rubygems (or dbi) installation?

$ gem env
RubyGems Environment:

VERSION: 0.9.4 (0.9.4)
INSTALLATION DIRECTORY: c:/ruby/lib/ruby/gems/1.8
GEM PATH:
- c:/ruby/lib/ruby/gems/1.8
REMOTE SOURCES:
- http://gems.rubyforge.org

TIA for any help
~Owein

oweinh · October 15, 2008, 9:20am

Owein H. wrote:

Quick question:

I thought using rubygems that to use the dbi and mysql to access a MySQL
database from Ruby I would only have to do the following:

gem ‘mysql’
gem ‘dbi’

and then I could do whatever, eg:

dbh = DBI.connect(‘DBI:Mysql:money’, ‘mysqluser’, ‘pass3’)

HOWEVER, what I have to do is:

gem ‘mysql’
gem ‘dbi’
require ‘dbi’

Unless rubygems has (recently) changed, the ‘gem’ command doesn’t issue
any requires, it just modifies the load path to point at your gem.

I don’t know if this is recommended usage anymore, but provided you
haven’t installed dbi with the provided setup.rb, “require ‘dbi’” should
work without the gem statements.

-Erik

oweinh · October 15, 2008, 6:15pm

Erik H. wrote:

Unless rubygems has (recently) changed, the ‘gem’ command doesn’t issue
any requires, it just modifies the load path to point at your gem.

I don’t know if this is recommended usage anymore, but provided you
haven’t installed dbi with the provided setup.rb, “require ‘dbi’” should
work without the gem statements.

-Erik

Hmmmm… that definitely does not work. When I say “require ‘dbi’” it
returns true, but then dbh = DBI.connect(‘DBI:Mysql:money’, ‘mysqluser’,
‘pass3’) causes an exception about DBI not being defined… I’m not on
my ruby box right now, so I cannot give the exact notation… basically,
what I show above works, the two gem commands and then the require.

In the pickaxe, pg 221 (2nd ed) it says I need to say

require ‘rubygems’
require_gem ‘<gem_name>’

but rubygems appears to be required automatically when I start up, and
require_gem says that I should use ‘gem’ instead. So, if require
‘rubygems’ is not required (haha) and “require_gem” is just “gem”, I’m
back to my original question, should I not be able to just say

gem ‘dbi’

and, incidently, if I installed dbi using the gem installer, and you say
I should be able to “require ‘dbi’” without the gem statements, why does
that not work? Do I have to do something special to get things that were
loaded as gems to be recognized differently than what I do for regular
packages?

oweinh · October 15, 2008, 9:41pm

Erik H. wrote:

Is this 1.9? I’m not sure how rubygems would be automatically required
in 1.8.x. I guess it’s also possible that the require overrides have
been removed from rubygems. require_gem has been deprecated for some
time and probably has been removed by now.

Well, either way, I can test it at home (personally, I prefer the gem
statements to magic, moving-target require statements) but it doesn’t
seem like your problem has anything to do with DBI or DBI::DBD::Mysql,
but either rubygems or your misunderstanding of the version you’re
working with.

-Erik

No, I’m 1.8.6… some patch level. I have been doing a lot of testing in
irb and have not really tried it in a program… maybe irb does some
automatic gem stuff that does not get done when running a program?
Anyway, I’ll try it out on my ruby box when I’m done with work tonight
to see if that works differently than running stuff in irb.

oweinh · October 15, 2008, 7:05pm

Owein H. wrote:

I don’t know if this is recommended usage anymore, but provided you
haven’t installed dbi with the provided setup.rb, “require ‘dbi’” should
work without the gem statements.
Hmmmm… that definitely does not work. When I say “require ‘dbi’” it
returns true, but then dbh = DBI.connect(‘DBI:Mysql:money’, ‘mysqluser’,
‘pass3’) causes an exception about DBI not being defined… I’m not on
my ruby box right now, so I cannot give the exact notation… basically,
what I show above works, the two gem commands and then the require.

Is this 1.9? I’m not sure how rubygems would be automatically required
in 1.8.x. I guess it’s also possible that the require overrides have
been removed from rubygems. require_gem has been deprecated for some
time and probably has been removed by now.

Well, either way, I can test it at home (personally, I prefer the gem
statements to magic, moving-target require statements) but it doesn’t
seem like your problem has anything to do with DBI or DBI::DBD::Mysql,
but either rubygems or your misunderstanding of the version you’re
working with.

-Erik

oweinh · October 16, 2008, 3:05am

$ ruby -v
ruby 1.8.6 (2007-09-24 patchlevel 111) [i386-mswin32]

$ gem env
RubyGems Environment:

VERSION: 0.9.4 (0.9.4)
INSTALLATION DIRECTORY: c:/ruby/lib/ruby/gems/1.8
GEM PATH:
- c:/ruby/lib/ruby/gems/1.8
REMOTE SOURCES:
- http://gems.rubyforge.org

start of my ruby program:
p require(‘rubygems’)
p gem(‘mysql’)
p gem(‘dbi’)
dbh = DBI.connect(‘DBI:Mysql:money’, ‘mysqluser’, ‘xxxxxxx’)

returns:
false
true
true
C:/ruby/code/rdbm.rb:9: uninitialized constant DBI (NameError)

so, when I start ruby, rubygems is already included? Interesting.

adding "require(‘irb’) makes everything work. So my real problem is that
this is not how it says to do it in the ‘book’, and so I am concerned
that something might not be set up right.

oweinh · October 16, 2008, 6:22pm

On Wed, Oct 15, 2008 at 1:05 PM, Erik H. [email protected]
wrote:

Is this 1.9? I’m not sure how rubygems would be automatically required
in 1.8.x.

Via RUBY_OPT

oweinh · October 16, 2008, 7:18pm

Owein H. wrote:

so, when I start ruby, rubygems is already included? Interesting.

adding "require(‘irb’) makes everything work. So my real problem is that
this is not how it says to do it in the ‘book’, and so I am concerned
that something might not be set up right.

Nah, the book is just old. I’m fairly certain I never built an
autorequire into the DBI gems, which is at least part of the problem.
I’m not sure changing it would be a solution, however.

-Erik

oweinh · October 18, 2008, 2:43am

Erik H. wrote:

require “rubygems”
require “dbi”
What about the mysql package? Don’t you have to do something to load
that?

dbh = DBI.connect("DBI:Mysql:rubytest:localhost", "root")

Using the lines above, this is where is breaks. It cannot locate the
mysql driver. If I add “require ‘mysql’”, then I get:
DBI::InterfaceError: Could not load driver (uninitialized constant
Mysql::Driver)
from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:344:in load_driver' from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:227:in_get_full_driver’
from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:213:in `connect’

The only correct way I have to initialize these packages for use seems
to be using the “gem” command, and then I have to require “dbi”. Oh
well. At least it works.

If this breaks on your system, I’d be looking elsewhere (of ruby,
rubygems, and dbi… likely in the direction of your package
maintainers) to solve your problem.

I’m not sure what you mean by package maintainers…

oweinh · October 17, 2008, 10:38am

Owein H. wrote:

Quick question:

I thought using rubygems that to use the dbi and mysql to access a MySQL
database from Ruby I would only have to do the following:

gem ‘mysql’
gem ‘dbi’

and then I could do whatever, eg:

I just wanted to address this because… it’s been driving me nuts all
day.

Here’s a script that works great on my dev system, stock libs, gem env,
DBI and DBD::Mysql latest release gems:

~% gem env
[1:19]
RubyGems Environment:

VERSION: 0.9.4 (0.9.4)
INSTALLATION DIRECTORY: /var/lib/gems/1.8
GEM PATH:
- /var/lib/gems/1.8
REMOTE SOURCES:
- http://gems.rubyforge.org

require “rubygems”
require “dbi”

begin
# connect to the MySQL server
dbh = DBI.connect(“DBI:Mysql:rubytest:localhost”, “root”)
# get server version string and display it
row = dbh.select_one(“SELECT VERSION()”)
puts "Server version: " + row[0]
rescue DBI::DatabaseError => e
puts “An error occurred”
puts “Error code: #{e.err}”
puts “Error message: #{e.errstr}”
ensure
# disconnect from server
dbh.disconnect if dbh
end

If this breaks on your system, I’d be looking elsewhere (of ruby,
rubygems, and dbi… likely in the direction of your package
maintainers) to solve your problem.

-Erik

oweinh · January 10, 2009, 11:24am

Owein H. wrote:

Erik H. wrote:

require “rubygems”
require “dbi”
What about the mysql package? Don’t you have to do something to load
that?
dbh = DBI.connect("DBI:Mysql:rubytest:localhost", "root")
Using the lines above, this is where is breaks. It cannot locate the
mysql driver. If I add “require ‘mysql’”, then I get:
DBI::InterfaceError: Could not load driver (uninitialized constant
Mysql::Driver)
from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:344:in load_driver' from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:227:in _get_full_driver’
from c:/ruby/lib/ruby/site_ruby/1.8/dbi.rb:213:in `connect’

The only correct way I have to initialize these packages for use seems
to be using the “gem” command, and then I have to require “dbi”. Oh
well. At least it works.

If this breaks on your system, I’d be looking elsewhere (of ruby,
rubygems, and dbi… likely in the direction of your package
maintainers) to solve your problem.

I’m not sure what you mean by package maintainers…

Did this ever get solved? I have just built a new setup and wanted to
try ruby rails on it. It’s Centos 5.2

gem env

RubyGems Environment:

RUBYGEMS VERSION: 1.3.1
RUBY VERSION: 1.8.7 (2008-08-11 patchlevel 72) [i686-linux]
INSTALLATION DIRECTORY: /usr/local/lib/ruby/gems/1.8
RUBY EXECUTABLE: /usr/local/bin/ruby
EXECUTABLE DIRECTORY: /usr/local/bin
RUBYGEMS PLATFORMS:
- ruby
- x86-linux
GEM PATHS:
- /usr/local/lib/ruby/gems/1.8
- /home/grehom/.gem/ruby/1.8
GEM CONFIGURATION:
- :update_sources => true
- :verbose => true
- :benchmark => false
- :backtrace => false
- :bulk_threshold => 1000
REMOTE SOURCES:
- http://gems.rubyforge.org/

mysql is working fine

[grehom@localhost mysql]$ mysql --version
mysql Ver 14.12 Distrib 5.0.45, for redhat-linux-gnu (i686) using
readline 5.0

I get the same error running Erik’s script:

[grehom@localhost mysql]$ ruby erics_test.rb
/usr/local/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi.rb:294:in
load_driver': Unable to load driver 'Mysql' (underlying error: uninitialized constant DBI::DBD::Mysql) (DBI::InterfaceError) from /usr/local/lib/ruby/1.8/monitor.rb:242:in synchronize’
from
/usr/local/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi.rb:236:in
load_driver' from /usr/local/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi.rb:154:in _get_full_driver’
from
/usr/local/lib/ruby/gems/1.8/gems/dbi-0.4.1/lib/dbi.rb:139:in `connect’
from erics_test.rb:6
[grehom@localhost mysql]$

oweinh · January 10, 2009, 12:03pm

Owein H. wrote:

require ‘dbi’

Try upper case:
require ‘DBI’

It works for me. Dunno why.

oweinh · January 10, 2009, 1:11pm

David B. wrote:

Owein H. wrote:

require ‘dbi’

Try upper case:
require ‘DBI’

It works for me. Dunno why.

Try adding a:

gem ‘dbd-mysql’

after the require ‘dbi’.

Basically, you have to have both packages loaded so DBI can require
them.

If you’ve done a setup.rb installation, you shouldn’t have any issues
here.

oweinh · July 30, 2009, 4:13pm

Erik H. wrote:

David B. wrote:

Owein H. wrote:

require ‘dbi’

Try upper case:
require ‘DBI’

It works for me. Dunno why.

Try adding a:

gem ‘dbd-mysql’

after the require ‘dbi’.

Basically, you have to have both packages loaded so DBI can require
them.

If you’ve done a setup.rb installation, you shouldn’t have any issues
here.

Hey all…

I’m having a lot of problem trying to use DBI with Mysql in last 2 days
and I have no idea of how to do it, and I’ve already tried dozens of
different solutions.

Here is a brief history of my tests:

The code I’m testing is:

require “dbi” (or “DBI”)

class test_dbi

def selectAll

   begin
     # connect to the MySQL server
     dbh = DBI.connect("DBI:Mysql:CMASDB_V41_2:192.168.253.29",

“cmasAdmin”, “cmas_admin”)
# get server version string and display it
row = dbh.select_one(“SELECT VERSION()”)
puts "Server version: " + row[0]
rescue DBI::DatabaseError => e
puts “An error occurred”
puts “Error code: #{e.err}”
puts “Error message: #{e.errstr}”
ensure
# disconnect from server
dbh.disconnect if dbh
end

end

end

With this code I got the follwing error message:

Could not load driver (uninitialized constant Mysql::Driver)
RAILS_ROOT: C:/eclipse322/workspace/cmas41_rails

Application Trace | Framework Trace | Full Trace
C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:511:in load_driver' C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:401:in_get_full_driver’
C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:381:in connect' app/models/test_dbi.rb:11:inselectAll’

Then I tried:

require “rubygems”
require “dbi”
gem “dbd-mysql”

And I got:

Could not load driver (no such file to load – DBD/mysql/mysql)

RAILS_ROOT: C:/eclipse322/workspace/cmas41_rails

Application Trace | Framework Trace | Full Trace
C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:511:in load_driver' C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:401:in_get_full_driver’
C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:381:in connect' app/models/test_dbi.rb:13:inselectAll’

So I figured out that there is no mysql directory in
C:\ruby\lib\ruby\site_ruby\1.8\DBD
And I copied the file
C:\ruby\lib\ruby\gems\1.8\gems\dbd-mysql-0.4.3\lib\dbd\Mysql.rb to the
folder C:\ruby\lib\ruby\site_ruby\1.8\DBD\MYSQL.

And tried again and have a different error:

Could not load driver (uninitialized constant DBI::TypeUtil)
RAILS_ROOT: C:/eclipse322/workspace/cmas41_rails

Application Trace | Framework Trace | Full Trace
C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:511:in load_driver' C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:401:in_get_full_driver’
C:/ruby/lib/ruby/site_ruby/1.8/dbi/dbi.rb:381:in connect' app/models/fedag_dbi.rb:13:inselectAll’

I installed using gem install the follwing packages:

C:>gem install dbi
Successfully installed dbi-0.4.2
1 gem installed
Installing ri documentation for dbi-0.4.2…
Installing RDoc documentation for dbi-0.4.2…

C:>gem instal dbd-mysql
Successfully installed dbd-mysql-0.4.3
1 gem installed
Installing ri documentation for dbd-mysql-0.4.3…
Installing RDoc documentation for dbd-mysql-0.4.3…

C:>gem install dbd-pg
Successfully installed dbd-pg-0.3.8
1 gem installed
Installing ri documentation for dbd-pg-0.3.8…
Installing RDoc documentation for dbd-pg-0.3.8…

C:>
C:>
C:>
C:>gem install ruby-oci8
Successfully installed ruby-oci8-2.0.2-x86-mswin32-60
1 gem installed
Installing ri documentation for ruby-oci8-2.0.2-x86-mswin32-60…
Installing RDoc documentation for ruby-oci8-2.0.2-x86-mswin32-60…

My gem list is:

C:>gem list

*** LOCAL GEMS ***

actionmailer (2.1.2)
actionpack (2.1.2)
activerecord (2.1.2)
activeresource (2.1.2)
activesupport (2.1.2)
builder (2.1.2)
cgi_multipart_eof_fix (2.5.0)
ci_reporter (1.6.0)
columnize (0.3.0)
commonwatir (1.6.2)
dbd-mysql (0.4.3)
dbd-pg (0.3.8)
dbi (0.4.2)
deprecated (2.0.1)
firewatir (1.6.2)
fxri (0.3.6)
fxruby (1.6.6)
gem_plugin (0.2.3)
highline (1.5.1)
hoe (1.12.2)
hpricot (0.4)
linecache (0.43)
log4r (1.0.5)
mongrel (1.1.5)
mongrel_cluster (1.0.5)
mysql (2.7.3)
net-sftp (2.0.2)
net-ssh (2.0.11)
paginator (1.1.1)
pg (0.8.0)
rails (2.1.2)
rake (0.8.7, 0.7.2)
rcov (0.8.1.2.0)
ruby-debug (0.10.3)
ruby-debug-base (0.10.3)
ruby-oci8 (2.0.2)
ruby-postgres (0.7.1.2006.04.06)
rubyforge (1.0.3)
rubygems-update (1.3.3)
s4t-utils (1.0.4)
sources (0.0.1)
tzinfo (0.3.13)
user-choices (1.1.6)
watir (1.6.2)
win32-api (1.4.0)
win32-clipboard (0.4.1)
win32-dir (0.3.1)
win32-eventlog (0.4.3)
win32-file (0.5.3)
win32-file-stat (1.2.3)
win32-process (0.6.0, 0.5.1)
win32-sapi (0.1.3)
win32-sound (0.4.0)
windows-api (0.3.0)
windows-pr (1.0.5, 0.6.2)
xml-simple (1.0.12)

I’m working on this for the last 2 days and I have no idea of how to
solve this.

Any help will be apreciated!

–Helio