Introduction:
This entry guides you through the steps on how to run a direct SQL Update query using the ETL Step “JDBC SQL Updater”.
The example in this entry reads the values to be updated from a XLSX file and use those values as parameters to be parsed into the update query. Values could also be hardcoded into the update query if required.
Prerequisites:
1/ To establish a connection to your database table, feel free to refer to the following entries below as a guide:
- Creating an Oracle JDBC connection pool
- Creating a MSSQL JDBC connection pool
- Creating a Postgresql JDBC connection pool
2/ Do refer to the following entry here as a guide on how to create an ETL chain that reads an external XLSX file:
In this example, we are using the following XLSX data to update the pre-created “Persons” database table:
PersonID DOB LastName FirstName 1 21/4/1958 White Johnson johnson@example.com
3/ This example uses the “Persons” database table which has the following set of records with PersonID as its Primary Key.
Steps:
-
In the same chainset created based on “Prerequisite - Point 2”, create another chain.
For example, ‘04 - SQLUpdater’ -
Add the following steps into the newly created chain in Step 2:
“Chain Call”, “JDBC Definition”, “JDBC SQL Updater”
-
In the step “Chain Call”, in the dropdown field select the chain that reads the external XLSX file.
- You may wish to add more than 1 “Chain Call” step if the values you require is from different files.
- In the step “JDBC Definition”, select the Database connection and create the SQL update query based on your requirements.
- The example above updates the email column value based on the PersonID column value.
- Save the ETL Chain and click on the play button to run the Chain.
- If successful, you should be able to see the records has been updated.
