Articles in this section

See more

Execute any SQL

  • Updated
Follow

When to use this Flow type

In Etlworks, it is possible to execute any SQL statement (including DDL) or a list of the ; separated SQL statements.

Create Flow

Step 1. Start creating an SQL Flow by creating a database Connection.

Step 2. Continue by opening the Flows window, clicking Add flow, and typing execute SQL in the Select Flow Type box:

sql.png

Step 3. Select the destination Connection created in step 1.

Step 4. Select the Parameters tab and enter the SQL to execute and other parameters:

Parameters:

  • SQL: SQL to execute. You can enter multiple ; delimited SQL statements. If the SQL is an Anonymous Block Statement, the ; is used as a part of the block and not as a statement separator.
  • SQL is Anonymous Block Statement : if this option is enabled, the SQL is treated as an executable statement that can contain control statements, variables, and other SQL statements. It can be used to implement procedural logic in a scripting language. By default, it is assumed that the SQL is not an anonymous block statement, so the parameter must be explicitly set.

An example of the anonymous block statement (TSQL):

  • DECLARE 
   @param1 varchar(100);
BEGIN
   set @param1 = '_xyz';

   insert into loop_test (name) 
   values (CONCAT('{name}', @param1));
END;
  • Auto Commit: if this option is enabled (the default), the transaction will be automatically committed.
  • Open new connection: enable this option if you want the flow to open a new database connection on each run. It is specifically useful if you want to execute the SQL flow in a loop that updates database connection parameters on each iteration.
  • Log executed SQL statement: enable this flag if you want to log executed SQL statements.
  • Script: the JavaScript or Python program that will be executed before executing SQL. You can use it to set the Flow variables that can be referenced as {parameters} in SQL. You can also use it to conditionally disable the SQL Flow.
  • Flow variables: the static flow variables. Variables can be references in SQL as {variable_name}.

Exception handling

  • On Exception: by default, any error causes the halt of execution. When ignore is selected, the errors will be ignored, and execution will continue. Read how to configure the Flow to ignore all or specific exceptions.
  • Exception Mask: you can specify what errors should be ignored and still halt the execution for all other errors. Enter part or all of the exception string. This field works only when the Ignore on Exception option is selected.
  • Execute on Exception: a JavaScript program that will be executed in case of any error regardless of whether this error is ignored by the flow or not. Read more. 
  • Rollback on Exception: some databases (for example, PostgreSQL) require transaction rollback in case of any exception.

Flow control

  • Execute if Error: if this option is selected, Etlworks will execute this SQL if an error occurs. It could be useful if, for example, you want to clean up a database after a failed data integration Flow.

Related articles