>ODBC application must call FreeStmt(SQL_CLOSE) and >FreeStmt(SQL_RESET_PARAMS). But VFP doesn't call it.
the ODBC spec is unclear if SQLCancel frees any statement data like bound parameters or columns.
since there is a special function to do this, I think it's the responsibility of the ODBC client (VFP in this case) to make sure that a statement is in the correct state. Since VFP caches a common statement handle that it reuses, it has to call SQLFreeStmt(SQL_UNBIND) and SQLFreeStmt(SQL_RESET_PARAMS) to make sure the cached statement handle is in a cleared state before reusing it. VFP indeed calls these functions when all goes well, but when an error occurs in SQLExecuteDirect it just calls SQLCancel and then returns from SQLEXEC.
"Unbinding parameter markers: All parameters that SQLBindParameter() binds remain bound until you perform one of the following actions: * Call SQLFreeHandle() with the HandleType argument set to SQL_HANDLE_STMT. * Call SQLFreeStmt() with the fOption argument set to SQL_RESET_PARAMS. * Call SQLBindParameter() again for the same parameter ipar number. "
SQLCancel is not mentioned there either to have any affect on bound parameteres/columns.
the right calls are issued to reuse a cached statement handle!
At least for me this is a bug in VFP, which is caused by the fact that it caches a common statement handle but doesn't reset it correcly when an error occurs.