COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Upgrading to Cognos 10 => Topic started by: Mauri on 18 Nov 2016 07:30:22 am

Title: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)
Post by: Mauri on 18 Nov 2016 07:30:22 am
Can someone please confirm that they are successfully able to view text BLOB fields in Cognos 10.2.2 reports published using Dynamic Query Mode?  We've been using them successfully from v8 - 10.2.1.  We have determinants set in Framework Manager so the issue isn't there.  When we publish outside of DQM in Framework (v10.2.2) we see the entire field.  When we publish in 10.2.2 using DQM, we don't get errors but the field gets cut off at 1024 characters and our actual BLOB fields are MUCH larger than that.  It is being recognized as a text BLOB in Framework Manger so i'm just wondering if this issue is on our side or if 10.2.2 published in DQM really does have issues with BLOB fields.
Title: Re: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)
Post by: MFGF on 18 Nov 2016 08:20:16 am
Can someone please confirm that they are successfully able to view text BLOB fields in Cognos 10.2.2 reports published using Dynamic Query Mode?  We've been using them successfully from v8 - 10.2.1.  We have determinants set in Framework Manager so the issue isn't there.  When we publish outside of DQM in Framework (v10.2.2) we see the entire field.  When we publish in 10.2.2 using DQM, we don't get errors but the field gets cut off at 1024 characters and our actual BLOB fields are MUCH larger than that.  It is being recognized as a text BLOB in Framework Manger so i'm just wondering if this issue is on our side or if 10.2.2 published in DQM really does have issues with BLOB fields.

Hi,

Yep - DQM does this. For an explanation of why (and a workaround you can add in your FM model), see the below IBM Support Doc:

http://www-01.ibm.com/support/docview.wss?uid=swg21657723

Cheers!

MF.
Title: Re: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)
Post by: Mauri on 18 Nov 2016 08:58:41 am
Well, that's getting a bit closer!  Thanks for the info.  I'm now getting the following error:

XQE-PLN-0286
The second argument to the 'cast' function is a character data type with a size that exceeds 65535.

The expression is:  cast(substring([BLOB Data Item],1,93000) as varchar(93000))
I also tried:  cast(substring([BLOB Data Item],1,65000) as varchar(65000))

The BLOB text field is approx. 93K characters. :(  Its huge, I know.
Title: Re: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)
Post by: MFGF on 18 Nov 2016 09:43:05 am
Well, that's getting a bit closer!  Thanks for the info.  I'm now getting the following error:

XQE-PLN-0286
The second argument to the 'cast' function is a character data type with a size that exceeds 65535.

The expression is:  cast(substring([BLOB Data Item],1,93000) as varchar(93000))
I also tried:  cast(substring([BLOB Data Item],1,65000) as varchar(65000))

The BLOB text field is approx. 93K characters. :(  Its huge, I know.

I believe 65535 is the largest size you can cast a varchar to. 93000 is simply too large. The second statement should work, though.

Cheers!

MF.
Title: Re: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)
Post by: Mauri on 09 Jan 2017 11:31:55 am
FYI, I ended up opening a ticket for this through IBM and they currently have an request for enhancement (RFE) open for this exact issue.  If you are experiencing the same issue, PLEASE go in and vote so this enhancement is included in the next Cognos release.

Here is the link to vote:  https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=89829

Title: Re: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)
Post by: Yasser on 18 Jan 2017 01:51:15 am
I'm facing the same issue,

Also the workaround is not working with me !!

I've a stored procedure returns a CLOB text of 60k character and I've inserted it in the FWM using the query

cast(substring([Oracle_DB].[SP1].[HTML] ,1,60000) as varchar(60000))

it is throwing me the following error !!

XQE-PLN-0286 The second argument to the 'cast' function is a character data type with a size that exceeds 65535.

Is there anything wrong with the query?
Title: Re: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)
Post by: bdbits on 18 Jan 2017 04:15:26 pm
BLOBS/CLOBS have always been a significant problem in every toolset I've worked with, but Cognos is especially annoying and very limited in that respect. I totally get why people use them (primarily rich or very long text), but they are a PITA. Much better to store them as documents on a filesystem and store the pathname, in my opinion. But, I am sure you have no choice.

So, it looks like you are using Oracle. If you put that expression in Oracle PL/SQL, does it work? I had thought the max for varchar was actually something like a pathetic 4k. Which means the workaround won't work, either. Maybe LONGTEXT would work, maybe not. I found a reference on Oracle data types here.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330 (https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330)

I feel your pain but have no cure.  :'(
Title: Re: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)
Post by: Yasser on 19 Jan 2017 03:06:03 am
BLOBS/CLOBS have always been a significant problem in every toolset I've worked with, but Cognos is especially annoying and very limited in that respect. I totally get why people use them (primarily rich or very long text), but they are a PITA. Much better to store them as documents on a filesystem and store the pathname, in my opinion. But, I am sure you have no choice.

So, it looks like you are using Oracle. If you put that expression in Oracle PL/SQL, does it work? I had thought the max for varchar was actually something like a pathetic 4k. Which means the workaround won't work, either. Maybe LONGTEXT would work, maybe not. I found a reference on Oracle data types here.
https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330 (https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54330)

I feel your pain but have no cure.  :'(

Many thanks bdbits for your feeling :)

Actually I want to crack my head on the wall, I've tried every solution can be and some weird workarounds, Nothing working at all  >:(  :o  ???


Title: Re: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)
Post by: bensah on 12 Jun 2017 01:43:35 am
Hi,

workaround for this issue. Please split the blob column after cast with multiple columns and in the report append all the columns. So that user will see all the data .

Thanks,
Benu