MySQL Bugs: #83504: SQLSetStmtAttr FAILS

Bug #83504	SQLSetStmtAttr FAILS
Submitted:	24 Oct 2016 16:43	Modified:	31 Oct 2016 10:55
Reporter:	Adam Manley	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	5.03.06.00	OS:	Windows (Windows 8.1)
Assigned to:		CPU Architecture:	Any
Tags:	Concurency, ODBC, SQLSetStmtAttr

Description:
The connector creates READONLY cursor result sets no matter what attributes are set before SQLExecDirect is called.

See the "How to repeat" section below.  From the ODBC Documentation:

"When an application sets SQL_ATTR_CURSOR_SENSITIVITY to SQL_SENSITIVE"
"Other Attributes set implicitly: SQL_ATTR_CONCURRENCY to SQL_CONCUR_LOCK, SQL_CONCUR_ROWVER, or SQL_CONCUR_VALUES, as specified by the driver. It is never set to SQL_CONCUR_READ_ONLY."

My Environment:
MySQL Server 5.7 Installed on network

32-bit MySQL ODBC 5.3 Unicode Driver (version 5.03.06.00) Running on Windows 8.1 (64 bit windows) (user logon account has full privileges to the database)

WIN/32 C++ Application compiled against standard Microsoft ODBC libraries("odbc32.lib" "odbccp32.lib")

How to repeat:
  [...code to create the handle for connection and statement omitted...works with all other platforms tested...]

  SQLUINTEGER iConcurrency;
  SQLINTEGER iDummyLen;

  rc = SQLGetConnectAttr(m_refCon.GetHdbc(), SQL_ATTR_ACCESS_MODE, &iConcurrency, sizeof(iConcurrency), NULL);
  if(iConcurrency !=  SQL_MODE_READ_WRITE )
     FAIL; // NOT A PROBLEM HERE

  rc = SQLSetStmtAttr(m_hstmt, SQL_ATTR_CURSOR_SENSITIVITY, (SQLPOINTER)SQL_SENSITIVE, SQL_IS_UINTEGER);
  if(!SQL_SUCCEEDED(rc))
    FAIL; // LOOKS OK HERE, TOO, rc is SUCCESS_WITH_INFO and the diagnostic field says "[01S02] [502] [MySQL][ODBC 5.3(w) Driver][mysqld-5.7.16-log]Option value changed to default cursor sensitivity(unspecified) "

  rc = SQLGetStmtAttr(m_hstmt, SQL_ATTR_CONCURRENCY, (SQLPOINTER)&iConcurrency, SQL_IS_POINTER, &iDummyLen);
  
  if(iConcurrency == SQL_CONCUR_READ_ONLY)
     FAIL;  //HERE WE HAVE A PROBLEM

Please provide the complete test code file using Files tab. Thanks in advance.

Example program file to reproduce the bug

Attachment: odbcsql.cpp (text/plain), 22.75 KiB.

The file I uploaded is an edited version of the example / test code from:
https://code.msdn.microsoft.com/windowsapps/ODBC-sample-191624ae

See my comments and code inserted near "Test MySQL..."

Hope this helps & Best of luck

Hi Adam,

Unfortunately, the Connector/ODBC does not support the cursor sensitivity. Therefore, any options other than SQL_UNSPECIFIED are going to end with the same error.

This is so because of the limitations in MySQL network protocol. There is really no way for a cursor to be sensitive to any data changes because once the query is executed the result should be treated rather as a momentary snapshot of the data than something with a cursor.

Regretfully I must say this functionality is not supported and the bug is closed as such. Sorry for the inconvenience.