Tim Bunce wrote:
> > 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.
Good point; thank you. I ran across the problem when running
a .cgi script, and so didn't see the warnings which presumably
went to the httpd log.
> > 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.
Well, I don't really know how the driver does things.
I do know that I can reproduce the same errors from the
underlying SQL engines by issuing incorrect queries like
the ones above.
> Please be specific about which drivers and versions you tested,
> plus which version of perl you were using.
I found similar errors with both MySQL (DBD:mysql) and PostgreSQL (DBD:Pg).
I have attached perl code, the resulting error messages, and details
of the various versions below.
> > 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.
That would be nice, yes.
> > 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.
Ah. Too bad. Certainly it was my assumption that since in perl
a scalar is (usually) a scalar, and that the type to convert to had
been specified, that the DBI/DBD interface would do the right thing.
This was exerbated by my failure to understand quickly the error that the
error message "Unknown column 'S002'" was really trying to indicate
an error in type conversion.
Thanks for your time.
Regards,
Jim Mahoney
Marlboro College
== cut here for code ==================
#!/usr/bin/perl -w
#################################################
#
# Testing string vs number binding under DBI and DBD::mysql, DBD:Pg
#
# Software versions:
# perl 5.6.0 built for 8386-linux.
# MySQL 3.23.35
# PostgreSQL 7.0.3
# DBI.pm
# $Id: DBI.pm,v 10.32 2000/06/14 20:04:03 timbo Exp $
# $VERSION = '1.14';
# DBD::mysql
# $Id: dbd.pm.in,v 1.6 1999/10/21 20:05:43 joe Exp $
# $VERSION = '2.0415';
# DBD::Pg
# $Id: Pg.pm,v 1.38 2000/07/10 17:47:51 mergl Exp $
# $VERSION = '0.95';
#
# - Jim Mahoney ([EMAIL PROTECTED]) 4/3/2001
#################################################
use strict;
my ($dbase, $user, $password, $table) = ("test", "test", "", "demo");
my $driver = "DBI:mysql:$dbase";
#my ($dbase, $user, $password, $table) = ("po", "po", "*******", "demo");
#my $driver = "DBI:Pg:dbname=$dbase";
my ($id, $number) = ("S002", 2);
# If the following numeric test isn't commented out,
# then the program dies with the given error messages because
# perl thinks that $id is a number. Without this test, everything works
fine.
#
# The errors are
#
# -- mysql driver --
## Argument "S002" isn't numeric in numeric eq (==) at ./test_mysql.pl
line41.
## DBD::mysql::st execute failed: Unknown column 'S002' in 'field list'
## at ./test_mysql.pl line 52.
## Issuing rollback() for database handle being DESTROY'd without explicit
## disconnect().
#
# -- Pg driver --
## Argument "S002" isn't numeric in numeric eq (==) at ./test.pl line 41.
## DBD::Pg::st execute failed: ERROR: Attribute 's002' not found
## Database handle destroyed without explicit disconnect.
#
# I ran across this problem in a .cgi script, and didn't see
# the perl warnings (which were sent to the httpd log) but only the
# "Unknown column 'S002'" and "Attribute 's002' not found" errors.
#
if ( $id == -1 ) {
die " Error - illegal ID \n";
}
# ------- Add a row to the table -----------------
# The table 'demo' was created with
# CREATE TABLE demo (id VARCHAR(32), number INT);
use DBI;
my $dbh = DBI->connect($driver, $user, $password, {RaiseError=>1});
my $sth = $dbh->prepare( "INSERT INTO $table VALUES (?,?)" );
$sth->execute($id, $number);
print " Success : row ($id , $number) added to table='$table' .\n";
# -------- Display the table --------------------
print " The table is now: \n";
my $ref = $dbh->selectall_arrayref("SELECT * FROM $table");
while (my $rowref = shift @$ref){
while (my $field = shift @$rowref) {
printf("%12s", $field);
}
print "\n";
}
$dbh->disconnect();
