PLI: How to Convert an SQL Error into Text Message

You can use the subroutine DSNTIAR to convert an SQL return code into a text message. DSNTIAR takes data from the SQLCA, formats it into a message, and places the result in a message output area that you provide in your application program.


DSNTIAR syntax: By calling the DSNTIAR module in the PLI program you can covert error message into Text Message.

CALL DSNTIAR ( sqlca, message, lrecl );

The DSNTIAR parameters have the following meanings:
SQLCA – An SQL communication area.

Message

An output area, in VARCHAR format, in which DSNTIAR places the message text. The first halfword contains the length of the remaining area; its minimum value is 240.

The output lines of text, each line being the length specified in LRECL, are put into this area. For example, you could specify the format of the output area as:

DCL DATA_LEN     FIXED BIN(31) INIT(132);
DCL DATA_DIM     FIXED BIN(31) INIT(10);
DCL 1 ERROR_MESSAGE AUTOMATIC,
    3 ERROR_LEN FIXED BIN(15) UNAL INIT((DATA_LEN*DATA_DIM)),
    3 ERROR_TEXT(DATA_DIM) CHAR(DATA_LEN);
⋮
CALL DSNTIAR ( SQLCA, ERROR_MESSAGE, DATA_LEN );

Where ERROR_MESSAGE is the name of the message output area, DATA_DIM is the number of lines in the message output area, and DATA_LEN is the length of each line.

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.