On Mon, Apr 02, 2001 at 09:53:10PM -0400, Jim Mahoney wrote:
>
> 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.
> my $id = "S23";
> my $size = 1.2;
> $sth->execute( $id, $size );
> 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"; }
That'll generate a warning in perl if warnings are enabled.
> 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 );
That's a bug in the driver. It should take more care.
Please be specific about which drivers and versions you tested,
plus which version of perl you were using.
> 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.
Which the driver docs should make clear.
> 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.
The driver can't know that (easily, for various reasons) so it doesn't help.
Tim.
