Henri,
Firstly, bare in mind I've changed by 3.0003_1 to avoid the corruption bug I
posted earlier - I can supply a change for you but it is just currently a hack
to avoid the corruption.
I get:
> describe tbl_tst_xx;
Field:Type:Null:Key:Default:Extra:
cv1:varchar(255):YES::{NULL}::
ci2:int(11):YES::{NULL}::
ci3:smallint(6):YES::{NULL}::
DBD::mysql::st execute failed: Out of range value adjusted for column 'ci3' at
row 1 at z.pl line 15.
and I have:
sql_mode=traditional
If I omit the SQL_INTEGER off the bind param 3 call the execute succeeds. It
makes no difference to me whether the string contains / or not. With bind
param 3 changed I get:
xxxxxxxxxxxxxxxxxxxxxxxxa, 875770417, 0
and that is indeed what is in the database. If I remote the SQL_INTEGER off the
second bind param call I correctly get:
xxxxxxxxxxxxxxxxxxxxxxxxa, 1234567890, 0
So my guess it is more to do with setting the type to SQL_INTEGER because when
you do this dbd::mysql says they are integers and when you omit the SQL_INTEGER
dbd::mysql says they are strings (see a DBI trace).
Looking at dbdimp.c I cannot understand how this code ever worked. As I read
it, if you use TYPE => SQL_INTEGER, dbd::mysql sets the MYSQL_BIND
buffer_type to MYSQL_TYPE_LONG which is supposed to be a int C type but the
buffer points to a string. Are you saying using TYPE => SQL_INTEGER worked in
dbd::mysql at some time in the past?
Also, looking at DBD::ODBC, parameters are always bound as strings although the
ODBC driver might be asked to interpret them as integers as in the ValueType
and ParameterType arguments to SQLBindParameter.
If you omit the TYPE=>, it works for me.
BTW, I presume you missed the select from your example code.
Martin
--
Martin J. Evans
Easysoft Ltd, UK
http://www.easysoft.com
On 10-May-2006 Henri Asseily wrote:
> There's a bug in binding parameters in DBD::mysql 3.0003_1, when
> binding a string that has slashes in it.
> Below is how to reproduce it.
>
> Create a simple table:
>
> === TABLE ===
>
> CREATE TABLE tbl_tst_xx (
> cv1 varchar(255) NULL,
> ci2 int(11) NULL,
> ci3 smallint(6) NULL
> )
>
> ======
>
>
> Then write a perl script that uses bound parameters:
>
>
> === SCRIPT ===
>
> use strict;
> use warnings;
> use DBI qw(:sql_types);
> use Data::Dumper;
>
> my @dsn = ("dbi:mysql:database=XXX;host=localhost;port=3306;");
>
> my $sql;
> my $dbh;
> my $sth;
> my $results;
>
> $dbh = DBI->connect( @dsn, 'username', 'password', {
> PrintError => 1,
> AutoCommit => 0,
> RaiseError => 0,
> ChopBlanks => 1
> });
>
> $sql = 'insert into tbl_tst_xx (cv1, ci2, ci3) values (?,?,?)';
> $sth = $dbh->prepare( $sql );
> $sth->bind_param(1, 'http://test/test/test.gif',
> { TYPE => SQL_VARCHAR});
> $sth->bind_param(2, 1234567890, { TYPE => SQL_INTEGER });
> $sth->bind_param(3, 0, { TYPE => SQL_INTEGER });
> $sth->execute;
>
> do {
> $results = $sth->fetchall_arrayref;
> foreach (@$results) {
> warn join("\t", @$_)."\n";
> }
> } while ($sth->more_results);
> $sth->finish;
>
> ======
>
> Run the script and see what is inserted into the table.
> You'll notice that the integers are completely off. If you run the
> same test but put in the varchar field a simple string without the
> slashes, you're ok.
> If you run the same script but without bound parameters (and with
> slashes), the insert is good.
