What is wrong in procedure?

macgayver · #1 (**permalink**) 09-17-11, 19:08

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?

ARWinner · #2 (**permalink**) 09-19-11, 09:55

What tool/method are you using to create the Stored Procedure?

Andy

Peter.Vanroose · #3 (**permalink**) 09-19-11, 13:56

Quote:

Originally Posted by macgayver

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 "@".

macgayver · #4 (**permalink**) 09-20-11, 06:25

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech