Getting SQLCODE -204 SQLSTATE 42704 at PREPARE in stored procedure

IuliaS · #1 (**permalink**) 04-02-09, 06:12

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

umayer · #2 (**permalink**) 04-02-09, 08:09

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 )

IuliaS · #3 (**permalink**) 04-02-09, 08:53

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

Internet Services

Web Development

Digital Marketing

Consumer Tech