How can we dynamically connect to Database dynamically at run time
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
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.
***Updated by moderator: Lochan to add Categories***