Can I call SQLExecute after SQLFreeStmt without SQLPrepare?

Question

I have the following sequence of code calls:

SQLPrepare
SQLExecute(hstmt, SQL_CLOSE);
SQLFreeStmt
//It works till here
SQLExecute //Now it fails.

Why am I required to call SQLPrepare again, I just freed the cursor. I shouldn't prepare the SQL statement again.

Answer 1

SQLFreeStmt(hstmt, SQL_CLOSE) cleans up everything to do with that statement handle, take a look at the summary:

SQLFreeStmt stops processing associated with a specific statement, closes any open cursors associated with the statement, discards pending results, or, optionally, frees all resources associated with the statement handle.

If you don't want to use SQLPrepare again you can use SQLExecDirect instead.

Answer 2

The correct behaviour is that SQLPrepare/SQLExecute/SQLFreStmt(stmt, SQL_CLOSE) should allow another SQLExecute on the same stmt handle without re-preparing. You can see this as a valid state transition in the ODBC Programmers Guide. You might use this sequence if you had done a SQLPrepare(sql) and only fetched some of the rows (instead of all of them) as without the SQLFreeStmt(stmt, SQL_CLOSE) or fetching until SQL_NO_DATA was returned you'd get an invalid cursor state if you issued another SQLExecute. SQLFreeStmt(stmt, SQL_DROP) is equivalent to SQLFreeHandle(SQL_HANDLESTMT,stmt) and frees the entire stmt handle meaning you cannot use it again at all.