Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2006
    Posts
    3

    Question Unanswered: HELP with and SQLSTATE=42601

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post the entire CREATE PROCEDURE statement?

    Andy

  3. #3
    Join Date
    Mar 2006
    Posts
    3
    sorry, in the post are the details,

    thanks
    Last edited by Pablo_Cba; 03-30-06 at 21:10.

  4. #4
    Join Date
    Mar 2006
    Posts
    3
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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!

Posting Permissions

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