DBI "drop table"

baxy77bax has asked for the wisdom of the Perl Monks concerning the following question:

hi, it is a small but irritating problem. well my script is :


use strict;
use DBI;
use Data::Dumper;

my $driver = "mysql";
my $dns = "database=baxy";
my $username = "baxy";
my $password  = "";

my $dbh = DBI -> connect ("dbi:$driver:$dns", $username, $password, {A
+utoCommit => 1});
my $s = 'perl';
drop($s);

my $sth = $dbh->prepare ("create table perl(id INTEGER NOT NULL,
                                       name VARCHAR (255),
                                       title VARCHAR (255),
                                       ph VARCHAR (255)
                                     )") || die "$DBI::errstr";
                                     



$sth -> execute()|| die "$DBI::errstr";



sub drop{
my $r = $_[0];
my $sti = $dbh->prepare("drop table ?");
$sti -> execute($r) || die"$DBI::errstr";
}
[download]

and in it i'm trying to create a sub that would drop the table whenever i call it. so if i'd like to drop the table named "perl" i would write drop(perl). i tried to resolve the problem with placeholders but it is not working. any suggestions would be usefull.

Comment on DBI "drop table" Download Code

Replies are listed 'Best First'.
Re: DBI "drop table" by moritz (Cardinal) on Apr 12, 2008 at 19:01 UTC
Placeholders for table and column names are not supported (I don't know if that's a DBI or a MySQL restriction). So you can't use the `prepare($sql); execute($tablename)` scheme here. You can run the the table name through `$dbh->quote` method instead, and interpolate it into the SQL. (BTW instead of writing `or die "$DBI::errstr"` behind each operation you can just as well use the `RaiseError => 1` option in connect).	[reply] [d/l] [select]
Re^2: DBI "drop table" by ikegami (Patriarch) on Apr 12, 2008 at 20:32 UTC
Placeholders for table and column names are not supported (I don't know if that's a DBI or a MySQL restriction). I think most databases have that restriction. I think they need the table names to properly prepare. You can run the the table name through $dbh->quote method instead, and interpolate it into the SQL. No. `$dbh->quote` is for string literals only. For example, it's valid for `$dbh->quote("one\ntwo\0three")` to return `CONCAT('one', CHAR(12), 'two', CHAR(0), 'three')`. `$dbh->quote_identifier` is the appropriate function here.	[reply] [d/l] [select]
Re^3: DBI "drop table" by tinita (Parson) on Apr 13, 2008 at 11:18 UTC
I think most databases have that restriction. I think they need the table names to properly prepare. i recently stumbled across this while trying to make an application compatible with SQLite. in SQLite the table even has to exist - otherwise a prepare for a drop-statement will fail. so if you prepare all your statements at the start of your script, and you have create- and drop-statements also (e.g. for temporary tables) this won't work in SQLite. but i'd think this could be a bug because i don't know why a prepare for a drop should need the existing table.	[reply]
Re: DBI "drop table" by GrandFather (Saint) on Apr 12, 2008 at 20:23 UTC
So you got bit by this did you? Perl is environmentally friendly - it saves trees	[reply]
Temporary table instead? (was DBI "drop table") by doom (Deacon) on Apr 13, 2008 at 06:49 UTC
By the way: have you thought about using a temporary table? Then you wouldn't need an explicit drop, the table would go away when you close the connection. I believe you'd just insert the word `TEMPORARY`, i.e. `CREATE TEMPORARY TABLE perl`, and so on.	[reply] [d/l] [select]
Re: DBI "drop table" by DBAugie (Beadle) on Apr 13, 2008 at 13:09 UTC
Preface: My expertise is limited to Oracle. This is not necessarily applicable to other RDBMS. placeholders work in statements like SELECT, DELETE, or UPDATE. These types of statements are known as data modification language (DML). DML changes data inside an object. drop table is part of the data definition language. DDL actually changes the objects themselves. Traditionally, database engines do not tolerate variables in DDL. Oracle has an "execute immediate" statement to add this functionality. Perhaps mySql has a similar statement? r, Augie	[reply]
Re: DBI "drop table" by radiantmatrix (Parson) on Apr 15, 2008 at 18:25 UTC
As others have said, placeholders are for data -- table names are not data. Kudos, though, for thinking about placeholders and the implication that you care about safety in DBI-based scripts! What you want in this case is `quote_identifier` to properly quote the table name, then inline that into a `do`: `sub drop { my ($dbh, $table_name) = @_; my $quoted_name = $dbh->quote_identifier($table_name); $dbh->do("DROP TABLE $quoted_name"); }` [download] This would be called as `drop($dbh,'perl');` to drop a table named 'perl'. <–radiant.matrix–> Ramblings and references The Code that can be seen is not the true Code I haven't found a problem yet that can't be solved by a well-placed trebuchet	[reply] [d/l] [select]