Discussion

Retrieve BLOB through Connect SQL?

Can i retrieve BLOB content using connect SQL? I have a table in external data base which is replica of pega pc_work table. I want to retrieve the records of that table through connect SQL. I am able to get the values of only exposed columns but i need to get values which are in blob also? Please help me.

**Moderation Team has archived post**

This post has been archived for educational purposes. Contents and links will no longer be updated. If you have the same/similar question, please write a new post.

Comments

Keep up to date on this post and subscribe to comments

March 30, 2011 - 1:47pm

No... BLOB content is only meant to be read using Obj- operations.

quote:


Originally posted by RAMAKRIM

Can i retrieve BLOB content using connect SQL? I have a table in external data base which is replica of pega pc_work table. I want to retrieve the records of that table through connect SQL. I am able to get the values of only exposed columns but i need to get values which are in blob also? Please help me.

March 30, 2011 - 2:12pm

RAMAKRIM wrote: "I have a table in external data base which is replica of pega pc_work table. I want to retrieve the records of that table through connect SQL."

Huh? What makes a table "external" is generally that it doesn't use pzInsKey/pxObjClass/pvPvStream. So what is stopping you from mapping to it?

Jon

March 30, 2011 - 2:15pm

Thanks Robert for your response. Is there any work around we can achieve this as i cant use the Obj-Methods?

March 30, 2011 - 5:22pm

you are right john. I dont have any pzinskey/pxobjclass or pzpvstream. My table is a replica of pc_work but with different column names.I have blob content stored in one of the column in external table. I am just wondering is there any way i can get that blob content from that column of the table.

March 30, 2011 - 11:21pm

Jon is right. You should be able to create a class which inherits from your current work class, set it as a class group, then update the D-A-DB-Table for that class to point to this other table. Then you should be able to do Obj-Open on items from that table.

Rob

quote:


Originally posted by RAMAKRIM

you are right john. I dont have any pzinskey/pxobjclass or pzpvstream. My table is a replica of pc_work but with different column names.I have blob content stored in one of the column in external table. I am just wondering is there any way i can get that blob content from that column of the table.

March 31, 2011 - 9:34am

Rob/John :

My actual requirement was my WO's are purged from my system and stored in another database as DB Records.

The primary purpose of our purging is to save the space so after that i should not have my WO information in direct or indirect way in my current DB System. I should not again load them in to my system through import archive. Through some connect-sql i want to interact with the external data base to get the records and exploring the possibilities.

March 31, 2011 - 1:05pm

The BLOB is only meaningful when it's access through PRPC using Obj- operations. If you still need access to some of the information on the work object, you'll probably want to look at creating a data warehouse to store that information. You can create R-D-Index's or use a tool like BIX to accomplish this.

But you could always create different branch in your class structure that points to archived work objects. For example, let's assume that Acme-Work points to your work objects in the pc_work table. You could create Acme-Archive-Work (which has directed inheritance from Acme-Work) as a class group. Then us the D-A-DB-Table to point to the other database/table used to store the archived work objects.

Rob Dunie

quote:


Originally posted by RAMAKRIM

Rob/John :

My actual requirement was my WO's are purged from my system and stored in another database as DB Records.

The primary purpose of our purging is to save the space so after that i should not have my WO information in direct or indirect way in my current DB System. I should not again load them in to my system through import archive. Through some connect-sql i want to interact with the external data base to get the records and exploring the possibilities.


February 5, 2015 - 8:43am

You can use connect sql rules to get the blob from a DB row.

 

The SQL would need to do a select on the pzpvstream some something like this:

 

SELECT pzpvstream FROM "tablename" where "clause"

This would get the blob and pega would read it and translate it to the full object.