SQL Error Mapping

The SQL Error Mapping feature enables you to customize how error information is returned to the SQLCODE, SQLSTATE, SQLERR and MFSQLMESSAGETEXT fields in your application. This is done by replacing the values normally returned by both OpenESQL and your database with values you specify. This feature is useful when migrating applications between different databases, and when your application logic depends on receiving specific values for error and warning conditions.
Note: The SQL Error mapping feature also applies to GET DIAGNOSTICS statements, using the data in RETURNED_SQLCODE, RETURNED_SQLSTATE, and MESSAGE_TEXT.

Changing the error code logic in an application containing logic originally designed for a specific database can be cumbersome. Consider these scenarios:

These are just a few simple examples, but error code mapping allows maximum flexibility in preserving the current error handling in your application code. When you provide search criteria based on what the new database returns in error situations (using value 0 when SQLCODE or SQLSTATE values returned don't matter), and specify the error values for the original database, you can ensure that your application receives the error codes it expects.

When error mapping is enabled, it is processed after an embedded SQL statement completes execution. If SQLCODE is non-zero or SQLSTATE is not '00000', the error map is used to determine if SQLCODE, SQLSTATE, and optionally the associated error message, should be replaced with values from the error map. This is done by scanning error map records in order until either of the following conditions is met:

SQL error mapping files

You control error mapping using an error mapping file. This is a simple text file that specifies which error conditions to map, the replacement values for SQLCODE and SQLSTATE, and optionally replacement values for the error message, including complete suppression of the error message. You can specify mappings based on the returned values of SQLCODE, SQLSTATE or a substring within the error message, or any combination of these.

Location
The default location for mapping files is %ProgramData%\Micro Focus\sqlcodes
Note: You can override the default location using the MF_ERRORMAP_PATH system environment variable.
Filename
You can name an SQL error mapping file using any prefix you choose; however, all error mapping files must have an .emap extension.
Contents
Each record in a mapping file contains the following values in this order, delimited by commas:
{SC-ret-val|0},{SS-ret-val|00000},[msg-substr],SC-repl-val,SS-repl-val,[msg-substr-repl-val]
Where:
SC-ret-val|0
The returned database value for SQLCODE, or 0 (zero), to indicate that the returned database SQLCODE value doesn't matter.
SS-ret-val|0
The returned database value for SQLSTATE, or 0 (zero), tto indicate that the returned database SQLSTATE value doesn't matter.
msg-substr
The returned database error message substring, if applicable. Specify a string of characters that appear in the message returned by the database. The error is mapped if the substring is present in the error message, and when the SQLCODE and SQLSTATE conditions are also satisfied. The following syntax rules apply when providing a substring:
  • If the substring contains a comma, enclose the entire substring in single (') or double (') quotes
  • Message substrings are case sensitive
Note: The full error message is used for the substring search rather than the 70 bytes subset returned in SQLERRMC.

Other than providing a substring, you also have these two options:

  • Omit a value by including a space before the next comma delimiter. The original message is returned, effectively switching off error message replacement for substrings.
  • Specify a single tilde (~) character. This populates the message-receiving field, consisting of SQLERRMC, MFSQLMESSAGETEXT (or the host variable for MESSAGE_TEXT with GET DIAGNOSTICS), with spaces. SQLERRML in the SQLCA is also set to zero rather than the number of characters returned in SQLERRMC.
.
SC-repl-val
Original database replacement value for SQLCODE.
SS-repl-val
Original database replacement value for SQLSTATE.
msg-substr-repl-val
Replacement value for the error message, if applicable. Syntax rules for msg-substr also apply to msg-substr-repl-val. When omitted, the initial error message is not replaced. Use a single tilde (¬) character to completely suppress the message.
SQL error mapping record examples
Example 1
This example is based on a migration from DB/2 for z/OS to PostgreSQL. The mapping file record changes the SQLCODE returned when a singleton SELECT returns more than one row from 1 to -811, but leaves the error message intact. It matches by SQLCODE and SQLSTATE:
1, 21000, ,-811, 21000
Example 2
This example is based on a migration from DB/2 for z/OS to PostgreSQL. The record maps errors that contain the string "duplicate" when a primary key or unique constraint error occurs, and changes the error message to "Unique constraint violation". Notice that the return values PostgreSQL provides for both SQLCODE and SQLSTATE don't matter. The search criteria is based solely on the returned PostgreSQL error message alone:
0, 00000,"duplicate", -803, 22002, Unique constraint violation
Example 3
ODBC generates a warning when a host variable is smaller than the returned value. If an application tests for SQLCODE being non-zero rather than negative, this can break application logic. To completely suppress the warning condition, including the error message, the following record matches warnings where SQLSTATE has the value 01004:
0, 01004, , 0, 00000, ~
Example 4
As an alternative to Example 3, when migrating a legacy application to an environment using UTF-8 and you want to test whether your host variables are large enough, the following record changes this warning to an error with SQLCODE -55 and SQLSTATE "22XYZ":
0, 01004, , -55, 22XYZ, Host variable too small

SQL error mapping enablement

Use the ERRORMAP SQL compiler directive option to enable error mapping. See ERRORMAP for details.

If you intend to use multiple error mapping files in one program, see the SET ERRORMAP reference topic for details.