Description:
I've been trying out some of my code with DBD-mysql-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 program below 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 patch below (aginst 3.0006_1) fixes this by moving the switch statement inside the if (SvOK() ...) { } block a few lines higher up.
How to repeat:
dbi.pl
------
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();
Suggested fix:
--- 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 */
Description: I've been trying out some of my code with DBD-mysql-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 program below 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 patch below (aginst 3.0006_1) fixes this by moving the switch statement inside the if (SvOK() ...) { } block a few lines higher up. How to repeat: dbi.pl ------ 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(); Suggested fix: --- 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 */