SQL: Execute Scripts and Functions

This integration has been sunset

It is replaced by SQL ODBC Task.

Disclaimer

Your use of this download is governed by Stonebranch’s Terms of Use, which are available at https://www.stonebranch.com/integration-hub/Terms-and-Privacy/Terms-of-Use/

Overview

This Universal Task allows users to execute SQL scripts and functions against a MySQL, PostgreSQL, Microsoft SQL Server, Oracle and SAP HANA database.

It uses an agentless connection via ODBC towards SQLSERVER, MySQL and PostgreSQL and the oracle basic instant client to connect to an Oracle database.

Software Requirements

Software Requirements for Universal Template and Universal Task

  • Universal Agent needs to be installed with python option (--python yes).
  • The python ODBC module pyodbc v4.0.30 needs to be installed for MySQL, PostgreSQL and Microsoft SQL Server connections.

  • The python SAP HANA module hdbcli v2.6.58 needs to be installed for SAP HANA connections.

  • The python Oracle module cx_oracle 8.0.1needs to be installed for Oracle Database connections.

Software Requirements Universal Agent

  • Universal Agent for Linux or Windows Version 7.0.0.0 or later is required.

Software Requirements Universal Controller

  • Universal Controller 7.0.0.0. or later is required.

Software Requirements for the Application to be Scheduled

The Universal Task has been tested for the following databases, Versions and Connector. Please make sure that the connector is installed for your database.

NameVersionDatabase Connector

MySQL

8

MySQL ODBC 8.0 Unicode Driver

PostgreSQL

13

PostgreSQL Unicode

Microsoft SQL Server

15

either one of them:

  • SQL Server Native Client 11.0,

  • ODBC Driver 17 for SQL Server

Oracle

18XE

Oracle Instant Client v19.6.0.0.0

SAP HANA


not required - part of the SAP Python hdbcli module

Note

If you have a different Database Connector than mentioned in the table above. you can add a new connector to the Universal Task by adding it to the Universal Template of the Universal Task with name SQL.

In Universal Controller

Administration → Universal Templates → SQL → Fields → odbc_drivername → New - button

Key Features

The solution supports the following file transfer scenarios:

  • The Universal Task supports execution of SQL scripts for Oracle, MySQL, PostgreSQL, Microsoft SQL Server and SAP HANA.

  • For Oracle the execution of SQL scripts and oracle PLSQL blocks are supported.

  • All connections are agentless via ODBC for SQLSERVER, MySQL and PostgreSQL.

  • Oracle connections are performed agentless using the oracle basic instant client.

  • SAP Hana Database connections are performed agentless using the SAP HANA client for Python.

  • The Universal Task supports both Universal Agent for Linux/Unix and Windows.

  • You can select different log-levels e.g. Info and debug.

  • You can decide whether or not the SQL-output is provided in the standard out.

  • All Passwords are encrypted using Controller Credentials.

  • For SQLSERVER Windows Authentication and SQLSERVER Authentication is supported.


Import SQL Universal Task Downloadable Universal Template

To use this downloadable Universal Template, you first must perform the following steps:

  1. This Universal Task requires the Resolvable Credentials feature. Check that the Resolvable Credentials Permitted system property has been set to true.
  2. To import the Universal Template into your Controller, follow the instructions here.
  3. When the files have been imported successfully, refresh the Universal Templates list; the Universal Template will appear on the list.

Configure SQL Universal Tasks

For the new Universal Task type, create a new task and enter the task-specific Details that were created in the Universal Template.

Field Descriptions for SQL Universal Task

The following will provide a configuration Example for each of the supported Databases:

  • Database Type: MySQL

  • Database Type: Oracle - SQL

  • Database Type: Oracle - PLSQL Block

  • Database Type: PostgreSQL

  • Database Type: Microsoft SQL Server

  • Database Type: SAP HANA

Database Type: MySQL

FieldDescription

Database Type

