Hi to all,

First of all, sorry about my english..

I´m running db2 v8.2 express on windows 2003 server, and the following error ocurrs when i call a stored procedure from a web application developed in j2ee.

[IBM][CLI Driver][DB2/NT] SQL0104N An unexpected token " " was found following "WHERE ID=1023". Expected tokens may include: " ".SQLSTATE=42601

the stored procedure is simply:
SELECT * FROM EMPLOYEES WHERE ID=1023

I execute this stored procedure and somethimes it works succesfully and sometimes throws this error. The strange thing is that i had debug the SP in database and it really works. Other stranger thing is that somethimes the error An unexpected token " " was found following "WHERE ID=1023" is not the same all times, sometimes its An unexpected token "FROM " was found following "FROM EMPLOYEES"..

I really dont know why this happend, if someone could help me, i will appreciate to much..

thanks

Can you post the entire CREATE PROCEDURE statement?

Andy

sorry, in the post are the details,

thanks

ok, this is the complete error:

[IBM][CLI Driver][DB2/NT] SQL0104N Se ha encontrado una señal imprevista "ESCHEMA1" a continuación de "PR.ID FROM". Entre las señales esperadas se puede incluir: "WHERE". SQLSTATE=42601

(this error is not same all times, changes the wrong signal or changes the hope signal)

the stored procedure is a stored that i call "dinamic" stored procedure, because i will give to the SP a few parameter, and if the parameter is not null, it creates & prepare the SENTENCE generating the "WHERE" clauses with the not null values.
here is:

--------------------------------------------------------------------------
CREATE PROCEDURE ESQUEMA1.ADMINISTRADORES_SELECTALL ( IN IDA INTEGER,
IN NOMBREUSUARIOA VARCHAR(500),
IN CONTRASENIAA VARCHAR(500))
------
-- SQL Procedimiento almacenado
------
P1: BEGIN ATOMIC

DECLARE SENTENCIA VARCHAR(1000);
DECLARE CONDICION VARCHAR(1000);

DECLARE CURSOR1 CURSOR WITH RETURN FOR SENTENCIA;

SET SENTENCIA = ' SELECT ID, ' ||
' NOMBREUSUARIO, ' ||
' CONTRASENIA, ' ||
' CREAR, ' ||
' MODIFICAR, ' ||
' BORRAR ' ||
' FROM ESQUEMA1.ADMINISTRADORES ';

IF(IDA IS NOT NULL)THEN
SET CONDICION = ' WHERE ID = ' || CHAR(IDA);
END IF;

IF(NOMBREUSUARIOA IS NOT NULL AND NOMBREUSUARIOA <> '')THEN
IF(CONDICION IS NOT NULL)THEN
SET CONDICION = CONDICION || ' AND UPPER(NOMBREUSUARIO) LIKE ''' || UPPER(NOMBREUSUARIOA) || '''';
ELSE SET CONDICION = ' WHERE UPPER(NOMBREUSUARIO) LIKE ''' || UPPER(NOMBREUSUARIOA) || '''';
END IF;
END IF;

IF(CONTRASENIAA IS NOT NULL AND CONTRASENIAA <> '')THEN
IF(CONDICION IS NOT NULL)THEN
SET CONDICION = CONDICION || ' AND UPPER(CONTRASENIA) LIKE ''' || UPPER(CONTRASENIAA) || '''';
ELSE SET CONDICION = ' WHERE UPPER(CONTRASENIA) LIKE ''' || UPPER(CONTRASENIAA) || '''';
END IF;
END IF;


SET SENTENCIA = SENTENCIA || CONDICION;
PREPARE SENTENCIA FROM SENTENCIA;
OPEN CURSOR1;


END P1 --------------------------------------------------------------------------

other SPs that causes the same error is:

--------------------------------------------------------------------------

CREATE PROCEDURE ESQUEMA1.BIBLIOGRAFIAS_SELECT ( IN IDB INTEGER )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Procedimiento almacenado
-- ID
------------------------------------------------------------------------
P1: BEGIN ATOMIC
-- Declarar cursor
DECLARE cursor1 CURSOR WITH RETURN FOR

SELECT ID, TITULO, AUTOR, EDITORIAL, IDMATERIA, ARCHIVO, EXTENSION
FROM ESQUEMA1.BIBLIOGRAFIAS AS BIBLIOGRAFIAS
WHERE ID = IDB;

-- Cursor dejado abierto para aplicaci&#243;n cliente
OPEN cursor1;
END P1
--------------------------------------------------------------------------

I really dont know if the first stored procedure causes the error, and then the other SP leave working because that error...

sorry for my english, thanks for your time.

i am open for suggestion

Pablo,
I do not know why the second SP gets the error. It is static SQL and is prepared at compile/bind time so that error makes no sense to me. I am fairly certain it is residual effect from the first SP. As far as what is wrong with your first SP, I suspect that you get the error when the parameter IDA is null and one of the other parameters is not null. If you look at your code, you are conditionally setting the local variable CONDICION based if it null or not when you are looking at the second and third parameters. The problem is is that when IDA is null CONDICION has net been explicitly set to anything when you are looking at the other two parameters. I am not certain what a varchar local variable is set to at compile time, but I would guess that it is not NULL. What you need to do is set it null on the declare statement:

DECLARE CONDICION VARCHAR(1000) DEFAULT NULL;

or maybe just explicitly set it to null at the very beginning:

SET CONDICION = NULL;

I suspect that the SQL you are building looks like this:

SELECT ... FROM ESQUEMA1.ADMINISTRADORES AND ...

You might want to look into converting it to static SQL. That will eliminate this error entirely.

Andy

P.S. your english is quite good!