Solved

Using sub-Sproc to supply dataset to parent sProc?

Posted on 2008-10-09
6
418 Views
Last Modified: 2012-05-05
Before I go off into "left-field" chasing something impossible ... let me ask a question  ...

In an sProc, I declare and open a cursor ... like this ...

     CREATE PROCEDURE MyLIB.AA_PROC ()
          P1 : BEGIN
               DECLARE C1 CURSOR FOR SELECT * FROM MyLIB.MyTBL;
               OPEN C1;
          END P1;

If, inside BB_PROC, I wanted to return the results returned from AA_PROC, is that possible?
Meaning, maybe, something like this?

     CREATE PROCEDURE MyLIB.BB_PROC ()
          P1 : BEGIN
               DECLARE C9 CURSOR FOR SELECT * FROM MyLIB.AA_PROC;
               OPEN C9;
          END P1;

If this is possible, could someone show me the proper syntax?
0
Comment
Question by:volking
  • 4
  • 2
6 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22680060
it is possible, but not in the way you wrote it
you will have to see how to read a result set back from a procedure
look here
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyresultsete2.htm
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22680074
0
 
LVL 5

Author Comment

by:volking
ID: 22680329
@momi
You're right, the link you gave (repeated below) is EXACTLY what I'm trying to do ...
     http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyresultsete2.htm

But, there is no SQL source code in the examples?
There is RPG, ODBC, JDBC ... but no SQL?

sigh ... I can keep guessing ... but a Syntax Example in SQL would be great!
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 37

Expert Comment

by:momi_sabag
ID: 22681775
have you checked out the second link?
0
 
LVL 5

Accepted Solution

by:
volking earned 0 total points
ID: 22682338
@momi
yes. The second link is to a hebrew website. But the key command "RETURN TO CLIENT" does not appear anywhere and besides, it talks about COBOL, not SQL ...
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22684456
here you go with the example from the hebrew link :

CREATE PROCEDURE dbload(inout load_statement VARCHAR(1000)
                      , out my_result INTEGER
                      , out sql_state CHAR(5)
                      , out sql_code INTEGER
  )
    SPECIFIC dbload1
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
dbload1: BEGIN
    DECLARE SQLSTATE char(5) default '00000';
    DECLARE SQLCODE  integer default 0;
    DECLARE step INTEGER;
    body: BEGIN
        DECLARE loc RESULT_SET_LOCATOR VARYING;
        DECLARE ROWS_READ BIGINT;
        DECLARE ROWS_SKIPPED BIGINT;
        DECLARE ROWS_LOADED BIGINT;
        DECLARE ROWS_REJECTED BIGINT;
        DECLARE ROWS_DELETED BIGINT;
        DECLARE ROWS_COMMITTED BIGINT;
        DECLARE ROWS_PARTITIONED BIGINT;
        DECLARE NUM_AGENTINFO_ENTRIES BIGINT;
        DECLARE MSG_RETRIEVAL VARCHAR(512);
        DECLARE MSG_REMOVAL VARCHAR(512);


        DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING
        BEGIN
            SET sql_state = sqlstate;
            SET sql_code = sqlcode;
        END;


        SET step = 1;
        IF load_statement = '' THEN
            SET load_statement = 'LOAD FROM (DATABASE core SELECT * FROM
safebox.test) OF CURSOR MESSAGES ON SERVER REPLACE INTO safebox.test';
        END IF;
        CALL SYSPROC.ADMIN_CMD(load_statement);
        SET step = 2;
        ASSOCIATE RESULT SET LOCATOR (loc)
        WITH PROCEDURE SYSPROC.ADMIN_CMD;
        SET step = 3;
        ALLOCATE my_curs CURSOR
        FOR RESULT SET loc;
        SET step = 4;
        OPEN my_curs;
        SET step = 5;
        FETCH FROM my_curs INTO
            ROWS_READ
          , ROWS_SKIPPED
          , ROWS_LOADED
          , ROWS_REJECTED
          , ROWS_DELETED
          , ROWS_COMMITTED
          , ROWS_PARTITIONED
          , NUM_AGENTINFO_ENTRIES
          , MSG_RETRIEVAL
          , MSG_REMOVAL
          ;
        SET step = 6;
        SELECT RESULT_KEY
        INTO my_result
        FROM FINAL TABLE (
            INSERT INTO LOAD_RESULTS (
                    ROWS_READ
                  , ROWS_SKIPPED
                  , ROWS_LOADED
                  , ROWS_REJECTED
                  , ROWS_DELETED
                  , ROWS_COMMITTED
                  , ROWS_PARTITIONED
                  , NUM_AGENTINFO_ENTRIES
                  , MSG_RETRIEVAL
                  , MSG_REMOVAL
               )
               VALUES (
                    ROWS_READ
                  , ROWS_SKIPPED
                  , ROWS_LOADED
                  , ROWS_REJECTED
                  , ROWS_DELETED
                  , ROWS_COMMITTED
                  , ROWS_PARTITIONED
                  , NUM_AGENTINFO_ENTRIES
                  , MSG_RETRIEVAL
                  , MSG_REMOVAL
               )
          );
        SET step = 7;
        CLOSE my_curs WITH RELEASE;
        SET step = 99;
    END body;
    RETURN step;
END dbload1@


0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now