Results 1 to 6 of 6

Thread: Dynamic Cursors

  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Unanswered: Dynamic Cursors

    All,

    I am using DB2, IBM Data Studio Release 2.2.1.0.
    Putting my hands on to stored proc development, here is an issue I face.

    Assume other parts of a Stored Procedure's body are present and absolutely compilable.

    I am declaring a CURSOR
    like below
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT * FROM TBL_1;
    OPEN cursor1;
    declare continue handler for not found set v_found_flg = 'N';

    Above deploys absolutely fine.

    But when I attempt to change above to a dynamic cursor, like below.

    DECLARE e_msg varchar(400);
    DECLARE cursor1 CURSOR WITH RETURN FOR s2;
    SET e_msg = 'SELECT * FROM TBL_1';
    PREPARE s2 FROM e_msg;
    OPEN cursor1;
    declare continue handler for not found set v_found_flg = 'N';

    It is a simple change above, but I get an error on the line next to "OPEN cursor1", says expecting TOKEN. Note: I have not changed any other part of the existing stored procedure. All I did are :
    1. added a varchar declaration
    2. changed the declare cursor line
    3. added a new line of code --> the set e_msg line
    4. added another new line of code, the prepare line you see above

    Have no clue what I've not done rightly.
    Last edited by dbuser29; 10-04-11 at 06:13.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    could you publish the complete message you get..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Oct 2011
    Posts
    3
    Assume this is the very next line after my OPEN cursor1;


    declare continue handler for not found set v_found_flg = 'N';


    the error I get is :
    ILLEGAL USE OF KEYWORD CONTINUE. TOKEN WAS EXPECTED. SQLCODE=-199, SQLSTATE=42601, DRIVER=4.11.69

    As I mentioned, between the static and dynamic approaches, no other part of the program changed.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    i have tried it
    the declare continue handler has to be at the end of the declare section : see below

    CREATE PROCEDURE DB2INST1.TT
    (
    )
    LANGUAGE SQL
    BEGIN
    DECLARE v_found_flg char(1);
    DECLARE e_msg varchar(400);
    DECLARE s2 varchar(400);
    DECLARE cursor1 CURSOR WITH RETURN FOR s2;
    DECLARE CONTINUE HANDLER FOR NOT FOUND set v_found_flg = 'N';
    SET e_msg = 'SELECT * FROM SYSIBM.SYSDUMMY1';
    PREPARE s2 FROM e_msg;
    OPEN cursor1;
    END
    DB20000I The SQL command completed successfully.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Oct 2011
    Posts
    3

    Resolved

    Great. It worked for me too! Thanks for your time.
    Basics ! Basics !! Basics !!!

  6. #6
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    ok
    have a look at doc
    Compound SQL (Procedure)
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Tags for this Thread

Posting Permissions

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