This entry guides you through the steps on how to use the ETL step, “JDBC Updater” to update your database table based on the records retrieved.
In this example, the sample records is read from an external XLSX file. Feel free to refer to the following entry here on how to create an ETL chain to read an XLSX file.
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
1/ The sample records used as follows.
- As seen in the screenshot, there is a slight discrepancy in the values for the column, “account_id”. (column account_id should be an integer instead of a decimal)
- Hence, to rectify the column type, we add a step called “Change Field Type”.
2/ Add the ETL step, “Change Field Type”.
3/ Next, add the step “JDBC Updater”. In this example, only values in the “account_name” column will be updated.
4/ If failureCount equals 0, records should have been updated successfully.
- Database table prior to the update
- Database table after the update
- To update the database table using values from more than 1 excel spreadsheet, you could utilize the step called Chain Call.
- For example, with reference to the screenshot(s) below
→ The first 2 chains (i.e. “01 - View Address XLSX” and "02 - View Name ") is used to read an excel spreadsheet and make the necessary field types revisions if required.
→ The third chain (i.e. 03 - Update Account DB) calls the first 2 chains to use the records and proceeds to update the database table using the necessary column values.