multiple commands in database

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

Here is my code I am guessing there is a better way to do multiple commands is the database. This code fails at the first DBI command. and retuns the error
ct_cmd_alloc failed at ./multi_copy_segmap.pl line 127. no statement executing at ./multi_copy_segmap.pl line 142. ct_cmd_alloc failed at /nms/site/perl-5.6.0/lib/site_perl/5.6.0/sun4-solaris/DBD/Sybase.pm line 121.

sub change_dboption_true(){

my ( $dbh, $DB ) = @_;
my $option = "single user";
my $switch = "true";
my $sql;
   
    $sql = "use master";
    $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement [ $s
+ql ] :: $DBI::errstr"); FAILS HERE

    $sql = "sp_dboption $DB, \"$option\", $switch";
    $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement [ $s
+ql ] :: $DBI::errstr");

    $sql = "use $DB";
    $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement [ $s
+ql ] :: $DBI::errstr");

    $sql = "checkpoint";
    $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement [ $s
+ql ] :: $DBI::errstr");

    $$dbh->disconnect;

}#end change_dboption_true
[download]

Comment on multiple commands in database Download Code

Replies are listed 'Best First'.
Re: multiple commands in database by thezip (Vicar) on Jan 16, 2003 at 21:10 UTC
First, wrap the SQL statements within an eval. Second, you'll want to enable transactions for multiple queries by setting autocommit to 0. After the queries have executed, then you'll need to do a manual commit. Finally, check to see if the error message $@ has been set. If there is an error message, issue a rollback. I have embedded the necessary statements in your code. BTW, this code is untested, so your mileage may vary... sub change_dboption_true(){ my ( $dbh, $DB ) = @_; my $option = "single user"; my $switch = "true"; my $sql; ==> eval { ==> $dbh->{AutoCommit} = 0; # enable transactions $sql = "use master"; $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement +[ $sql ] :: $DBI::errstr"); FAILS HERE $sql = "sp_dboption $DB, \"$option\", $switch"; $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement +[ $sql ] :: $DBI::errstr"); $sql = "use $DB"; $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement +[ $sql ] :: $DBI::errstr"); $sql = "checkpoint"; $$dbh->do($sql) or $app->error($FATAL,"Can't do SQL statement +[ $sql ] :: $DBI::errstr"); ==> $dbh->commit; # manually commit the changes ==> } ==> if ($@) { ==> # handle the error ... insert other code here ==> $dbh->rollback; ==> } $$dbh->disconnect; }#end change_dboption_true [download] Where do you want them* to go today?*	[reply] [d/l]
Re: Re: multiple commands in database by mpeppler (Vicar) on Jan 16, 2003 at 22:41 UTC
While advocating the use of transactions for multiple statements is a good idea in general, I would advise against it in this case. This script executes a number of system procedures (sp_dboption, checkpoint, etc) which are specific to Sybase (and maybe MS-SQL), and which will probably not work in a transaction - these don't affect user tables anyway. Michael	[reply]
Re: multiple commands in database by mpeppler (Vicar) on Jan 16, 2003 at 22:38 UTC
What is line 127 of your script? The failed ct_cmd_alloc() (an internal error in DBD::Sybase) is the root cause of the problem, I think. Michael	[reply]
Re: multiple commands in database by poj (Abbot) on Jan 16, 2003 at 22:29 UTC
Is the $$dbh correct ? or should it be $dbh poj	[reply]