Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    2

    Red face Unanswered: What is wrong in procedure?

    I'm trying to compile this procedure and came across the following error:

    Code:
    ---------- STATEMENT ----------
    
    CREATE PROCEDURE GRETAGAURDA.VALIDA_ACESSO
      (IN P_USER VARCHAR(60),
        IN P_PASS VARCHAR(250),
        OUT P_RETORNO SMALLINT)
      SPECIFIC GRETAGAURDA.VALIDA_ACESSO
      DYNAMIC RESULT SETS 1
      MODIFIES SQL DATA
      DETERMINISTIC
      LANGUAGE SQL
    BEGIN
    
    DECLARE CODIGO_U INTEGER;
    DECLARE RETORNO SMALLINT;
    
    SET CODIGO_U = 0;
    SET RETORNO = 0;
    
    SELECT COUNT(*),ID INTO RETORNO, CODIGO_U FROM GRETAGAURDA.USUARIOS 
    WHERE USUARIO = P_USER AND SENHA = P_PASS
    GROUP BY ID;
    
    IF (RETORNO = 0) THEN 
    	SET RETORNO = 0;
    END IF;
    END
      ;
    
    COMMENT ON SPECIFIC PROCEDURE GRETAGAURDA.VALIDA_ACESSO
      IS ''
    
    
    ---------- ERROR MESSAGE ----------
    
    An exception occurred
    Message: [IBM][CLI Driver][DB2/NT64] SQL0104N  Um token inesperado ";" foi localizado após "= 0;  END IF;  END  ".  Os tokens esperados podem incluir: ":".  LINE NUMBER=26.  SQLSTATE=42601
    SQL: CREATE PROCEDURE GRETAGAURDA.VALIDA_ACESSO
      (IN P_USER VARCHAR(60),
        IN P_PASS VARCHAR(250),
        OUT P_RETORNO SMALLINT)
      SPECIFIC GRETAGAURDA.VALIDA_ACESSO
      DYNAMIC RESULT SETS 1
      MODIFIES SQL DATA
      DETERMINISTIC
      LANGUAGE SQL
    BEGIN
    
    DECLARE CODIGO_U INTEGER;
    DECLARE RETORNO SMALLINT;
    
    SET CODIGO_U = 0;
    SET RETORNO = 0;
    
    SELECT COUNT(*),ID INTO RETORNO, CODIGO_U FROM GRETAGAURDA.USUARIOS 
    WHERE USUARIO = P_USER AND SENHA = P_PASS
    GROUP BY ID;
    
    IF (RETORNO = 0) THEN 
    	SET RETORNO = 0;
    END IF;
    END
      ;
    
    COMMENT ON SPECIFIC PROCEDURE GRETAGAURDA.VALIDA_ACESSO
      IS ''
    Method: TDB2DirQuery.Execute
    if I remove this block of code, then passes normally.

    IF (RETORNO = 0) THEN
    SET RETORNO = 0;
    END IF;

    can anyone help me?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What tool/method are you using to create the Stored Procedure?

    Andy

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by macgayver View Post
    I'm trying to compile this procedure and came across the following error:

    Code:
    ---------- STATEMENT ----------
    
    CREATE PROCEDURE GRETAGAURDA.VALIDA_ACESSO
     ...
    BEGIN
    stmt;
    ...
    END
      ;
    
    COMMENT ON SPECIFIC PROCEDURE GRETAGAURDA.VALIDA_ACESSO
      IS ''
    Your problem is most likely that you mix two different uses of the ";" character:
    1) inside the procedure definition, as the (required) SQL PL syntax for statement termination, and
    2) after the final END, for your client application to know which two statements (viz. the "CREATE" and the "COMMENT" statements) to be passed separately to DB2.

    Clearly this cannot work: either the client application thinks the first ";" is for him, thus sending an incomplete CREATE statement to DB2, or not detecting the separators, in which case the whole set is sent as one "CREATE" statement to DB2.

    Solution: (1) tell your client that it must use "@" as a separator instead of ";", and (2) replace the last ";" (after "END") by "@".
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Sep 2011
    Posts
    2

    Talking [Solved]

    I changed the editor, then switched automatically to this new terminator character @ to solving the problem, as everyone had suspected the two, thank you all for your reply.

Posting Permissions

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