Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2005
    Posts
    5

    Unhappy Unanswered: need help.Pb to execute procedure sql

    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

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What is the datatype of B.ARTH_ACTH_ID column ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2005
    Posts
    5
    hello, It's a bigint.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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';
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Mar 2005
    Posts
    5
    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.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    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
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jan 2005
    Posts
    191
    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.
    Last edited by jacampbell; 03-10-05 at 01:15.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •