need help.Pb to execute procedure sql

cedrikz · #1 (**permalink**) 03-09-05, 04:21

hi,

I have big problem when i execute my sql procedure
I have 2 parameters, and i must call this parameter in my sql statement
My procedure is :

Code:

CREATE PROCEDURE PFJALBL(IN PAR_ACTH_ID INTEGER, IN PAR_NB_ARC VARCHAR(255))
   DYNAMIC RESULT SETS 1
   LANGUAGE SQL
   SPECIFIC PFJALBL
BEGIN
DECLARE stmt varchar(1000);
DECLARE c2 CURSOR FOR s1;
-- FOR SELECT

SET stmt = 'SELECT A.NUM_CIRCULATION_TRANCHE FROM TRHACTH A,TRHARTH B WHERE B.ARTH_ACTH_ID=''PAR_ACTH_ID'' FETCH FIRST '|| PAR_NB_ARC ||' ROWS ONLY';
PREPARE s1 FROM stmt;
EXECUTE s1;
OPEN c2;
END%

i get this message :
SQL0401N The data types of the operands for the operation "=" are not
compatible. SQLSTATE=42818

first : syntax is it correct? I use '||' for call the parameters, this is correct?
call procedure : call PFJALBL(1,'3').

I have try with this another procedure :

Code:

CREATE PROCEDURE PTEST(IN PAR_ACTH_ID INTEGER, IN PAR_NB_ARC VARCHAR(255))
   DYNAMIC RESULT SETS 1
   LANGUAGE SQL
   SPECIFIC PTEST
BEGIN

DECLARE c2 CURSOR WITH RETURN TO CLIENT
FOR SELECT A.NUM_CIRCULATION_TRANCHE FROM TRHACTH A,TRHARTH B WHERE B.ARTH_ACTH_ID=PAR_ACTH_ID FETCH FIRST  PAR_NB_ARC  ROWS ONLY;

OPEN c2;
END%

but the parameter PAR_NB_ARC is not interpreted.
How execute this procedure???

Thanks

sathyaram_s · #2 (**permalink**) 03-09-05, 05:57

What is the datatype of B.ARTH_ACTH_ID column ?

cedrikz · #3 (**permalink**) 03-09-05, 07:35

hello, It's a bigint.

sathyaram_s · #4 (**permalink**) 03-09-05, 08:39

In your first procedure
SET stmt = 'SELECT A.NUM_CIRCULATION_TRANCHE FROM TRHACTH A,TRHARTH B WHERE B.ARTH_ACTH_ID='||CHAR(PAR_ACTH_ID)||' FETCH FIRST '|| PAR_NB_ARC ||' ROWS ONLY';

cedrikz · #5 (**permalink**) 03-09-05, 09:20

I obtain this message.
SQL0518N The statement named in the EXECUTE statement is not in a prepared
state or is a SELECT or VALUES statement. SQLSTATE=07003

So i have add an insert statement, because I have the impression it's impossible to execute a select statement directly.
Do you think my reasoning it's correct?

This is my new code :

Code:

CREATE PROCEDURE PFJALBL(IN PAR_ACTH_ID BIGINT, IN PAR_NB_ARC VARCHAR(255))
   LANGUAGE SQL
   SPECIFIC PFJALBL
BEGIN
DECLARE STMT VARCHAR(1000);
DECLARE TOTO CHAR(100);
DECLARE MESSAGE CHAR(100);
DECLARE TABLE_NAME CHAR(30);
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
   SET MESSAGE = 'ok';

SET STMT = 'DELETE TABLE TRHARCS';
PREPARE S1 FROM STMT;
EXECUTE S1;


SET STMT = 'INSERT INTO TRHARCS '||
'SELECT A.NUM_CIRCULATION_TRANCHE FROM TRHACTH A,TRHARTH B WHERE B.ARTH_ACTH_ID=? '||
'FETCH FIRST '||
PAR_NB_ARC ||' ROWS ONLY';
PREPARE S3 FROM STMT;
EXECUTE S3 USING PAR_ACTH_ID;
END%

Regards.

sathyaram_s · #6 (**permalink**) 03-09-05, 18:13

Have you tried using the debugger to understand where's its going wrong ?

It is strange that a CONTINUE HANDLER is defined for an SQLEXCEPTION

Cheers
Sathyaram

jacampbell · #7 (**permalink**) 03-09-05, 23:09

Your original PFJALBL could have had:

DECLARE c2 CURSOR FOR s1 with return;

SET stmt = 'SELECT A.NUM_CIRCULATION_TRANCHE FROM TRHACTH A,TRHARTH B WHERE B.ARTH_ACTH_ID= cast(? as bigint) FETCH FIRST '|| PAR_NB_ARC ||' ROWS ONLY';
PREPARE s1 FROM stmt;
OPEN c2 using PAR_ACTH_ID;

(ie added with return, added cast(? as bigint), removed execute s1, and added using par_acth_id). And for those that ask, I think one should always use cast(? as <column-data-type>) to create typed parameter markers for input parameters.

There is little point in using the specific name clause for a procedure. It defaults to the procedure's name. (Different for overloaded functions, of course.) It creates something else to go wrong.

Internet Services

Web Development

Digital Marketing

Consumer Tech