So far you have completed a few lessons on how update override functionality can be used for custom data manipulation and web service updates.
In this lesson you will learn how custom SQL updates can be used for performing manual updates to a relational source (table, view, stored procedure, or SQL Exit), using update overrides and JDBC.
Objectives
After completing this lesson, you will be able to:
Add update functionality to a previously created update override.
Write an update override for performing manual updates to a relational source (table, view, stored procedure, or update override) via JDBC.
Create an update override for a physical data service.
Setup the update override to be a JDBC client and write logic to update the database table.
Overview
Update overrides are useful in situations where you need to perform some custom updates or create a custom query.
In this particular case, since the previous update override lacks update functionality, you can add an update statement to the override.
25.1 Adding SQL Update Statements to an Update Override File
You can add SQL update statements to an update override file, thereby enabling custom data manipulations in relational databases.
Objectives
In this exercise, you will:
Import the Java folder, which contains the MySQLExit.java file.
Add SQL update statements to the Java file.
Instructions
Right-click the SQL folder located in DataServices project, choose Import, and select the Java folder from the <beahome>\weblogic81\samples\LiquidData\EvalGuide folder.
Click Import and verify that the Java folder is added to the SQL folder.
Open MySQLExit.java, located in the DataServices\SQL\Java folder.
Locate the line "Type in your UPDATE SQL statements here".
Enter the two following SQL statements and store them into updateStr and updateStr1 respectively:
"UPDATE RTLCUSTOMER.CUSTOMER SET FIRST_NAME=?, LAST_NAME=? WHERE CUSTOMER_ID=?";
"UPDATE RTLCUSTOMER.ADDRESS SET CITY=?, STATE=?, ZIPCODE=?, COUNTRY=? WHERE ADDR_ID=?";
Your code should look like the following:
String updateStr = "UPDATE RTLCUSTOMER.CUSTOMER SET FIRST_NAME=?, LAST_NAME=? WHERE CUSTOMER_ID=?";
String updateStr1 = "UPDATE RTLCUSTOMER.ADDRESS SET CITY=?, STATE=?, ZIPCODE=?, COUNTRY=? WHERE ADDR_ID=?";
Figure 25-1 MySQLExit.java
Save MySQLExit.java and close the file.
Build DataServices project.
25.2 Associating an SQL-Based Data Service and Update Override
You must now set the update override class to the MySQLExit. This will let you get any updated changes and pass the new value.
Objectives
In this exercise, you will:
Associate the update override class with the MySQLExit.
Confirm the settings in the Property Editor.
Instructions
Open MySQL.ds in Design View. The file is located in the DataServices\SQL folder.
Click the MySQL Data Service header. The Property Editor opens.
In the Property Editor, set the update override class by selecting MySQLExit from the DataServices\SQL\Java folder.
Save the MySQL.ds file.
Build your DataServices project.
25.3 Testing Updates
You are now ready to test whether the update override functions correctly.
Objectives
In this exercise, you will:
Test the update override, by using the MySQL data service to make changes to the underlying relational data source.
View the results.
Instructions
Open MySQL.ds in Test View.
Select MySQL(x1) from the Function drop-down list, enter CA, and click Execute.
Click Edit.
Test if updates are getting propagated to the database, by completing the following steps:
Select any Customer node.
Modify City and Zip Code elements.
Click Submit to issue the update override commit command and propagate changes to the database.
Select MySQL(x1) from the function drop-down list, enter CA, and click Execute to confirm that your database is updated.
Lesson Summary
In this lesson, you learned how to:
Create an update override for a physical data service.
Setup the update override to be a JDBC client and write logic to update the database table.
