SEO

I'm trying to get the SQLERRMC info from the SQLCA into my SP so that I
can use the information it provides. I'm successfully getting the
SQLCODE and SQLSTATE and so added the SQLERRMC to the code which
obtains these. It doesn't seem to work however -

create procedure dbair001.sp001testerr
(
,OUT p_sqlstate CHAR(5)
,OUT p_sqlcode INTEGER
,OUT p_sqlerrmc VARCHAR(70)
)
specific dbair001.sp001testerr
begin
--
-- Declare return codes
--
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE SQLERRMC VARCHAR(70);
--
-- Define working storage
--
declare v_dynstmt VARCHAR(100);
--
-- Define error handlers
--
DECLARE EXIT HANDLER FOR SQLEXCEPTION
SELECT
SQLSTATE,
SQLCODE,
SQLERRMC
INTO
p_sqlstate,
p_sqlcode,
p_sqlerrmc
FROM
sysibm.sysdummy1;
--
-- Start of procedure logic
--
-- Initialize output parameters with defaults
--
VALUES (SQLSTATE,SQLCODE, SQLERRMC) INTO p_sqlstate, p_sqlcode,
p_sqlerrmc;
--
-- Set up SQL to unknown table
--
SET v_dynstmt = 'INSERT INTO DBAIR001.T0010TEST (COL1) VALUES
(1)';
PREPARE v_prepstmt FROM v_dynstmt;
EXECUTE v_prepstmt;
end#

This doesn't work however -

bash-2.05b$ db2 "call dbair001.sp001testerr(?,?,?)"

Value of output parameters
--------------------------
Parameter Name : P_SQLSTATE
Parameter Value : 42704

Parameter Name : P_SQLCODE
Parameter Value : -204

Parameter Name : P_SQLERRMC
Parameter Value : -

Return Status = 0

Any idea what I'm doing wrong ?

Thanks

Phil Nelson

Use the GET_DIAGNOSTICS statement:
http://publib.boulder.ibm.com/infoce...n/r0005647.htm

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

Serge Rielau wrote:

> Use the GET_DIAGNOSTICS statement:
>
http://publib.boulder.ibm.com/infoce...n/r0005647.htm
>
> Cheers
> Serge
>

Thanks Serge : I should have remembered that !!!

Phil