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::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
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::DBD::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 ’
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::DBD::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 ’
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.