When a SQL error occurs, &RETCODE contains the SQL error code.

I'd like to capture the text of a SQL error into a Dialogue Manager variable so I can display it in a HTML page, along with the SQL error code.

Example:

(FOC1394) CONNECT FAILURE 
(FOC1400) SQLCODE IS -1032 (HEX: FFFFFBF8) 
: [08001] [IBM][CLI Driver] SQL1032N  No start database manager command wa
: s issued.  SQLSTATE=57019  
(FOC1406) SQL OPEN CURSOR ERROR. 

&RETCODE is set to -1032. I'd like to capture the message "[IBM][CLI Driver] SQL1032N No start database manager command was issued. SQLSTATE=57019".

I imagine this is not possible.

I find it interesting that the documentation "Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS - Version 7 Release 6.4" makes no mention at all about &RETCODE.

This message has been edited. Last edited by: Francis Mariani,

This code i just the code WF get's back from the datasource.

I think you'll have better luck if you get this text from a trace file in the edatemp directory.

I actually had a ticket in on this since we were only getting part of the message. The trick was SET TRACEON = NWH//FSTRACE. Code below dumps the output into a file which you can then parse.

Kind of clunky and really should be straigthforward like populating a system variable - but so it goes...


-*------------------------------------------------------------------------
-* a bunch of statements I tried
-*------------------------------------------------------------------------
-*SET TRACEWRAP = 132
-*SET TRACEON = SQLAGGR//FSTRACE
-*SET TRACEON = SQLDI//FSTRACE
-*SET TRACEON = STMTRACE//FSTRACE
-*SET TRACEON = STMTRACE/1/FSTRACE
-*SET TRACEON = SQLTRANS/1/FSTRACE
-*SET TRACEON = SQLLI/1/FSTRACE
-*SET TRACEON = NWH2/1/FSTRACE
-*SET TRACEON = SQLCALL//FSTRACE
-*SET TRACESTAMP= OFF
SET TRACEUSER = ON
SET TRACEUSER = D:\IBI\APPS\MYAPP\usertrc.trc
SET TRACEOFF = ALL
SET TRACEON = NWH//FSTRACE

SET SQLENGINE = SQLMSS
SQL SET SERVER DEV_DV20_OLTP
SQL SET CONVERSION LONGCHAR ALPHA
SQL SET VARCHAR OFF
SQL SET DATETIME OFF
-RUN
SQL
SELECT DISTINCT Name AS 'ProductName'
, ColumnThatDoesNotExist
FROM AdventureWorks.Production.Product
ORDER BY Name
;
TABLEF FILE SQLOUT
PRINT ProductName
ON TABLE HOLD AS HSYS_PN1
END
-RUN
SET TRACEOFF = ALL
SET TRACEUSER = OFF
-EXIT


Francis very few systems keep their messages in tables however this is a solution that works for Oracle
and depends on internal function SQLERRM. SQLERRM exists in DB2 as well so you may well be able to modify it.

Create an object using the following code

 
create type t_oracle_error is object (
error_num varchar2(9),
error_desc varchar2(1024)
);

create type t_oracle_error_tab is table of t_oracle_error;

create or replace function OracleErrors return t_oracle_error_tab pipelined is
  l_error t_oracle_error := t_oracle_error(null,null);
begin
  for errNo in reverse -32799..0 loop
    l_error.error_num := errNo;
    l_error.error_desc := sqlerrm(errNo);
    if l_error.error_desc not like '%Message % not found;%' and l_error.error_desc != 'ORA'||to_char(errNo,'FM09999')||': ' then
      pipe row(l_error);
    end if;
  end loop;
  return;
end OracleErrors;

then a simple passthru request like

SQL SQLORA select * from table(oracleerrors) WHERE ERROR_NUM = &RETCODE;
TABLE FILE SQLOUT
PRINT *
ON TABLE SAVE
END
-RUN
-READ SAVE &X.A21. &ERROR.A10. &ERROR_TXT.A1000
-TYPE &ERROR // &ERROR_TXT

