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

I am trying to write something which opens this file called abstract.sql: 

  select city, date from weather where temp_lo < ?

[download]
and have it $sth->execute(@ARGV)

but I am able to get DBI to force an instructive error message when I forget to supply command-line arguments. Instead I get 

 Database handle destroyed without explicit disconnect, <A> line 1.

[download]

Here is my program: 

use DBI;
use strict;

open A, 'abstract.sql' or die 'cannot open abstract.sql';

my $sql = join '', <A>;

my $connect = 'dbi:Pg:dbname=mydb';

my $dbh = DBI->connect($connect,'postgres','take_a_guess_pal',
                       { RaiseError => 1,
                         PrintError => 1
                         }
                       );

my $sth = $dbh->prepare($sql);
$sth->execute(@ARGV);   ### SHOULD THROW ERROR WHEN NO ARGS, YET
                                                ### QUERY HAS PLACEHOL
+DER VALUES?? HOW TO FIX?

warn "ROWS ", $sth->rows;

use Data::Dumper;

open T, '>testexec.out' or die 'cannot create output file';
while (my $rec = $sth->fetchrow_hashref) {
    print Dumper($rec);
}

[download]

Replies are listed 'Best First'.
Re: DBI execute is not failing when placeholder values are not supplied
by runrig (Abbot) on Oct 24, 2001 at 20:02 UTC
    I already answered this on the DBI mailing list, but just so its here also... DBD::Pg is only checking for the correct number of arguments to execute() IF you supply arguments.

    Sounds like a bug to me, and shouldn't be too hard of a patch for someone to write. DBD::Oracle has similar XS code, but since Oracle natively supports placeholders, you end up with an Oracle error if you don't supply any args to a statement with placeholders. The DBD::Pg XS code needs to do the checking explicitly itself.

