SQL Data Source objects allow DataPower to interact with external SQL databases. These objects are part of the DataPower appliance's ability to connect to databases such as IBM DB2, Oracle, Microsoft SQL Server, Sybase or MySQL, enabling DataPower to execute SQL queries and retrieve or manipulate data from these databases. SQL Data Sources are often used in scenarios where DataPower is processing requests that involve backend database interactions. You would need to have respective license installed to use this feature.
Required details to create a SQL Data Source of type Oracle DB :
- Connection username - DB Username
- Connection password - DB Password
- Data source type
- Data source ID
- Data source host - DB Server
- Data source port - DB port
1. Create a Password alias object with DB Password, as this will be mapped during the creation of SQL Data Source.
3. Click on Add and configure the DB details accordingly as mentioned above.
Click on Apply for the object creation.
4. Click on Save configuration and ensure that object state is up.
5. Click on Services icon and click on Multi-protocol gateway (MPGW).
6. Click on Add and configure the MPGW accordingly with
- Use "default" XML Manager
- Dynamic Backend (Skip backend by using Set Variable in processing policy - service/mpgw/skip-backside as 1)
- Use a customised processing policy with Match Rule as /sqltest and SQL action
- HTTP frontside handler
- Request and response type is set "Non-XML"
Ensure to create a processing policy which has a SQL action as part of it as displayed below.
In SQL action, select the SQL Data Source that was created previously from the dropdown.
The source of the SQL statement to run can be static, derived from a stylesheet or in a variable.
Static - The action uses the SQL statement in the SQL Text field.
Stylesheet - The action derives the SQL statement from the stylesheet in the Transform field against the contents of the Input Context field.
Variable - The action uses the SQL statement in the variable that is specified by the Variable Name field.
In SQL Text field enter the SQL statement that you want to execute.
Example: "Select * from persons;" as shown in the above screenshot
Note : Can execute any SQL statements like CREATE, UPDATE, etc.
Click on Apply to create the MPGW.
7. Click on Save configuration and ensure that object state is up.
8. Using Postman/Insomnia hit the endpoint "http://<DP_HostName>:<PortOfHTTPHandler>/sqltest" as a GET request
Below is the sample response for the same
Refer the sample screenshot of DB as shown below for the query Select * from persons; for comparison with the above response
PS : The above steps are performed on DP version 10.6.1.0