Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2010
    Posts
    4

    Unanswered: Procedure not returning resultset

    I am attempting to select the first 50 rows only of data and then update two columns and return the resultset of the rows that were updated to my calling application. The procedure below gets the rows and performs the update but does not return the resultset. Can someone explain to me what I am doing wrong that is causing the result set not to be returned?

    CREATE PROCEDURE PROCESSVENDORPO
    (in p_source varchar(50), in p_channel varchar(50), out p_rowcount integer)
    DYNAMIC RESULT SETS 1
    SPECIFIC PROCESSVENDORPO
    LANGUAGE SQL MODIFIES SQL DATA
    tr: begin
    DECLARE v_sqlstatus INTEGER DEFAULT 0;
    DECLARE v_counter INTEGER DEFAULT 0;
    DECLARE v_vendor_po_id integer;
    DECLARE v_orderid CHAR(30);

    DECLARE c1 CURSOR WITH RETURN FOR
    select vendor_po_id, orderid from vendor_po where source=p_source and channel=p_channel order by orderdate
    FETCH FIRST 50 ROWS ONLY;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_sqlstatus = -1;
    OPEN c1;

    loop_over_po:
    LOOP
    FETCH FROM c1
    INTO v_vendor_po_id, v_orderid;
    IF v_sqlstatus = -1 THEN
    LEAVE loop_over_po;
    END IF;
    Update vendor_po set state='InProcess' where vendor_po_id = v_vendor_po_id;
    SET v_counter = v_counter + 1;
    END LOOP loop_over_po;
    SET p_rowcount = v_counter;

    end tr

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    If you want to return the resultset, you open the curosor and then return .. You are not allowed to fetch from it within the procedure.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Nov 2010
    Posts
    4

    Update result set

    Ok so can I update the entire result set with out using a FETCH and then return the result set? and if so how?

  4. #4
    Join Date
    Nov 2010
    Posts
    4

    Eureka!

    So I figured out my problem. I modified the input so I could pass in the number of rows I wanted to select and update instead of having that value hardcoded. So this procedure will will take in three parameters to use in my where clause then update the rows and return the result set of the updated rows and the rowcount of the rows that were updated.

    CREATE PROCEDURE PROCESSVENDORPO
    (in p_source varchar(50), in p_channel varchar(50),in p_batchsize integer, out p_rowcount integer)
    DYNAMIC RESULT SETS 1
    SPECIFIC HARLEDB.PROCESSVENDORPO
    LANGUAGE SQL MODIFIES SQL DATA
    tr: begin
    DECLARE v_sqlstatus INTEGER DEFAULT 0;
    DECLARE SQLCODE INTEGER DEFAULT 0;
    DECLARE v_counter INTEGER DEFAULT 0;
    DECLARE v_vendor_po_id integer;
    DECLARE v_orderid CHAR(30);
    DECLARE v_orderitemid CHAR(30);
    DECLARE v_orderpayload VARCHAR(16000);
    DECLARE v_orderdate TIMESTAMP;
    DECLARE v_currenttime TIMESTAMP;
    DECLARE v_receiveddate TIMESTAMP;
    DECLARE v_destination CHAR(250);
    DECLARE v_priority CHAR(30);
    DECLARE v_sql VARCHAR(300);
    DECLARE v_batchsize char(4);
    DECLARE v_stmt statement;

    DECLARE c1 cursor for v_stmt;


    DECLARE c2 CURSOR WITH RETURN FOR
    select vendor_po_id, orderid, orderitemid, orderpayload, orderdate, receiveddate, destination, priority
    from harledb.vendor_po where state='InProcess' and LASTUPDATEDDATE=v_currenttime;

    set v_currenttime=current timestamp;

    set v_batchsize = RTRIM(CHAR(p_batchsize));

    set v_sql='select vendor_po_id, orderid, orderitemid, orderpayload, orderdate, receiveddate, destination, priority'||
    ' from harledb.vendor_po where source='|| '''' || p_source||''''||
    ' and channel='|| ''''|| p_channel||''''||
    ' and state='||'''Waiting'''||
    ' and type='||'''New'''||
    ' order by orderdate FETCH FIRST '||
    v_batchsize||
    ' ROWS ONLY';

    prepare v_stmt from v_sql;

    OPEN c1;


    loop_over_po:
    LOOP
    FETCH FROM c1
    INTO v_vendor_po_id, v_orderid, v_orderitemid, v_orderpayload,
    v_orderdate, v_receiveddate, v_destination, v_priority;
    IF SQLCODE=100 THEN
    LEAVE loop_over_po;
    END IF;
    Update vendor_po set state='InProcess', LASTUPDATEDDATE=v_currenttime
    where vendor_po_id = v_vendor_po_id;

    SET v_counter = v_counter + 1;

    END LOOP loop_over_po;
    SET p_rowcount = v_counter;
    CLOSE c1;


    OPEN c2;

    end tr

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think you could do that with a single statement, actually:

    Code:
    select 
     vendor_po_id, orderid, orderitemid, orderpayload, 
     orderdate, receiveddate, destination, priority
    from final table (
     update 
      vendor_po 
     set 
      state='InProcess', LASTUPDATEDDATE=current_date
     where 
      vendor_po_id in (
       select 
        vendor_po_id 
       from 
        harledb.vendor_po 
       where 
        source = p_source  
        and channel = p_channel
        and state='Waiting'
        and type='New'
       order by orderdate 
       FETCH FIRST 50 ROWS ONLY
      )
    ) t;

  6. #6
    Join Date
    Nov 2010
    Posts
    4

    Dynamically change the fetch

    I wanted to be able to dynamically change the fetch amount.

Posting Permissions

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