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.