Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2003
    Posts
    16

    Unanswered: Dynamic SQL support in DB2 SQL Procedure Language

    HI All,
    I want to write a "DB2 Stored Procedure" which should take a tablename as input and should be able to retrieve its column names and their values and put them into local variables. Can I do this using Dynamic SQL ? The Application development guide of DB2 V7.2 says that DECLARE CURSOR, FETCH, and SELECT INTO statements are not available to be executed dynamically(although these can be executed as static SQL). Is there any way out of this ??
    Regards,
    Buddy Amazing
    IBM Certified DB2 DBA/AppDvlpr

  2. #2
    Join Date
    Nov 2001
    Location
    Finland, Kirkkonummi
    Posts
    17
    Have you tried to do it like this?

    DECLARE sqlText VARCHAR (1024);

    DECLARE cursor1 CURSOR FOR dynstatement;
    (or DECLARE cursor1 CURSOR WITH RETURN FOR dynstatement; if you return it out of procedure)

    SET sqlText = 'SELECT .....whatever ';

    PREPARE dynstatement FROM sqlText;
    OPEN cursor1;

    and do whatever you do with cursors (or just return control out of procedure).

    Hopefully this helps.

    Vesku

  3. #3
    Join Date
    Jan 2003
    Posts
    16
    HI vesku,
    Rightnow I don't have any compiler available to write down and test my own SQL Procedure. So I just wanted to know from your experience whether anybody had success about writing this type of Dynamic SQL program inside a DB2 SQL Procedure. I'm still sceptical about the success of the method suggested by you(although it looks a lot logical), because the DB2 SQL Procedure language is still in its primitive form. Most of the people suggest to go for ESQL programming instead.
    Regards,
    Buddy Amazing
    IBM Certified DB2 DBA/AppDvlpr

  4. #4
    Join Date
    Nov 2001
    Location
    Finland, Kirkkonummi
    Posts
    17
    I've done a few SQL Stored Procedures myself using SQLServer, Oracle and DB2 SQL languages.

    I found a bit better example (although the logic is the same).

    DECLARE sqlText VARCHAR(200);
    DECLARE at_end INTEGER DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';
    DECLARE curFolders CURSOR FOR dynstatement;
    DECLARE CONTINUE HANDLER FOR not_found SET at_end=1;

    DECLARE GLOBAL TEMPORARY TABLE
    SESSION.ParentIDs ( CID INT NOT NULL)
    ON COMMIT PRESERVE ROWS WITH REPLACE NOT LOGGED;

    insert staff into SESSION.ParentIDs

    SET sqlText = 'SELECT ID FROM ';
    SET sqlText = sqlText || ' SESSION.ParentIDs ';
    SET at_end = 0;
    PREPARE dynstatement FROM sqlText;
    OPEN curFolders;
    FETCH FROM curFolders INTO intCID, intTID;
    WHILE at_end=0 DO
    ..whatever..
    FETCH FROM curFolders INTO intCID, intTID;
    END WHILE;
    CLOSE curFolders;

    I had to do this becouse cursors must be defined before temporary tables and thus I could not use temporary table name when defining cursor. The example sql is altered a bit ( I dropped joined tables).

    Vesku

  5. #5
    Join Date
    Nov 2001
    Location
    Finland, Kirkkonummi
    Posts
    17
    A clarify: What I meant when I stated I've done a few procedures myself is that DB2 SQL is not that primitive. It is just slightly different than TSQL. They both have their strenghts and flaws.

    Vesku

Posting Permissions

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