Will return the error number and text which you can use as a !IBI.AMP value in an HTML form.

PS. did a quick check and MSSQL does not apear to have SQLERRM or an alternative available.

RickW, thanks for the suggestion, but I'd rather not set traces on for each report request.

JG, unfortunately, the people in control will not create a stored procedure for me, so that's out.

There is a server or client setting where we can select the error message type - FOCUS or DBMS, but I can't seem to find this described anywhere, and can't find it on either the client or server console.

This message has been edited. Last edited by: Francis Mariani,

Once you've caught the messages in the trace file and determined that they should be revealed, this code will find and display them neatly:

-*******************************************************************************
-* set up specimen data
APP FI usertrc DISK usertrc.FTM
-RUN
-WRITE usertrc 123456789.123456789.123456789.123456789.123456789.123456789.123456789.123456789.
-WRITE usertrc (FOC1394) CONNECT FAILURE
-WRITE usertrc (FOC1400) SQLCODE IS -1032 (HEX: FFFFFBF8)
-WRITE usertrc : [08001] [IBM][CLI Driver] SQL1032N No start database manager command wa
-WRITE usertrc : s issued. SQLSTATE=57019
-WRITE usertrc (FOC1406) SQL OPEN CURSOR ERROR.
-RUN
-*******************************************************************************

 APP FI tracemfd DISK usertrc.MAS
-RUN
-WRITE tracemfd FILENAME=usertrc,SUFFIX=FIX,$
-WRITE tracemfd SEGNAME=ONLY,SEGTYPE=S0,$
-WRITE tracemfd FIELD=TEXT,TEXT,A80,$
-RUN
 DEFINE FILE usertrc
  ?/I1S=IF EDIT(TEXT,'9') EQ '(' THEN 10 ELSE IF EDIT(TEXT,'99') EQ ': ' THEN 20 ELSE 0;
  Msg#/D5.0=IF ? EQ 10 THEN LAST Msg# + 1 ELSE LAST Msg#;
  Message/A1000V = IF ? EQ 10 THEN TEXT ELSE IF ? EQ 20 THEN LAST Message || SUBSTR(80,TEXT,3,80,78,'A78') ELSE ' ';
 END
 TABLE FILE usertrc
  WRITE Message
  BY Msg#
  IF Message NE ' '
 ON TABLE SET PAGE-NUM OFF
 ON TABLE SET STYLE *
  UNITS=IN, SQUEEZE=ON, ORIENTATION=LANDSCAPE, $
  TYPE=REPORT, GRID=ON, FONT='COURIER NEW', SIZE=9, $
 ENDSTYLE
 END

Output for the specimen:

Msg# Message 
1.   (FOC1394) CONNECT FAILURE 
2.   (FOC1400) SQLCODE IS -1032 (HEX: FFFFFBF8)[08001] [IBM][CLI Driver] SQL1032N No start database manager command was issued. SQLSTATE=57019 
3.   (FOC1406) SQL OPEN CURSOR ERROR. 

BREAKTHROUGH!

I returned to this issue today, and prompted by JG's suggestion of a stored procedure, I tried a little harder to solve the puzzle. I think I have a 90% solution, which I am happy with for the moment.

DB2 has a stored procedure called SQLCAMESSAGE that returns the text for a SQL error.

As per the "Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS, Version 7 Release 6.5" manual, chapter "Generating a Synonym for a Stored Procedure", I created a synonym for stored procedure SYSIBM.SQLCAMESSAGE. It took a bit of work figuring out how to use the Master. Thankfully, I have AQT (Advanced Query Tool), an ODBC-compliant database query tool. From running the stored procedure in AQT, I determined that I need to pass many more parameters than the SQL error code - this helped me write the fex. Of course, elements are missing in the returned error message text, like User ID or Table Name, but since this is also the case with the message text returned by FOCUS command ? 9999, I can live with it.

