Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Location
    USA
    Posts
    4

    Red face Unanswered: Problem with DB2 Stored Procedures

    I have a stored procedure that perform an insert and then returns the newly inserted row.

    When I execute the procedure from SP Builder it works fine. When
    I run the procedure through ADO, it inserts the same row multiple times. Three to be exact!?!

    Has anybody seen this behavior before? If I comment out the OPEN of the cursor and re run, I get only one record inserted.

    It seems that I don't have the procedure coded properly to perform the insert and return the result set when execute from ADO.

    Here is an example of the procedure:

    P1: BEGIN

    -- Declare temp timestamp var
    DECLARE v_dtime TIMESTAMP;

    -- Declare cursor
    DECLARE cursor1 CURSOR WITH RETURN FOR
    SELECT ID as ID,
    DCT_NAME as DCT_NAME,
    USER_ID as USER_ID,
    CRE_DTIME as CRE_DTIME,
    LAST_CNG_DTIME as LAST_CNG_DTIME,
    LAST_CNG_ID as LAST_CNG_ID,
    MODEL_IND as MODEL_IND
    FROM PHDEM1.DICTIONARY
    WHERE ID = IDENTITY_VAL_LOCAL();

    -- Value temp timestamp var
    SET v_dtime = CURRENT TIMESTAMP;

    INSERT INTO
    PHDEM1.DICTIONARY
    (
    DCT_NAME,
    USER_ID,
    CRE_DTIME,
    LAST_CNG_DTIME,
    LAST_CNG_ID,
    MODEL_IND
    )
    VALUES
    (
    eq_add_dct.p_dct_name,
    eq_add_dct.p_user_id,
    v_dtime,
    v_dtime,
    eq_add_dct.p_last_cng_id,
    eq_add_dct.p_model_ind
    );

    -- Cursor left open for client application
    OPEN cursor1;

    END P1

  2. #2
    Join Date
    Dec 2002
    Location
    USA
    Posts
    4

    Smile Re: Problem with DB2 Stored Procedures

    We figured out what was causing our issue.

    In my original post, I failed to mention that we were generating XML from ADO. This is where the issue arises.

    On the Connection object, we did not specify a CursorLocation. Default is UseServer. We added a CursorLocation = UseClient and it resolved our problem.

    Apparently when ADO prepares to generate XML it re-runs the query again if you are using a Server Side Cursor Service. Since we were running an insert and returning a record set, multiple records were generated.

    Once the Client Sider Cursor Service was used, ADO doesn't need to re-execute the query again. That is what we are thinking.

    Cheers!

    Mark

Posting Permissions

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