Hi Meir,
Remember that the process of changing the ? placeholders to actual variables
is called "binding".. and that can go wrong if for example you have 4 ?
placeholders
and provide 3 values or you have 0 ? and provide 3 values.
Because of your missing a backquote you effectively got an SQL statement
with 0 bind variables. The first time perl::DBI was able to figure that out
was the mismatch of what it thought were 0 ? placeholders with 3 you
provided.
Look into some better text editors.. the best ones will colour-highlight
your strings
and it'll be easier to find when you make an omission like yours. I've never
liked the `test`.`params` strict way of MySQL table naming.. you already
know you're using the `test` database so let's just call the table 'params'.
Also the qq() is a bit of overkill, let's just write is as a single perl
string,
and I'll use normal single quotes around the field names and table name
params.
my $sql=" REPLACE INTO 'params'
('ID','AsOf_date','Value') VALUES
(?,?,?)
";
You will need more complex qq() operators sometimes, but for your task
I find the simpler string much preferable.
Some MySQL utilities like mysqldump will write table names as
`test`.`params`
but that's to cater for silly tablenames with spaces in them or unicode or
stuff
and you know that's not the case with your data, so go with the simpler
form.
Hope this helps,
Stuart.
On Sun, Jul 27, 2014 at 7:44 PM, Meir Guttman <[email protected]> wrote:
> 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
>
>
>
