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
