If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > Getting SQLCODE -204 SQLSTATE 42704 at PREPARE in stored procedure

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 06:17.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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 )
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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 07:15.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On