Tweet
CREATE PROCEDURE XNSTEST.NST () LANGUAGE SQL SPECIFIC XNSTEST.NST BEGIN DECLARE V1 VARCHAR(70); SET V1 = 'AAA'; SIGNAL SQLSTATE '01Z00' SET MESSAGE_TEXT = V1; END;
-
-
SQL system error when reCALLing an SQL Stored Procedure with a SIGNAL statement
Hello everybody, I would like to report a problem I'm facing with SQL Stored Procedures in iSeries (V5R3): if a Warning or Exception is signalled by the procedure, specifying a variable to hold the returned MESSAGE_TEXT, repeating the call from the same session results to SQLCODE -901 (SQL system error). As indicated in the corresponding job log, an attempt is made to create an already existing file member QTEMP/QAP0ZDMP(QP0Z0[jobnumber]). This does not happen if MESSAGE_TEXT value is assigned by means of a constant. It also does not happen using the RESIGNAL statement from within a handler. I include a sample procedure that cal be used (except for lib/proc name) to show the case. Any help appreciated! Thanks in advance, Nikolaos Soumelides
Comment