Bug #4264 when

• Submitted: 23 Jun 2004 19:47
• Modified: 24 Oct 2005 20:31
• Reporter: bill zheng
• Status: Closed
• Category: Connector / ODBC
• Severity: S2 (Serious)
• Version: 3.51
• OS: Windows (windows 2000)
• Assigned to: Peter Harvey
• CPU Architecture: Any

[23 Jun 2004 19:47] bill zheng

Description:
the mysql database is being accessed by perl DBI with DBD::ODBC.
when using bind_param_array/execute_array to insert rows into a table, an extra blank row is always created.
numeric columns on the row are set to 0.
varchar columns on the row are set to string "NULL".

further tests:
1.I tested with DBD::mysql and DBD::ADO, no problem.
2.I tested with MSSQL accessed through ODBC, no problem.
3.I tested to use bind_param/execute, still with DBD::ODBC, no problem.

configuration:
myODBC v3.51
mySQL server 4.0.16-max-debug
perl -v
This is perl, v5.8.4 built for MSWin32-x86-multi-thread
(with 3 registered patches, see perl -V for more detail)

Copyright 1987-2004, Larry Wall

Binary build 810 provided by ActiveState Corp. http://www.ActiveState.com
ActiveState is a division of Sophos.
...

DBI v 1.42
DBD::ODBC v1.07

the reason i think it is a MyODBC driver problem is that:
after i turned on ODBC tracing, i noticed SQLBindParameter and SQLExecute are called for the correct number of times. (SQLBindParameter called once for each column, and SQLExecute's called only once)

How to repeat:
1.create the test table:
CREATE TABLE tbl_test ( 
    typeid_ 	tinyint NOT NULL DEFAULT 0,
    type_   	varchar(50) NOT NULL,
    typeabr_	varchar(50) NULL 
    );
2.create a ODBC data source
3.modify perl script below to supply the right data source info, run it to add one row to tbl_test.
#--begin--
use strict;
use DBI;
my($dataSource, $user, $pwd, $strSQL, $sth, $tuples, @tuple_status, $dbh);

#DSN setting
$dataSource="DBI:ODBC:dsn_name",
$user="user",
$pwd="pwd",

$dbh = DBI->connect($dataSource, $user, $pwd);
$strSQL = "INSERT INTO tbl_test (typeid_, type_, typeabr_) VALUES (?,?,?)";

#insert 1 row
$sth = $dbh->prepare($strSQL);
#bind parameters
$sth->bind_param_array(1, [30]);
$sth->bind_param_array(2, ["aa"]);
$sth->bind_param_array(3, ["bb"]);
$tuples = $sth->execute_array({ ArrayTupleStatus => \@tuple_status });
$sth->finish();
#--end--
4.check tbl_test to see if you have one extra blank row

[31 Aug 2004 22:00] MySQL Verification Team

Thank you for the bug report:

mysql> select * from tbl_test;
+---------+-------+----------+
| typeid_ | type_ | typeabr_ |
+---------+-------+----------+
|       0 | NULL  | NULL     |
|      30 | aa    | bb       |
+---------+-------+----------+
2 rows in set (0.00 sec)

[28 Sep 2004 2:41] Jeff Martin

I just logged bug #5778.  I'll bet that the root cause of this bug (#4364) is that the Perl DBI for ODBC always calls the ODBC function SQLNumResultCols() after every SQLPrepare().  Bug #5778 demonstrates that this combination results in an extra "blank" row inserted into the table.

[12 Apr 2005 2:01] Peter Harvey

This is probably a result of a prepared INSERT being silently executed so as to determine Rows/Cols. This has been address and I beleive in the current distro (MyODBC 3.51.11).

[24 Oct 2005 20:31] Peter Harvey

Fixed in c/odbc v3.51.11 (fix also in v3.51.12 etc).