Question
Accenture
IN
Last activity: 23 Sep 2016 6:50 EDT
How to read multiple property from db
As we all know that using pr_read_fraom_stream, we can read the property present in blob.
But this is for single property, is it possible to read multiple property ?
***Updated by Moderator: Vidyaranjan. Removed user added #helpme and Ask the Expert tags. Apologies for confusion, shouldn't have been an end-user option***
-
Like (0)
-
Share this page Facebook Twitter LinkedIn Email Copying... Copied!
Pegasystems
IN
Kindly elaborate your use case.
You can read multiple properties by calling this function multiple times (once for each property).
If your intent is to get all values from page list and page group (or value list and value group), then you must use a declare index to normalize rather than using UDFs.
Note that UDFs drain on performance compared to getting data from optimized columns.
Accenture
IN
Hi Nis,
Not looking for page list for now, but working with them would be addition knowledge item for my day :)
In a post i found that through pr_read_from_stream we can read values from blob through a select query, so was wondering if there are function which can bring multiple properties.
Did not consider the option to use the function on multiple column , as thought that would be a performance hit.
As in this case, blob will be opened for each record, and for each column. Posted this post, because thought there might be function which can bring multiple values
is pr_read_from_streamn is a User Defined Function?
If i want to write user defined function for these blob , where should i begin my learning ?
Thanks
~Ross
Pegasystems
IN
is pr_read_from_streamn is a User Defined Function?
Yes, it is a user defined function which can "pierce" the BLOB to retrieve the value of a scalar property
If i want to write user defined function for these blob , where should i begin my learning ?
Kindly elaborate on the use case you are trying to solve by writing your own function to read the BLOB. Note that the content inside the BLOB is an encoded stream of bytes which only the Pega platform understands.
Accenture
IN
Thanks Nis for the reply that was knowledgeable to me.
Just curios to know , if there is a udf to retrieve value from blob then there must be a udf where multiple values can be retrieved from the blob, or may be i can customize the udf as per any requirement, Will I be able to write such udf ?
And i can use this udf in many place , Like take the first non null values from the 3 fields, or may be extract blob information into a seperate table.
Where can i find the list udf available in pega 7.1.7?
Can you please look into this post as well , as i have not received any reply yet for this
https://collaborate.pega.com/question/how-can-i-call-activity-release-lock-declaratively
Regards
~Ross
Pegasystems
IN
Just curios to know , if there is a udf to retrieve value from blob then there must be a udf where multiple values can be retrieved from the blob, or may be i can customize the udf as per any requirement, Will I be able to write such udf ?
While it is feasible, it is currently not available. The UDFs are functions written in Java (C# for MS SQL Server) and deployed in the database if it has not been unchecked as part of the installation. As mentioned before, the encoding of the byte stream is custom to Pega and thus you will not able to write your own UDF (or modify existing one).
Where can i find the list udf available in pega 7.1.7?
UDFs are deployed inside the database and cannot be viewed from the designer studio. Please refer to this PDN article - https://collaborate.pega.com/discussion/understanding-and-troubleshooting-user-defined-functions
Accenture
SG
Hi Ross,
Can you let us know where can we find this "pr_read_from_stream " in Designer studio. As i serached all the function but i didnt find it
Accenture
IN
Hi Aditya,
Its resides in db, thats where i found it.
@pdnsupportteam: I dont received alert in my inbox like the way i used to get in mesh.pega.com. Can you pls help me ?
Regards ,
~Ross
Pegasystems Inc.
IN
Hi Ross,
You can create requests to PDN team by clicking on the black feedback button available on the right side of your browser screen.
I had sent you a reply on our private conversation through PDN messages stating the same, I guess you have not got a chance to read that yet!
Regards,
Lochan | Community Moderator | Pegasystems Inc.
Accenture
IN
Yes i have replied to pdn team by clicking the black feedback button some time back, Replies to these kind of mesage comes to email / inbox, inbox would be great to check ☺
If the message sent by you contains a solution, can you please send that again , as i can see lots of group chat with bigger images for staled post follow up.
Regards,
~Ross
Pegasystems Inc.
IN
Hi Ross,
Thank you. You would get any update on the PDN requests as email notifications.
I just wanted to ensure that you have set your profile to receive email notifications from PDN and suggest to click Subscribe to Comments on the post as an extra measure.
Regards,
Lochan
Accenture
IN
Hi Lochan,
Thanks for the information, So that means post notification will be received as email !
Pegasystems Inc.
GB
"pr_read_from_stream" is a Java (or .Net Assembly in SQLServer) backed function that resides and runs in the database server - as opposed to running in the Application Server's ( ~=PRPC's) JVM.
The primary purpose of this Function (and its related functions) is to enhance the PRPC Reporting Mechanism - to avoid having the end-user having to 'expose' properties (Where 'exposing a property' means something like 'mirror' scalar values held in the BLOB so that the data is available directly in a non-binary Database Column (such as a VARCHAR column etc)).
As Rajiv pointed out: the function only works on Scalar values; it cannot 'descend' into complex types (such as Page Lists, Page Groups etc) - in fact I'm not sure how such a Function would work - it would presumably have to bring back an XML representation of the complex types, which itself would need to be (possible recursively) examined again in order to traverse the structure further).
If you want to process complex, recursive PRPC Data structures, you should use OOTB PRPC Rules (such as Data Transforms, Activities and the like) rather than using the 'pr_read*' functions.
I hope this make sense,
Thanks,
John
Accenture
IN
no no, Let me elaborate a bit about the requirement.
May be there is something already there in pega, just need the name of the udf.
Suppose there is a blob for A-B-C class, under which there are three property say a,b,c.
as Nis suggested that this can be done by calling multiple time pr_read_from stream, i.e my query will look like "select pr_read_from_stream(a),pr_read_from_stream(b),pr_read_from_stream(c) from table name where some condition"
I was trying to find some UDF where i will be able to form a query like "select pr_read_from_stream(a,b,c) from table name where some condition".
If it is not there, then can i create a udf which will do similar stuf , and how to start reading about creating my own udf.
Another post that i am looking into it , if possible kindly revert on this post as well
https://collaborate.pega.com/question/how-can-i-call-activity-release-lock-declaratively
Regards,
Ross
Pegasystems Inc.
GB
Ah : ok - you need to fetch multiple scalar properties from the BLOB in one read - I get it now.
I have another question though; if you intend to have this form of SQL:
select pr_read_from_stream(a,b,c) from table name where some condition".
Do you plan on having a 'splat' argument for the parameters (a,b,c,[...],n) - or just a fixed set of parameters ? [Cos I'm not sure how a 'splat' would work here?]
Secondly: how will your function return those multiple values - in the form a VIEW (a recordset with a heading per argument) I guess ? (or perhaps 'Table-Valued' functions that are available in SQLServer for instance?)
Are you calling this SQL from Java ? Couldn't you generate the SQL you need ? I'm not sure where the advantage of having a slightly shorter way of writing it would be ? (I guess it would be 'syntactic sugar' of sorts maybe?)
ie. generate something like:
select
pr_read_from_stream('.pyUseCheckOut' ,pzInsKey,pzpvstream),
pr_read_from_stream('.pyDescription' ,pzInsKey,pzpvstream),
pzinskey
from pr4_rule_ruleset;
(this is based on the suggestion already made by NISTR).
Accenture
IN
Thanks for the reply,
Variable number of argument would be great.
returning multiple values should be in the form of rows that we get in sql.
No i am not calling from java, just was wondering to gain knowledge for bringing multiple values from blob.
Can i write such customized udf , using some api/function if not already there ?
Pegasystems
IN
Can i write such customized udf , using some api/function if not already there ?
This is currently not feasible as I mentioned that the byte stream in the BLOB column is encoded and known only to the Pega platform engine. You could raise an enhancement request outlining your use case.
Accenture
IN
Thanks for the reply Nis ☺
As this was just a knowledge related POC , not raising the request with Pega.
Have Good Day !
~Ross
Accenture
IN
Any Suggestion or updates on this ?
Pegasystems Inc.
GB
Can you write out explcitly what your SQL would look like with the proposed UDF that you would have to write.
I'm not sure how this would work: two things (as I mentioned above) come to mind:
1. A SQL function usually returns a single value (not a 'table of values')
2. A SQL function usually has a fixed parameter list - not a variable one.
I could be out of date on this; perhaps SQL Function can return an arbitary number of results from a single Function Call; and perhaps Function call can have multiple arguments - I'm not sure what that looks like.
So: the following SQL will achieve what you asked for, using the existing UDF functions:
select
pr_read_from_stream('.pyUseCheckOut' ,pzInsKey,pzpvstream),
pr_read_from_stream('.pyDescription' ,pzInsKey,pzpvstream),
pzinskey
from pr4_rule_ruleset;
But you need this is in a short-hand (a sort-of 'syntactic-sugared' version); my question is - what would this SQL look like (from the point of view of calling the [proposed] UDF ) ?
Pegasystems Inc.
GB
Just a thought - you could perhaps use Dynamic SQL (generated in a Strored PROC) here to convert your list of arguments into the longer versioned SQL above - the result of which would be a (virtual) Table (or VIEW) of results.
Your Stored Proc could (for instance) take a single String/VARCHAR param - which contains a comma-separated list; then you would have to parse this into separate components; which you could then use to generate dynamic SQL, which you could then execute within the SPROC.
The implementation of this will vary , depending on what database you are using.
And of course: this will still only work for Scalar Properties (I think?) help within the BLOB.
Pegasystems Inc.
GB
OK: so there are apparently ways and means of returning multiple results from a DB Function : see this Oracle Post for instance : https://community.oracle.com/thread/369849