Overview
In Etlworks it is possible to execute any SQL from the JavaScript or Python code.
Use cases
The typical use-case for executing SQL from JavaScript is to update the execution status at run-time in the complex nested Flow or to save the records rejected by the validation into the errors table.
Example
The example below demonstrates how to execute SQL from JavaScript. The prerequisites for this example are that the JavaScript Flow is a part of the nested Flow and the database Connection is either used in one of the inner Flows or is added as a named Connection to the nested Flow.
var ex = etlConfig.getLastException();
etlConfig.setLastException(null);
var props = com.toolsverse.config.SystemConfig.instance().getContextProperties();
var stagingConnection = etlConfig.getConnection("Staging Postgres");
var statusSql = "insert into feeds_status (filename, url, error, finished)
values (?,?,?,?)";
var message = null;
if (ex != null) {
message = ex.getMessage();
if (Utils.isNothing(message)) {
message = com.toolsverse.util.Utils.getStackTraceAsString(ex);
}
}
var when = new java.util.Date();
try {
com.toolsverse.etl.sql.util.SqlUtils.
executeSql(stagingConnection, statusSql, props.get('filename'),
props.get('url'), message, when);
} catch (ex) {
etlConfig.log(ex.toString());
}
In the example above:
Step 1. Get the reference to the database Connection:
var stagingConnection = etlConfig.getConnection("Staging Postgres");
Step 2. If needed, set the bind variables and executed the SQL:
com.toolsverse.etl.sql.util.SqlUtils.executeSql(stagingConnection, statusSql,
props.get('filename'), props.get('url'), message, when);
Required packages
com.toolsverse.config, com.toolsverse.etl.sql.util
Comments
0 comments
Please sign in to leave a comment.