Hi Patrick,
Patrick Galbraith wrote:
NOTICE: (as I said in the last release) Very soon, perhaps in a release
or so, I intend to stabilise the dev release and make that the
production release. This means that server
side prepared statements will be turned on by default.
[...]
As the community, one think you can do if you are interested in helping
is to turn on server side prepare statements in 3.0006, or try the
development driver, 3.0006_1 to see if there are any problems. I've
tested the code as much as I can, but I know nothing tests code like
1000s of developers thinking of unique ways of using the driver that I
never could have imagined. If you find a bug, please report it to me,
I've been trying out some of my code with 3.0006_1 and I've found a
problem with the server prepare statement code: when passing the
undefined value to bind_param() to insert a NULL into an INT or DOUBLE
column, bind_param() issues a warning about "Use of uninitialized value
in subroutine entry".
It didn't use to do this, and doesn't do it now if server prepare is
disabled. The attached dbi.pl program demonstrates this: the output is
Use of uninitialized value in subroutine entry at dbi.pl line 27.
If you switch to emulated prepare then the warning doesn't appear.
The warning comes from the calls to SvIV() and SvNV() in the switch
statement within dbd_bind_ph(): these functions warn when their argument
is undefined.
The attached patch (aginst 3.0006_1) fixes this by moving the switch
statement inside the if (SvOK() ...) { } block a few lines higher up.
Cheers,
- Steve
------------------------------------------------
Radan Computational Ltd.
The information contained in this message and any files transmitted with it are
confidential and intended for the addressee(s) only. If you have received this
message in error or there are any problems, please notify the sender
immediately. The unauthorized use, disclosure, copying or alteration of this
message is strictly forbidden. Note that any views or opinions presented in
this email are solely those of the author and do not necessarily represent
those of Radan Computational Ltd. The recipient(s) of this message should check
it and any attached files for viruses: Radan Computational will accept no
liability for any damage caused by any virus transmitted by this email.
use strict;
use warnings;
use DBI qw(:sql_types);
my $tmp_dbh = DBI->connect(
'dbi:mysql:database=mysql', 'root', undef,
{ AutoCommit => 1, PrintError => 0, RaiseError => 1 }
);
$tmp_dbh->do('CREATE DATABASE IF NOT EXISTS test');
$tmp_dbh->disconnect();
my $dsn = 'dbi:mysql:database=test;mysql_server_prepare=1'; # warns
#my $dsn = 'dbi:mysql:database=test;mysql_emulated_prepare=1'; # doesn't warn
my $dbh = DBI->connect(
$dsn, 'root', undef,
{ AutoCommit => 1, PrintError => 0, RaiseError => 1 }
);
$dbh->do('DROP TABLE IF EXISTS foo');
$dbh->do(qq{CREATE TABLE foo (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
num INT
) ENGINE=InnoDB});
$dbh->do("INSERT INTO foo VALUES(NULL, 1)");
my $rows = $dbh->selectall_arrayref('SELECT * FROM foo');
printf "ROW 1: id = %s, num = %s\n",
$rows->[0][0], (defined $rows->[0][1] ? $rows->[0][1] : 'NULL');
my $sql = 'UPDATE foo SET num = ? WHERE id = ?';
my $sth = $dbh->prepare($sql);
$sth->bind_param(1, undef, SQL_INTEGER);
$sth->bind_param(2, 1, SQL_INTEGER);
my $num_rows = $sth->execute();
print "UPDATE affected $num_rows rows\n";
$rows = $dbh->selectall_arrayref('SELECT * FROM foo');
printf "ROW 1: id = %s, num = %s\n",
$rows->[0][0], (defined $rows->[0][1] ? $rows->[0][1] : 'NULL');
$dbh->disconnect();
--- DBD-mysql-3.0006_1/dbdimp.c.orig 2006-06-14 10:15:15.833207900 +0100
+++ DBD-mysql-3.0006_1/dbdimp.c 2006-06-14 10:17:30.820174600 +0100
@@ -3961,77 +3961,77 @@
if (imp_sth->use_server_side_prepare)
{
if (SvOK(imp_sth->params[idx].value) && imp_sth->params[idx].value)
+ {
buffer_is_null= 0;
+
+ switch(sql_type) {
+ case SQL_NUMERIC:
+ case SQL_INTEGER:
+ case SQL_SMALLINT:
+ case SQL_BIGINT:
+ case SQL_TINYINT:
+ /* INT */
+ if (!SvIOK(imp_sth->params[idx].value) && dbis->debug >= 2)
+ PerlIO_printf(DBILOGFP, "\t\tTRY TO BIND AN INT NUMBER\n");
+
+ buffer_type= MYSQL_TYPE_LONG;
+ imp_sth->fbind[idx].numeric_val.lval=
SvIV(imp_sth->params[idx].value);
+ buffer=(void*)&(imp_sth->fbind[idx].numeric_val.lval);
+ if (dbis->debug >= 2)
+ PerlIO_printf(DBILOGFP,
+ " SCALAR type %d ->%ld<- IS A INT NUMBER\n",
+ sql_type, (long) (*buffer));
+ break;
+
+ case SQL_DOUBLE:
+ case SQL_DECIMAL:
+ case SQL_FLOAT:
+ case SQL_REAL:
+ if (!SvNOK(imp_sth->params[idx].value) && dbis->debug >= 2)
+ PerlIO_printf(DBILOGFP, "\t\tTRY TO BIND A FLOAT NUMBER\n");
+
+ buffer_type= MYSQL_TYPE_DOUBLE;
+ imp_sth->fbind[idx].numeric_val.dval=
SvNV(imp_sth->params[idx].value);
+ buffer=(char*)&(imp_sth->fbind[idx].numeric_val.dval);
+
+ if (dbis->debug >= 2)
+ PerlIO_printf(DBILOGFP,
+ " SCALAR type %d ->%f<- IS A FLOAT NUMBER\n",
+ sql_type, (double)(*buffer));
+ break;
+
+ case SQL_CHAR:
+ case SQL_VARCHAR:
+ case SQL_DATE:
+ case SQL_TIME:
+ case SQL_TIMESTAMP:
+ case SQL_LONGVARCHAR:
+ case SQL_BINARY:
+ case SQL_VARBINARY:
+ case SQL_LONGVARBINARY:
+ buffer_type= MYSQL_TYPE_STRING;
+ break;
+
+ default:
+ buffer_type= MYSQL_TYPE_STRING;
+ break;
+ }
+
+ if (buffer_type == MYSQL_TYPE_STRING)
+ {
+ buffer= SvPV(imp_sth->params[idx].value, slen);
+ buffer_length= slen;
+ if (dbis->debug >= 2)
+ PerlIO_printf(DBILOGFP,
+ " SCALAR type %d ->%s<- IS A STRING\n",
+ sql_type, buffer);
+ }
+ }
else
{
buffer= NULL;
buffer_is_null= 1;
- }
-
- switch(sql_type) {
- case SQL_NUMERIC:
- case SQL_INTEGER:
- case SQL_SMALLINT:
- case SQL_BIGINT:
- case SQL_TINYINT:
- /* INT */
- if (!SvIOK(imp_sth->params[idx].value) && dbis->debug >= 2)
- PerlIO_printf(DBILOGFP, "\t\tTRY TO BIND AN INT NUMBER\n");
-
- buffer_type= MYSQL_TYPE_LONG;
- imp_sth->fbind[idx].numeric_val.lval= SvIV(imp_sth->params[idx].value);
- buffer=(void*)&(imp_sth->fbind[idx].numeric_val.lval);
- if (dbis->debug >= 2)
- PerlIO_printf(DBILOGFP,
- " SCALAR type %d ->%ld<- IS A INT NUMBER\n",
- sql_type, (long) (*buffer));
- break;
-
- case SQL_DOUBLE:
- case SQL_DECIMAL:
- case SQL_FLOAT:
- case SQL_REAL:
- if (!SvNOK(imp_sth->params[idx].value) && dbis->debug >= 2)
- PerlIO_printf(DBILOGFP, "\t\tTRY TO BIND A FLOAT NUMBER\n");
-
- buffer_type= MYSQL_TYPE_DOUBLE;
- imp_sth->fbind[idx].numeric_val.dval=
SvNV(imp_sth->params[idx].value);
- buffer=(char*)&(imp_sth->fbind[idx].numeric_val.dval);
-
- if (dbis->debug >= 2)
- PerlIO_printf(DBILOGFP,
- " SCALAR type %d ->%f<- IS A FLOAT NUMBER\n",
- sql_type, (double)(*buffer));
- break;
-
- case SQL_CHAR:
- case SQL_VARCHAR:
- case SQL_DATE:
- case SQL_TIME:
- case SQL_TIMESTAMP:
- case SQL_LONGVARCHAR:
- case SQL_BINARY:
- case SQL_VARBINARY:
- case SQL_LONGVARBINARY:
- buffer_type= MYSQL_TYPE_STRING;
- break;
-
- default:
- buffer_type= MYSQL_TYPE_STRING;
- break;
- }
-
- if (buffer_is_null)
buffer_type= MYSQL_TYPE_NULL;
-
- if (buffer_type == MYSQL_TYPE_STRING)
- {
- buffer= SvPV(imp_sth->params[idx].value, slen);
- buffer_length= slen;
- if (dbis->debug >= 2)
- PerlIO_printf(DBILOGFP,
- " SCALAR type %d ->%s<- IS A STRING\n",
- sql_type, buffer);
}
/* Type of column was changed. Force to rebind */
