Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Unanswered: SQL0206N "SQLSTATE" is not valid in the context where it is used. LINE NUMBER=53.

    when executing db2 -td@ -f./sql/update_product.sql

    I m getting following message:

    DB20000I The SET SERVEROUTPUT command completed successfully.

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0206N "SQLSTATE" is not valid in the context where it is used. LINE
    NUMBER=53. SQLSTATE=42703



    The sql file has :

    SET SERVEROUTPUT ON
    @
    CREATE OR REPLACE PROCEDURE UPLOADPRODUCTATTRIBUTES
    BEGIN
    DECLARE v_eisidentifier VARCHAR(100);
    DECLARE v_categoryGuid DECIMAL(19,0);
    DECLARE v_localeGuid DECIMAL(19,0);
    DECLARE v_realmGuid DECIMAL(19,0);
    DECLARE v_catchingPhrase VARCHAR(120) ;
    DECLARE v_genericName VARCHAR(80);
    DECLARE v_ingredients VARCHAR(900);
    DECLARE v_quantity VARCHAR (60);
    DECLARE v_dose VARCHAR(40);
    DECLARE v_nutritionFacts VARCHAR (900);
    DECLARE v_productDescription VARCHAR(900);
    DECLARE v_recommendedFor VARCHAR(200);
    DECLARE v_promotionMessage VARCHAR(200);
    DECLARE v_message VARCHAR (4500);
    DECLARE v_proudctGuid DECIMAL(19,0);
    DECLARE C1 CURSOR FOR
    SELECT
    EISIDENTIFIER,
    CATEGORY_GUID,
    LOCALEGUID,
    REALMGUID,
    CATCHING_PHRASE,
    GENERIC_NAME,
    INGREDIENTS,
    QUANTITY,
    DOSE,
    NUTIRITION_FACTS,
    PRODUCT_DESCRIPTION,
    RECOMMENDED_FOR,
    PROMOTION_MESSAGE,
    MESSAGE
    FROM
    TEMP_UPLOAD_PRODUCT_ATTRIBUTES;

    OPEN C1;
    FETCH FROM C1 into v_eisidentifier,
    v_categoryGuid,
    v_localeGuid,
    v_realmGuid,
    v_catchingPhrase,
    v_genericName,
    v_ingredients,
    v_quantity,
    v_dose,
    v_nutritionFacts,
    v_productDescription,
    v_recommendedFor,
    v_promotionMessage,
    v_message;

    WHILE (SQLSTATE = '00000')
    DO
    IF EXISTS (SELECT 1 FROM PRD_PRODUCT WHERE EISIDENTIFIER = v_eisidentifier)
    THEN
    SELECT GUID INTO v_productGuid FROM PRD_PRODUCT WHERE EISIDENTIFIER = v_eisidentifier;

    UPDATE PRD_PRODUCT_L10N
    SET CATEGORY_GUID = v_categoryGuid,
    REALMGUID = v_realmGuid,
    CATCHING_PHRASE =v_catchingPhrase,
    GENERIC_NAME =v_genericName,
    INGREDIENTS =v_ingredients,
    QUANTITY=v_quantity,
    DOSE=v_dose,
    NUTIRITION_FACTS=v_nutritionFacts,
    PRODUCT_DESCRIPTION=v_productDescription,
    RECOMMENDED_FOR=v_recommendedFor,
    PROMOTION_MESSAGE=v_promotionMessage,
    MESSAGE=v_message
    WHERE PRODUCTGUID =v_proudctGuid and LOCALGUID =v_categoryGuid;

    IF NOT EXISTS (SELECT 1 FROM PRD_PRODUCT_CATEGORY WHERE CATEGORY_GUID=v_categoryGuid AND PRODUCT_GUID=v_productGuid)
    THEN
    INSERT INTO PRD_PRODUCT_CATEGORY (CATEGORY_GUID, PRODUCT_GUID) VALUES (v_categoryGuid,v_productGuid);
    ELSE
    -- do nothing
    END IF;
    ELSE
    -- INSERT INTO --- should not be the case....
    END IF;


    FETCH C1 into v_productGuid,
    v_categoryGuid,
    v_localeGuid,
    v_realmGuid,
    v_catchingPhrase,
    v_genericName,
    v_ingredients,
    v_quantity,
    v_dose,
    v_nutritionFacts,
    v_productDescription,
    v_recommendedFor,
    v_promotionMessage,
    v_message
    ;
    END WHILE;
    CLOSE c1;
    END
    @

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This works for me (not sure if there are other ways):

    Add the following declare variables to your existing list:

    DECLARE o_SQLSTATE CHAR(5) DEFAULT '00000';
    DECLARE SQLSTATE CHAR(5) DEFAULT '00000';


    Add the following after your Declare Cursors:
    ---------------------------------------------
    -- Declare exit handlers
    ---------------------------------------------
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    SELECT SQLSTATE INTO o_sqlstate
    FROM sysibm.sysdummy1;


    Then use 0_SQLSTATE instead of SQLSTATE in your loop.

    I don't see any commit statements. Is that intentional? Depending on the number of inserts, you may want to do intermediate commits.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that whole procedure body might be consentrated in an UPDATE(or MERGE) statement and an INSERT statement,
    without using cursor and loop.

    The procedure doesn't receive any parameter,
    so the procedure might be not neccesary, if you executed the two statements directly by using db2 command.

    Example 1: UPDATE statement.
    Code:
    UPDATE PRD_PRODUCT_L10N ppl
       SET (  CATEGORY_GUID
            , REALMGUID
            , CATCHING_PHRASE
            , GENERIC_NAME
            , INGREDIENTS
            , QUANTITY
            , DOSE
            , NUTIRITION_FACTS
            , PRODUCT_DESCRIPTION
            , RECOMMENDED_FOR
            , PROMOTION_MESSAGE
            , MESSAGE
           ) = (
           SELECT CATEGORY_GUID
                , REALMGUID
                , CATCHING_PHRASE
                , GENERIC_NAME
                , INGREDIENTS
                , QUANTITY
                , DOSE
                , NUTIRITION_FACTS
                , PRODUCT_DESCRIPTION
                , RECOMMENDED_FOR
                , PROMOTION_MESSAGE
                , MESSAGE
            FROM  TEMP_UPLOAD_PRODUCT_ATTRIBUTES tupa
            INNER JOIN
                  PRD_PRODUCT                    pp
              ON  pp .EISIDENTIFIER = tupa.EISIDENTIFIER
            WHERE ppl.PRODUCTGUID   = pp  .GUID
              AND ppl.LOCALGUID     = tupa.CATEGORY_GUID
           )
     WHERE EXISTS (
           SELECT 0
            FROM  TEMP_UPLOAD_PRODUCT_ATTRIBUTES tupa
            INNER JOIN
                  PRD_PRODUCT                    pp
              ON  pp .EISIDENTIFIER = tupa.EISIDENTIFIER
            WHERE ppl.PRODUCTGUID   = pp  .GUID
              AND ppl.LOCALGUID     = tupa.CATEGORY_GUID
           )
    ;
    Example 2: INSERT statement.
    Code:
    INSERT INTO PRD_PRODUCT_CATEGORY
    ( CATEGORY_GUID , PRODUCT_GUID )
    SELECT tupa.CATEGORY_GUID , pp  .GUID
     FROM  TEMP_UPLOAD_PRODUCT_ATTRIBUTES tupa
     INNER JOIN
           PRD_PRODUCT                    pp
       ON  pp .EISIDENTIFIER = tupa.EISIDENTIFIER
     WHERE NOT EXISTS (
           SELECT 0
            FROM  PRD_PRODUCT_CATEGORY ppc
            WHERE ppc.CATEGORY_GUID = tupa.CATEGORY_GUID
              AND ppc.PRODUCT_GUID  = pp  .GUID
           )
    ;
    Last edited by tonkuma; 01-30-12 at 23:49.

Tags for this Thread

Posting Permissions

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