Results 1 to 4 of 4
  1. #1
    Join Date
    May 2004
    Posts
    6

    Unanswered: db2 cursor declaration

    I am trying to build this DB2 stored procedure :




    DELETE FROM PROJVAR_XREF
    WHERE VARIABLE_ID=v_VariableId;

    UPDATE MODEL_MDI
    SET ROOTVAR = NULL
    WHERE ROOTVAR = v_VariableId;

    CALL PROC_DELETE_VARIABLE(v_VariableId);

    END IF;



    END LOOP fetch_loop;

    CLOSE c_ProjectVariable1;


    DELETE FROM MODEL_MDI WHERE MODEL_MDI.PROJECT_ID = v_ProjectId ;


    END p1


    but i was constantly getting an error :
    SQL0104N An unexpected token "<cursor declaration>" was found following "". Expected tokens may include: "<SQL statement>". LINE NUMBER=20. SQLSTATE=42601

    i think there is some problem with the cursor declaration i am unable to resolve , Hope you can get a solution to this .
    Please help its very urgent

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Your DECKARE CURSOR Statment should be at the beginning of the procedure (or more precisely, at the beginning of a compound statement before any 'executable' SQL Statement ...

    Ex:

    BEGIN
    DECLARE v_salary DOUBLE;
    DECLARE v_years SMALLINT;
    DECLARE v_id SMALLINT;
    DECLARE at_end INT DEFAULT 0;
    DECLARE not_found CONDITION FOR SQLSTATE '02000';

    -- CAST salary as DOUBLE because SQL procedures do not support DECIMAL
    DECLARE C1 CURSOR FOR
    SELECT id, CAST(salary AS DOUBLE), years
    FROM staff;
    DECLARE CONTINUE HANDLER FOR not_found
    SET at_end = 1;

    SET v_id=100 ;
    OPEN C1;

    etc ...

    If the declare currsor is after the SET v_id=100 statement, then you will get an error

    HTH

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

  3. #3
    Join Date
    May 2004
    Posts
    6
    Here is my complete code can u make corrections :

    CREATE PROCEDURE PROC_DELETE_PROJECT (IN P_PROJECT_NAME VARCHAR(35))
    LANGUAGE SQL

    p1: BEGIN

    DECLARE v_ProjectId INT DEFAULT 0;
    DECLARE v_VariableId INT DEFAULT 0;
    DECLARE v_DataConnectionId INT DEFAULT 0;
    DECLARE v_Count INT DEFAULT 0;
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE SQLSTATE CHAR(5);

    DECLARE not_found
    CONDITION for SQLSTATE '02000';

    DECLARE CONTINUE HANDLER for not_found
    SET at_end = 1;

    DECLARE c_ProjectVariables CURSOR FOR
    select projvar_xref.variable_id
    from projvar_xref
    where projvar_xref.project_id= v_ProjectId;

    SELECT PROJECT_MDI.ID
    INTO v_ProjectId
    FROM PROJECT_MDI
    WHERE PROJECT_MDI.NAME = P_PROJECT_NAME ;

    SELECT DATACONNECTION_ID
    INTO v_DataConnectionId
    FROM DATAPROJ_XREF
    WHERE PROJECT_ID=v_ProjectId;



    DELETE FROM DATAPROJ_XREF WHERE DATAPROJ_XREF.PROJECT_ID = v_ProjectId;
    DELETE FROM DATACONNECTION_MDI WHERE ID=v_DataConnectionId;
    DELETE FROM CUPROJ_XREF WHERE CUPROJ_XREF.PROJECT_ID = v_ProjectId;

    OPEN c_ProjectVariables;
    fetch_loop:
    LOOP

    FETCH c_ProjectVariables INTO v_VariableId;
    IF at_end <> 0 THEN LEAVE fetch_loop;
    END IF;

    SELECT COUNT(*) into v_Count
    FROM PROJVAR_XREF
    WHERE variable_id=v_VariableId
    AND project_id <> v_ProjectId;

    IF v_Count > 0 THEN

    DELETE FROM PROJVAR_XREF
    WHERE VARIABLE_ID=v_VariableId
    AND PROJECT_ID=v_ProjectId;

    UPDATE MODEL_MDI
    SET ROOTVAR = NULL
    WHERE ROOTVAR = v_VariableId;


    ELSE



    DELETE FROM PROJVAR_XREF
    WHERE VARIABLE_ID=v_VariableId;

    UPDATE MODEL_MDI
    SET ROOTVAR = NULL
    WHERE ROOTVAR = v_VariableId;

    CALL PROC_DELETE_VARIABLE(v_VariableId);

    END IF;



    END LOOP fetch_loop;

    CLOSE c_ProjectVariables;


    DELETE FROM MODEL_MDI WHERE MODEL_MDI.PROJECT_ID = v_ProjectId ;


    END p1

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    put your cursor declaration before declare handler
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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