Execute an SQL script in specified connection. Statements in script can be delimited using a line what contains only one of:

• --
• /
• GO Each statement is executed in own transaction.

If --optimistic (-o) option is passed each statement are wrapped in try/finally block and script execution will continue even after error in individual statement

Exceptions in statements what contains --@optimistic string is forced to be optimistic.

SQL script can be a lodash template. In this case it preparsed using options = {conn: connectionConfig, cfg: execSqlOptionsObject, process}

Template example:

<% if (conn.dialect.startsWith('Oracle')) { %> create synonym rep_reportResult for rep_reportResult@<%process.env.LINKED_DB%>; <% } else { %> non SQL server statement <% } %>

one more statement for any DBMS;

Usage from a command line:

ubcli execSsq -? ubcli execSql -c connectionName -f path/to/script.sql -o // run a statement and output colored beautified result ubcli execSql -sql 'select * from uba_user' -withResult -noLogo | sed -n "/--BEGIN/,/--END/p" | tail -n +2 | head -n -2 | jq -r . Usage from a code

  
      const execSql = require('@unitybase/ubcli/lib/execSql')
 let options = {
      connection: 'main',
      file: './myScript.sql',
      optimistic: true,
      progress: false
  }
 execSql(options)

 // exec SQL script in default connection
 options = {
      sql: `BEGIN
      import_users.do_import;
      END;
      /
      delete from myTable where code = 'oldCode';`
  }
 execSql(options)