Type of database to connect:

  • MySQL

  • Oracle

  • PostgreSQL

  • Microsoft SQL Server

  • SAP HANA

Database Name

Database name or oracle service name

Database Server

Database Servername e.g. localhost

Database Port

Port of the Database e.g.

  • MySQL: 3306

  • Oracle: 1541

  • PostgreSQL: 5432

  • Microsoft SQL Server: 1433

  • SAP HANA: 39013

Database Credentials

Database Connection Credentials

ODBC Drivername

Name of the ODBC driver - the field is only relevant for MySQL, PostgreSQL and Microsoft SQL Server.

The following driver are available for selection:

  • MySQL: MySQL ODBC 8.0 Unicode Driver

  • MS SQL Server: SQL Server Native Client 11.0,

  • MS SQL Server: ODBC Driver 17 for SQL Server

  • PostgreSQL: PostgreSQL Unicode

Additional driver can be added in the SQL Task Universal Template under:

Administration → Universal Templates → SQL → Fields → odbc_drivername → New

Script

The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name.

Get Output

prints the SQL output to STDOUT

In case of an Oracle Stored procedure print the dbms_output to STDOUT.

Loglevel

Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL]

Example: Run a SQL script on MySQL

Database Type: Oracle - SQL

FieldDescription

Database Type

Type of database to connect:

  • MySQL

  • Oracle

  • PostgreSQL

  • Microsoft SQL Server

  • SAP HANA

Database Name

Database name or oracle service name

Database Server

Database Servername e.g. localhost

Database Port

Port of the Database e.g.

  • MySQL: 3306

  • Oracle: 1541

  • PostgreSQL: 5432

  • Microsoft SQL Server: 1433

  • SAP HANA: 39013

Database Credentials

Database Connection Credentials

Mode

Connection Authorization Mode:

[sysdba | sysasm | sysoper | sysbkp | sysdgd | syskmt | None]

The field is only relevant oracle connections

Script Type

The field is only relevant for oracle connections

[SQL Script | PL/SQL Block ]

  • SQL Script - select to execute an SQL Script

  • PL/SQL Block - select to run an PL/SQL Block

Script

The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name.

Get Output

Prints the SQL output to STDOUT

In case of an Oracle Stored procedure print the dbms_output to STDOUT.

Loglevel

Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL]

Example: Run a SQL Script on Oracle - SQL

Database Type: Oracle - PLSQL Block

FieldDescription

Database Type

Type of database to connect:

  • MySQL

  • Oracle

  • PostgreSQL

  • Microsoft SQL Server

  • SAP HANA

Database Name

Database name or oracle service name

Database Server

Database Servername e.g. localhost

Database Port

Port of the Database e.g.

  • MySQL: 3306

  • Oracle: 1541

  • PostgreSQL: 5432

  • Microsoft SQL Server: 1433

  • SAP HANA: 39013

Database Credentials

Database Connection Credentials

Mode

Connection Authorization Mode:

[sysdba | sysasm | sysoper | sysbkp | sysdgd | syskmt | None]

The field is only relevant oracle connections

Script Type

The field is only relevant for oracle connections

[SQL Script | PL/SQL Block ]

  • SQL Script - select to execute an SQL Script

  • PL/SQL Block - select to run an PL/SQL Block

Script

The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name.

Get Output

prints the SQL output to STDOUT

In case of an Oracle Stored procedure print the dbms_output to STDOUT.

Loglevel

Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL]

Example: Run an Oracle - PLSQL Block

Database Type: PostgreSQL

FieldDescription

Database Type

Type of database to connect:

  • MySQL

  • Oracle

  • PostgreSQL

  • Microsoft SQL Server

  • SAP HANA

Database Name

Database name or oracle service name

Database Server

Database Servername e.g. localhost

Database Port

Port of the Database e.g.

  • MySQL: 3306

  • Oracle: 1541

  • PostgreSQL: 5432

  • Microsoft SQL Server: 1433

  • SAP HANA: 39013

