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 */
