FIX: DB-Library sp_cursor Fails with Error Message 16933 if Bracketed Identifiers are Passed with the Tablename Parameter (290413)

dbcursor(hcursor, CRS_INSERT, 1, "curTblTest", "'a'");
				

MORE INFORMATION

The Microsoft® SQL ServerT OLE DB provider, SQL Server ODBC driver, and DB-Library programming libraries have special cursor functions that are optimized for cursor operations between a client application and a computer that is running SQL Server. These cursor functions (or APIs) use server cursors that are implemented by the use of special system stored procedures within SQL Server of which sp_cursor is one. For more information, refer to the "API Server Cursors" topic in SQL Server Books Online.

The OLE DB provider, ODBC driver and DB-Library programming interface all have different ways of calling these system stored procedures. These system stored procedures appear in SQL Server Profiler traces of Microsoft ActiveX Data Objects (ADO), OLE DB, ODBC, and DB-Library applications that use API server cursors. These system stored procedures are only intended for the internal use of the SQL Server Provider for OLE DB, the SQL Server ODBC driver, and the DB-Library DLL. The full functionality of these procedures is available to the applications through the use of the cursor functionality of the database APIs. Specifying the procedures directly in an application is not supported.

Steps to Reproduce Behavior

The example that follows uses the dbcursor DB-Library cursor function, which you can use to perform positional updates, deletes or inserts. You can find more information about the use of the dbcursor function in SQL Server Books Online. The parameters that are passed to the dbcursor function include the table name of the table that is being modifed. The actual implementation of the function consists of a call to sp_cursor with the table name passed as a parameter.

1. Create the following table by using SQL Server Query Analyzer:

   create table curTblTest ( col1 char(10))
   					

2. Use the dbcursor DB-Library API to insert a single row:

   /* 
    * For clarity purposes, DB connection and error handling steps 
    * were not included in the sample code. 
    */ 
   :
   DBCURSOR  *hcursor;
   DBINT	  pstat[5];
   char 	  col1[5][11];
   :
      hcursor = dbcursoropen(dbproc,
                             "select col1 from [curTblTest]", 
                              CUR_DYNAMIC, CUR_LOCKCC, 5, pstat);
      dbcursorbind(hcursor, 1, NTBSTRINGBIND, 11, NULL, (LPBYTE )col1);
      dbcursor(hcursor, CRS_INSERT, 1, "[curTblTest]", "'a'");
      dbcursorclose(hcursor);
   					

   The code fails with the 16933 error message. To observe what was sent to the computer running SQL Server, use the SQL Profiler utility and capture the following events with the appropiate Data columns:
   
   
   • Stored Procedures - RPC: Starting
   • Error and Warning - Exception

The following sample Profiler trace was captured:

RPC:Starting

exec sp_cursor @cursor = 180150000, @optype = 4, @rownumber = 1, 
@table = '[curTblTest]  ... padded with 244 spaces ...  ', @VALUES = 
'''a'''

Exception
    
Error: 16933, Severity: 16, State: 2

SQL Server was not able to parse "@table = '[curTblTest]        '" 
correctly.