Executing Multiple Firect ODBC SQL Calls

-->

Environment: VC++, SQL Server

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()
{
      // free the statement handle
      SQLFreeHandle( SQL_HANDLE_STMT, m_hStmt );
      // free the environment handle
      SQLFreeHandle( SQL_HANDLE_ENV, m_hEnv );
      // close the connection handle
      SQLDisconnect( m_hDbc );
      // free the connection handle
      SQLFreeHandle( SQL_HANDLE_DBC, m_hDbc );
 
      // 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.



Comments

  • i have pbm in dynamic database creation

    Posted by Legacy on 09/22/2003 12:00am

    Originally posted by: aparna

    i have pbm in dynamic database creation.my reqmt is to create a database dynamically.i dont have any DSN or anything else.i have to create database in SQL format.is there any API call.can anyone help me please.

    Reply
  • I Emergency Need Help in Querying database via ODBC driver or API in Visual C++6

    Posted by Legacy on 04/09/2003 12:00am

    Originally posted by: moharram zarbeizadeh

    with special thanks to all of that write this useful code to help beginers of vc++ .
    i was build a database in ms access and i like to access via ODBC Driver in VC++6 but i don't know how i can to query ACCESS DB via VC++ Code how i can do it.
    with thanks.
    mzz

    Reply
  • thanks, danke, shukria

    Posted by Legacy on 09/23/2002 12:00am

    Originally posted by: jonny

    thanx a lot buddy for taking out the time and putting this
    online, u saved me a lot of time and headache.

    dunno why but unixODBC & mySQL gives the same prob.

    i will soon be writing a step-by-step tutorial for setting
    up odbc/sql in linux and accessing it through a c prog.

    Reply
  • Making multiple ODBC calls using perl's DBI::ODBC

    Posted by Legacy on 04/11/2002 12:00am

    Originally posted by: Anthony Carbone

    Same principle has to be applied when using perl's DBI::ODBC .

    For example this code produces a 'invalid cursor state' error:

    $cursor = $dbh->prepare("<SQL statement>");
    while (1) {
    $cursor->execute();
    }
    $cursor->finshed();

    This code fixes the problem:

    while (1) {
    $cursor = $dbh->prepare("<SQL statement>");
    $cursor->execute();
    $cursor->finshed();
    }

    Reply
  • Making multiple ODBC calls - Missing SQLFreeStmt for cleanUp.

    Posted by Legacy on 03/05/2002 12:00am

    Originally posted by: Suresh Matlapudi

    I used to face the same problem when I stated working on ODBC 2 years before. Your Second method is one way of solution, But obviousley not the right one ( one connection for each statement) and soon you will face memory problems with high volume application ( Depends on the ODBC Drivers).

    The right method is, you need to call SQLFreeStmt after every SQLExecDirect call (after processing the results).
    This is to clean up the privious results.

    Add the following three line after your ExecuteDirectODBC call.
    /*
    ** Call SQLFreeStmt to end the results
    */
    SQLFreeStmt (GlobalOdbcData[ProcNum].hstmt, SQL_UNBIND);
    SQLFreeStmt (GlobalOdbcData[ProcNum].hstmt, SQL_RESET_PARAMS);
    SQLFreeStmt (GlobalOdbcData[ProcNum].hstmt, SQL_CLOSE);


    I couldn't test your code with my lines. Hope this will fix the problem.

    Reply
Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • Live Event Date: May 6, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT While you likely have very good reasons for remaining on WinXP after end of support -- an estimated 20-30% of worldwide devices still are -- the bottom line is your security risk is now significant. In the absence of security patches, attackers will certainly turn their attention to this new opportunity. Join Lumension Vice President Paul Zimski in this one-hour webcast to discuss risk and, more importantly, 5 pragmatic risk mitigation techniques …

  • Hurricane Sandy was one of the most destructive natural disasters that the United States has ever experienced. Read this success story to learn how Datto protected its partners and their customers with proactive business continuity planning, heroic employee efforts, and the right mix of technology and support. With storm surges over 12 feet, winds that exceeded 90 mph, and a diameter spanning more than 900 miles, Sandy resulted in power outages to approximately 7.5 million people, and caused an estimated $50 …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds