Is there a particular reason to make your life harder than it has to be?
Anyway, DBI generally allows only one statement per do or prepare.
So you have to break it up into statements, for example with a SQL Parser | [reply]
[d/l]
[select] |
If the operations are related and need to be "atomic", then running them individually but as a transaction is a good way to go. Turn off autocommit ($dbh{AutoCommit} => 0, and then run each statement following them with a call to $dbh->commit;. You'll need to check $@ to see if something went wrong, and if so, perform a rollback $dbh->rollback to back out all the data that did go in the transaction.
On the other hand, you can also put multiple statements in a single scalar and have DBI execute that as well, e.g.
{
local $/;
open SQLFILE, "< file.sql" or die "Can't open file.sql: $!";
$stmts = <SQLFILE>;
close SQLFILE;
}
eval {
# Turn off autocommit, keep from dying on erros (but print them)
$dbh{AutoCommit} => 0;
$dbh{RaiseError} => 0;
$dbh{PrintError} => 1;
$dbh->do($stmts);
$dbh->commit;
};
if ($@) {
print "Error: $@\n $DBI::errstr\n";
$dbh->rollback;
}
Just make sure each statement ends in a ';' (semicolon).
---
echo S 1 [ Y V U | perl -ane 'print reverse map { $_ = chr(ord($_)-1) } @F;'
Warning: Any code posted by tuxz0r is untested, unless otherwise stated, and is used at your own risk.
| [reply]
[d/l] |
It might be worth taking advantage of the three-argument open, lexically scoped filehandles and the automatic closure of same when they go out of scope. So instead of your
{
local $/;
open SQLFILE, "< file.sql" or die "Can't open file.sql: $!";
$stmts = <SQLFILE>;
close SQLFILE;
}
you could do
my $stmts = do
{
local $/;
open my $sqlFH, q{<}, q{file.sql} or die qq{open: file.sql: $!\
+n};
<$sqlFH>;
};
I hope this is of interest. Cheers, JohnGG | [reply]
[d/l]
[select] |
it did not worked can you suggest any other way.
| [reply] |