Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253

    Unanswered: ...and More Cursors....

    I've been doing a lot of cursor posting - my apologies. But can't seem ot find a whole bunch on it (tried v7 and v8 references).

    I put together a stored procedure taking what I know of DB2 and SQL Server and cursors.

    Was hoping someone could take a look and let me know how far off the mark I may be. I'd appreciate any hints or help...thanks!!!
    CREATE PROCEDURE AIM.PurgeImagesFromCache ( )
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    RESULT SETS 1
    LANGUAGE SQL
    P1: BEGIN
    DECLARE intRetrievalID INTEGER;

    DECLARE PURGEITEMS_CURSOR CURSOR FOR
    (SELECT
    AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
    FROM
    AIM.ARCHIVERETRIEVAL,
    AIM.AIMRETRIEVEDITEM
    WHERE
    AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP));

    OPEN PURGEITEMS_CURSOR;

    FETCH FROM PURGEITEMS_CURSOR;

    WHILE SQLSTATE='0000' DO


    UPDATE AIM.AIMRetrievedItem
    SET ImageFront=NULL,
    ImageFrontSize=NULL,
    ImageFrontType=NULL,
    ImageBack=NULL,
    ImageBackSize=NULL,
    ImageBackType=NULL
    WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = intRetrievalID;

    FETCH NEXCT FROM PURGEITEMS_CURSOR;
    END;
    CLOSE PURGEITEMS_CURSOR;
    DEALLOCATE PURGEITEMS_CURSOR;
    END P1

    Again, thanks in advance.
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: ...and More Cursors....

    I think this can be put in an UPDATE Statement instead of using a cursor

    See if this thread throws light

    http://www.tek-tips.com/viewthread.c...PID=178&page=1

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    The linkj just takes me to the home page....
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    mmm ... tek-tips behaves that way!!

    on the home page, search for 'ibm db2' (find forum) ..

    Click on the resuling link

    On page 2(if you are visiting today), you will find the thread 'Updating Multiple fields with Table Joins'

    HTH

    Sathyaram



    Originally posted by ansonee
    The linkj just takes me to the home page....
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2002
    Location
    Minneapolis, MN
    Posts
    253
    I was able to eventually get to the page, but - call me stupid - the solutions discussed there sisn't ring any bells for me. The examples on the site are updating multiple rows of a table with values from another table. I'm looking to do a straight update...

    Again, any hints would be great.

    UPDATE AIM.AIMRetrievedItem
    SET ImageFront=NULL,
    ImageFrontSize=NULL,
    ImageFrontType=NULL,
    ImageBack=NULL,
    ImageBackSize=NULL,
    ImageBackType=NULL
    WHERE AIM.AIMRetrievedItem.ArchiveRetrievalID = (SELECT
    AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID
    FROM
    AIM.ARCHIVERETRIEVAL,
    AIM.AIMRETRIEVEDITEM
    WHERE
    AIM.AIMRETRIEVEDITEM.ARCHIVERETRIEVALID = AIM.ARCHIVERETRIEVAL.ARCHIVERETRIEVALID
    AND AIM.ARCHIVERETRIEVAL.EXPIRATIONDATE < DATE(CURRENT TIMESTAMP));
    Anthony Robinson

    "If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."

  6. #6
    Join Date
    Mar 2004
    Posts
    448
    First what i looking is that you open a cursor ,fetching rows
    and wants to update the fetched row.

    why you need to put result set 1.you are not using that cursor in another procedure to get the result set.

    you want to update a fetched row then you can use

    update <>

    ....
    .... where current of <cursor name>

    use the variable to hold values during the fetch part and use the same variables to update.

    I think there is no need to deallocate the cursor. it works with associate and allocate way.

Posting Permissions

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