Working with External Database
Integration
To work with External DB integration, we need to follow below steps.
1. Create a Database in DB environment (local or remote), define required privileges.
2. Create a Table , defined require privileges.
3. Get the connection details of DB from DBA.
4. Create a Database rule in PRPC, to connect to external DB.
5. Create a Data class, properties and define keys.
6. Create a Database table rule, map the above class to External Table.
7. Use Obj or RDB Methods to access the data from external Tables.
8. When using Obj Methods, no need to create Connect-SQL rules.
9. When using RDB methods, we have to Create Connect-SQL rules, and write our own SQL
queries.
Below are the RDB methods that PRPC Provides.
1. RDB-List (Return pxResutls)
2. RDB-Open (Return sing Instance Page)
3. RDB-Delete (Deletes instances from table)
4. RDB-Save (Creates or Updates instance of table)
Note :
While working with home version pgsql environment, make sure to use all LOWER CASE alphabets
to define database, tables and column names.
How to create a DB, Tables and
define
privileges using Postgres SQL
Environment
Requirement: Create an External Database ‘primextdb’. Create a table ‘vindetails’.
Column VIN is Primary Key.
Make a connection between PRPC environment and above DB and it’s tables.
Implementation:
1. Create DB
Click on PLUS icon
Click on save
Click on save again
Now, DB is created. By default above db has a schema with name ‘public’.
2. Create table
Go to Security tab
Save. Done.
Add some test data.
Done.
3. Create a Database rule to connect to above DB.
Test Connection
Done.
4. Create a data Class.
Save. Done.
Create Properties.
Save. Done.
Define VIN as a key for the above class.
Save. Done.
5. Create a database table rule and map the above class to external table.
Save.
Test connectivity
Class PRIM-Data-vindetials is mapped to table public.vindetails in primextdb.
This is an Obj-external class. You may access the class with Obj- activity methods.
DONE.
Note : When using Obj-Browse for external Tables, we hae to un check the option “Get Row Key”
Obj-Open-by-Handle, Obj-Delete-by-Handle methods wont work on External Table unless we have
pzInsKey columns available there.
Locking will not work on External Tables.
Working with RDB Methods
RDB methods can be used to invoke Connect SQL Rule.
Connect-SQL Rule : This rule defines SQL Queries.
In a connect SQL rule , we can write 4 different Queries in 4 tables.
1. Open : We can write select query based on key. It returns page.
2. Delete : We can write delete Query.
3. Save : We can write insert or update queries.
4. Browse : select query which returns page list.
Now, we need to create an activity and use RDB methods to access above rule.
Now, let’s have a look at RDB methods
All this methods accepts same parameter values.
Open Class, Request Type , Access = Package name on SQL rule.
All parameters given same values going to invoke same connect SQL rule.
Based on Methods name Process Commander identifies, which tab of connect sql rule to
be executed.
DONE.
FYI…
The queries we write in Conenct SQL rule should be in below format.
SELECT "vinid" AS "VINID" , "company" AS "Company" , "model" AS "Model", "makeyear"
AS "MakeYear"
from {CLASS:Arbe-Data-VINDetails}
where vinid = '{Asis:MyServicePage.VINRequest.VINID}'
Asis : Using this Key Word ASIS, trims off the Spaces around the values being passed to DB
table. Also if any special chars around the values also will be removed.
FYI…
When Columns and property names of Table and Class are not matching follow below
steps of external mapping.
Save. Done.
After this our queries should be written using alian keywork ‘as’.
SELECT "vinid" AS "VINID" , "company" AS "Company" , "model" AS "Model", "makeyear"
AS "MakeYear"
In place of table name, we can write class name as shown below. At run class names gets
resolved into table name using Database table rule.
from {CLASS:Arbe-Data-VINDetails}
DONE.
How to call A Stored Procedure using Connect SQL rule
DONE.
