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.
| Name | Version | Database Connector |
MySQL | 8 | MySQL ODBC 8.0 Unicode Driver |
PostgreSQL | 13 | PostgreSQL Unicode |
Microsoft SQL Server | 15 | either one of them:
|
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:
- This Universal Task requires the Resolvable Credentials feature. Check that the Resolvable Credentials Permitted system property has been set to true.
- To import the Universal Template into your Controller, follow the instructions here.
- 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
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
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:
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
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
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 ]
|
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
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
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 ]
|
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
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
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:
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
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
Database Credentials | Database Connection Credentials |
Authentication | Supported Methods:
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:
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
| Field | Description |
Database Type | Type of database to connect:
|
Database Name | Database name or oracle service name |
Database Server | Database Servername e.g. localhost |
Database Port | Port of the Database e.g.
|
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 |
|---|---|
User documentation for creating Universal Templates in the Universal Controller user interface. | |
User documentation for creating Universal Tasks in the Universal Controller user interface. |