From: Jim Freeze <jim@...>
Date: 2002-07-14T02:00:26+09:00
Subject: DBI create table question

Hi:

I am looking at Ruby DBI today for the first time.
I have an sql statement that will create a table 
but when I use dbi, it gives me an error. Maybe
someone can clue me in.

<< This works >>

mysql < test.sql

--- begin test.sql ------
use test;
drop table if exists passport;

CREATE TABLE passport(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  email VARCHAR(64) NOT NULL,
  type ENUM('text', 'html') NOT NULL,
  passport_date DATE NOT NULL,
  last_activity DATE NOT NULL,
  new_profiles ENUM('n', 'y') NOT NULL,
  INDEX (email),
  PRIMARY KEY (id));
--- end test.sql ----------


<< This does NOT work >>
--- begin dbi.rb -----
require 'dbi'

DBI.connect('DBI:Mysql:test', 'name', 'pass') { | dbh |

  sql = <<-EOF
drop table if exists passport;
CREATE TABLE passport(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(64) NOT NULL,
type ENUM('text', 'html') NOT NULL,
passport_date DATE NOT NULL,
last_activity DATE NOT NULL,
INDEX (email),
PRIMARY KEY (id));
  EOF
  puts sql
  puts "creating table..."
  dbh.do(sql)
}
--- end dbi.rb ----

ruby dbi.rb returns

 ruby dbi.rb 
drop table if exists passport;
CREATE TABLE passport(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(64) NOT NULL,
type ENUM('text', 'html') NOT NULL,
passport_date DATE NOT NULL,
last_activity DATE NOT NULL,
INDEX (email),
PRIMARY KEY (id));
creating table...
/usr/local/lib/ruby/site_ruby/1.6/DBD/Mysql/Mysql.rb:222:in `do': You have an error in your SQL syntax near '; (DBI::DatabaseError)
CREATE TABLE passport(id INT UNSIGNED NOT NULL AUTO_INCREMENT,
email VARCHAR(6' at line 1
        from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:568:in `do'
        from dbi.rb:17
        from dbi.rb:3:in `connect'
        from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:322:in `connect'
        from dbi.rb:3


Is there some limitation with dbi or am I not correctly
formatting the do argument?

Thanks
-- 
Jim Freeze
If only I had something clever to say for my comment...
~