MySQL Bugs: #70562: SQLRowCount() returns wrong result after update with long data

Bug #70562	SQLRowCount() returns wrong result after update with long data
Submitted:	9 Oct 2013 13:04	Modified:	11 Nov 2013 6:37
Reporter:	Dimitry Sibiryakov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.2.5	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
SQLRowCount() works well after update without long data fields, but always return "0 rows affected" if SQLParamData() and SQLPutData() are called after SQLExecute() has returned SQL_NEED_DATA.

It seems that in contravention with ODBC specs SQLRowCount() does not use cached value of SQL_DIAG_ROW_COUNT field which is reset to zero by SQLParamData/SQLPutData calls.

How to repeat:
Just add call of SQLRowCount() into standard SQLPutData() example from MSDN.

Suggested fix:
Cache value of SQL_DIAG_ROW_COUNT in SQLExecute(), SQLExecDirect, SQLBulkOperations, SQLSetPos, or SQLMoreResults calls as described in ODBC specs.

Hi Dimitry,

Thank you for your interest in MySQL Software.

I tried to repeat problem using the MSDN code example as you said.
However, SQLRowCount() returned the correct number of affected rows (1).
Can you please modify the test case (I will upload it in my next post as bug70562.c) and send it back to me?

The code with SQLRowCount() I inserted is marked as
// ----------------- BEGIN --- The piece of code added to MSDN example.

Test case

Attachment: bug70562.c (text/plain), 5.43 KiB.

Well... It means that my guess about MSDN example was wrong.

I don't have MySQL at hand at the moment (the issue was reported by user), but structurally my code has only two differences from yours:
1) it uses SQLPrepare() before SQLBindParameters()
2) SQL statement is UPDATE

If you won't be able to reproduce the issue with these changes, I'll go into deeper investigation.

Thanks for your patience.

Dimitry,

Thanks for your reply.
Unfortunately, I still cannot repeat the problem.
I modified the test case for using SQLPrepare/SQLExecute on the UPDATE query, but SQLRowCount keeps returning the correct number of rows.

Uploading the modified test case (it also has compact macros for checking the result instead of 5-line if(){...} constructions).

Test case version 2

Attachment: bug70562v2.c (text/plain), 5.91 KiB.

Modified test case. First row count is ok, second is wrong

Attachment: bug70562v3.c (text/plain), 6.77 KiB.

Dimitry,

Thank you for updating the test case.

Please note that the second update returns the correct row count (even though it is 0) because the update has not changed anything. It just tried to update the text column using the data that is already there from the previous update.
So, the number of affected rows is 0.

The second update will show the number of affected rows as 1 if you put different data for the update (Data[0]='Z'; before statement is executed) or use the ODBC option to return found rows instead of affected rows: append "FOUND_ROWS=1" to the connection string. FOUND_ROWS can also be set in the GUI dialog:

 Details -> Cursors/Results -> [x] Return matched rows instead of affected rows.

Hence, I am setting the status Not a bug.
Please let me know if you think otherwise.