|
midrange.com
--
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-2025 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.
