From: Rob McMillin
Date: January 23 2000 5:58am
Subject: DBD::mysql: variable binding
List-Archive: http://lists.mysql.com/perl/547
Message-Id: <388A9880.72DEE893@pricegrabber.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Okay, so now I understand what's going on. Can somebody tell me why the
following crashes?  I've been taking this up with Sasha in private
e-mail, but I thought it would be useful to hear others' spin on this as
well...

----------------------------------------------------------------------
#!/usr/bin/perl

use DBD::mysql;

$dsn = "DBI:mysql:database=test;mysql_read_default_file=/etc/my.cnf";
$dbh = DBI->connect($dsn,"test_user",undef)
    || die "couldn't connect to database, bye\n";

$dbh->do("create table crash (some_column varchar(50))");

#
# Populate it with something
#
$insert = $dbh->prepare("insert into crash set some_column=?");

$insert->execute(1);
$insert->execute("(yo soy un perdidor)");

$dbh->do("drop table crash");
$dbh->disconnect;
----------------------------------------------------------------------

The error you get back is

-------
DBD::mysql::st execute failed: You have an error in your SQL syntax near
'soy un perdidor)' at line 1 at /home/rlm/src/perls/crash_test.pl line
17, <IN> chunk 6.
-------

Pretty clearly, what seems to be happening is that the first time the
query is run, the execute binds the numeric type to the placeholder.
However, they don't say in the DBI man page that the types for the
placeholder come from the variables passed through the execute. My
intuition is that it should care about the types of the columns rather
than the types of the incoming variables.  Am I way off base here?  And
even if I'm wrong about the documentation (PLEASE, somebody, show me
where this is if you think I'm totally out of it), I think this is a bug
considering the way Perl likes to convert back and forth between
numerics and string values.

At this point, it's obvious this isn't a MySQL problem (directly), so
I'll take this off to the mSQL-MySQL list.

--
     http://www.pricegrabber.com | The best deals, all the time.