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

    Unanswered: Parameterized ResultSet return to Java

    Hello,

    Im running DB2 v9.5.700.579 on AIX 6.1 SP4

    I'm very new to DB2. I also can't use the search feature of this forum (i get no results), so my apologies if something like this has been asked already.

    Can I do something like this:

    Code:
    CREATE PROCEDURE PROCEDURE1 
    (IN parm INT  )
    	DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure 
    ------------------------------------------------------------------------
    
    P1: BEGIN
    IF parm = 1 THEN
     SELECT * FROM schema.tableA;
    END IF;
     
    IF parm = 2 THEN
    SELECT * FROM schema.tableB;
    END IF;
    
    END P1
    How do I add the cursors to return the result set? I have tried giving each SELECT their own "DECLARE cursorX CURSOR WITH RETURN FOR" statement and opening them both at the end, and repeating the same cursor in each block hoping it would resove it at "run time." Both give me the unexpected token error SQLCODE=-104, SQLSTATE=42601.

    If you know a good example on the web of what I am trying to do I'd really appreciate the link.

    Thanks in advance.
    Last edited by achdumeingute; 01-24-12 at 18:18. Reason: added db2 and server version

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You cannot "add cursors". Either compose your statement dynamically or insert data into a temporary table then return a cursor over that table.

  3. #3
    Join Date
    Jan 2012
    Posts
    16
    I was just trying to get the syntax to wrap the entire thing in a cursor or each select in a cursor. In the end, I've gotten the temp table example to work.

    Code:
    CREATE PROCEDURE PROCEDURE1 
    (IN parm INT  )
    	DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure 
    ------------------------------------------------------------------------
    P1: BEGIN
    
    DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMPVALS (
    COL1 INTEGER
    ) NOT LOGGED;
    
    IF parm = 0 THEN
     	INSERT INTO SESSION.TEMPVALS (SELECT id FROM schema.tableA);
    END IF;
    
    
    IF parm = 1 THEN
     	INSERT INTO SESSION.TEMPVALS (SELECT id FROM schema.tableB);
    END IF;
     
    IF parm = 2 THEN
    	INSERT INTO SESSION.TEMPVALS (SELECT id FROM schema.tableC);
    END IF;
    
    begin
     -- Declare cursor  
    	 DECLARE cursor1 CURSOR WITH RETURN FOR     
    	 SELECT col1 FROM SESSION.TEMPVALS;	 
    	 -- Cursor left open for client application
    	OPEN cursor1;
    end;
    
    END P1
    I'm not a big fan of what I've done here, but it gets me what I want. Deadlines and lack of knowledge about a subject are not fun

    Thank you for your response!

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that using UNION ALL might be better, like...
    Code:
       DECLARE cursor1 CURSOR WITH RETURN FOR
       SELECT id FROM schema.tableA
       WHERE parm = 0
       UNION ALL
       SELECT id FROM schema.tableB
       WHERE parm = 1
       UNION ALL
       SELECT id FROM schema.tableC
       WHERE parm = 2
       ;

    More to say,
    it must be better to incorporate directly such simple query into Java code(or any host programs) than to create a procedure.

    If same query was used in many programs, create a view for the query, like...

    Example 1:
    Code:
    CREATE OR REPLACE VIEW get_id_by_parm AS
    SELECT id , parm
     FROM  schema.tableA
         , (SELECT 0 FROM sysibm.sysdummy1) p(parm)
    UNION ALL
    SELECT id , parm
     FROM  schema.tableB
         , (SELECT 1 FROM sysibm.sysdummy1) p(parm)
    UNION ALL
    SELECT id , parm
     FROM  schema.tableC
         , (SELECT 2 FROM sysibm.sysdummy1) p(parm)
    ;

    The view in Example 1 worked efficiently on my DB2 9.7.5 on Windows/XP,
    if a value for parm was hardcoded.
    (Only the schema.tableB was accessed, if "parm = 1" was specified.)
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT id
     FROM  get_id_by_parm
     WHERE parm = 1
    ;
    ------------------------------------------------------------------------------
    
    ID         
    -----------
            201
    
      1 record(s) selected.

    Extracted from output of db2exfmt
    Code:
    Original Statement:
    ------------------
    SELECT id
    FROM get_id_by_parm
    WHERE parm = 1
    
    
    Optimized Statement:
    -------------------
    SELECT Q1.ID AS "ID"
    FROM SCHEMA.TABLEB AS Q1
    
    Access Plan:
    -----------
            Total Cost:             0.0295303
            Query Degree:           1
    
            Rows
           RETURN
           (   1)
            Cost
             I/O
             |
              1
           IXSCAN
           (   2)
          0.0295303
              0
             |
              1
       INDEX: SYSIBM
     SQL120125114122000
             Q1

    Test data was created by the following:
    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE TABLE schema.tableA
    ( id   INTEGER     NOT NULL PRIMARY KEY
    , desc VARCHAR(30)
    );
    
    CREATE TABLE schema.tableB
    ( id   INTEGER     NOT NULL PRIMARY KEY
    , desc VARCHAR(30)
    );
    
    CREATE TABLE schema.tableC
    ( id   INTEGER     NOT NULL PRIMARY KEY
    , desc VARCHAR(30)
    );
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    INSERT INTO schema.tableA
    ( id )
    VALUES  101 , 102 , 103
    ;
    
    INSERT INTO schema.tableB
    ( id )
    VALUES  201
    ;
       
    INSERT INTO schema.tableC
    ( id )
    VALUES  301 , 302 , 303 , 304 , 305
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    
    DB20000I  The SQL command completed successfully.
    Last edited by tonkuma; 01-25-12 at 20:26. Reason: Remove outer SELECT from Example 1.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    This will work also:

    Code:
    CREATE PROCEDURE PROCEDURE1 
    (IN parm INT  )
    	DYNAMIC RESULT SETS 1
    ------------------------------------------------------------------------
    -- SQL Stored Procedure 
    ------------------------------------------------------------------------
    
    P1: BEGIN
       DECLARE CURSOR1 CURSOR WITH RETURN FOR 
       SELECT * FROM schema.tableA;
    
       DECLARE CURSOR2 CURSOR WITH RETURN FOR 
       SELECT * FROM schema.tableB;
    
       IF parm = 1 THEN 
          OPEN CURSOR1;
       END IF;
     
       IF parm = 2 THEN 
          OPEN CURSOR2;
       END IF;
    
    END P1
    Andy

Posting Permissions

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