DBD::mysql 3.0006 - More bind problems 2006-07-20 - By Matthew Braid
Back
Hi all,
I had a problem with bind parameters last year (see http://lists.mysql.com/perl
/3588) and had to revert to 2.9008 to keep things working.
We had another upgrade cycle and tried to go to 3.0006. The original problem
seems to have been fixed, but we ran in to a new similar problem that has once
again made us downgrade to 2.9008 just to keep things running.
Basically, if a query has a bind parameter and a string with '? (single-quote,
question-mark) in it in a particular order, DBD::mysql seems to think the '?
actually means single-quote, bind-parameter. This of course breaks the query.
To show this, make a table with the specs:
CREATE TABLE test (
test1 varchar(128) not null,
test2 varchar(128) not null,
test3 varchar(128) not null);
And then run (filling in necessary login details):
use DBI;
use DBI::mysql;
my $prob = "'?"; # Problem string
my $ok = "Something mundane"; # OK string
my $qmark = "Something else mundane"; # Anoter mundane string
$| = 1;
print "DBI VERSION: ", $DBI::VERSION, "\n";
print "DBD::mysql VERSION: ", $DBD::mysql::VERSION, "\n";
my $dsn = "DBI:mysql:database=DB;host=HOST"; # FILL ME IN!
my $dbh = DBI->connect($dsn, UNAME, PASSWORD, # FILL ME IN!
{RaiseError => 0,
PrintError => 1});
for my $order ([$ok, $prob, $qmark], [$ok, $qmark, $prob],
[$qmark, $prob, $ok], [$qmark, $ok, $prob],
[$prob, $ok, $qmark], [$prob, $qmark, $ok]) {
my ($ok, $qmark, $prob) = @$order;
my $query = ("INSERT INTO test VALUES (" . $dbh->quote($ok) . ", ?, " .
$dbh->quote($prob) . ")");
print "================\nQUERY IS $query\nERROR IS ";
my $sth = $dbh->prepare($query);
$sth->execute($qmark);
print "\n================\n\n";
}
__END__
Using DBD::mysql 2.9008 gives the correct output:
DBI VERSION: 1.51
DBD::mysql VERSION: 2.9008
================
QUERY IS INSERT INTO test VALUES ('Something mundane', ?, 'Something else
mundane')
ERROR IS
================
================
QUERY IS INSERT INTO test VALUES ('Something mundane', ?, '\'?')
ERROR IS
================
================
QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, 'Something
mundane')
ERROR IS
================
================
QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, '\'?')
ERROR IS
================
================
QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something else mundane')
ERROR IS
================
================
QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something mundane')
ERROR IS
But DBD::mysql 3.0006 does this:
DBI VERSION: 1.51
DBD::mysql VERSION: 3.0006
================
QUERY IS INSERT INTO test VALUES ('Something mundane', ?, 'Something else
mundane')
ERROR IS
================
================
QUERY IS INSERT INTO test VALUES ('Something mundane', ?, '\'?')
ERROR IS DBD::mysql::st execute failed: called with 1 bind variables when 2 are
needed at test.pl line 27.
================
================
QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, 'Something
mundane')
ERROR IS
================
================
QUERY IS INSERT INTO test VALUES ('Something else mundane', ?, '\'?')
ERROR IS DBD::mysql::st execute failed: called with 1 bind variables when 2 are
needed at test.pl line 27.
================
================
QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something else mundane')
ERROR IS
================
================
QUERY IS INSERT INTO test VALUES ('\'?', ?, 'Something mundane')
ERROR IS
================
It seems string processing becomes broken once a real bind placeholder has been
found.
Hopefully this will be fixed soon. Especially since the latest actually-working
version I can find is over a year old.
MB
--
MySQL Perl Mailing List
For list archives: http://lists.mysql.com/perl
To unsubscribe: http://lists.mysql.com/perl?unsub=mysql@(protected)
|
|
