SQL with DBI

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

Replies are listed 'Best First'.
Re: SQL with DBI by neniro (Priest) on Jun 26, 2005 at 19:05 UTC
`my @tables_to_drop = qw(Grab_Addr2 Grab_MCC1 Grab_MCC2); foreach my $table (@tables_to_drop) { $dbh->do("DROP TABLE $table;") or die $dbh->errstr(); }` [download] As CountZero described above it is easier to use a foreach-loop. You don't need a statement-handle to drop tables (see DBI for more details). And you can use the qw-quoting operator to quote the tables in your array. The code above is untested but should be okay.	[reply] [d/l]
Re^2: SQL with DBI by sk (Curate) on Jun 26, 2005 at 20:45 UTC
Extending on what neniro and CZ have said- you can also do something like this `my $tables = join (',',@tables_to_drop;)` and use it in the drop statement `$dbh->do("DROP TABLE $tables;") or die dbh->errstr;` I am assuming your SQL engine allows multiple table drops when they separated by commas. It works for me in MySQL Also note that using the $variable inside the query is sometimes considered to be dangerous esp if it is a user input. http://www.perl.com/pub/a/1999/10/DBI.html cheers SK	[reply] [d/l] [select]
Re^3: SQL with DBI by VSarkiss (Monsignor) on Jun 27, 2005 at 15:30 UTC
I am assuming your SQL engine allows multiple table drops when they separated by commas. It works for me in MySQL Slightly OT, but mysql is the exception rather than the rule. The SQL-92 standard only allows one table name in a `drop table` statement, and both Oracle and SQL Server follow the standard (i.e., only allow a single table). Do not rebuke them with harsh words ... but rather lead them gently - with URLs - so that they may learn wisdom.	[reply] [d/l]
Re^2: SQL with DBI by Anonymous Monk on Jun 26, 2005 at 19:15 UTC
Works great thanks guys....	[reply]
Re: SQL with DBI by CountZero (Bishop) on Jun 26, 2005 at 18:50 UTC
Unless you have activated `$dbv->{RaiseError} = 1;` you should always check the return value of the DBI-methods to find out if anything went wrong and if so what error the DB-engine or DBI/DBD returned, by adding `or die $drop_table->errstr;` after the `execute`. Also, you do not need the `$drop_table->finish` method and rather than using a C-style `for` loop a `foreach` is considered more Perlish. CountZero "If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law	[reply] [d/l] [select]
Re: SQL with DBI by Anonymous Monk on Jun 26, 2005 at 18:27 UTC
sorry heres the code, it shouldnt have NOWAIT in there. `@tables_2_drop = ("Grab_Addr2", "Grab_MCC1", "Grab_MCC2"); for ($i = 0; $i < @tables_2_drop; $i++) { # # Query to Find cal_dim_i for Sundays date $drop = "Drop table $tables_2_drop[$i]"; print "Finding Cal_dim_i for Current Date.\n"; ### Prepare a SQL statement for execution $drop_table = $dbv->prepare( "$drop"); ### Execute the statement in the database $drop_table->execute; $drop_table->finish; }` [download] janitored by ybiC: Close unbalanced <code> tag	[reply] [d/l]