Question
Anthem
US
Last activity: 15 Sep 2015 16:37 EDT
postgres sql database integration
I am trying to integrate with a postgres sql database.
We are integrating a sql database with pega 7.1.6. We have created the database instance and used the database class mapping tool to map the class in Data. I can see each column as they are displayed as a property underneath the table name.
The hierarchy is like this Bsu-Intergrat-Data.
How do I actually use the properties that were created in the database class mapping tool. I would gather I need a data page somewhere. Does that data page need to be on the work or data side?
I have tried to make a data page and then also try to use connector as a source of the data page which I dont seem to be doing correctly. The data page creation screen at the data source I ran the connect and metadata wizard which then created the Bsu-Integrat-Work-Int class where I can again see the properties from the actual database but not see them or manipulate them in pega.
The connection is good to the database and the mapping correctly displays each column. But how do i actually query the database and show results in pega?
Thanks for your help in advance.
Pegasystems Inc.
US
Michael,
I'm not sure I exactly follow what you are trying to do. Do you want to create an object that you can interact with on the clipboard and write to that specific table? You created the class "Bsu-Intergrat-Data" and allowed the system to build a table with columns for the various properties you care about? If you create a clipboard page of that class and map values into your page, can you successfully to an obj-save to see it get written to the DB? If that works, an obj-open-by-handle should work to get instances out from the database (or any of the other methods for reviewing objects in the database, like obj-browse).
I'm not 100% certain I actually answered the question for you. It might help if you could clearly outline the business problem you need to solve with this. Then it would be easier to offer guidance on how best to do that.
Thanks,
Mike Townsend
Anthem
US
Thank you for the comment.
This was initially a problem mapping and connecting to the postgres database. I have had some correspondence with a pega employee and I have defined the problem we are having in depth with him.
I am trying to view the contents of a database on the ui. Also we want to edit a record add a new one and then delete when necessary.
First we connected to the Database through the records explorer. In System admin dropdown under databases we added our postgres db along with table we want. the connection was successful. Seen in this screenshot (TestDBConnect)
Next we mapped the db using the databass class mapping tool. This resulted in the data object structure you see in this screenshot
Thank you for the comment.
This was initially a problem mapping and connecting to the postgres database. I have had some correspondence with a pega employee and I have defined the problem we are having in depth with him.
I am trying to view the contents of a database on the ui. Also we want to edit a record add a new one and then delete when necessary.
First we connected to the Database through the records explorer. In System admin dropdown under databases we added our postgres db along with table we want. the connection was successful. Seen in this screenshot (TestDBConnect)
Next we mapped the db using the databass class mapping tool. This resulted in the data object structure you see in this screenshot
After that I created a data page the location is where you see data page in the previous screenshot. I will show you the configuration I have for the data page in this screenshot (DataPage).
If you would check the Object type. Because I have referenced the work side and the data side trying to make it work. In this example we have the data side. Also I am not sure what data transform to use on the configuration as well. (NOTE: I HAVE TAKEN THE DATA TRANSFORM OUT)
This DataPage is Located here (DataPageLocation)
Now to the work side of things. Here we have my case structure. (Work)
As you can see DBProperty is the property I have created as a page. Name is the is db column I am trying to reference and then utilize in my UI.
Here is the configuration of the of our DBProperty (DBproperty)
From there I drag the name property under DBProperty Into the UI to try to manipulate and use it. This feature pops up and i'm not sure what to do with this screen. (NameError)
Then if I drag the DBProperty into a section and run the case this error message pops up. (Error)
There are a number of other things I have tried to do to many to go into detail. I have ran the connector and metadata wizard with no luck
Pegasystems
US
Michael Talbott, you are on the right path with the Data Page. Don't use activity for reading data from a DB.
When you drag the property onto the UI, Pega prompts you for a Page to understand where is this property going to come from.
The name of the page should be "pyWorkPage.DbProperty"
Anthem
US
Hey Marc thank you for the comment.
Here is what error screen is thrown from the clipboard. I really think I am close to getting this.
Anthem
US
Is there any known issues with psql 9.3.6?
Pegasystems
IN
Hi Michael,
Can you please elaborate what you mean by known issues with PostgreSQL 9.3.6? If you execute the Data page directly, are you getting the results?
-Rajiv
Anthem
US
Here are tracer results. It acts like the database isn't responding at all. I was told yesterday my configuration is good in detailed conversation with a pega employee.
Pegasystems Inc.
US
Could you show database operations in Tracer & see what you get?
Anthem
US
I'm not even sure where to begin to find my sql queries for the database. The only SQL queries I see right now in tracer is to the pegadata database.
Pegasystems Inc.
US
I don't think there is a distinction between databases in Tracer--it should show you all or nothing.
Another options would be to try DBTrace.
I think this explains what you are trying to do:
https://pdn.pega.com/integration/how-to-map-an-external-database-table-to-a-class
You'd use Obj-* methods to interact with this external DB table.
Anthem
US
Thank you for the reply.
We are working in pega version 7.1.6. I have viewed this article and read it very thoroughly. The connector and metadata wizard took me through a very similar process and then created activities that seem to be the methods you refer to the Obj-* methods. My question is where do I utilize these activities?
here is a screenshot of the outcome of the connector and metadata wizard.
You can also see in the work class Int- is created where my database columns are mapped out there as well yet I still can't utilize them.
Pegasystems Inc.
JP
Have you tried defining a simple report definition on the external data class and test run the report definition to see if it can produce results?