Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2009
    Posts
    44

    Unanswered: Data returned in result set columns is truncated

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What happens if you call the SP from the CLP?

    Andy

  3. #3
    Join Date
    Jul 2009
    Posts
    44
    same error from command line as well

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post your DB2 version, OS, Code for the SP and any DDL for the tables referenced?

    Andy

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Also post the exact error you got from the CLP.

    Andy

  6. #6
    Join Date
    Jul 2009
    Posts
    44
    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

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I still cannot help until I see the code of the SP and the DDL of the referenced tables.

    Andy

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.)
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Jul 2009
    Posts
    44
    ------------------------------------------------
    -- 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??
    Last edited by rocker86; 07-29-09 at 08:16.

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by rocker86
    ------------------------------------------------
    -- 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

  11. #11
    Join Date
    Jul 2009
    Posts
    44
    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

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

  13. #13
    Join Date
    Jul 2009
    Posts
    44
    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??

  14. #14
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Can you post the entire stored procedure, and what the exact problem is? Please supply all the details. These fragments are not helping.

    Andy

  15. #15
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by rocker86
    Its just that the temp variable contains a partial string.
    How do you know that?
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

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