Error handling in 4D typically follows two paradigms:

• Error trapping via ON ERR CALL
• Checking return values

Note: it is possible to use the ON ERR CALL paradigm in 4D ODBC Pro (via ODBC_SetErrorHandler) but this is not the preferred method.

ODBC programing introduces a third paradigm that combines checking return values with "diagnostic records".

Notice that every 4D ODBC Pro command returns a value. Phase one of handling ODBC errors is of course to check this value but that's only part of the story. When ODBC commands fail, they create diagnostic records. A diagnostic record is an error code for all intents and purposes but is far more robust than a simple scalar value because it contains a plethora of information about the error. For a thorough overview of diagnostic records, please see this page. Here are some important highlights:

• If any ODBC function raises a return code other than SQL_SUCCESS or SQL_INVALID_HANDLE you need to check for diagnostic records (SQL_SUCCESS_WITH_INFO could be considered an exception as long as you understand what has happened).


• Diagnostic records can be created for ODBC environment, connection, statement, or descriptor handles. You might see errors at the environment level if there are problems with the ODBC configuration. You might see connection errors when connecting to the target database. Statement errors occur during statement execution as well as processing results. And so on.


• There are two types of diagnostic records, header and status; be sure to check both!


• The diagnostic records persist until another ODBC function is called, so it's very important to immediately process an error before calling any further ODBC functions.

The important point is that in order to properly handle ODBC errors you need to loop over and collect information from the diagnostic records; of course this implies there can be more than one record too! Here is a very simple example:

$result_l:=ODBC_SQLPrepare ($statementID_l;"SELECT * FROM Employee") If ($result_l#SQL_SUCCESS)    $i:=1    Repeat       $diagResult_l:=ODBC_SQLGetDiagRec (SQL_HANDLE_STMT;$statementID_l;$i;\       $SQLState_t;$nativeError_l;$message_t;$messageLength_l)       If ($diagResult_l=SQL_SUCCESS)           // Do something with the error data.          $i:=$i+1       End if    Until ($diagResult_l=SQL_NO_DATA) End if