$dbh->prepare not triggering die

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

A few days ago I started writing apps using Mason. For some reason the following code;

my $sql = "this is not sql"  ;
my $sth = $dbh->prepare($sql) or $m->abort(500) ;
[download]

does not trigger the abort, finding this a bit strange, I then ran the following under mod_perl

use DBI ;

my $r = Apache->request;
$r->content_type("text/html");
$r->send_http_header();

$dbh = DBI->connect('dbi:mysql:test:localhost', 'user', 'pass' );

my $sql = "blah blah blah"  ;
my $sth =  $dbh->prepare($sql) or die('sql prepare error');
$sth->execute()  ;
my ($name) = $sth->fetchrow_array;

$r->print( ".$name.\n") ;
[download]

In both cases the DBD::mysql::st error is written to the error log, but not the die error from my program. To try and prevent myself from going mad, I did the exact same thing using traditional cgi and still no die() error. Even if the script is executed from the command line it still doesn't die!!

Shouldn't prepare return a undefined reference on error, forcing the die to be executed?

If it helps, I'm running Debian Woody, with Perl 5.6.1 and DBI 1.31

Comment on $dbh->prepare not triggering die Select or Download Code

Replies are listed 'Best First'.
Re: $dbh->prepare not triggering die by Enlil (Parson) on Dec 02, 2002 at 09:06 UTC
I believe that the `prepare` is probably always returning true (from the documentation). You might want to move the `or die` statement from the `prepare` line to the `execute` line. The docs say: Drivers for engines without the concept of preparing a statement will typically just store the statement in the returned handle and process it when $sth->execute is called, You might want to read the documentation under Database Handle Methods for further info. -enlil	[reply] [d/l] [select]
Re: $dbh->prepare not triggering die by Abigail-II (Bishop) on Dec 02, 2002 at 10:05 UTC
What I usually do is: `my $dbh = DBI -> connect ("DBI:$driver:database=$db;host=$host", $user, $password, {RaiseError => 1, PrintError => 0, AutoCommit => 0, Taint => 1}) or die "Failed to connect: $DBI:errstr\n"; ... eval { my $sth = $dbh -> prepare ($cmd); $dbh -> execute (@variables); while (my @row = $dbh -> fetchrow_array) { ... process @row ... } $dbh -> commit; }; if ($@) { $dbh -> rollback; croak "Database query failed: $@"; }` [download] Abigail	[reply] [d/l]
Re: Re: $dbh->prepare not triggering die by UnderMine (Friar) on Dec 02, 2002 at 11:15 UTC
This methodology works well with larger database such as Oracle or SQL Server. However the normally distributed version of mysql does not have transactional support :( which makes things more difficult as rollback and commit are just no-op functions. mysqlmax and the commercial versions do have transactional support but not many people use them. Transactional support is a whole new ball game especially when you need multiple sub-procedures with an error in any of them causing a rollback. I tend to move the transaction start and transaction commit/rollback into a sub-procedures on their own to handle this. Hope it helps UnderMine	[reply]
Re: $dbh->prepare not triggering die by Abigail-II (Bishop) on Dec 02, 2002 at 13:24 UTC
Re: Re: $dbh->prepare not triggering die by UnderMine (Friar) on Dec 02, 2002 at 16:46 UTC
Re^2: $dbh->prepare not triggering die by diotalevi (Canon) on Dec 02, 2002 at 12:45 UTC
Re: $dbh->prepare not triggering die by dws (Chancellor) on Dec 02, 2002 at 19:18 UTC
Shouldn't prepare return a undefined reference on error, forcing the die to be executed? prepare() is a funny beast. For many databases, it does a "please ignore the man behind the curtain" trick, saving the query until execute() time, when placeholders are expanded by the driver before sending the complete query over the wire to the server. In these case, errors in the query aren't detected until you try to execute(). Why is this? It's so that we can have a generic, RDBMS-independent API. Some databases don't support reusable queries. Others (e.g., Postgress) are just now supporting them. But by coding as if your database supports prepared queries, you're setting yourself up to benefit from performance gains if and when support for them is added. But even if you're using a database that supports reusable queries, you want to check for errors at both prepare() and execute() time, since errors can happen at both.	[reply]
Re: $dbh->prepare not triggering die by dilbert (Novice) on Dec 03, 2002 at 10:08 UTC
Try checking for the value of $dbh->errstr after doing the prepare. If it is defined, something went wrong and you can safely die ;-) `my $sth = $dbh->prepare($sql); unless ($dbh->errstr){die "Something went wrong".$dbh->errstr;} ...` [download] This also works after an execute, by the way.	[reply] [d/l]


Don't ask to ask, just ask
	PerlMonks