mysql -- cannot connect (can in Perl)

[John P.]

My goal: to allow database connections to a remote database from Google
SketchUp using their Ruby scripting interpreter.

Using Ruby 1.8.6 since that is what Google's SketchUp incorporates.

D:\work\Ruby>gem install dbi
Successfully installed dbi-0.4.5
1 gem installed
Installing ri documentation for dbi-0.4.5...
Installing RDoc documentation for dbi-0.4.5...

D:\work\Ruby>gem install dbd-mysql
Successfully installed dbd-mysql-0.4.4
1 gem installed
Installing ri documentation for dbd-mysql-0.4.4...
Installing RDoc documentation for dbd-mysql-0.4.4...

D:\work\Ruby>ruby -v
ruby 1.8.6 (2010-02-04 patchlevel 398) [i386-mingw32]

D:\work\Ruby>

When I try to run:

#!/usr/bin/ruby -w
# simple.rb - simple MySQL script using Ruby DBI module
#
# for database drivers, see:
http://ruby-dbi.rubyforge.org/rdoc/index.html and "Classes"
# for Mysql, Pg, SQLite, ODBC
#
require "dbi"

begin
# connect to the MySQL server
dbh = DBI.connect("DBI:Mysql:database=test;host=plug", "rubyscript",
"test")
# get server version string and display it
row = dbh.select_one("SELECT VERSION()")
puts "Server version: " + row[0]

# Select all rows from sample_data
sth = dbh.prepare('select * from colors')
sth.execute

# Print out each row
while row=sth.fetch do
p row
end

# Close the statement handle when done
sth.finish

rescue DBI:atabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
dbh.disconnect if dbh
end

I get the following:

D:\work\Ruby>test_dbi_dbd-pg.rb
D:/work/Ruby/test_dbi_dbd-pg.rb:7:in `require': no such file to load
-- dbi (Loa
dError)
from D:/work/Ruby/test_dbi_dbd-pg.rb:7

D:\work\Ruby>

Here's what happens when I try under Ruby 1.9.2:

D:\work\Ruby>ruby test_dbi_dbd-mysql.rb
lib/rational.rb is deprecated
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi/handles.rb:12:
warning: o
ptional boolean argument is obsoleted
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:300:in
`block in load_
driver': Unable to load driver 'Mysql' (underlying error:
uninitialized constant
DBI:BD::Mysql) (DBI::InterfaceError)
from C:/Ruby/192/lib/ruby/1.9.1/monitor.rb:201:in
`mon_synchronize'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:242:in `l
oad_driver'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:160:in `_
get_full_driver'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:145:in `c
onnect'
from test_dbi_dbd-mysql.rb:11:in `<main>'

D:\work\Ruby>ruby test_dbi_dbd-mysql.rb
lib/rational.rb is deprecated
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi/handles.rb:12:
warning: o
ptional boolean argument is obsoleted
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:300:in
`block in load_
driver': Unable to load driver 'Mysql' (underlying error:
uninitialized constant
DBI:BD::Mysql) (DBI::InterfaceError)
from C:/Ruby/192/lib/ruby/1.9.1/monitor.rb:201:in
`mon_synchronize'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:242:in `l
oad_driver'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:160:in `_
get_full_driver'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:145:in `c
onnect'
from test_dbi_dbd-mysql.rb:11:in `<main>'

D:\work\Ruby>ruby -v
ruby 1.9.2p180 (2011-02-18) [i386-mingw32]

D:\work\Ruby>echo %PATH%
C:\Ruby\192\bin;C:\Perl12\site\bin;C:\Perl12\bin;C:\Program
Files\ImageMagick-6.
6.4-Q16;C:\Perl\site\bin;C:\Perl\bin;C:\Python26\Scripts;C:\Python26\;C:\WINDOWS
\system32;C:\WINDOWS;C:\WINDOWS\system32\WBEM;C:\Program
Files\TortoiseSVN\bin;C
:\Qt\2010.01\qt\bin;C:\Program Files\TortoiseHg\;C:\Program
Files\TortoiseGit\bi
n;C:\Documents and Settings\jlpoole\Application Data\Python\Scripts

D:\work\Ruby>


Note: Using Perl and installing the DBI package and the DBD-mysql
package I am able to successfully run this Perl script:

use strict;
use DBI;

my $database = "test";
my $hostname = "plug";
my $port = "3306";
my $user = "rubyscript";
my $password = "test";
my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";

my $dbh = DBI->connect($dsn, $user, $password) or die "$DBI::errstr";
#my $dbh = DBI->connect($dsn, $user) or die "$DBI::errstr";
my $sql = qq{ select id, name from colors };
my $sth = $dbh->prepare($sql);
$sth->execute;
while (my @row = $sth->fetchrow_array){
print "$row[0]\t$row[1]\n";
}
$sth->finish;
$dbh->disconnect;
print "completed\n";

with results of:

D:\work\perl>perl test_dbi_mysql.pl
1 red
2 blue
3 green
completed

D:\work\perl>

I've spent several hours trying to get Ruby to work with MySQL (spent
several more with PostgreSQL, but gave up as I finally determined there
is a built-in version requirement of Ruby 1.8.7 for the pg package).

Having read the frustration of another developer which basically asked
the question of "What's so cool about Ruby?" when it took him three days
trying to connect to a database, I thought I'd share what I've tried and
contrast it with Perl which seamlessly installed modules and works.
Something is missing in the documentation, and or how the gem packaging
system works to give a green light that the desired functionality is
working. Maybe I've overlooked something?

Any rate, if anyone has constructive suggestions on how to get Ruby to
connect to a Mysql database (on another server), please reply.

 

[andrew mcelroy]

[Note: parts of this message were removed to make it a legal post.]

I don't know about dbi, but mysql2 gem is what I use for working with mysql.

My goal: to allow database connections to a remote database from Google
SketchUp using their Ruby scripting interpreter.

Using Ruby 1.8.6 since that is what Google's SketchUp incorporates.

D:\work\Ruby>gem install dbi
Successfully installed dbi-0.4.5
1 gem installed
Installing ri documentation for dbi-0.4.5...
Installing RDoc documentation for dbi-0.4.5...

D:\work\Ruby>gem install dbd-mysql
Successfully installed dbd-mysql-0.4.4
1 gem installed
Installing ri documentation for dbd-mysql-0.4.4...
Installing RDoc documentation for dbd-mysql-0.4.4...

D:\work\Ruby>ruby -v
ruby 1.8.6 (2010-02-04 patchlevel 398) [i386-mingw32]

D:\work\Ruby>

When I try to run:

#!/usr/bin/ruby -w
# simple.rb - simple MySQL script using Ruby DBI module
#
# for database drivers, see:
http://ruby-dbi.rubyforge.org/rdoc/index.html and "Classes"
# for Mysql, Pg, SQLite, ODBC
#
require "dbi"

begin
# connect to the MySQL server
dbh = DBI.connect("DBI:Mysql:database=test;host=plug", "rubyscript",
"test")
# get server version string and display it
row = dbh.select_one("SELECT VERSION()")
puts "Server version: " + row[0]

# Select all rows from sample_data
sth = dbh.prepare('select * from colors')
sth.execute

# Print out each row
while row=sth.fetch do
p row
end

# Close the statement handle when done
sth.finish

rescue DBI:atabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
dbh.disconnect if dbh
end

I get the following:

D:\work\Ruby>test_dbi_dbd-pg.rb
D:/work/Ruby/test_dbi_dbd-pg.rb:7:in `require': no such file to load
-- dbi (Loa
dError)
from D:/work/Ruby/test_dbi_dbd-pg.rb:7

D:\work\Ruby>

Here's what happens when I try under Ruby 1.9.2:

D:\work\Ruby>ruby test_dbi_dbd-mysql.rb
lib/rational.rb is deprecated
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi/handles.rb:12:
warning: o
ptional boolean argument is obsoleted
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:300:in
`block in load_
driver': Unable to load driver 'Mysql' (underlying error:
uninitialized constant
DBI:BD::Mysql) (DBI::InterfaceError)
from C:/Ruby/192/lib/ruby/1.9.1/monitor.rb:201:in
`mon_synchronize'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:242:in `l
oad_driver'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:160:in `_
get_full_driver'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:145:in `c
onnect'
from test_dbi_dbd-mysql.rb:11:in `<main>'

D:\work\Ruby>ruby test_dbi_dbd-mysql.rb
lib/rational.rb is deprecated
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi/handles.rb:12:
warning: o
ptional boolean argument is obsoleted
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:300:in
`block in load_
driver': Unable to load driver 'Mysql' (underlying error:
uninitialized constant
DBI:BD::Mysql) (DBI::InterfaceError)
from C:/Ruby/192/lib/ruby/1.9.1/monitor.rb:201:in
`mon_synchronize'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:242:in `l
oad_driver'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:160:in `_
get_full_driver'
from
C:/Ruby/192/lib/ruby/gems/1.9.1/gems/dbi-0.4.5/lib/dbi.rb:145:in `c
onnect'
from test_dbi_dbd-mysql.rb:11:in `<main>'

D:\work\Ruby>ruby -v
ruby 1.9.2p180 (2011-02-18) [i386-mingw32]

