Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    4

    Unanswered: Stored Procedure - Getting back data from sub select

    I am trying to update a record with a sub select and retrieve a "REQUESTID" from the sub select to allow this value to be used later in my stored procedure. For some reason I can not get this value. Does anyone have a solution? I am thinking I can maybe do a select (with fetch first 1 rows only) with an isolation level (not sure which one) to get a REQUESTID, then update it, then do the select again. But I would rather get the value from the sub select. Here is my proc:


    CREATE PROCEDURE MYDBA.PROCEDURE2 ( )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN

    DECLARE SOMEREQUEST INT DEFAULT 0;

    -- Declare cursor for returning select
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT REQUESTID, REQUESTFILE, REQUESTSTATUS
    FROM MYTABLE
    WHERE REQUESTID = SOMEREQUEST
    FOR READ ONLY;

    -- Find a single record with REQUESTSTATUS = 'N and update it.
    UPDATE MYTABLE
    SET REQUESTSTATUS = 'P'
    WHERE (REQUESTID in
    (SELECT RequestID as SOMEREQUEST FROM MYTABLE WHERE (REQUESTSTATUS = 'N')
    FETCH FIRST 1 ROWS ONLY));

    -- Cursor left open for client application
    OPEN cursor1;

    END P1

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The FETCH FIRST clause cannot be used like that.

    Why not do it this way:

    Declare a cursor for the subselect. Open it. FETCH the first row of the result set. Update the row. Close the cursor. COMMIT. Then open your original cursor.

    Andy

  3. #3
    Join Date
    Dec 2003
    Posts
    4

    Thanks for the response

    The problem I have is that I will have several machines running this SQL at the same time and I want a machine to have exclusive lock on the record so that no other process can read or update it. It is my understanding that if I use an update command I will get this. Basically I want to read a single record to update (locked), update the record, then do some more procesing with the record.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That being the case, then try this:

    Read the row (add the FOR UPDATE clause)
    Update the row
    Do your processing
    Commit

    The lock will be held until the commit, preventing others into the process.

    Andy

  5. #5
    Join Date
    Dec 2003
    Posts
    4
    Tried it but for some reason it does not compile...

    This works fine:
    SELECT RequestID FROM IRREQUESTSTATUS WHERE REQUESTSTATUS = 'N' FETCH FIRST 1 ROWS ONLY FOR UPDATE;

    But this will not compile:
    DECLARE vRequestTID VARCHAR(32) DEFAULT '0';

    SELECT RequestID INTO vRequestTID FROM IRREQUESTSTATUS WHERE REQUESTSTATUS = 'N' FOR UPDATE FETCH FIRST 1 ROWS ONLY FOR UPDATE;

    The error: SETIRACTIVESTATUS_SLH2: 19: [IBM][CLI Driver][DB2/SUN] SQL0104N An unexpected token "FOR UPDATE" was found following "REQUESTSTATUS = 'N'". Expected tokens may include: "<space>". LINE NUMBER=19. SQLSTATE=42601


    But when I take out the FOR UPDATE it compiles:
    DECLARE vRequestTID VARCHAR(32) DEFAULT '0';

    SELECT RequestID INTO vRequestTID FROM IRREQUESTSTATUS WHERE REQUESTSTATUS = 'N' FOR UPDATE FETCH FIRST 1 ROWS ONLY;

    Any thoughts on what I might be doign wrong?

  6. #6
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    The FOR UPDATE clause is not valid for SELECT INTO statement.

    Use a cursor instead;

    DECLARE CURSOR with FOR UPDATE (no FETCH FIRST)
    OPEN the cursor
    FETCH (this will get the first row)
    UPDATE
    Do your processing
    COMMIT

    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
  •