Mysql -- cannot connect (can in Perl)

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:inmon_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:inmon_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.

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

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?

On Sun, Mar 6, 2011 at 10:11 PM, John P. [email protected] wrote:

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

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/Connector-C/mysql-connector-c-noinstall-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.

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?

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

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs