FIX: SQLPrepare() Returns Incorrect Error on FOR UPDATE

• Microsoft SQL Server, version 6.0
• Microsoft Open Data Base Connectivity, version 2.5

SYMPTOMS

When you use the ODBC SQL Server driver in conjunction with Microsoft SQL Server version 6.0 server-side cursors to attempt the creation of an updateable rowset via SQLPrepare(), the FOR UPDATE portion of the SELECT statement returns an incorrect error message.

WORKAROUND

If you do not need to use SQLPrepare() to create your updateable rowset, then you should use SQLExecDirect() to achieve this functionality.

Otherwise, the same functionality can be accomplished by excluding the FOR UPDATE portion of the SELECT statement from the string passed to SQLPrepare(). When this query has been prepared, the rowset may still be operated on with modification operations using SQLExtendedFetch() and SQLSetPos().

STATUS

Microsoft has confirmed this to be a problem in the ODBC SQL Server Driver version 2.50.0121. This problem has been corrected in the ODBC SQL Server Driver version 2.65.0201. For more information, contact your primary support provider.

MORE INFORMATION

For example, with the SQLSetStmtOption() settings made to invoke server side cursor usage (as defined in SQL Books Online under the ODBC SQL Server Driver section headings "Using ODBC Cursors" and "Creating Cursors") the statement:

   Select * from <table_name> for update

   Return Code = SQL_ERROR = -1
   szSqlState = "37000"
   *pfNativeError = 1003
   *pcbErrorMsg = 105
   szErrorMsg="[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1:
   FOR UPDATE clause allowed only for DECLARE CURSOR."