October 18, 2007, 05:40 PM
Francis MarianiCapture SQL error text
A FOCUS error code is captured in DM variable &FOCERRNUM.
This can be used to determine the error text by doing:
-SET &FOC_ERR_MSG = FEXERR(&ERRORNUM,'A100');
A SQL error code is captured in &RETCODE.
Is there any way to capture the text of the SQL error?
I would love to be able to capture some of the following in a DM variable.
(FOC1400) SQLCODE IS -551 (HEX: FFFFFDD9)
: [42501] [IBM][CLI Driver][DB2/6000] SQL0551N "MZINSER" does not have th
: e privilege to perform operation "SELECT" on object "EQP.EQ_USER". SQLS
: TATE=42501
Thanks,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
October 19, 2007, 06:12 AM
GamPFrancis,
A search in the ibi database revealed that this is not possible.
Having said that, there is of course a workaround thinkable.
This involves writing traces to a pre-destined location and then process these traces.
I have created an example of this (mis)using one of my odbc connections.
The code I came up with is below:
SET TRACEUSER = C:/IBI/APPS/SESSION/USERTRC.TRC
SET TRACEUSER=ON
SET TRACEOFF=ALL
SET TRACEON=SQLAGGR/1/FSTRACE
SET TRACEON=STMTRACE/1/FSTRACE
SET TRACEON=SQLDI/2/FSTRACE
-RUN
SQL SQLODBC
SELECT NAAM, BEDRIJF, TESTVELD
FROM ADRESLIJST;
END
-SET &ERRNUM = &FOCERRNUM;
TABLE FILE SQLOUT
PRINT *
END
SET TRACEUSER=OFF
SET TRACEOFF=ALL
-IF &ERRNUM EQ 0 THEN GOTO :ENDFEX;
APP FI USERTRC DISK SESSION/USERTRC.TRC (LRECL 213 RECFM V
FILEDEF MAS DISK USERTRC.MAS
-RUN
-WRITE MAS FILENAME=TRACE, SUFFIX=FIX
-WRITE MAS SEGNAME=TRACE
-WRITE MAS FIELD=DUMMY, FORMAT=A1 , ACTUAL=A1 , $
-WRITE MAS FIELD=TIME , FORMAT=A8 , ACTUAL=A8 , $
-WRITE MAS FIELD=DUMMY, FORMAT=A1 , ACTUAL=A1 , $
-WRITE MAS FIELD=CODE , FORMAT=A2 , ACTUAL=A2 , $
-WRITE MAS FIELD=DUMMY, FORMAT=A1 , ACTUAL=A1 , $
-WRITE MAS FIELD=LINE , FORMAT=A200, ACTUAL=A200, $
-RUN
DEFINE FILE USERTRC
REGEL/A200 = IF LINE CONTAINS 'SQLError errtxt'
THEN SUBSTR(200,LINE,29,150,122,REGEL)
ELSE IF EDIT(LINE,'99') EQ ' '
THEN SUBSTR(200,LINE,3,150,148,REGEL)
ELSE LINE;
END
TABLE FILE USERTRC
PRINT REGEL
WHERE LINE CONTAINS 'SQLError errtxt' OR
EDIT(LINE,'99') EQ ' ';
ON TABLE HOLD
END
-RUN
-SET &ERRMSG = '';
-:LOOP
-READ HOLD &LINE.200
-IF &IORETURN NE 0 THEN GOTO :ENDLOOP;
-SET &ERRMSG = &ERRMSG || (' ' | &LINE);
-GOTO :LOOP
-:ENDLOOP
-TYPE &ERRMSG
-:ENDFEX
Hope you can make use of this example.
October 19, 2007, 10:25 AM
Francis MarianiGamP, thanks very much for your example, I will give it a try.
Cheers,
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server