The Master file, sysibm_sqlcamessage.mas:

FILENAME=SYSIBM_SQLCAMESSAGE, SUFFIX=DB2     , $
  SEGMENT=INPUT, SEGTYPE=S0, $
    FIELDNAME=SQLCODE, ALIAS=P0001, USAGE=I11, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLERRML, ALIAS=P0002, USAGE=I6, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLERRMC, ALIAS=P0003, USAGE=A70, ACTUAL=A70,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLERRP, ALIAS=P0004, USAGE=A8, ACTUAL=A8,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLERRD0, ALIAS=P0005, USAGE=I11, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLERRD1, ALIAS=P0006, USAGE=I11, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLERRD2, ALIAS=P0007, USAGE=I11, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLERRD3, ALIAS=P0008, USAGE=I11, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLERRD4, ALIAS=P0009, USAGE=I11, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLERRD5, ALIAS=P0010, USAGE=I11, ACTUAL=I4,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLWARN, ALIAS=P0011, USAGE=A11, ACTUAL=A11,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=SQLSTATE, ALIAS=P0012, USAGE=A5, ACTUAL=A5,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=MESSAGEFILENAME, ALIAS=P0013, USAGE=A20, ACTUAL=A20,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
    FIELDNAME=LOCALE, ALIAS=P0014, USAGE=A33, ACTUAL=A33,
      MISSING=ON, ACCESS_PROPERTY=(NEED_VALUE), $
  SEGMENT=OUTPUT, SEGTYPE=S0, PARENT=INPUT, $
    FIELDNAME=RETURN_CODE, ALIAS=P0000, USAGE=I11, ACTUAL=I4,
      MISSING=ON,
      TITLE='Return Code', $
    FIELDNAME=LOCALE, ALIAS=P0014, USAGE=A33, ACTUAL=A33,
      MISSING=ON, $
    FIELDNAME=MESSAGE, ALIAS=P0015, USAGE=A4096, ACTUAL=A4096,
      MISSING=ON, $
    FIELDNAME=RCODE, ALIAS=P0016, USAGE=I11, ACTUAL=I4,
      MISSING=ON, $

The Access file, sysibm_sqlcamessage.acx:

 SEGNAME=INPUT, CONNECTION=BSL, STPNAME=SYSIBM.SQLCAMESSAGE, $
 SEGNAME=OUTPUT, STPRESORDER=0, $

The fex:

-SET &ECHO=ALL;

-DEFAULT &RETCODE = -551;

-SET &SQL_ERROR = &RETCODE;

-IF &SQL_ERROR EQ 0 GOTO NO_SQL_ERROR;

TABLE FILE SYSIBM_SQLCAMESSAGE
PRINT
OUTPUT.MESSAGE/A400
WHERE INPUT.SQLCODE EQ &SQL_ERROR
AND INPUT.SQLERRML EQ 0
AND INPUT.SQLERRMC IS MISSING
AND INPUT.SQLERRP IS MISSING
AND INPUT.SQLERRD0 EQ 0
AND INPUT.SQLERRD1 EQ 0
AND INPUT.SQLERRD2 EQ 0
AND INPUT.SQLERRD3 EQ 0
AND INPUT.SQLERRD4 EQ 0
AND INPUT.SQLERRD5 EQ 0
AND INPUT.SQLWARN IS MISSING
AND INPUT.SQLSTATE IS MISSING
AND INPUT.MESSAGEFILENAME IS MISSING
AND INPUT.LOCALE EQ 'en_US.iso88591';
ON TABLE HOLD AS HSQLOUT
END
-RUN

-READ HSQLOUT &SQLERRMSG.A400

-TYPE SQL error: &SQL_ERROR - &SQLERRMSG
-EXIT

-NO_SQL_ERROR
-TYPE No SQL error

System variable &RETCODE will contain the SQL error after a database read. Pass &RETCODE to SQLCAMESSAGE.