[Chicago-talk] perl DBI ?

[Pete Krawczyk]

Subject: Re: [Chicago-talk] perl DBI ?
From: Richard Reina <richard at rushlogistics.com>
Date: Wed, 16 Mar 2005 09:15:58 -0800 (PST)

}However, do you happen to know why is it that it
}works when it's a stand alone program?  Would you
}happen to know of a work around for creating a table
}with a unique name from w/in another program. 

Well, I'll be damned:

$ cat bind.pl
#!/usr/bin/perl -Tw
use strict;
use DBI;
my ($db,$un,$pw) = ('test');

my $dbh = DBI->connect("DBI:mysql:database=$db",$un,$pw);
my $table_sth = $dbh->prepare('CREATE TABLE TMP_? (NAME CHAR(1))');
$table_sth->execute($$); $table_sth->finish;
my $insert_sth = $dbh->prepare("INSERT INTO TMP_$$ VALUES (?)");
$insert_sth->execute("A"); $insert_sth->finish;
my $select_sth = $dbh->prepare("SELECT NAME FROM TMP_?");
$select_sth->execute($$); my $vals = $select_sth->fetchall_arrayref;
$select_sth->finish;
print scalar(@$vals) . " rows in table TMP_$$\n";
$dbh->do("DROP TABLE TMP_$$");
$dbh->disconnect;
exit(0);

$ ./bind.pl 
1 rows in table TMP_10493

Oh, here's why:  (perldoc DBI)
    With most drivers, placeholders can't be used for any element of a
    statement that would prevent the database server from validating the
    statement and creating a query execution plan for it. For example:

      "SELECT name, age FROM ?"         # wrong (will probably fail)
      "SELECT name, ?   FROM people"    # wrong (but may not 'fail')

So apparently the DBD::mysql driver isn't playing by the same rules as 
most of the other DBD drivers, and is just inserting the binds by itself 
at execute time.

So given that, I couldn't explain why your code works in one situation and 
not the other.

I would also echo Andy's question about why you need to create a unique 
table in your code.

-Pete K
-- 
Pete Krawczyk
  perl at bsod dot net