Skip to main content

This content is closed to future replies and is no longer being maintained or updated.
Links may no longer function. If you have a similar request, please write a new post.

Question

 Nalina Ravibiruthoji (Nalins_99)
Booz Allen Hamilton
Lead Engineer
Booz Allen Hamilton
US
Nalins_99 Member since 2015 32 posts
Booz Allen Hamilton
Posted: May 9, 2018
Last activity: May 14, 2018
Posted: 9 May 2018 14:57 EDT
Last activity: 14 May 2018 9:37 EDT
Closed
Solved

Report Definition error in retreiving large string data

Hi

We have to run a report definition on a data class, in which one of the property stores a RTE of 5000 characters. When RD is run we get the error:

An error occured on executing the query for the report definition - There was a problem getting a list: code: 6522 SQLState: S0001 Message: A .NET Framework error occurred during execution of user-defined routine or aggregate "pr_read_from_stream": System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 5066 bytes to a T-SQL type with a smaller size limit of 2000 bytes. System.Data.SqlServer.TruncationException: at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)

We are not sure, where this size is specified and if we can change it or how to handle this?

When we run an activity Obj-Open-by-handle, we are getting back the content for the property.

Any help is appreciated. Thank you

To see attachments, please log in.
User Experience

Accepted Solution

Posted: 6 years ago
Posted: 14 May 2018 9:36 EDT
Nalina Ravibiruthoji (Nalins_99)
Booz Allen Hamilton
Lead Engineer
Booz Allen Hamilton
US

Thanks for the explanation. But I am not able to make changes in Prod. So found a round about way to fix it. Removed the property that contains the long string and called it from the activity (obj-open-by-handle) to display it in the model dialog. But still I will look into it in dev, and try to make the changes. Thanks and appreciate your help.

To see attachments, please log in.
Posted: 6 years ago
Posted: 10 May 2018 13:54 EDT
Anil Vanaparthi (vanaa)
PEGA
Senior Manager, Services Engineering, Case Management
Pegasystems Inc.
IN
Is it an exposed property? I think max size of exposed property will be 2000 after that it would be stored in blob. If your property is not exposed, try exposing it and run the Rd.
To see attachments, please log in.
Posted: 6 years ago
Posted: 10 May 2018 16:41 EDT
Nalina Ravibiruthoji (Nalins_99)
Booz Allen Hamilton
Lead Engineer
Booz Allen Hamilton
US

It is not an exposed property. Still it limits to 2000. Is there a way to increase this?

To see attachments, please log in.
Posted: 6 years ago
Posted: 10 May 2018 19:32 EDT
Xavier Merlin (MERLX)
PEGA
Principal Software Solutions Engineer
PEG
GB

The limit is not set by Pega but by the database.

This is due to the limit in size of the data type returned by the function PR_READ_FROM_STREAM.

In Oracle, the limit is 4000:

https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1822

Code found in Oracle:

Show More

The limit is not set by Pega but by the database.

This is due to the limit in size of the data type returned by the function PR_READ_FROM_STREAM.

In Oracle, the limit is 4000:

https://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1822

Code found in Oracle:

create or replace FUNCTION             pr_read_from_stream
(property in varchar2, insKey in varchar2, 
storage_stream in blob)
return varchar2 DETERMINISTIC
 
I'm puzzled why the limit is 2000 in your case. I expected more like 4000 character (8000 bytes).
Check how the function is defined in the database. Maybe a limit in the output is defined.
 
Could you share the code you find in the database for pr_read_from_stream?
I don't have an SQL server database at hand to check.
 
If the limit is set somehow on the output parameter, I expect you can increase it and you should be fine but this should be tested.
 
Otherwise, look at this:
 
Behind the scene, when a report definition is using unoptimized properties, Pega uses the java function pr_read_from_stream to retrieve the value of a property in the blob. This is for performance reason.
You face limitation about this function.
When you open the item, the full blob is being read (no pr_read_from_stream used) hence it works fine.
 
Also, it could be considered to disable UDF (which disable the use of these java function in the database) but I advice against it as Pega is not tested without UDF and even I have seen recently developer facing issues trying it and having ultimately to optimize property or activate UDF.
 

Show Less
To see attachments, please log in.

Accepted Solution

Posted: 6 years ago
Posted: 14 May 2018 9:36 EDT
Nalina Ravibiruthoji (Nalins_99)
Booz Allen Hamilton
Lead Engineer
Booz Allen Hamilton
US

Thanks for the explanation. But I am not able to make changes in Prod. So found a round about way to fix it. Removed the property that contains the long string and called it from the activity (obj-open-by-handle) to display it in the model dialog. But still I will look into it in dev, and try to make the changes. Thanks and appreciate your help.

To see attachments, please log in.
Posted: 6 years ago
Posted: 11 May 2018 1:53 EDT
Anil Vanaparthi (vanaa)
PEGA
Senior Manager, Services Engineering, Case Management
Pegasystems Inc.
IN

without exposing the property you may not be able to fix this. As suggested by Merlx, you can set the column size to 4000.

To see attachments, please log in.

We'd prefer it if you saw us at our best.

Pega Collaboration Center has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice