Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)

[Mauri]

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.

[MFGF]

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.

[Mauri]

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.

[MFGF]

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.

[Mauri]

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

[Yasser]

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?

[bdbits]

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

I feel your pain but have no cure. 

[Yasser]

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

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     

[bensah]

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