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:
- My code expects Oracle SQLCODE 1403 at end of result set processing and not SQLCODE 100 as produced by other databases.
- My code expects z/OS DB2 SQLCODE -811 when a SELECT INTO returns more than one row.
- My code does not expect data truncation warnings after a FETCH statement, but my new database sets SQLCODE 1.
- When inserting a row that results in a duplicate key error, my code expects original database error codes
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:
- It reaches the end of the map, in which case SQLCODE, SQLSTATE, and the error message are left unchanged
- A match is made on some combination of SQLCODE, SQLSTATE, and a substring present in the error message
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
$COBDIR/etc/MicroFocus/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|0},[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.