CODEWITHSUNDEEP
c++oracle-databaseodbc
bobobobo
bobobobo

Reputation: 67276

Invalid cursor state when attempt to close

I have a large number of INSERT statments to run. As I run them, I understandably get "maximum open cursors exceeded".

OK Oracle, so I will close the cursors immediately after running the INSERT statements.

SQLCloseCursor( hStmt )

But Oracle says to this "Invalid cursor state."

Why is Oracle not happy with me closing the cursor? I tested this same type of query out through a MySQL dsn and MySQL doesn't seem to complain about closing a cursor immediately after an INSERT statement.

edit --

Here is the code that executes the query

CHECK is a function that checks an SQLRESULT and logs error if any returns TRUE if successful, FALSE if failure. "status()" uses SQLGetDiagRec() to log the rest of the error info.

  SQLINTEGER nonquery( char * nonquery )
  {
    SQLINTEGER rowsAffected = 0 ;

    SQLHANDLE hStmt ;
    CHECK( SQLAllocHandle( SQL_HANDLE_STMT, hConn, &hStmt ), "allocate handle for statement" ) ;

    if( !CHECK( SQLExecDirectA( hStmt, (SQLCHAR*)nonquery, SQL_NTS ), "execute query" ) ) 
    {
      status( SQL_HANDLE_STMT, hStmt, __LINE__ ) ;
    }

    // Get rows affected
    if( !CHECK( SQLRowCount( hStmt, &rowsAffected ), "row count after non-query" ) )
    {
      status( SQL_HANDLE_STMT, hStmt, __LINE__ ) ;
    }

    if( ! CHECK( SQLFreeStmt( hStmt, SQL_CLOSE ), "Sql free stmt" ) )
    {
      status( SQL_HANDLE_STMT, hStmt, __LINE__ ) ;
    }

    // CLose up.
    if( !CHECK( SQLCloseCursor( hStmt ), "close cursor" ) )
    {
      status( SQL_HANDLE_STMT, hStmt, __LINE__ ) ;
    }

    return rowsAffected ;
  }

I call nonquery like so

nonquery( "sql statement" ) ;

So I'm getting:

[24000][0] [Oracle][ODBC]Invalid cursor state.

At first, (which might be due to an INSERT statement has no cursor?), and later, after many inserts (nonquery is simply called many, many times in a row), I get

ORA-01000: maximum open cursors exceeded

Upvotes: 1

Views: 3443

Answers (3)

anon
anon

Reputation:

I have a large number of INSERT statments to run. As I run them, I understandably get "maximum open cursors exceeded".

Actually, this doesn't make sense to me - INSERT statements don't use a cursor. Are you sure this is the error you are getting This would explain why you get:

SQLCloseCursor( hStmt )

But Oracle says to this "Invalid cursor state."

as there would be no cursor.

The fact that MySQL doesn't complain could be due to differences in the drivers. Are they both ODBC 3.0?

Edit: Having looked at your code, I have two questions. Firstly, could we see the SQL commands that cause the problem? Secondly, SQLRowCount is a somewhat dubious function - many databases cannot support it for certain query types, and I can see how it might need a cursor itself. Can you try using a version of your function that doesn't call SQLRowCount?

Edit2: Think that Alan has identified your problem. You free a statement and then call clopse cursor on it - this is undefined by ODBC. If you really think you need to close a cursor (which I don't) close it before freeing the statement.

Upvotes: 1

Alan
Alan

Reputation: 13731

I think you're closing the cursor twice:

SQLFreeStmt(SQL_ CLOSE) - From the MSDN: "Closes the cursor associated with StatementHandle (if one was defined) and discards all pending results"

Therefore, calling SQLCloseCursor will return a "Invalid Cursor State" (see Note).

I think what you need is:

SQLCloseCursor(hStmt);

SQLFreeHandle(SQL_HANDLE_STMT,hStmt) // replace SQLFreeStmt with this

Upvotes: 1

Gary Myers
Gary Myers

Reputation: 35401

Inserts do use a cursor. If you are doing lots of inserts, you should be reusing the cursor. The pattern should be

OPEN cursor
  start loop
     BIND variables
     EXECUTE CURSOR
  end loop
CLOSE cursor

In your case, I don't see an explicit open cursor, so I'd guess you are relying on c++ to manage that implicitly, and it doesn't seem to be doing a good job. Judging by the code here you need to fit SQLPrepare into the logic.

Upvotes: 0

Related Questions