Microsoft KB Archive/294149

= BUG: Function Sequence Error Not Returned for Transactions When Connection Pooling Is Disabled =

Article ID: 294149

Article Last Modified on 12/5/2003

-

APPLIES TO


 * Microsoft Data Access Components 2.0
 * Microsoft Data Access Components 2.1
 * Microsoft Data Access Components 2.5
 * Microsoft Data Access Components 2.6

-



This article was previously published under Q294149



SYMPTOMS
When ODBC connection pooling is disabled, using the following sequence in a loop does not result in a &quot;Function sequence error&quot;: SQLExecDirect SQLFetch SQLSetStmtOption (SQL_ATTR_AUTOCOMMIT,SQL_AUTOCOMMIT_OFF) SQLTransact(SQL_COMMIT) SQLSetStmtOption (SQL_ATTR_AUTOCOMMIT,SQL_AUTOCOMMIT_ON) SQLFetch When connection pooling is enabled, a &quot;Function sequence error&quot; is returned on the second iteration.



STATUS
Microsoft has confirmed that this is a bug in the Microsoft products that are listed at the beginning of this article.



MORE INFORMATION
When an operation is committed or rolled back, all statement handles of any resultsets are freed unless the SQL_PRESERVE_CURSORS option is set to ON. For this reason, subsequent fetch operations should fail.

Steps to Reproduce Behavior
  From Query Analyzer, run the following code to create the table &quot;sytest&quot;: if exists (select * from sysobjects where id = object_id(N'[dbo].[sytest]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[sytest] GO

create table sytest(col1 int) insert into sytest Values(1) insert into sytest Values(2) insert into sytest Values(3)   Create a new Win32 console application and paste the following code in your project:
 * 1) include 
 * 2) include 
 * 3) include 
 * 4) include 

int StmtError(HSTMT);

void main(void) {   SQLHENV henv; SQLHDBC hdbc; SQLHSTMT hstmt; SQLRETURN nstatus; SQLCHAR szConnect[1024];

// Enable connection pooling. nstatus = SQLSetEnvAttr(NULL,SQL_ATTR_CONNECTION_POOLING,(SQLPOINTER)SQL_CP_ONE_PER_DRIVER,SQL_IS_INTEGER); nstatus = SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv); nstatus = SQLSetEnvAttr(henv,SQL_ATTR_ODBC_VERSION,(SQLPOINTER) SQL_OV_ODBC3,0); nstatus = SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);

for (int i=0;i<=10;i++) {   nstatus = SQLDriverConnect(hdbc,NULL,(unsigned char *)&quot;DSN=toCaveman;UID=sa;PWD=Password1;&quot;,         SQL_NTS, szConnect, 256, NULL, SQL_DRIVER_NOPROMPT);

nstatus = SQLAllocHandle(SQL_HANDLE_STMT,hdbc,&hstmt); nstatus = SQLExecDirect(hstmt,(SQLCHAR*) &quot;select col1 from sytest&quot;,SQL_NTS); nstatus = SQLFetch(hstmt); nstatus = SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,(void *)SQL_AUTOCOMMIT_OFF,0); nstatus = SQLTransact(henv,hdbc,SQL_COMMIT); nstatus = SQLSetConnectAttr(hdbc,SQL_ATTR_AUTOCOMMIT,(void *)SQL_AUTOCOMMIT_ON,0); nstatus = SQLFetch(hstmt); if(nstatus == SQL_ERROR) {   StmtError(hstmt); }   nstatus = SQLFreeHandle(SQL_HANDLE_STMT, hstmt); nstatus = SQLDisconnect(hdbc); }

nstatus = SQLFreeHandle(SQL_HANDLE_DBC,hdbc); nstatus = SQLFreeHandle(SQL_HANDLE_ENV,henv);

printf(&quot;Done&quot;); }

StmtError(HSTMT hstmt) {   // Variables for SQLDiagRec. RETCODE rc = 0; char mstate[6] = &quot;\0&quot;; long native = 0; char mtext[300] = &quot;\0&quot;; short mlength = 0; short i = 0;

while ((rc = SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, ++i, (unsigned char *)&mstate,        &native, (unsigned char *)&mtext, 300, &mlength)) != SQL_NO_DATA) printf(&quot;\nODBC Error:\t%s\n&quot;,mtext);

return(0); };                    Change the connection string to use your database and server. Compile and then run the code. On the second iteration, you should receive the following error in the console window:

[Microsoft][ODBC Driver Manager] Function sequence error

</li> Comment out the line of code where SQLSetEnvAttr is called to enable the connection pooling.</li> Recompile and run the code. You should not see any errors.</li></ol>

Keywords: kbbug kbpending KB294149

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.