Author Topic: Text BLOB field getting cut off in DQM - Upgrade from 10.2.1 to 10.2.2 (DQM)  (Read 895 times)

Offline Mauri

  • Full Member
  • ***
  • Join Date: Jun 2012
  • Posts: 5
  • Forum Citizenship: +0/-0
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.
« Last Edit: 18 Nov 2016 07:46:29 am by Mauri »

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 9,364
  • Forum Citizenship: +567/-8
  • Cognos Software Muppet
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.
Meep!

Offline Mauri

  • Full Member
  • ***
  • Join Date: Jun 2012
  • Posts: 5
  • Forum Citizenship: +0/-0
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.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 9,364
  • Forum Citizenship: +567/-8
  • Cognos Software Muppet
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.
Meep!

Offline Mauri

  • Full Member
  • ***
  • Join Date: Jun 2012
  • Posts: 5
  • Forum Citizenship: +0/-0
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


Offline Yasser

  • Data Guy
  • Full Member
  • ***
  • Join Date: Jan 2017
  • Posts: 40
  • Forum Citizenship: +0/-0
  • I love Data
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?

Offline bdbits

  • Super Moderator
  • Statesman
  • ******
  • Join Date: Feb 2010
  • Posts: 1,703
  • Forum Citizenship: +97/-0
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.  :'(

Offline Yasser

  • Data Guy
  • Full Member
  • ***
  • Join Date: Jan 2017
  • Posts: 40
  • Forum Citizenship: +0/-0
  • I love Data
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  >:(  :o  ???



 



           
Twittear