Invalid cursor state when attempt to close

Answer 1

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.

Answer 2

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.

Answer 3

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