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

    Getting SQLCODE -204 SQLSTATE 42704 at PREPARE in stored procedure

    Hello everyone again,
    I'm having trouble with a stored procedure. I can't understand why it will NOT make a prepare statement out of a string. Really stuck here. Any ideas will be greatly appreciated.
    Code:
    DECLARE FORMULA_V VARCHAR(3900);
    DECLARE STMT VARCHAR(4500);
    DECLARE CURSOR_COTA CURSOR FOR PREP_STMT;
    
    SELECT RTRIM(LTRIM(FORMULA)) INTO FORMULA_V
    	FROM FORMULA_TABLE
    	WHERE ID = SP_GET_VALUE.ID;
    
    SET STMT = 'SELECT ('||FORMULA_V||') AS VALOARE FROM '||SP_GET_VALUE.TABLE_NAME||' WHERE ID = '||CHAR(ID_UNIC);
    insert into poi values(3,stmt);
    
    PREPARE PREP_STMT FROM STMT;
    
    OPEN CURSOR_COTA;
    insert into poi values(3,'opened cursor');
    I've run the procedure in debug mode and it just crashes at the PREPARE statement. If I simply run the STMT from the poi table it executes just fine.
    The error's description is this:
    42704 An undefined object or constraint name was detected. -204
    When I call the procedure from the application it crashes at FETCH. I have absolutly no idea what's wrong. Could it be that the varchar is too big? Would that create a problem? Why can I execute the STMT from the poi table and it just crashes at the PREPARE?
    Thank you.

    Iulia S
    Last edited by IuliaS; 04-02-09 at 07:17.

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    Is the tablename stored in SP_GET_VALUE.TABLE_NAME unqualified ?

    if yes, does the stored procedure attempt to use an other SCHEMA name to qualify the table, than you use when you execute the query


    are you sure that the query you execute is the same that the stored procedure attempts to PREPARE ?
    ( It might be possible that the query is inserted into the poi-table and when the PREPARE fails it is ROLLBACKED out of the table )

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    Quote Originally Posted by umayer
    Is the tablename stored in SP_GET_VALUE.TABLE_NAME unqualified ?

    if yes, does the stored procedure attempt to use an other SCHEMA name to qualify the table, than you use when you execute the query


    are you sure that the query you execute is the same that the stored procedure attempts to PREPARE ?
    ( It might be possible that the query is inserted into the poi-table and when the PREPARE fails it is ROLLBACKED out of the table )
    Hello!
    Thank you very much for the quick reply. I don't have the SCHEMA name in front of the SP_GET_VALUE.TABLE_NAME. I added the schema now and it's the same result. There are 4 other procedures that prepare statements like this and none of them use the SCHEMA name in front of table names. All procedures are created the same way. The application calling the procedures uses JAS to connect to DB2 and I use the same user when executing the query in the command line. I don't see any reason why this procedure should use another schema.
    I do a DELETE FROM POI before calling and I a SELECT * FROM POI after. I also added the same insert after the PREPARE statement. Could it still be a different query?
    I really am running out of ideas. It's still not working. Could someone please point me in the right direction?
    Thank you very much again.

    IuliaS
    Last edited by IuliaS; 04-03-09 at 08:15.

Posting Permissions

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