How can we dynamically connect to Database dynamically at run time
Hello All,
We have a requirement, where we need to connect to different Auction external database based on a specific location. So when a user A from say NewYork is logged in, the application is to connect to the Auction external DB in NY and so on. Once it is connected to that Database, it will execute a SQL on that Database and fetch the results.
We have 70 such locations. The way we have built this is create a Database instance(DATA-ADMIN-DB-NAME instance) for each of the 70 locations in Pega. Each of the instance points to the Datasource of the corresponding Auction Database. We then created a 70 Data-Admin-DB-Table instances each of which points to the corresponding Database instances. We then created a Class instance (under Data-) that points to the corresponding Data-Admin-DB-Table instance.
We then have a SQL connect rule in (Data-) class, which executes query to be executed. In the SQL query, we are referring to class instance dynamically by passing the name as
{class: {MyLegacyItem.ConnectClassName}}
The questions i have is:
- Is this the only way we can achieve dynamic connection to different external Databases? Do we have to always create a DATA-ADMIN-DB-NAME instance, one Data-Admin-DB-Table instance and a Class instance. Is there any way in which the JDBC Data source name, can be passed dynamically to the DATA-ADMIN-DB-NAME instance, so that i can avoid creating Data-Admin-DB-Table instance and a Class instance.
- Can i have a page property in Data- class, so that values returned from RDB-List call is directly populated in the property page
Appreciate your insight and information on this.
Thanks,
Swami
***Updated by moderator: Lochan to add Categories***
Bank Of America
US
1. Create a Data table with 4 columns- Location, Database name,schema name and Table name.
2. Fetch the Database name,schema name and Table name by Passing the Location using data page look up.
3. Create a single Instance of Data-Admin-DB-Table.
4. Do an Obj-Open-By_Handle on the Data-Admin-DB-Table instance created in step 3.
5. Do a property-set and update the pyDatabaseName,pyTableName and pySchemaName with the value retrieved from step 2.
6. Do an Obj-Save and Commit then and there.
7. Then you can call your own retrieval methods or storing methods.
The only thing is that you have to call this utility before Object creation and retrieval. So at run time the Data-Admin-DB-Table will behave differently for different locations.