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

In reply to DBI Binding Inconsistancy by Arguile

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.