Database Credentials

Database Connection Credentials

ODBC Drivername

Name of the ODBC driver - the field is only relevant for MySQL, PostgreSQL and Microsoft SQL Server.

The following driver are available for selection:

  • MySQL: MySQL ODBC 8.0 Unicode Driver

  • MS SQL Server: SQL Server Native Client 11.0,

  • MS SQL Server: ODBC Driver 17 for SQL Server

  • PostgreSQL: PostgreSQL Unicode

Additional driver can be added in the SQL Task Universal Template under:

Administration → Universal Templates → SQL → Fields → odbc_drivername → New

Script

The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name.

Get Output

prints the SQL output to STDOUT

In case of an Oracle Stored procedure print the dbms_output to STDOUT.

Loglevel

Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL]

Example: Run a SQL Script on PostgreSQL

Database Type: Microsoft SQL Server

FieldDescription

Database Type

Type of database to connect:

  • MySQL

  • Oracle

  • PostgreSQL

  • Microsoft SQL Server

  • SAP HANA

Database Name

Database name or oracle service name

Database Server

Database Servername e.g. localhost

Database Port

Port of the Database e.g.

  • MySQL: 3306

  • Oracle: 1541

  • PostgreSQL: 5432

  • Microsoft SQL Server: 1433

  • SAP HANA: 39013

Database Credentials

Database Connection Credentials

Authentication

Supported Methods:

  • Microsoft SQL Server - Windows Authentication

  • Microsoft SQL Server - SQL Server Authentication

Note: If “Microsoft SQL Server - Windows Authentication” is chosen you should select under the Agent “Credentials” the Windows user, who should execute the script. The database credentials are not used for SQLSERVER -Windows Authentication.

ODBC Drivername

Name of the ODBC driver - the field is only relevant for MySQL, PostgreSQL and Microsoft SQL Server.

The following driver are available for selection:

  • MySQL: MySQL ODBC 8.0 Unicode Driver

  • MS SQL Server: SQL Server Native Client 11.0,

  • MS SQL Server: ODBC Driver 17 for SQL Server

  • PostgreSQL: PostgreSQL Unicode

Additional driver can be added in the SQL Task Universal Template under:

Administration → Universal Templates → SQL → Fields → odbc_drivername → New

Script

The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name.

Get Output

prints the SQL output to STDOUT

In case of an Oracle Stored procedure print the dbms_output to STDOUT.

Loglevel

Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL]

Example: Run a SQL Script on Microsoft SQL Server

Database Type: SAP HANA

FieldDescription

Database Type

Type of database to connect:

  • MySQL

  • Oracle

  • PostgreSQL

  • Microsoft SQL Server

  • SAP HANA

Database Name

Database name or oracle service name

Database Server

Database Servername e.g. localhost

Database Port

Port of the Database e.g.

  • MySQL: 3306

  • Oracle: 1541

  • PostgreSQL: 5432

  • Microsoft SQL Server: 1433

  • SAP HANA: 39013

Database Credentials

Database Connection Credentials

Script

The database script to execute Note: The name should not contain a Universal Controller Variable e.g. script name: sb-${ENV}-proc01 will not work, because it contains a variable (${ENV}) in the name.

Column Separator

The field is only relevant for SAP HANA connections

Output separator

[ Semicolon | Comma | Hash | Whitespace ]

Get Output

prints the SQL output to STDOUT

In case of an Oracle Stored procedure print the dbms_output to STDOUT.

Loglevel

Universal Task logging settings [DEBUG | INFO| WARNING | ERROR | CRITICAL]

Example: Run a SQL Script on SAP HANA

Document References

This document references the following documents:

Name

Description

Universal Templates

User documentation for creating Universal Templates in the Universal Controller user interface.

Universal Tasks

User documentation for creating Universal Tasks in the Universal Controller user interface.