Handling multiple Direct ODBC calls that fail.

Have you ever tried to issue multiple direct ODBC calls only to have some of the calls after the first fail?  I encountered this problem on a project I was working on.  The first SQL command would complete successfully but some of the following SQL commands would fail.  The error produced indicated that an "Invalid cursor state" existed.  The message provided me with no clues to what the nature of the problem was.  The following sample shows code that fails producing the "Invalid Cursor State" message.  The UpdateUserName() function contained the commands that produced the error.  The sample was issued to an MS SQL Server running version 6.5:

//
// Change a given users name in the SQL database
//
void CSqlConnector::UpdateUserName( const char * pSysAdmnPwd, const char *pLogin, const char *pNewUserName, const char *pOldUserName )
{
   CString SqlCommand;
   
   try
   {
       // a DSN has been created earlier named MyDSN that
       // points to the SQL server
       AllocateODBCHandles( “MyDSN”, "sa", pSysAdmnPwd );

       // issue a direct SQL call (call a system procedure in this case)
       SqlCommand.Format( "sp_dropuser %s", pOldUserName );
       ExecuteDirectODBC( m_hStmt, SqlCommand );

//
// NOTE: this command works without using the allocation/deallocation process
//
       // switch to the user database
       SqlCommand.Format( "USE %s", “MyDatabaseName” );
       ExecuteDirectODBC( m_hStmt, SqlCommand );

//
// THIS STATEMENT FAILED FOR ME
//
       // add the user with the new name
       SqlCommand.Format( "sp_adduser %s, %s", pLogin, pNewUserName );
       ExecuteDirectODBC( m_hStmt, SqlCommand );


       // Release the ODBC handles because
       // the SQL commands have completed  
       FreeODBCHandles();
   }
   catch(...)
   {
       // handle error condition
   }
}

//
// directly execute an ODBC command
//
SQLRETURN CSqlConnector::ExecuteDirectODBC( SQLHSTMT hStmt, CString command )
{
   //
   // issue the SQL command that is to execute
   //
   return SQLExecDirect(hStmt, (unsigned char *)command.operator LPCSTR(), SQL_NTS);
}

//
//
//
void CSqlConnector::AllocateODBCHandles( const CString & DSN, const CString & UID, const CString & Pwd )
{
   try
   {
       // Allocate the Environment Handle
       SQLAllocHandle( SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_hEnv );

       // Notify ODBC that this is an ODBC 3.0 application.
       SQLSetEnvAttr(m_hEnv, SQL_ATTR_ODBC_VERSION,
                        (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_INTEGER);

       // Allocate the Connection handle
       SQLAllocHandle( SQL_HANDLE_DBC, m_hEnv, &m_hDbc );

       // set the connection timeout value to 15 seconds
       SQLSetConnectOption(m_hDbc, SQL_LOGIN_TIMEOUT, 15);

       // Connect to the database
       SQLConnect(m_hDbc, (SQLCHAR*) DSN.operator LPCSTR(), SQL_NTS,
                       (SQLCHAR*) UID.operator LPCSTR(), SQL_NTS,
                       (SQLCHAR*) Pwd.operator LPCSTR(), SQL_NTS);

       // retrieve a statement handle
       SQLAllocHandle( SQL_HANDLE_STMT, m_hDbc, &m_hStmt );
   }
   catch(...)
   {
       // free the database handle if it exists
       if( NULL != m_hDbc )
       {
           // close the connection handle
           SQLDisconnect( m_hDbc );
       }

       // free the environment handle if it exists
       if( NULL != m_hEnv )
       {
           // free the environment handle
           SQLFreeHandle( SQL_HANDLE_ENV, m_hEnv );
       }
   }
}

//
//
//
void CSqlConnector::FreeODBCHandles()
{
   SQLFreeHandle( SQL_HANDLE_STMT, m_hStmt );
   // close the connection handle
   SQLDisconnect( m_hDbc );
   // free the database handle
   SQLFreeHandle( SQL_HANDLE_DBC, m_hDbc );
   // free the environment handle
   SQLFreeHandle( SQL_HANDLE_ENV, m_hEnv );

   // mark the handles as not in use
   m_hEnv = NULL;
   m_hDbc = NULL;
   m_hStmt = NULL;
}

I checked the newsgroups for anyone having the same problem. I found several posts where others encountered the same problem.  No answers were posted to their questions.  After trying a number of angles I finally stumbled on one that worked.  The code worked for me when I allocated the ODBC handles before each command and then deallocated them after each call. The following is a snippet code that demonstrates this method:

//
// Change a given users name in the SQL database
//
void CSqlConnector::UpdateUserName( const char * pSysAdmnPwd, const char *pLogin, const char *pNewUserName, const char *pOldUserName )
{
   CString SqlCommand;
   
   try
   {
       // a DSN has been created earlier named MyDSN that
       // points to the SQL server
       AllocateODBCHandles( “MyDSN”, "sa", pSysAdmnPwd );

       // add the login
       SqlCommand.Format( "sp_dropuser %s", pOldUserName );
       ExecuteDirectODBC( m_hStmt, SqlCommand );

       // Release the ODBC handles because
       // the SQL command has completed  
       FreeODBCHandles();

       AllocateODBCHandles( “MyDSN”, “sa”, pAdminPwd ))

       // switch to the user database
       SqlCommand.Format( "USE %s", “MyDatabaseName” );
       ExecuteDirectODBC( m_hStmt, SqlCommand );

//
// THE COMMAND SUCCEEDS THIS TIME
//
       // add the user with the new name
       SqlCommand.Format( "sp_adduser %s, %s", pLogin, pNewUserName );
       ExecuteDirectODBC( m_hStmt, SqlCommand );

       // Release the ODBC handles because
       // the SQL commands have completed  
       FreeODBCHandles();
   }
   catch(...)
   {
       // handle error condition
   }
}

I’ve noticed that some statements would work successfully without the allocation/deallocation process. It appears that certain statements need to be "flushed" before other commands may be issued.  Closing and opening the ODBC handles appears to handle this.  I hope this article helps clear up the same problem for others.  

I found one other interesting artifact that there is a memory leak.  Running a stress test on the code verified this to be true.  I searched through some news groups and found a number of posts complaining about the same problem.  The consensus was that the allocation and deallocation of the environment handle was the source of the problem.  It was suggested that the environment handle should be allocated/deallocated only once.  I’m not sure if that is the true source of the problem.  I’ll need to do further research to determine where the memory leak is coming from.

You might also like...

Comments

Dale Harkness

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“To iterate is human, to recurse divine” - L. Peter Deutsch