Dear DBI folks,
The other day I had a trivial typo in my DBI SQL query that gave me an
outlandish error message and grief. So outlandish that I spent two hours
looking for the culprit.
All it was is a missing closing back-tic in the db name part of the
`database_name`.`table_name` SQL clause, see the code.
Here is the test program:
<code>
use strict;
use warnings;
use utf8;
use DBI;
my %conn_attrs = (
RaiseError => 0,
PrintError => 0,
AutoCommit => 1,
mysql_enable_utf8 => 1,
);
my $dbh = DBI->connect (
'DBI:mysql:test:localhost',
'my_account',
'secret',
\%conn_attrs
);
my $sql = qq(
REPLACE INTO `test.`params`
-- missing ^
-- back-tick |
-- here ----------+
(`ID`,`AsOf_date`,`Value`) VALUES
(?,?,?)
);
my $sth = $dbh->prepare($sql) // die "'prepare' error:\n$DBI::errstr";
my $affected = $sth->execute('0123', '2014-06-24', 1000) // die "'execute'
error:\n$DBI::errstr";
$affected += 0;
print "$affected row was inserted";
__END__
</code>
This resulted with the following error message:
'execute' error:
called with 3 bind variables when 0 are needed at DBI_error_test.pl line 31.
Wherefrom in Scott's name did you take the idea that "... 0 (bind variables)
are needed...???"
BTW, executing the very same SQL in MySQL WorkBench resulted with a straight
forward "Syntax error ..."
Well, IMHO, MySQL does not, repeat does not, merit any reward for clear and
meaningful error messages. But this DBI/DBD one might be a winner. Is that a
bug or is there a good reason for that?
And finally Versions:
========
OS: Win 7 (Fully updated)
Strawberry Perl 5.18.2
MySQL 5.5.25a
perl module installed latest
---------- --------- -------
DBI 1.6300 1.6310
DBD::ODBC 1.4700 1.5000
DBD::mysql 4.0250 4.0270
Regards,
MeirG
