Articles in this section

See more

Execute SQL from JavaScript or Python

  • Updated
Follow

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

Related articles