MySQL Bugs: #3099: Insert occuring on PHP->odbc_prepare prior to PHP->odbc

Bug #3099	Insert occuring on PHP->odbc_prepare prior to PHP->odbc_execute
Submitted:	7 Mar 2004 13:32	Modified:	11 Apr 2005 18:37
Reporter:	Chris Moore	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.06	OS:	Windows (Win2000)
Assigned to:	Peter Harvey	CPU Architecture:	Any

Description:
When using PHP 4.3.4 and the Unified ODBC Functions (ODBC_PREPARE & ODBC_EXECUTE) to issuen an "INSERT" SQL statement, MyODBC seems to be forcing the execution of an "INSERT" statement on the ODBC_PREPARE and on the ODBC_EXECUTE resulting in "Duplicate key error".  The Insert statement being prepared uses placeholders (e.g. ?).  Hence, the PREPARE statement inserts NULLS where those placeholders exist while the EXECUTE statement attempts to validly execute the INSERT statement, but an erroneous record already has been inserted during the PREPARE resulting in a "duplicate key error".  This works fine on other databases (e.g. MS SQLServer, Accesss, Oracle).

How to repeat:
PHP 4.3.4
MySQL 4.0.18
MyODBC 3.51.06
Windows 2000 Professional

Suggested fix:
Suppress the execution of the statement during the preparation of the statement.

$query = "Insert into LABELS (id,code,label) Values (1,?,?)"
$params[] = 1033;
$params[] = "Test Label";

$CID = odbc_connect(datasource,dbuser,dbpassword);
$RID = odbc_prepare($CID,$query);
odbc_execute($RID,$params);

Warning: odbc_execute(): SQL error: [MySQL][ODBC 3.51 Driver][mysqld-4.0.18-max-debug]Duplicate entry '1' for key 1, SQL state 23000 in SQLExecute in C:\source\test.php on line 7

Verified on WinXP (SP1), PHP 4.3.6, MyODBC 3.51.06.  I am attaching three files:  an odbc trace file from the normal myodbc3.dll, a trace file from myodbc3d.dll (debugging version), and the sql log (just one INSERT query).

ODBC trace file (standard, non-debugging MyODBC)

Attachment: 3099.log (application/octet-stream, text), 4.23 KiB.

MyODBC trace file (debugging DLL)

Attachment: myodbc.log (application/octet-stream, text), 10.17 KiB.

MyODBC SQL log

Attachment: myodbc.sql (text/x-sql), 475 bytes.

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.