Bug #68404 Cannot use prepared statements if autoincrement is set
Submitted: 16 Feb 2013 20:41 Modified: 20 Mar 2013 19:44
Reporter: Dennis Drew Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / C++ Severity:S1 (Critical)
Version:1.1.1 OS:Any
Assigned to: CPU Architecture:Any

[16 Feb 2013 20:41] Dennis Drew
Description:
If you call prepareStatement() using wild card (?,?,?) then try to bind you will have a problem if there are autoincrement fields. Of course you would not want to set the value for a autoincrement column, but if you try and skip one of them, the parser returns {_Str="Column count doesn't match value count at row 1" }. If you include all of the columns then the database write fails because you will chalange the value of the autoincrement field.

How to repeat:

DB rectape has a table with 3 columns - id (autoincrement)
duration (int) and localEntryPoint (string)

pstmt = con->prepareStatement("INSERT INTO rectape (id,duration,localEntryPoint) VALUES (?,?,?)");

pstmt->setInt(1,1);
pstmt->setInt(2,2);
pstmt->setString(3,"test")

try{
    pstmt->executeUpdate();
   }catch(sql::SQLException &e){
	strcpy( cStr1,e.what() );
	LOG4CXX_INFO(LOG.reportingLog, cStr1);
   }

>>> Fails because you are overwriting the autoincrement field <<<

pstmt = con->prepareStatement("INSERT INTO rectape (id,duration,localEntryPoint) VALUES (?,?)");

pstmt->setInt(2,2);
pstmt->setString(3,"test")

>>>preparedStatement fails because you skipped a field, there are three<<<

Suggested fix:

prepareSatement filter needs to be smart enough to recognize data fields that don't need to / should not be - written to.
[20 Feb 2013 19:17] Sveta Smirnova
Second case is correct behavior: query is invalid.
[20 Feb 2013 19:44] Sveta Smirnova
Thank you for the report.

As I wrote in previous comment, second error is not a bug, but badly written query.

Regarding to first error I can not repeat described behavior: data inserted fine unless I explicitly specify value for auto increment field which already exists in the table. Please check if this is not your case.
[21 Mar 2013 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".