While I was doing up some webforms for data entry (my most favouritist job ever), I encountered a perplexing problem.

First a bit of functional info, the form in question is a simple insert/update form. If you're adding a new record you don't pass the id (primary key) in the query string and you get a blank form; if you do pass an id the form is filled in from the database. At least that was the idea.

# flakey
my $sth = $dbh->prepare_cached(<<SQL);
    SELECT *
    FROM foo
    WHERE id = ?
SQL
$sth->bind_param(1, $q->param('id'), SQL_INTEGER);
$sth->execute;
[download]

So when there's no query parameter 'id' it's undefined/null so there should be an empty result set returned. Right? Wrong. Somehow my form kept filling in with the same information whenever I didn't specifiy an id. I checked typos, db records, and simplified my SQL down to a SELECT *.

After a while I concluded it somehow had to be in the SQL execution. So I tried the following statement and, lo and behold, it worked perfectly.

# functional
my $sth = $dbh->prepare_cached(<<SQL);
    SELECT *
    FROM foo
    WHERE id = ?
SQL
$sth->execute( $q->param('id') );
[download]

So it had something to do with how I was binding the value. And for some reason the 'flakey' code kept returning the fourth record. I was at a complete loss to explain it, until I remebered what SQL_INTEGER mapped to. SQL_INTEGER is an imported constant from DBI and numerically equals, you guessed it, the number 4.

My argument to bind_param() then was (1, ,4) which it interpretted as (1,4)! Not the behavior I'd expect from Perl. Manually specifying (1,undef,4) in the bind_param() properly returned an empty set. So what was going on?

My question's not really asking how to solve it: I've already altered my procedure a bit and am going along happily. My question is: Is my original method exposing a flaw in my fundamental understanding of Perl's undef/null/empty string/etc. or is this something I should mention to the maintainers of DBI?

Thanks,
         Arguile