Implementing the components of the Retrieve Policy Details Integration Service
- Implement the Set Query component:
- Click on the Set Query component (Figure 16) and
click on Implementation.
Figure 16. Component diagram of Retrieve Policy Details
- In the Script section, enter the following SQLstring:
SELECT POLICYNUM as POLICYNUM, POLICYISSUEDATE as POLICYISSUEDATE, POLICYSTATUS as POLICYSTATUS, POLICYCOMMENCEMENTDATE as POLICYCOMMENCEMENTDATE, POLICYMATURITYDATE as POLICYMATURITYDATE, PROPOSERNAME as PROPOSERNAME, FIRSTLIFEASSURED as FIRSTLIFEASSURED, FIRSTLIFEASSUREDNAME as FIRSTLIFEASSUREDNAME, GENDERFIRSTLIFEASSURED as GENDERFIRSTLIFEASSURED, SECONDLIFEASSURED as SECONDLIFEASSURED, SECONDLIFEASSUREDNAME as SECONDLIFEASSUREDNAME, GENDERSECONDLIFEASSURED as GENDERSECONDLIFEASSURED, VIP as VIP, CLAIMNUMBER as CLAIMNUMBER, CLAIMSTATUS as CLAIMSTATUS FROM DB2ADMIN.POLICYDETAILS WHERE POLICYNUM=?
- In the Binding section, click on the
Select button to select the variable to store
the SQL string into a private variable, and then select the
private sql variable (Figure 17).
Figure 17. Implementation of Set Query component
- Click on the Set Query component (Figure 16) and click on Implementation.
- Implement the Set Parameter component:
- Select the Set Parameter component and click on its Implementation property (Figure 18).
- In the Script section, enter the following script (see Figure
tw.local.params = new tw.object.listOf.SQLParameter(); tw.local.params = new tw.object.SQLParameter(); tw.local.params.value=tw.local.policyNum; tw.local.params.type="VARCHAR";
Figure 18. Implementation of Set Parameter component
- Implement the SQL Execute Statement component.
- You have already defined the SQL string and kept it in a private variable named sql. For this component, you need to pass the SQL string into this component as one of the inputs. Furthermore, this SQL component also requires a parameters list, a return type, a data source name, and and so on as input. Fortunately, you have planned for the return type, data source name, and parameters list.
- Click the SQL Execute Statement component and select the Data Mapping property.
- Click the mapping button to map the SQL string as
shown in Figure 19.
Figure 19. Mapping the data for the SQL Execute Statement component
- Uncheck the "Use default" checkbox of parameters to edit its content. Click the mapping button to map the data to the private variable named params.
- Uncheck the "Use default" check of returnType, and enter
PolicyDetailsas its value as shown in Figure 20.
- Edit dataSourceName and insert
jdbc/sampleDBas its value.
- For the results(ANY) field of the mapping output
section, choose policyDetailsList as the
Figure 20. Full mapping data of the SQL Execute Statement component