Hi,

I am running a stored procedure in DB2 in which I retrieve values from one table & put them in another. However on fetching data from the first table, I am not getting the entire string. I use data studio & it clearly tells me that "Data returned in result set columns is limited to the first 100 bytes or characters" Can I change this??
Also this dsnt seem to be a data studio specific issue as When I invoke the stored procedure from BIRT, the same error pops up.
Would really appreciate any help,
Regards

What happens if you call the SP from the CLP?

Andy

same error from command line as well

Can you post your DB2 version, OS, Code for the SP and any DDL for the tables referenced?

Andy

Also post the exact error you got from the CLP.

Andy

the DB2 version I use is 9.5 on windows XP. The error exactly is caused basically because once i fetch data from the first table, I put it in a string & append it with a SQL statement:


FETCH FROM C_XYZ INTO data_from_first_table;

SET text='UPDATE ZZZ SET col1=' || data_from_first+table || ' where col2='789'

So the error is occuring because none of my variables text/data_from_first_table are storing more than 100 characters, inspite of them being declared with big numbers.

I just wrote a select query on that table for that particular large data and it just returned the first 100 characters only.when run thru data studio)

Surprisingly, the same select query when run from command line fetches the complete value. But the stored procedure still fails even from the command prompt.

the exact error is:
"the value 'SET text='UPDATE ZZZ SET co......' is too long

I still cannot help until I see the code of the SP and the DDL of the referenced tables.

Andy

You are aware that what you're doing is a really bad idea if you don't know for sure which strings you may ever fetch? Just consider that the string may contain a single-quote. Unless you escape this, you will change the semantics of the SQL statement. (That's called SQL injection and its a security problem.)

------------------------------------------------
-- DDL Statements for table "DB2ADMIN"."W_OBJ_LIT_STRING"
------------------------------------------------


CREATE TABLE "DB2ADMIN"."XYZ" (
"ID" INTEGER NOT NULL ,
"LARGE" LONG VARCHAR FOR BIT DATA ,
"LITVAL" VARCHAR(1024) ,
"HASH" VARCHAR(40) )
IN "USERSPACE1" ;


Ok, well I have been able to specifically locate the problem. for the following stored procedure;

P1: BEGIN
-- Declare cursor
DECLARE temp VARCHAR(1000);

DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT LITVAL FROM DB2admin.XYZ where id=154; --particular row with the large data

OPEN cursor1;

FETCH FROM cursor1 INTO temp;

END P1

Ok so now if I remove the 'FETCH FROM cursor1 INTO temp;' statement, the retrieved LITVAL is of the full length. However, once I fetch into the variable temp, only the truncated string goes into temp when I check while debugging. Clearly I am looking for a way to define the fetch size for the cursor. Is there a way??

You are reading LITVAL from table that can have a length of up to 1024 and trying to stuff it into temp which is 1000 in length and you are surprised to have a truncation problem? Either increase temp to 1024 or change your query to only return 1000 bytes of LITVAL. Do the first option--it makes sense.

Andy

thanks for those quick replies andy.. really appreciate it. However The retrieved truncated values are around 110 characters in length only.
The long string itself which I am trying to retrieve is around 500 characters only & would easily get into the temp variable

Be that as it may, you should still increase temp to 1024 for when the length of LITVAL is over 1000 characters.

Can you post the complete and exact error message you are getting?

Andy

I increased the temp variable as suggested, still no success.
Well for the stored procedure I just pasted in the message above there is no error message. Its just that the temp variable contains a partial string.

Is there a way u can specify the size in bytes of data that a fetch query fetches??

Can you post the entire stored procedure, and what the exact problem is? Please supply all the details. These fragments are not helping.

Andy

How do you know that?