Beefy Boxes and Bandwidth Generously Provided by pair Networks
P is for Practical
 
PerlMonks  

DBI "drop table"

by baxy77bax (Chaplain)
on Apr 12, 2008 at 18:48 UTC ( #680013=perlquestion: print w/ replies, xml ) Need Help??
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"; }
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
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).

      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.

        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.
Re: DBI "drop table"
by GrandFather (Cardinal) on Apr 12, 2008 at 20:23 UTC

    So you got bit by this did you?


    Perl is environmentally friendly - it saves trees
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.
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

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"); }

    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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://680013]
Approved by McDarren
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (10)
As of 2014-08-28 11:48 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (259 votes), past polls