RE: sqlca.sqlcode in stored procedure
Mark
In versions prior to 7.2 (I have actually tried it with 7.1x but I think =
it will work in 5.x as well), you cannot. You can simulate the behavior =
however doing some workarounds. Here it is:
CREATE PROCEDURE myproc(arg1 type1, arg2 type2, ...)
RETURNING returnType1, returnType2, ..., INTEGER, INTEGER;
DEFINE v_status, v_count INTEGER;
BEGIN
ON EXCEPTION set v_status
IF (v_status < 0) THEN
LET returnVal1 =3D NULL;
LET returnVal2 =3D 0; /* initialize returns */
LET v_count =3D 0;
RETURN returnVal1, ..., v_status. v_count;
END IF
END EXCEPTION
LET v_count =3D 0;
LET v_status =3D 0;
SELECT COUNT(*) INTO v_count
FROM myTable
WHERE (myCondition); IF (v_count =3D 0) THEN
LET v_status =3D 100;
RETURN returnVal1, ..., v_status, v_count;
END IF
SELECT myColList INTO returnVal1, ...
FROM myTable
WHERE (myCondition);
RETURN returnVal1, ..., v_status, v_count;
END PROCEDURE;
Note the SQLNOTFOUND is not an exception, so it will not be trapped so =
you must test for it seperately. If you are concerned that COUNT(*) will =
produce too many seqscans, you can try the following code sequence =
instead:
SELECT 1 INTO v_count
FROM myTable
WHERE (myCondition);
and check if v_count is NULL. If it is set it to 0 and set v_status =3D =
100. Your calling program must check v_count and v_status and do the =
appropriate thing.
Of course starting 7.2 (and possibly a parallel 5.x version, check this =
out on your system) the following syntax is supported. The sqlca.sqlcode =
is returned in:
DBINFO("sqlca.sqlerrd2");
HTH
Sujit Pal
----------
From: Mark S. Winsor[SMTP:wmark@pvar.com]
Sent: Wednesday, July 16, 1997 8:09 PM
To: informix-list@rmy.emory.edu
Subject: sqlca.sqlcode in stored procedure
How can I get the value of sqlca.sqlcode in a stored procedure? It
generates an error 201 when I try it. I'm using 5.01 SE.
--
wmark@pvar.com