DBI create table question

ruby-talk
1

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:indo’
from dbi.rb:17
from dbi.rb:3:in connect' from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:322:inconnect’
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…
~

2

[…]

You can only execute ONE SQL statement at a time via DBI.

Try:

dbh.do(“DROP TABLE IF EXISTS PASSPORT”)

sql = <<-EOF
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

dbh.do(sql)

– Dossy

···

On 2002.07.14, Jim Freeze jim@freeze.org wrote:

<< 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,
–
Dossy Shiobara mail: dossy@panoptic.com
Panoptic Computer Network web: http://www.panoptic.com/
“He realized the fastest way to change is to laugh at your own
folly – then you can let go and quickly move on.” (p. 70)

3

/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:indo’
from dbi.rb:17
from dbi.rb:3:in connect' from /usr/local/lib/ruby/site_ruby/1.6/dbi/dbi.rb:322:inconnect’ from dbi.rb:3

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

Neither. The semicolon at the end of your statement is the problem.
Remove it. You don’t need to terminate a SQL statement with a semicolon
when using DBI or some other interface into the database.

Kirk Haines

4

<< 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,
[…]

You can only execute ONE SQL statement at a time via DBI.

Thanks

···

On Sun, Jul 14, 2002 at 02:23:27AM +0900, Dossy wrote:

On 2002.07.14, Jim Freeze jim@freeze.org wrote:

Try:

dbh.do(“DROP TABLE IF EXISTS PASSPORT”)

sql = <<-EOF
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

dbh.do(sql)

– Dossy

–
Dossy Shiobara mail: dossy@panoptic.com
Panoptic Computer Network web: http://www.panoptic.com/
“He realized the fastest way to change is to laugh at your own
folly – then you can let go and quickly move on.” (p. 70)

–
Jim Freeze
If only I had something clever to say for my comment…
~