my $sql = qq| create table colors (
               color varchar(60) default NULL
            ) type=MyISAM;
      |;
my $sth = $dbh->prepare($sql) or die "error." . $dbh->errstr;
$sth->execute() or die "error." . $dbh->errstr;
[download]

You can read all about it at the online mysql documentation: MySQL Table Types.

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)

As someone who does a lot of SQL from Perl (with Sybase, not MySQL, but does it matter?), may I suggest a little stylistic sugar. I write all of my SQL as HERE docs, rather than attempting to quote them with any of the myriad ways Perl has.

$sql = <<"__SQL__";  # treats it as if double quoted
CREATE TABLE colors (
   color varchar(60) default NULL
)
TYPE=MyISAM;
__SQL__
[download]

After awhile, your eyes will get used to picking out the HERE doc sections (since they are not indented with the Perl code).

This has nothing to do with solving your problem, exactly, but as with keeping consistent about indentation and other such seemingly mindless window dressings, the structure it adds help keep the Perl and the SQL visually segregated, hopefully so syntax errors will stand out.

Also, why are you calling prepare and execute if you have no need for placeholders? Just use $dbh->do($sql).

BTW, die makes print unnecessary, you don't need (and shouldn't use) both together.

dmm

Why type all that extra here-doc stuff?

$sql = '
CREATE TABLE colors (
   color varchar(60) default NULL
)
TYPE=MyISAM
';
[download]

And this looks even better if you don't need to store the SQL for later:

$dbh->do('
   CREATE TABLE colors (
      color varchar(60) default NULL
   )
   TYPE=MyISAM
');
[download]

Here docs are nice, but overkill in this situation since the database could care less how it 'appears'. Just so long as it is valid syntax.

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)

I think that the here doc is a good solution in this case. If not, then I can think of absolutely no situation where it would be a good solution. You avoid quoting issues (both ' and " are valid quotes in sql, so why restict yourself to just one because you chose to open your string with one or the other?) and save yourself a headache when trying to debug.

thor

HERE docs neatly avoid any quoting issues. And XEmacs colors 'em different so they REALLY stand out :)

dmm

colorvarchar(60)
[download]

color varchar(60)
[download]

If possible, try the same thing off the MySQL command-line client and see what occurs. Failing that, start looking for daft typos ;-)

A bit of advice for you. I have been told this a couple of times myself at this site.;)

Checking $DBI::errstr is best done when instantiating a DBI::db object, since you will not have a reference to the object if the connect fails. After the instantiation, say to the var $dbh, you should check the err() method or the more useful errstr() method: or die $dbh->errstr(). But....

An even lazier solution is to force errors to raise exceptions via the RaiseError attribute. You do so by passing a hash ref to the constructor like so:

my $dbh = DB->connect(
   'DBI:vendor:database:host','user','pass',
   { RaiseError => 1},
);
[download]

Now you don't have to 'or die' after every query (method call).

Setting 'PrintError' to false is useful in conjunction with 'RaiseError' set to true when you use eval to catch exceptions, rather than die on them. Doing so supresses the 'error messages' that are generated when DBI calls die.

just tryin to be helpful ;)

jeffa

L-LL-L--L-LL-L--L-LL-L--
-R--R-RR-R--R-RR-R--R-RR
F--F--F--F--F--F--F--F--
(the triplet paradiddle)

Hello Anonymous Monk,

I would first substitute warn where you have die.
Use $DBI::errstr instead of your own print statment, i.e:
$sth = $dbh->prepare($sql) or warn "$DBI::errstr.\n";

You don't need to specify the table TYPE if you are using MyISAM.
This is the default in MySQL (since 3.23).

Check out MySQL by Paul DuBois, New Riders. The book addresses
MySQL and using the Perl DBI module with MySQL.

-Lord Avatar "A simple truth is but a complicated lie..." -Nietzche