|
-- At 09:28 04/11/2002, Loyd Goodbar wrote: >Does anyone have a piece of code they would be willing to share? > >Ideally, I'm looking for a subprocedure (called from RPG/IV) that will >accept a SQLCODE and SQLERRM, and return the explanation text from the SQL >message file QSYS/QSQLMSG. Substitution variables would be replaced with >values in SQLERRM. Loyd, This should get you started. When SQLCOD <> SQL_OK, you execute the RtnSqlMsg routine. The message ends up in the DataSpace variable. The code is so minimal, I've never made it into a service procedure, but you could certainly do that. /COPY DSQLENUM * Select format RTVM0100 for the QMHRTVM * (Retrieve message description) API /DEFINE RTVM0100 /COPY DQMHRTVM /COPY DERRSTRUCT * ----------------------------------------------------------------------- * Return an application error message * ----------------------------------------------------------------------- CSR RtnAppMsg begsr C eval Replycode = RP_FAILURE C/EXEC SQL CLOSE SYSTABLES C/END-EXEC C return CSR endsr * ----------------------------------------------------------------------- * Return an SQL error message * ----------------------------------------------------------------------- CSR RtnSqlMsg begsr C eval VM_MsgID = 'SQL' C + %Subst(%EditW(SQLCOD:'0 '):6:4) C eval VM_MsgFile = 'QSQLMSG' C eval VM_MsgFLib = 'QSYS' C eval @VM_ReplaceDta = %Addr(SQLERM) C eval VM_RplDtaLen = %Size(SQLERM) C eval VM_RplSubstVal = '*YES' C call 'QMHRTVM' PL_QMHRTVM C eval DataSpace = %Subst(V1_RtnData:1:V1_MsgLen) C exsr RtnAppMsg CSR endsr /COPY CQMHRTVM That's it. The rest is all copybooks: ******************* CQMHRTVM ******************* * ----------------------------------------------------------------------- * Retrieve Message Description (QMHRTVM) Parameters * Macros List: None * Other copybooks required: * DERRSTRUCT Error return structure * DQMHRTVM Data definitions for QMHRTVM * Structure List: * None * ----------------------------------------------------------------------- C PL_QMHRTVM plist * Returned message information in the format specified in the * format name (VM_Format) parameter C parm VM_MsgInfo * Length of the message information structure C parm VM_MsgLen * Format to use for the message information * Valid values are 'RTVM0100', 'RTVM0200', and 'RTVM0300' C parm VM_Format * Message ID C parm VM_MsgId * Message file name. Subfield VM_MsgFile contains the * file name, VM_MsgFLib contains the library name C parm VM_QualMsgF * Values to insert in the substitution variables in the * message identivied by VM_QualMsgF and VM_MsgId C parm VM_ReplaceDta * Length of the replacement data space C parm VM_RplDtaLen * Replace substitution Values * '*YES', '*NO' C parm VM_RplSubstVal * Return format control characters * '*YES' Return the &N, &P and &B format control characters * within the message * '*NO' Do not return format control characters (Default) C parm VM_RtnFmtCtl * Standard error return format C parm E_ErrorStruct * Retrieve option * '*MSGID' Retrieve the message description specified by * the VM_MsgId parameter * '*NEXT' Retrieve the next message description in the * message file after the message description * specified by the VM_MsgId parameter * '*FIRST' Retrieve the first message description in the * message file. VM_MsgId is ignored. C parm VM_RtvOption * The coded character set identifier that you want your message text * returned in. This only applies to text returned in the message and * message help fields. The following values are allowed: * 0 (default) Use the CCSID of the current job. * 65535 Do not perform any conversion. * CCSID The valid CCSID to be used. C parm VM_MsgCCSID * The coded character set identifier that the supplied replacement * data is in. Valid values are 0, 65535, and any valid CCSID (see * VM_MsgCCSID for additional information). C parm VM_RplCCSID ********************* DERRSTRUCT ********************* /IF DEFINED(INCLUDEHDR) * -------------------------------------------------------------- * Generic API Error Structure * --------------------------- * Information in this copybook is from the AS/400 System API * Reference V4R4, Document Number SC41-5801-03, Section 1.2.4.1 * ------------------------------------------------------------- * Compiler directives * ------------------------------------------------------------- * If compiler directive switch BASED_STRUCT is defined, * this structure will be based on pointer @E_ErrorStruct, * and will not be initialized. In this form, it can be used * as a template for retrieving data from an unformatted * data space. * If compiler directive switch ERRC0200 is defined, error * structure format ERRC0200 is returned. If it is not defined, * structure format ERRC0100 (the original one) is returned. * The most frequent usage of this structure does not * require defining any of the directive switches. * Structure subfileds and the format they are contained in are: * ------------------------------------------------------------- * NAME TYPE FORMAT DESCRIPTION * ------------- ----- --------- -------------------------- * E_BytesProvid U10.0 All Size of the structure * (already set by default) * E_BytesAvail U10.0 All The number of bytes of * data returned if an * error occurred. After * an API call, E_BytesAvail * will be zero if the API * executed successfully * E_CCharKey I10.0 ERRC0200 The key value that enables * the message handler error * function. It is set to -1 * to enable CCHAR support. * E_ExcptCCSID U10.0 ERRC0200 CCSID of CCHAR data * The default (0) is *JOB * E_ExcptData 100 ERRC0100 Message Data for the message * identified in E_MsgID * E_@ExcptData * ERRC0200 Exception data pointer * E_ExcptDtaLen U10.0 ERRC0200 Length of exception data * E_ExcptDtaOfs U10.0 ERRC0200 Offset to exception data * E_MsgId A 7 All Exception error message ID * ------------------------------------------------------------- * Subfields in * ERRC0100 Type ERRC0200 Type * ------------- ---- ------------- ---- * E_BytesProvid In E_CCharKey In * E_BytesAvail Out E_BytesProvid In * E_MsgID Out E_BytesAvail Out * E_ExcptData Out E_MsgID Out * E_ExcptCCSID Out * E_ExcptDtaOfs Out * E_ExcptDtaLen Out * E_@ExcptData Out * ------------------------------------------------------------- /ENDIF /IF NOT DEFINED(E_ERRORSTRUCT) /DEFINE E_ERRORSTRUCT /IF NOT DEFINED(BASED_STRUCT) D E_ErrorStruct DS /IF DEFINED(ERRC0200) * Error Structure Format ERRC0200 D E_CCharKey 10I 0 Inz(-1) /ELSE * Error Structure Format ERRC0100 /ENDIF D E_BytesProvid 10U 0 Inz(%Size(E_ErrorStruct)) D E_BytesAvail 10U 0 Inz D E_MsgId 7 Inz * Unused D 1 /IF DEFINED(ERRC0200) D E_ExcptCCSID 10U 0 Inz D E_ExcptDtaOfs 10U 0 Inz D E_ExcptDtaLen 10U 0 Inz D E_@ExcptData * Inz(*Null) /ELSE D E_ExcptData 100 Inz /ENDIF /ELSE D @E_ErrorStruct S * Inz(*NULL) D E_ErrorStruct DS Based(@E_ErrorStruct) /IF DEFINED(ERRC0200) * Error Structure Format ERRC0200 D E_CCharKey 10I 0 /ELSE * Error Structure Format ERRC0100 /ENDIF D E_BytesProvid 10U 0 D E_BytesAvail 10U 0 D E_MsgId 7 D 1 /IF DEFINED(ERRC0200) D E_ExcptCCSID 10U 0 D E_ExcptDtaOfs 10U 0 D E_ExcptDtaLen 10U 0 D E_@ExcptData * /ELSE D E_ExcptData 100 /ENDIF /ENDIF /ENDIF ************************* DQMHRTVM ************************* * ----------------------------------------------------------------------- * Retrieve Message Description (QMHRTVM) Data Definitions * Macros List: RTVM0100 Return format RTVM0100 (default) * RTVM0200 Return format RTVM0200 * RTVM0300 Return format RTVM0300 * Other copybooks required: * DERRSTRUCT Error return structure * CQMHRTVM Parameter list form QMHRTVM * Structure List: * RTVM0100 Format for returned data * RTVM0200 Format for returned data * RTVm0300 Format for returned data * V3_SbsVarFmt Substitution variable format (based) * ----------------------------------------------------------------------- * Return format RTVM0100 by default /IF NOT DEFINED(RTVM0300) /IF NOT DEFINED(RTVM0200) /DEFINE RTVM0100 /ENDIF /ENDIF * Returned message information in the format specified in the * format name (VM_Format) parameter /IF DEFINED(RTVM0300) D @VM_MsgInfo S * Inz(%Addr(RTVM0300)) /ELSEIF DEFINED(RTVM0200) D @VM_MsgInfo S * Inz(%Addr(RTVM0200)) /ELSEIF DEFINED(RTVM0100) D @VM_MsgInfo S * Inz(%Addr(RTVM0100)) /ENDIF D VM_MsgInfo S 1 Based(@VM_MsgInfo) * Length of the message information structure /IF DEFINED(RTVM0300) D VM_MsgLen S 10I 0 Inz(%Len(RTVM0300)) /ELSEIF DEFINED(RTVM0200) D VM_MsgLen S 10I 0 Inz(%Len(RTVM0200)) /ELSEIF DEFINED(RTVM0100) D VM_MsgLen S 10I 0 Inz(%Len(RTVM0100)) /ENDIF * Format to use for the message information * Valid values are 'RTVM0100', 'RTVM0200', and 'RTVM0300' /IF DEFINED(RTVM0300) D VM_Format S 8 Inz('RTVM0300') /ELSEIF DEFINED(RTVM0200) D VM_Format S 8 Inz('RTVM0200') /ELSEIF DEFINED(RTVM0100) D VM_Format S 8 Inz('RTVM0100') D/ENDIF * Message ID D VM_MsgId S 7 * Message file name. Subfield VM_MsgFile contains the * file name, MsgFLib contains the library name D VM_QualMsgF DS D VM_MsgFile 10 D VM_MsgFLib 10 * Values to insert in the substitution variables in the * message identivied by VM_QualMsgF and VM_MsgId D @VM_ReplaceDta S * Inz(*Null) D VM_ReplaceDta S 1 Based(@VM_ReplaceDta) * Length of the replacement data space D VM_RplDtaLen S 10I 0 Inz * Replace substitution Values * '*YES', '*NO' D VM_RplSubstVal S 10 Inz('*NO') * Return format control characters * '*YES' Return the &N, &P and &B format control characters * within the message * '*NO' Do not return format control characters D VM_RtnFmtCtl S 10 Inz('*NO') * Retrieve option * '*MSGID' Retrieve the message description specified by * the VM_MsgId parameter * '*NEXT' Retrieve the next message description in the * message file after the message description * specified by the VM_MsgId parameter * '*FIRST' Retrieve the first message description in the * message file. VM_MsgId is ignored. D VM_RtvOption S 10 Inz('*MSGID') * The coded character set identifier that you want your message text * returned in. This only applies to text returned in the message and * message help fields. The following values are allowed: * 0 (default) Use the CCSID of the current job. * 65535 Do not perform any conversion. * CCSID The valid CCSID to be used. D VM_MsgCCSID S 10I 0 Inz(*ZEROS) * The coded character set identifier that the supplied replacement * data is in. Valid values are 0, 65535, and any valid CCSID (see * VM_MsgCCSID for additional information). D VM_RplCCSID S 10I 0 Inz(*ZEROS) /IF DEFINED(RTVM0100) * ----------------------------------------------------------------------- * Format RTVM0100 * ----------------------------------------------------------------------- D RTVM0100 DS Inz * Bytes Returned D V1_BytesRtn 10I 0 * Bytes Available D V1_BytesAvail 10I 0 * Length of message returned D V1_MsgLen 10I 0 * Length of message available D V1_MLenAvail 10I 0 * Length of msg help returned D V1_HlpLen 10I 0 * Length of help available D V1_HLenAvail 10I 0 * Returned Data D V1_RtnData 1024 /ENDIF /IF DEFINED(RTVM0200) * ----------------------------------------------------------------------- * Format RTVM0200 * ----------------------------------------------------------------------- D RTVM0200 DS Inz * Bytes Returned D V2_BytesRtn 10I 0 * Bytes Available D V2_BytesAvail 10I 0 * Message Severity D V2_MsgSev 10I 0 * Alert Index * The format number of the message data field. This number * is also called the resource name variable. For more * information, see the Alerts Support book D V2_AlertIdx 10I 0 * Alert Option * Whether and when an SNA alert is created and sent for * the message. Valid values: * *DEFER An alert is sent after local problem analysis * *IMMED An alert is sent immediately when the message * is sent to a message queue that has the allow * alerts attribute set to *YES * *NO No alert is sent * *UNATTEND An alert is sent immediately when the system is * running in unattended mode (when the value of the * alert status network attribute, ALRSTS is *UNATTEND). D V2_AlertOpt 9 * Log Indicator * The log problem indicator for the message retrieved: * N Problem not logged * Y Problem logged D V2_LogInd 1 D 2 * Length of default reply returned D V2_DftRpyLenR 10I 0 * Length of default reply available D V2_DftRpyLenA 10I 0 * Length of message returned D V2_MsgLen 10I 0 * Length of message available D V2_MLenAvail 10I 0 * Length of msg help returned D V2_HlpLen 10I 0 * Length of help available D V2_HLenAvail 10I 0 * Returned Data D V2_RtnData 2048 /ENDIF /IF DEFINED(RTVM0300) * ----------------------------------------------------------------------- * Format RTVM0300 * ----------------------------------------------------------------------- D RTVM0300 DS Inz * Bytes Returned D V3_BytesRtn 10I 0 * Bytes Available D V3_BytesAvail 10I 0 * Message Severity D V3_MsgSev 10I 0 * Alert Index * The format number of the message data field. This number * is also called the resource name variable. For more * information, see the Alerts Support book D V3_AlertIdx 10I 0 * Alert Option * Whether and when an SNA alert is created and sent for * the message. Valid values: * *DEFER An alert is sent after local problem analysis * *IMMED An alert is sent immediately when the message * is sent to a message queue that has the allow * alerts attribute set to *YES * *NO No alert is sent * *UNATTEND An alert is sent immediately when the system is * running in unattended mode (when the value of the * alert status network attribute, ALRSTS is *UNATTEND). D V3_AlertOpt 9 * Log Indicator D V3_LogInd 1 * Message ID D V3_MsgId 7 D 3 * Number of subst variable formats D V3_NbrVarFmt 10I 0 * CCSID conversion status of text: * 0 No conversion was needed CCSID already matched * 1 No conversion needed CCSID Hexidecimal * 2 No conversion occured, insufficient space for text * 3 Text converted using best fit conversion tables * 4 Conversion error occurred using best fit conversion tables * Default conversion completed successfully * -1 An error occurred on both the best fit and default conversions * No data was converted. D V3_CCSIDCvStT 10I 0 * CCSID Conversion Status of Replacement * text (See V3_CCSIDCvStT for codes) D V3_CCSIDCvStR 10I 0 * CCSID of text returned D V3_CCSIDRtn 10I 0 * Offset of default reply D V3_DftRpyOfs 10I 0 * Length of default reply returned D V3_DftRpyLenR 10I 0 * Length of default reply available D V3_DftRpyLenA 10I 0 * Offset of message data D V3_MsgOfs 10I 0 * Length of message returned D V3_MsgLenRtn 10I 0 * Length of message available D V3_MLenAvail 10I 0 * Offset of message help D V3_HlpOfs 10I 0 * Length of msg help returned D V3_HlpLenRtn 10I 0 * Length of help available D V3_HlpLenAvai 10I 0 * Offset of substitution variable formats D V3_SbsOfs 10I 0 * Length of subst var formats returned D V3_SbsLen 10I 0 * Length of subst var formats available D V3_SbsAvail 10I 0 * Length of subst var format element D V3_SbsElmLen 10I 0 * Returned Data D V3_RtnData 32767 * ----------------------------------------------------------------------- * Substitution Variable Format * ----------------------------------------------------------------------- D @V3_SbsVarFmt * Inz(*Null) D V3_SbsVarFmt DS Based(@V3SbsVarFmt) * Length of replacement data for substitution variable D V3_SbsDtaLen 10I 0 * Field size or decimal positions D V3_FieldSize 10I 0 * Substitution variable type * *QTDCHAR Quoted character (enclosed in apostrophes) * *CHAR Character without enclosing apostrophes * *CCHAR Convertible character string * *HEX A string of bytes formatted a hexidecimal value * *SPP A 16-byte space pointer to data in a space object * *DEC A packed decimal number that is formatted in the * message as a signed decimal value with a decimal point * *BIN A binary value that is either 2 or 4 bytes long, and is * formatted in the message as a signed decimal value * *DTS An 8-byte field that contains a system date/time stamp. * The date/time stamp contains the date followed by one * blank separator and then the time * *SYP A 16-byte system pointer to a system object * *ITV An 8-byte binary field that contains the time interval * (in seconds) for wait time-out conditions D V3_VarType 10 /ENDIF ****************************** DSQLENUM ****************************** * ------------------------------------------------------------------------ * DSQLENUM : SQL Code (SQLCOD) enumerated values * List all of the SQL Code values for which data are * successfully returned. * Return values other than SQL_OK indicate that the statment * executed successfully, but that a warning condition exists. * The value of SQLCOD should be checked after execution of each * SQL statement. * If SQLCOD < SQL_OK, the statement did not execute successfully. * This indicates a non-recoverable failure condition that should * not normally be encountered, although several that may be useful * are included at the end of this file * ------------------------------------------------------------------------ * No Errors D SQL_OK C 0 * Correlation without qualification occurred for column D SQL_CRL_NQL C 12 * Number of host variables is incorrect. D SQL_NR_HVAR C 30 * &1 applies to entire table (no where on update) D SQL_ENTIRE_TBL C 88 * End of file or row not found D SQL_EOF C 100 * Relational database &1 not the same as current server &2. D SQL_RLDB_SVR C 114 * Argument &1 of substringing function not valid. D SQL_SST_ARG C 138 * CHECK condition text too long. D SQL_CHECK_LEN C 177 * Query expression text for view &1 in &2 too long. D SQL_EXPR_LEN C 178 * Syntax of date, time, or timestamp value not valid. D SQL_DTTM_STX C 180 * Value in date, time, or timestamp string not valid. D SQL_DTTM_VAL C 181 * Result of date or timestamp expression not valid. D SQL_DTTM_RSLT C 183 * Mixed data not properly formed. D SQL_MIX_DATA C 191 * &1 in &2 type *&3 not found. D SQL_OBJ_ERR C 204 * Not enough SQLVAR entries were provided in the SQLDA. D SQL_DA_VARA C 237 * Not enough SQLVAR entries were provided in the SQLDA. D SQL_DA_VARB C 239 * Conversion error in assignment to host variable &2. D SQL_DTA_CVT C 304 * Too many host variables specified. D SQL_HST_VAR C 326 * Character conversion cannot be performed. D SQL_CHR_CVT C 331 * Characters conversion has resulted in substitution characters. D SQL_CHR_SBT C 335 * Datalink in table &1 in &2 may not be valid due to pending links. D SQL_DTA_LNK C 360 * Alias &1 in &2 created but table or view not found. D SQL_ALIAS_OBJ C 403 * Character in CAST argument not valid. D SQL_CAST_ARG C 420 * Value of parameter &4 in procedure &1 in &2 too long. D SQL_PRM_LEN C 445 * Truncation of data may have occurred for ALTER TABLE in &1 of &2. D SQL_DTA_TRNC C 460 *Not authorized to object &1 in &2 type *&3. D SQL_OBJ_NAUT C 551 * Not authorized to &1. D SQL_NAUT C 552 * Not all requested privileges revoked from object &1 in &2 type &3. D SQL_PRV_NRVK C 569 * Not all requested privileges to object &1 in &2 type &3 granted. D SQL_PRV_NGRT C 570 * Commit level &1 escalated to &2 lock. D SQL_CMT_ESC C 595 * Error occurred during DISCONNECT of relational database &1. D SQL_DSC_ERR C 596 * WHERE NOT NULL clause ignored for index &1 in &2. D SQL_IGN_NNL C 645 * Data conversion or data mapping error. D SQL_DTA_MAP C 802 * Mixed or DBCS CCSID not supported by relational database &1. D SQL_DBCS_NSUP C 863 * Outcome unknown for the unit of work. D SQL_UW_UNK C 990 * Table &1 in &2 created but could not be journaled. D SQL_CRT_NJRN C 7905 * Cursor not open D SQE_NOT_OPEN C -501 * Update prevented by referential constraint &1 in &2. D SQE_CONSTRAINT C -531 * Delete prevented by referential constraint D SQE_CONSTRDLT C -532 * Duplicate key value specified. D SQE_DUPKEY C -803 Pete Hall pbhall@ameritech.net -- * ----------------------------------------------------------------------- * Retrieve Message Description (QMHRTVM) Parameters * Macros List: None * Other copybooks required: * DERRSTRUCT Error return structure * DQMHRTVM Data definitions for QMHRTVM * Structure List: * None * ----------------------------------------------------------------------- C PL_QMHRTVM plist * Returned message information in the format specified in the * format name (VM_Format) parameter C parm VM_MsgInfo * Length of the message information structure C parm VM_MsgLen * Format to use for the message information * Valid values are 'RTVM0100', 'RTVM0200', and 'RTVM0300' C parm VM_Format * Message ID C parm VM_MsgId * Message file name. Subfield VM_MsgFile contains the * file name, VM_MsgFLib contains the library name C parm VM_QualMsgF * Values to insert in the substitution variables in the * message identivied by VM_QualMsgF and VM_MsgId C parm VM_ReplaceDta * Length of the replacement data space C parm VM_RplDtaLen * Replace substitution Values * '*YES', '*NO' C parm VM_RplSubstVal * Return format control characters * '*YES' Return the &N, &P and &B format control characters * within the message * '*NO' Do not return format control characters (Default) C parm VM_RtnFmtCtl * Standard error return format C parm E_ErrorStruct * Retrieve option * '*MSGID' Retrieve the message description specified by * the VM_MsgId parameter * '*NEXT' Retrieve the next message description in the * message file after the message description * specified by the VM_MsgId parameter * '*FIRST' Retrieve the first message description in the * message file. VM_MsgId is ignored. C parm VM_RtvOption * The coded character set identifier that you want your message text * returned in. This only applies to text returned in the message and * message help fields. The following values are allowed: * 0 (default) Use the CCSID of the current job. * 65535 Do not perform any conversion. * CCSID The valid CCSID to be used. C parm VM_MsgCCSID * The coded character set identifier that the supplied replacement * data is in. Valid values are 0, 65535, and any valid CCSID (see * VM_MsgCCSID for additional information). C parm VM_RplCCSID --
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.