DataPower DataBase Connectivity and Performing CRUD Operations
Here in this blog, we are going to explain the steps involved for IBM DataPower DataBase Connectivity and performing CRUD Operations.
Datapower SQL Data Source:
Follow the following steps to connect the database to DataPower.
Steps:
- Obtain the following details about the DataBase from the concerned team
Database Credentials/details:
- server name: DESKTOP-7GS24B1
- port: 1433
- IP: 192.168.0.230
- user: sa
- password: welcome@123
- database: ACE34
Note: The user type used for authentication should be SQL server authentication. Please don’t use a user account with Windows authentication
2. Go to the search tab and search password mapping alias in DataPower.
Create a password alias configuration with the password of the user account which will be used in the SQL data source object.
Click on apply and save configurations.
3. Navigate to the SQL data source object available under network > Other settings to create a new SQL data source object.
4. Create a new SQL data source and configure the details with the details gathered about the database.
Now click on data source configuration parameters and click on add and give the name and value.
Click on apply and save configurations.
5. Now configure mpgw.
Click on processing policy.
Drag advanced action.
Click on advanced action and select the SQL as action type.
Click on next.
Now select the SQL Data Source that we created(MS-SQL-DB).
Now select the SQL Input Method.
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:
SQL Statement: create table emp9(empid numeric(10) NOT NULL ,empname char(10),empsalary numeric(10),deptno numeric(10));
Click on done.
next drag advanced action and select a set variable for action type click on next and then select variable name as a var://service/mpgw/skip-backside
and enter variable assignment as 1(any value) and click on done.
And click on apply policy.
Now go to the postman and hit the URL
Go to the database:
If I want to insert the values into the table go to policy and click SQL action type in the SQL text field and enter the below statement.
SQL STATEMENT: insert into emp9 values(101,’anusha’,15000,2),(102,’saitejaswi’,20000,1),(103,’vishwa’,25000,4),(104,’vaishu’,25000,3),(105,’prakash’,30000,5),(106,’meghana’,20000,3);
Click on done and apply the policy. And hit URL in postman.
Now go to the database