D:\work\Ruby>echo %PATH%
C:\Ruby\192\bin;C:\Perl12\site\bin;C:\Perl12\bin;C:\Program
Files\ImageMagick-6.

6.4-Q16;C:\Perl\site\bin;C:\Perl\bin;C:\Python26\Scripts;C:\Python26\;C:\WINDOWS
\system32;C:\WINDOWS;C:\WINDOWS\system32\WBEM;C:\Program
Files\TortoiseSVN\bin;C
:\Qt\2010.01\qt\bin;C:\Program Files\TortoiseHg\;C:\Program
Files\TortoiseGit\bi
n;C:\Documents and Settings\jlpoole\Application Data\Python\Scripts

D:\work\Ruby>


Note: Using Perl and installing the DBI package and the DBD-mysql
package I am able to successfully run this Perl script:

use strict;
use DBI;

my $database = "test";
my $hostname = "plug";
my $port = "3306";
my $user = "rubyscript";
my $password = "test";
my $dsn = "DBI:mysql:database=$database;host=$hostname;port=$port";

my $dbh = DBI->connect($dsn, $user, $password) or die "$DBI::errstr";
#my $dbh = DBI->connect($dsn, $user) or die "$DBI::errstr";
my $sql = qq{ select id, name from colors };
my $sth = $dbh->prepare($sql);
$sth->execute;
while (my @row = $sth->fetchrow_array){
print "$row[0]\t$row[1]\n";
}
$sth->finish;
$dbh->disconnect;
print "completed\n";

with results of:

D:\work\perl>perl test_dbi_mysql.pl
1 red
2 blue
3 green
completed

D:\work\perl>

I've spent several hours trying to get Ruby to work with MySQL (spent
several more with PostgreSQL, but gave up as I finally determined there
is a built-in version requirement of Ruby 1.8.7 for the pg package).

Having read the frustration of another developer which basically asked
the question of "What's so cool about Ruby?" when it took him three days
trying to connect to a database, I thought I'd share what I've tried and
contrast it with Perl which seamlessly installed modules and works.
Something is missing in the documentation, and or how the gem packaging
system works to give a green light that the desired functionality is
working. Maybe I've overlooked something?

Any rate, if anyone has constructive suggestions on how to get Ruby to
connect to a Mysql database (on another server), please reply.

 

[John P.]

I believe that mysql2 requires that you have installed a full instance
of mysql on the machine making the connection.

Is there a solution that does not require the client to install a
complete database?

 

[andrew mcelroy]

[Note: parts of this message were removed to make it a legal post.]

I believe that mysql2 requires that you have installed a full instance
of mysql on the machine making the connection.

it does require libmysql, but other than that, I am not aware of it
requiring a full instance of mysql.

Andrew McElroy

 

[John P.]

Andrew Mcelroy wrote in post #985875:

it does require libmysql, but other than that, I am not aware of it
requiring a full instance of mysql.

Andrew McElroy

If libmysql is required, should the instructions and/or the gem
installation bring to the attention of the user this requirement?

I found libmysql at
http://dev.mysql.com/downloads/mirror.php?id=377978#mirrors

I selected San Jose:
http://dev.mysql.com/get/Downloads/...all-6.0.2-win32.zip/from/http://mysql.he.net/

and then unzipped the archive. The question I have is what do to with
this download. Are there any instructions on how to call
"mysql_config.exe", or should this staged unzipped archive be referenced
from ruby?

I feel like I'm shooting in the dark and apparently have missed a
critical howto or installation guide.

 

[andrew mcelroy]

[Note: parts of this message were removed to make it a legal post.]

I am rather shocked that you have having such difficulties.
going back to your original premise:
http://www.kitebird.com/articles/ruby-dbi.html#TOC_4

are you sure you have your credentials including port number correct?


You appear to be on windows: http://rubyinstaller.org/
use the try the 1.8 version of ruby again.

It really sounds like a bad environment setup.

Andrew McElroy

 

[John P.]

I think the Perl script that is run on the same Windows machine as the
Ruby script is proof that connections to the MySQL server (on "plug")
can be had and that all the variables, e.g. "hostname", "port", "user",
and "password" and working. For the hostname column in the "user" table
in Mysql associated with the account "rubyscript", I have the wildcard
"%", just in case a connection through Ruby has something different than
a connection from Perl.

Here's a punt. Could you share the directory path, relative to your
RUBY home, where any of the following files from the mysql "lib"
directory may exist:

libmysql.dll
libmysql.lib
mysqlclient.lib

Perhaps this can be finessed by simply placing those files somewhere
where Ruby might look for them?