Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5

    Unanswered: Problem with cursor is stored procedure

    I am experiencing a problem with a cursor in a stored procedure.
    I OPEN a cursor that will return 2 rows. In a loop, I fetch from the cursor, do some processing, call another stored procedure that
    only performs 2 inserts, then do a little more processing.
    On the second time through the loop, the FETCH statement returns
    SQLSTATE 02000 (the cursor is after the last row). It has something to do with the call to the second SP, because if I comment out the call, the second iteration of the FETCH returns the second row.

    I have tried to declare the cursor WITH HOLD and FOR READ ONLY,
    only to get the same result.

    This has been driving me nuts for several days now.

    Any help would be greatly appreciated.

    Andy

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    I am concerned about the way you are passing the parameters to the procedure being called inside the procedure. It should be some variables.

    To get an insight can you just elaborate it and send your procedure.

    Cheers,
    Prashant

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Here is the portion of the SP that opens the cursor then calls the other SP:

    SELECT count(*) into v_Num_Legs FROM PRODUCTION.PRESCHED_LEG WHERE (CODE = p_Preschedule_Code);
    SET v_Counter = 0;

    OPEN Cursor2;

    -- Loop to process legs
    WHILE v_Counter < v_Num_Legs DO
    FETCH Cursor2 INTO z_CODE,z_TRIP_LEG, z_PU_ADDRESS_CODE , z_PU_APARTMENT, z_PU_PHONE_AC, z_PU_PHONE, z_PU_PHONE_EXT, z_PU_FACILITY_CODE,
    z_PU_TIME, z_DO_ADDRESS_CODE , z_DO_APARTMENT, z_DO_PHONE_AC, z_DO_PHONE, z_DO_PHONE_EXT, z_DO_FACILITY_CODE, z_APPOINTMENT_TIME,
    z_TP_CODE, z_UPDATED_BY , z_UPDATED_ON , z_MILES, z_PU_BUILDING, z_DO_BUILDING, z_PU_DIRECTIONS, z_DO_DIRECTIONS, z_PU_PHYSICIAN,
    z_DO_PHYSICIAN, z_VEHICLE_CODE, z_COPAY, z_PUBLIC_TRANSIT, z_TRIP_LEG_LOOKUP_A, z_TRIP_LEG_LOOKUP_B, z_TRIP_LEG_LOOKUP_C,
    z_TRIP_LEG_TEXT_A, z_TRIP_LEG_TEXT_B, z_TRIP_LEG_TEXT_C, z_TRIP_LEG_TEXT_D, z_TRIP_LEG_TEXT_E;

    -- Adjust what needs to be adjusted
    SET z_DO_Time = NULL;
    SET z_Status = 1;
    SET z_Cost = NULL;
    SET z_Actual_PU_Time = NULL;
    SET z_Actual_DO_Time = NULL;
    SET z_Actual_PU_Odom = NULL;
    SET z_Actual_DO_Odom = NULL;
    SET z_WillCall_Recvd = NULL;
    SET z_Cancel_Code = NULL;
    SET z_TP_Override_Code = NULL;
    SET z_Recovery_User_Code = NULL;
    SET z_Recovery_State = NULL;
    SET z_Cost_Override = NULL;
    SET z_Estimated_Cost = NULL;

    -- Adjust PU Time and Appointment time timestamps

    SET z_PU_Time = TIMESTAMP(p_Date_Of_Trip_TMP,'00:00:00') + (z_PU_Time - TIMESTAMP(n_Start_Date,'00:00:00'));
    SET z_Appointment_Time = TIMESTAMP(p_Date_Of_Trip_TMP,'00:00:00') + (z_Appointment_Time - TIMESTAMP(n_Start_Date,'00:00:00'));

    CALL LCADUSER.P_Insert_Trip_Leg(p_Call_Center_Code_TMP, p_Date_Of_Trip_TMP,p_Trip_ID_TMP,
    z_Trip_Leg,z_PU_Address_Code,z_PU_Apartment,z_PU_P hone_AC,
    z_PU_Phone,z_PU_Phone_Ext,z_PU_Facility_Code,z_PU_ Time,z_DO_Address_Code,
    z_Do_Apartment,z_DO_Phone_AC,z_DO_Phone,z_DO_Phone _Ext,
    z_DO_Facility_Code,z_DO_Time,z_Appointment_Time,z_ TP_Code,z_Status,p_User_Code,
    z_Miles,z_Cost,z_Actual_PU_Time,z_Actual_DO_Time,
    z_PU_Building,z_DO_Building,z_PU_Directions,z_DO_D irections,z_PU_Physician,
    z_DO_Physician,z_WillCall_Recvd,z_Vehicle_Code,
    z_Copay,z_Cancel_Code,z_TP_Override_Code,z_Actual_ PU_Odom,z_Actual_DO_Odom,
    z_Public_Transit,z_Trip_Leg_Lookup_A,z_Trip_Leg_Lo okup_B,
    z_Trip_Leg_Lookup_C,z_Trip_Leg_Text_A,z_Trip_Leg_T ext_B,z_Trip_Leg_Text_C,
    z_Trip_Leg_Text_D,z_Trip_Leg_Text_E,
    z_REcovery_User_Code,z_Recovery_State,z_Cost_Overr ide,z_Estimated_Cost,
    SQLSTATE_OUT,SQLCODE_OUT,Message_Text_Out);

    IF SQLSTATE_OUT <> '00000'
    THEN
    ROLLBACK;
    SET Message_Text_Out = Message_Text_Out || ' [in P_Spawn_Trip]';
    return -1;
    END IF;


    SET v_Counter = v_Counter + 1;
    END WHILE;

    CLOSE Cursor2;
    COMMIT WORK;

    Due to message length limitations in this forum, the called SP
    will be in the next reply.

    Andy

    Originally posted by dahalkar_p
    Hi,

    I am concerned about the way you are passing the parameters to the procedure being called inside the procedure. It should be some variables.

    To get an insight can you just elaborate it and send your procedure.

    Cheers,

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Here is the called SP:

    Here is the called SP:

    CREATE PROCEDURE LCADUSER.P_Insert_Trip_Leg ( IN p_CC_Code int,
    IN p_Date_Of_Trip date,
    IN p_Trip_ID int,
    IN p_Trip_Leg char(1),
    IN p_PU_Address_Code int,
    IN p_PU_Apartment char(10),
    IN p_PU_Phone_AC smallint,
    IN p_PU_Phone int,
    IN p_PU_Phone_Ext char(6),
    IN p_PU_Facility_Code int,
    IN p_PU_Time timestamp,
    IN p_DO_Address_Code int,
    IN p_Do_Apartment char(10),
    IN p_DO_Phone_AC smallint,
    IN p_DO_Phone int,
    IN p_DO_Phone_Ext char(6),
    IN p_DO_Facility_Code int,
    IN p_DO_Time timestamp,
    IN p_Appointment_Time timestamp,
    IN p_TP_Code int,
    IN p_Status smallint,
    IN p_User_Code int,
    IN p_Miles int,
    IN p_Cost int,
    IN p_Actual_PU_Time timestamp,
    IN p_Actual_DO_Time timestamp,
    IN p_PU_Building varchar(30),
    IN p_DO_Building varchar(30),
    IN p_PU_Directions varchar(80),
    IN p_DO_Directions varchar(80),
    IN p_PU_Physician int,
    IN p_DO_Physician int,
    IN p_WillCall_Recvd timestamp,
    IN p_Vehicle_Code int,
    IN p_Copay int,
    IN p_Cancel_Code int,
    IN p_TP_Override_Code int,
    IN p_Actual_PU_Odom int,
    IN p_Actual_DO_Odom int,
    IN p_Public_Transit smallint,
    IN p_TripLeg_Lookup_A int,
    IN p_TripLeg_Lookup_B int,
    IN p_TripLeg_Lookup_C int,
    IN p_Trip_Leg_Text_A varchar(60),
    IN p_Trip_Leg_Text_B varchar(60),
    IN p_Trip_Leg_Text_C varchar(60),
    IN p_Trip_Leg_Text_D varchar(60),
    IN p_Trip_Leg_Text_E varchar(60),
    IN p_Recovery_User_Code int,
    IN p_Recovery_State SMALLINT,
    IN p_Cost_Override char(1),
    IN p_Estimated_Cost int,
    OUT SQLSTATE_OUT char(5),
    OUT SQLCODE_OUT int,
    OUT Message_Text_Out varchar(1000))
    SPECIFIC LCADUSER.P_Insert_Trip_Leg
    DYNAMIC RESULT SETS 0
    MODIFIES SQL DATA
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    LANGUAGE SQL
    ------------------------------------------------------------------------
    -- SQL Stored Procedure
    ------------------------------------------------------------------------
    P1: BEGIN
    -- Declare variables
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLCODE INT DEFAULT 0;
    DECLARE now TIMESTAMP;

    -- Declare handler

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
    GET DIAGNOSTICS EXCEPTION 1 Message_Text_Out = MESSAGE_TEXT;
    SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
    FROM SYSIBM.SYSDUMMY1;
    SET Message_Text_Out = Message_Text_Out || ' [in P_Insert_Trip_Leg]';
    END;

    -- DECLARE EXIT HANDLER FOR SQLWARNING
    -- BEGIN
    -- GET DIAGNOSTICS EXCEPTION 1 Message_Text_Out = MESSAGE_TEXT;
    -- SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
    -- FROM SYSIBM.SYSDUMMY1;
    -- SET Message_Text_Out = Message_Text_Out || ' [in P_Insert_Trip_Leg]';
    -- END;
    --
    -- DECLARE EXIT HANDLER FOR NOT FOUND
    -- BEGIN
    -- GET DIAGNOSTICS EXCEPTION 1 Message_Text_Out = MESSAGE_TEXT;
    -- SELECT SQLSTATE, SQLCODE INTO SQLSTATE_OUT, SQLCODE_OUT
    -- FROM SYSIBM.SYSDUMMY1;
    -- SET Message_Text_Out = Message_Text_Out || ' [in P_Insert_Trip_Leg]';
    -- END;

    SET now = CURRENT TIMESTAMP - CURRENT TIMEZONE;

    INSERT INTO PRODUCTION.TRIP_LEG VALUES (p_CC_Code,p_Date_Of_Trip,p_Trip_ID,p_Trip_Leg,p_P U_Address_Code,p_PU_Apartment,p_PU_Phone_AC,p_PU_P hone,p_PU_Phone_Ext,
    p_PU_Facility_Code,p_PU_Time,p_DO_Address_Code,p_D o_Apartment,p_DO_Phone_AC,p_DO_Phone,p_DO_Phone_Ex t,p_DO_Facility_Code,p_DO_Time,p_Appointment_Time,
    p_TP_Code,p_Status,p_User_Code,now,p_Miles,p_Cost, p_Actual_PU_Time,p_Actual_DO_Time,p_PU_Building,
    p_DO_Building,p_PU_Directions,p_DO_Directions,p_PU _Physician,p_DO_Physician,p_WillCall_Recvd,p_Vehic le_Code,p_Copay,p_Cancel_Code,
    p_TP_Override_Code,p_Actual_PU_Odom,p_Actual_DO_Od om,p_Public_Transit,p_TripLeg_Lookup_A,p_TripLeg_L ookup_B,p_TripLeg_Lookup_C,p_Trip_Leg_Text_A,
    p_Trip_Leg_Text_B,p_Trip_Leg_Text_C,p_Trip_Leg_Tex t_D,p_Trip_Leg_Text_E,p_Recovery_User_Code,p_Recov ery_State,p_Cost_Override,p_Estimated_Cost);


    INSERT INTO PRODUCTION.Trip_Leg_Audit VALUES(p_CC_Code,p_Date_Of_Trip,p_Trip_ID,p_Trip_L eg,'INSERTED_ON',null,null,p_User_Code,now);

    SET SQLSTATE_OUT = SQLSTATE;
    SET SQLCODE_OUT = SQLCODE;
    SET Message_Text_Out = NULL;

    END P1


    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
  •