Mysql -- cannot connect (can in Perl)

John_P · 7 March 2011 02:50

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 `<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.

--
Posted via http://www.ruby-forum.com/.

Andrew_Mcelroy · 7 March 2011 03:25

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

···

On Sun, Mar 6, 2011 at 8:50 PM, John P. <jlpoole56@gmail.com> wrote:

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

--
Posted via http://www.ruby-forum.com/\.

John_P · 7 March 2011 04:11

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?

···

--
Posted via http://www.ruby-forum.com/.

John_P · 7 March 2011 15:04

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?

···

--
Posted via http://www.ruby-forum.com/.

Andrew_Mcelroy · 7 March 2011 04:32

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

···

On Sun, Mar 6, 2011 at 10:11 PM, John P. <jlpoole56@gmail.com> wrote:

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

--
Posted via http://www.ruby-forum.com/\.

John_P · 7 March 2011 05:47

Andrew Mcelroy wrote in post #985875:
...<snip>...

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.

···

--
Posted via http://www.ruby-forum.com/\.

Andrew_Mcelroy · 7 March 2011 06:36

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

···

On Sun, Mar 6, 2011 at 11:47 PM, John P. <jlpoole56@gmail.com> wrote:

Andrew Mcelroy wrote in post #985875:
...<snip>...
> 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.

--
Posted via http://www.ruby-forum.com/\.

Topic		Replies	Views
Installed mysql gem on windows but it isn't working ruby-talk	1	106	15 June 2007
DBI connection problem ruby-talk	5	80	18 April 2007
Ruby DBI Help Needed ruby-talk	0	72	26 November 2007
Initial Problem ruby-talk	0	66	21 January 2004
Ruby dbi installation issue ruby-talk	2	81	24 March 2004

Mysql -- cannot connect (can in Perl)

Related topics