I recently spent more time than I'd like to admit
trying to understand a bug in my DBI program, and
now that I understand what's going on, I'm writing
to suggest that the documentation show better how the type
conversion from perl scalars to SQL data types works in
the DBI - because it sure didn't do what I expected.
Here's what happened.
First I set up a table like this:
mysql> CREATE TABLE data (id VARCHAR(32), size INT);
Then, using a database handle $dbh
my $dbh = DBI->connect(...);
I add a row to the table, like this.
my $id = "S23";
my $size = 1.2;
# ... other code here ....
my $sth = $dbh->prepare("INSERT INTO data VALUES (?, ?)");
$sth->execute( $id, $size );
and everythings works fine. I now have a table that has
a row like this.
Table data:
id size
------+------
S23 | 1.2
------+------
The "gotcha" is when I put in the following
(fairly innocuous) line into the "... other code here ..."
block.
$if ($id == -1) { print " Ooops - illegal ID \n"; }
Now the "execute" fails with various error messages depending
on the specific DBD driver. The problem, apparently, is that
since $id has been tested for numeric equivalence, the parameter
binding in DBD treats $id as a number rather than a string
henceforth, and sends a SQL command something like
INSERT INTO data VALUES ( S23, 1.2 );
rather than the correct
INSERT INTO data VALUES ( 'S23', 1.2 );
Since perl (at least at the user level) makes no obvious distinction
between numeric and string data, the subtle distinction between
$id before and after the numeric test is not an easy one to find
with the debugger - yet it is exactly that distinction that DBD
uses in deciding what to do with $id.
Apparently the fact that the table has already been declared
to be VARCHAR(32) in the "id" column is not enough of a clue
that $id should be treated as character rather a string.
As I said, this behavior is not at all what I expected, and
not what I would have described as "doing the right thing".
In any case, I would suggest that this kind of conversion
be explained better in the appropriate DBI documentation.
Regards,
Jim Mahoney
Marlboro College
