MySQL Bugs: #40771: Calling SQLGetDiagField with DiagIdentifier SQL_DIAG_ROW

Bug #40771	Calling SQLGetDiagField with DiagIdentifier SQL_DIAG_ROW_COUNT always returns 0
Submitted:	17 Nov 2008 4:09	Modified:	8 Jan 2015 22:44
Reporter:	huang qing	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.01	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
First call SQLExecDirect with a Update statement to update a table; 
And then I call the SQLGetDiagField with DiagIdentifier SQL_DIAG_ROW_COUNT to the number of rows affected by the Update Statement, but I always get 0;

How to repeat:
//Create a Table Tmptable
Create Table TmpTable
(Col Integer);

//Code
SQLHANDLE hEnv;
SQLHANDLE hDBC;
SQLHANDLE hStmt;

SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv);
SQLSetEnvAttr(hEnv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);

SQLAllocHandle(SQL_HANDLE_DBC, hEnv, &hDBC);
SQLConnect(hDBC, (SQLCHAR*)"MySQL", SQL_NTS, (SQLCHAR*)"root", SQL_NTS, (SQLCHAR*)"root", SQL_NTS);
	
SQLAllocHandle(SQL_HANDLE_STMT, hDBC, &hStmt);
	
SQLCHAR* cStatement = (SQLCHAR*)"Insert Into TmpTable Values(1)";
SQLExecDirect(hStmt, cStatement, SQL_NTS);
	
//Here, RowCount is always 0
SQLINTEGER RowCount;
SQLGetDiagField(SQL_HANDLE_STMT, hStmt, 0, SQL_DIAG_ROW_COUNT, &RowCount, 0, NULL);
	
SQLFreeHandle(SQL_HANDLE_STMT, hStmt);
SQLDisconnect(hDBC);
SQLFreeHandle(SQL_HANDLE_DBC, hDBC);
SQLFreeHandle(SQL_HANDLE_ENV, hEnv);

Hi Huang and thanks for your report.

Verified just as described with more or less similar test case. Now what actually happens is that c/ODBC is not filling DiagRec unless there's error so you have: "There are 0 error records available" and not actual recordcount:
   rc = SQLGetDiagField(SQL_HANDLE_STMT, StmtHandle, 3, SQL_DIAG_ROW_COUNT, &RowCount, SQL_IS_UINTEGER, &rrcc);
   if (rc != SQL_SUCCESS) {
      printf("SQLExecute failed %d\n", rc);
      odbc_show_error_info(SQL_HANDLE_STMT, StmtHandle);
      rc = SQLCloseCursor(StmtHandle);
      rc = SQLFreeHandle(SQL_HANDLE_STMT, StmtHandle);
      rc = SQLDisconnect(DBCHandle);
      if (rc != SQL_SUCCESS) {
         printf("Disconnect failed %d\n", rc);
         odbc_show_error_info(SQL_HANDLE_DBC, DBCHandle); }
      rc = SQLFreeHandle(SQL_HANDLE_DBC, DBCHandle);
      rc = SQLFreeHandle(SQL_HANDLE_ENV, EnvHandle);
      exit (1); }

I do not wonder at this as "SQL_DIAG_ROW_COUNT" is only mentioned in error.c
I also suspect the same will occur with 3.51.

I need expert opinion here.

From the documentation for SQLGetDiagField()/SQL_DIAG_ROW_COUNT:

    This field contains the count of rows in the cursor. 

Since you do not have an open cursor this value is not applicable. Please instead use SQLRowCount(). From the documentation:

    SQLRowCount returns the number of rows affected by an UPDATE, INSERT, or DELETE statement;...

Hi Tonci Grgin.
My document says 'At least one diagnostic record — the header record — is returned even if the function succeeds'.And I think it's really reasonable.

Hi,Jess.Thanks for you advice.
But my document says if you want to get the count of a cursor, you should use the field identifier 'SQL_DIAG_CURSOR_ROW_COUNT' rather than 'SQL_DIAG_ROW_COUNT'.
If you use 'SQL_DIAG_CURSOR_ROW_COUNT', you get the count of rows in the cursor.
And if you use 'SQL_DIAG_ROW_COUNT', you get the number of rows affected by an insert, delete, or update statement. So in face, they're different.

You're correct. My apologies.

Added the following entry to the C/ODBC 5.3.5 changelog:

"Calling the SQLGetDiagField function with the DiagIdentifier SQL_DIAG_ROW_COUNT always returned “0,” even if there were updated, deleted, or modified rows. "