[reply]
Re: DBI execute is not failing when placeholder values are not supplied (boo)
by boo_radley (Parson) on Oct 24, 2001 at 20:22 UTC
    (disclaimer : I don't use postgre. Your code fails appropriately when translated to sybase)
    two things stand out from the DBD::PG changelog. 
    0.4   Jun 24, 1997
        - adapted to DBI-0.84:
          o new syntax for DBI->connect !
          o execute returns 0E0 -> n for     SELECT stmt
                                  -1 for non SELECT stmt
                                  -2 on error

    0.81 June 13, 1998
	- bug-fix from Rolf Grossmann <grossman@securitas.net>:
	  undefined parameters in an execute statement will be 
	  translated from 'undef' to 'NULL'. Also every parameter 
	  for bind_param() will be quoted by default (escape quote 
	  and backslash). Appropriate tests have been added to test.pl.
    The first seems to indicate that a failed exec will not die as may be expected with RaiseError, but rather return an error code, and the second may indicate a behavior where nonexistent params are automatically translated into null, although this might be a peculiar interpretation of this note.
    Perhaps contacting the module maintainer is in order.
[reply]
      Indeed DBD::Sybase fails on the execute with a somewhat appropriate error message, although the text of the message would seem to indicate that DBD::Sybase doesn't handle this situation as well as it could: 
      DBD::Sybase::st execute failed: Server message number=201 severity=16 
+state=2 line=0 procedure=*00001600000000_d028c5 text=Procedure *00001
+600000000_d028c5 expects parameter 
Invalid pointer param number 4, pointer value 0x0
, which was not supplied.
Server message number=201 severity=16 state=2 line=0 procedure=*000016
+00000000_d028c5 text=Procedure *00001600000000_d028c5 expects paramet
+er 
Invalid pointer param number 4, pointer value 0x0
, which was not supplied.

      [download]
      I should probably see if the parameter binding code could be made somewhat more robust...

      Michael

[reply]
[d/l]
      It may be interpretation, but I still think its a bug (missing != undefined). There's a difference between my @arr = (undef) and my @arr = (), because on the first, @arr == 1 and on the second, @arr == 0. And the DBD::Pg code DOES make an effort to check the number of parameters, so if it does find a mismatch, it reports the error rather than just binding NULL to the missing parameters. It shouldn't be any different when supplying no parameters.
[reply]
[d/l]
[select]
Re: DBI execute is not failing when placeholder values are not supplied
by Arguile (Hermit) on Oct 24, 2001 at 19:59 UTC
    Actually it shouldn't throw an error, here's why: 
    @ARGV == ()

    [download]

    Yep, that's the entire reason.

    If you don't pass anything on the command line @ARGV is an empty list. Databases allow NULL values and DBI represents a NULL as undef. When DBI evaluates @ARGV it autovivifies as many parameters as it needs. So what you're actually doing is binding NULL (undef) to each parameter.

    Update:

    Oops.. or according to runrig I could be entirely wrong.... Hehe (or maybe not entirely).

    Update 2:

    Well it appears DBD::Pg, DBD::mysql, and DBD::ODBC must all be off then. I just went through and tested them. In each case NULL was being bound to the parameters. But passing a list with even just undef in it throws an error. I guess that means autoviv isn't taking place? (Heh, I should have stuck with just admining DBMSs) 
    #!/usr/bin/perl -w
use strict;

use DBI;

my $dbh = DBI->connect('dbi:connect_sting', 'user', 'pass', {RaiseErro
+r => 1});

my $sth = $dbh->prepare('insert into foo (bar, qux) values (?,?)');

$sth->execute( () );

$sth->finish;
$dbh->disconnect;

    [download]
[reply]
[d/l]
[select]
      When DBI evaluates @ARGV it autovivifies as many parameters as it needs.

      Not true. (And since I'm posting this after your update, yes, you are wrong :-)
      When I do $sth->execute(@ARGV) with an empty @ARGV to a DBD::Oracle prepared statement with a placeholder, I get an Oracle error.

      Update:Well, I guess that's the way some of 'em do it. But that won't stop me from this:

      <rant>That's a silly-ass way of doing it!
undefined != missing - they're all wrong IMNSHO!
Whack'em all over the head w/a clue-by-four!</rant>

      [download]
      :-)
[reply]
[d/l]
[select]
Re: DBI execute is not failing when placeholder values are not supplied
by tommyw (Hermit) on Oct 24, 2001 at 20:08 UTC

    It behaves exactly as you want it to, for me. But I'm using Oracle, not postgres, and I get back:

    DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD ERR
+OR: OCIStmtExecute/Describe) at t.pl line 17, <A> line 2.
DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD ERR
+OR: OCIStmtExecute/Describe) at t.pl line 17, <A> line 2.

    [download]
    Note that it's an Oracle error. Maybe postgres just doesn't complain about such things?

    Unfortunately, my link to my home machine has just gone down, so I can't compare at the moment

    BTW, although you open testexec.out, you don't print to it. But that's irrelevant, given the earlier problem

[reply]
[d/l]
Re: DBI execute is not failing when placeholder values are not supplied
by Fletch (Bishop) on Oct 24, 2001 at 19:27 UTC

    You need to actually check the return value from the execute call to see if it failed. 

    ...
$sth->execute( @ARGV ) 
  or die "Error executing SQL: " . $sth->errstr . "\n";
...

    [download]

    Another alternative would be to check if there actually are arguments in @ARGV and die with a usage message if there aren't. 

    die "usage: $0 [temp]\n" unless @ARGV == 1;

    [download]
[reply]
[d/l]
[select]
      You need to actually check the return value from the execute call to see if it failed.

      Not true when the RaiseError attribute has been set.

      I like your idea of checking the contents of @ARGV tho'.

      --
      <http://www.dave.org.uk>

      "The first rule of Perl club is you don't talk about Perl club."

[reply]

Back to Seekers of Perl Wisdom