Disclaimer

Your use of this download is governed by Stonebranch’s Terms of Use, which are available at Stonebranch Integration Hub - Terms of Use.

Version Information

Template Name	Extension Name	Extension Version
SQL ODBC	ue-sql-odbc	1.0.0

Refer to Changelog for version history information.

Overview

This Universal Extension provides the capability to integrate with ODBC-compliant database, by running single or multiple SQL commands and retrieving the resulting data. While it is designed to work with any ODBC-compliant database, it is thoroughly tested against MySQL, MSSQL Server, PostgreSQL, Oracle, and SAP HANA databases.

As opposed to the native UAC SQL Task, which runs on the Controller, this integration allows the user to run an SQL ODBC Task from the Agent.

Key Features

Feature	Description
Support ODBC-compatible databases	The interaction with the supported databases is based on ODBC API and the non-vendor-specific Python library “pyodbc”. This translates to support for every database that is compatible with this API and has a corresponding ODBC driver. The extension is explicitly tested for MySQL 8.0, MSSQL Server 16.0, PostgreSQL 12.15, Oracle 21c Express Edition 21.3, and SAP HANA express edition 2.0 SPS 06.
Connectivity options	Multiple connectivity options available for maximum flexibility: Step-by-step selection of the connectivity parameters if only the basic parameters are needed like database host, port, etc. Use a DSN entry defined in the agent host. Use a FILE DSN file that the task can have access to. Manually define the ODBC connection string that will be used as-is for establishing the connection to the database server. Support for MSSQL “Windows Authentication”.
Support of SQL scripts	Input may contain one or multiple SQL commands, following the syntax of the target database server.
Automatic database commit or rollback	On successful SQL command execution, the extension commits the result, but if any errors occur a rollback is triggered.
Multiple options for data output	A task execution can provide the SQL query returned data with any of the following options: Extension output in JSON format. Standard output (STDOUT) in human-readable tabular format. A user-defined file in the file system, in JSON or CSV format.
Provide SQL execution metadata	The following metadata is provided as part of the Task Instance Extension output. Rows affected by the SQL command. The execution time of the command Any messages produced by the database server during the command execution. Rows affected and messages can vary greatly between different databases and drivers.

Requirements

This integration requires a Universal Agent and a Python runtime to execute the Universal Task.

Area	Details
Python Version	Requires Python 3.7, tested with Python 3.7.6.
Python Libraries	pyodbc 4.0. Installation of specific libraries and ODBC driver managers are required based on the OS (consult the official PyODBC installation instructions).
ODBC Drivers	ODBC database drivers installation based on vendor-specific instructions.
Universal Agent	Both Windows and Linux agents are supported: Universal Agent for Windows x64 Version >= 7.1.0.0. Universal Agent for Linux Version >= 7.1.0.0.
Universal Controller	Universal Controller Version >= 7.1.0.0
Network and Connectivity	Network connectivity to the target database server, from the agent executing the task.
Database	ODBC-compliant database server.

Supported Actions

Action: Run SQL Script

Execute an SQL UAC Script in the selected database. The user is given the option to choose where the output of the Task Instance is to be viewed. A database driver may or may not support the execution of multiple SQL commands. For instance, in MySQL case, the user should specify the “MULTI_STATEMENTS=1” parameter in the connection string in order to be able to run multiple SQL statements, whereas Oracle Database Driver does not support this feature at all, thus multiple SQL commands should be provided in a single PL/SQL block.

Configuration examples


Scenario A: Connect with a connection string and execute an SQL Script with multi SQL statements. “TRUSTSERVERCERTIFICATE=yes” should be present in the connection for the “Windows Authentication” authentication type.	Scenario B: Connect with a DSN file in an Oracle Database. Oracle driver does not support the execution of multiple SQL commands, hence all the SQL commands should be in a PL/SQL block.	Scenario C: Connect with a connection string and execute an SQL Script with multi SQL statements in MySQL Database. MULTI_STATEMENTS=1 is specified in the connection string.

Action Output

Output Type	Description (Successful Execution)	Example (Successful Execution)
EXTENSION	Standard Universal Extension format The extension output follows the standard Extension output format, providing: “exit_code“, “status“, “status_description“: General info regarding the task execution. “invocation” > “fields”: The task configuration used for this task execution. Result fields “execution_info”: “messages“: Messages generated by the database during the SQL command execution. “duration“: The execution time of the query is in seconds. This metric is produced in the extension (client-side) so it includes network delays, too. “result_sets”: The list of result sets returned by the SQL script. Most databases can return multiple result sets (when multiple SELECT statements are included in the script for example), but this always depends on the target database server and the corresponding ODBC driver installed on the UAC agent. This extension will always try to return all of the result sets provided by the SQL script execution. For each one of them, the following information is provided. “rows_affected”: The number of modified or fetched rows. The value can have different meanings or values depending on the target database server or the ODBC driver used. For example, MySQL and PostgreSQL usually provide this value for SELECT statements, while MS SQL Server returns just ‘-1'. Additionally, it is also possible that this value is valid only for the first SQL statement in the script, while for the rest of the statements no value or a negative value '-1’ is returned. “data“: A JSON representation of the retrieved data, in the form of an array of objects (one object for each data row retrieved - the keys of the attributes are the names of the columns, and the value is the actual data). In case no column names are returned or the user has selected to not display them, the data will be provided in the form of an array (result set) of arrays (values of a row).	{ "exit_code": 0, "status": "SUCCESS", "status_description": "Task executed successfully.", "invocation": { "fields": { ... }, }, "result": { "errors": [], "execution_info": { "messages": ["Sample message"], "duration": 1.5220 }, result_sets: [ { "rows_affected": 1, "data": [ { "id": 1, "firstname": "Alice", "username": "alice" }, { "id": 2, "firstname": "Bob", "username": "bob" }, ] } ] } }
	Description (Failed Execution)	Example (Failed Execution)
	Standard Universal Extension format “exit_code“, “status“, “status_description“: Will be set according to the specific error that happened during the execution. A list of possible values is provided in the extension exit codes table in this document. Result fields “errors“: The errors reported by the extension task during the execution. “execution_info”: “messages“: Messages generated by the database during the SQL command execution. “duration“: The execution time of the query is in seconds. This metric is produced in the extension (client-side) so it includes network delays, too.	{ "exit_code": 1, "status": "FAIL", "status_description": "Task execution failed.", "invocation": { "fields": { ... }, }, "result": { "errors": ["Sample message"], "execution_info": { "messages": ["Sample message"], "duration": 1.522 }, "results_sets": null } }
STDOUT	If STDOUT is selected in the “Data Output“ option, the extension outputs the retrieved data in a human-friendly tabular format.	== ====== ======= id firstname username == ====== ======= 1 Alice alice 2 Bob bob == ====== =======
STDERR	The SQL statement executed in the target database is printed when the Universal Extension Task is run with log level set to DEBUG.

SQL Script Definition and Guidelines

An SQL script is a file containing a sequence of SQL (Structured Query Language) statements or commands. It is used to perform various operations on a relational database, such as creating tables, inserting data, modifying data, retrieving data, or managing other database-related tasks.

The following guidelines provide recommendations and best practices for writing SQL scripts:

The extension does not perform any validation on the SQL script. The format of the script strongly relates to the Database and the capabilities of vendor-specific Database ODBC driver.
Commit statements should not be contained inside the script. Commit is explicitly executed only if the execution of the SQL Script is successful as a whole. SQL Script execution is atomic, meaning that if any command fails, or an unexpected error occurs, all the changes, if any, made to the database are rollbacked.
UAC variables can be used to change the content of the SQL script dynamically if required.

Input Fields

Field	Type	Default Value	Required	Description	Introduced in Version
Action	Choice	Run SQL Script	Yes	The action performed upon the task execution. Available actions are as follows: Run SQL Script	1.0.0
Database Type	Choice	ODBC Compatible Database	Yes	The database type. The following options are available: ODBC Compatible Database PostgreSQL MySQL Oracle SAP HANA MS SQL Server	1.0.0
Script	Script field		Yes	UAC script that contains the SQL command(s).	1.0.0
Connection Type	Choice	Basic Connection Info	Yes	Different options are possible for the selection of connection parameters. Available options are the following: Basic Connection Info Data Source (DSN) File Data Source (FILEDSN). This method is not available on Oracle. Connection String	1.0.0
Database Credentials	Credentials		Optional	Username and Password for connecting to the database. The user does not have to fill in this field in case the “Trusted Connection“ feature of MS SQL Server is used. Not Visible when field Connection Type = “Connection String”, except MS SQL Server	1.0.0
Authenticate with system user (Trusted Connection)	Checkbox	Unchecked	Optional	Check this field in order to use the “trusted connection“ option, when using a Windows agent to connect to an MS SQL Server. Required & visible only when field Database Type = “MS SQL Server”.	1.0.0
Database Host	Text		Optional	The host of the database server. Required & visible only when field Connection Type = “Basic connection info".	1.0.0
Database Port	Integer		Optional	The port is used to connect to the database server. Required & visible only when field Connection Type = “Basic connection info".	1.0.0
Database Name	Text		Optional	The name of the database in the target database server. Required & visible only when field Connection Type = “Basic connection info".	1.0.0
Database Driver	Dynamic choice		Optional	The driver is used for connecting to the database server. It is a dynamic choice field, with the values being retrieved from the system configuration. Required & visible only when field Connection Type = “Basic connection info".	1.0.0
Data Source Name (DSN)	Dynamic Choice		Optional	The DSN is used for connecting to the target database server. It is a dynamic choice field, with the values being retrieved from the system configuration. Required & visible only when field Connection Type = “Data Source Name (DSN)".	1.0.0
File Data Source Name (FILEDSN)	Text		Optional	The path of the FILEDSN file. Required & visible only when field Connection Type = “File Data Source Name (FILEDSN)".	1.0.0
Connection String	Text		Optional	The raw connection string that should be used to connect to the target database. Required & visible only when field Connection Type = “Connection String”.	1.0.0
Data Output	Multiple Choice	Extension Output	Yes	Data output methods for the SQL data retrieved. Available options are: Extension Output File Output STDOUT Notice: When option “File Output“ is selected and multiple result sets are the result of the SQL script execution (for example multiple SELECT STATEMENTS included in the SQL script), one file is created for each result set. The file naming convention adheres to the sequence file_1, file_2, file_3, etc.	1.0.0
Output File Path	Text		Optional	The file that will be used to save the SQL query data. If this does not exist it wit will be created, if it exists it will be truncated prior to being used. The file extension needs to be included. Required & visible only when field Data Output = “File Output”.	1.0.0
Output File Type	Choice	CSV	Optional	The internal representation of the Output File. Available options are as follows: CSV JSON Required & visible only when field Data Output = “File Output”.	1.0.0
Column Separator	Choice	Comma	Optional	The character to be used as a column separator. Available options are as follows: Comma (,) Semicolon (;) Hash (#) Pipe (\|) Space (“ “) Tab Required & visible only when field Output File Type = “CSV”	1.0.0
Use Quotes	Choice	Always	Mandatory	Choose when to use quotes in the CSV output. Available options are as follows: Always Only when special characters are included in the field data Only for non-numeric data Never Required & visible only when field Output File Type = “CSV”	1.0.0
Print Column Names	Checkbox	Selected	Optional	Choice to print or not the column names as a first line in the CSV file. Required & visible only when “CSV“ is the selected value in the “Output File Type“ field.	1.0.0
Show Advanced Settings	Checkbox	Unchecked	Yes	Controls show/hide of the fields that belong to the “Advanced“ section.	1.0.0
Charset	Choice	Default	Mandatory	Configure the charset to be used when parsing database data retrieved. Selecting the option “Use Default“, the extension will try to use the most commonly used charset for each database type, but you can also override this setting with one of the other options of the list. Available options are as follows: Default UTF-8 UTF-16 LE UTF-16 BE ISO-8859-1 (Latin 1) ISO-8859-9 (Latin 9) Visible only when “Advanced“ is selected.	1.0.0
Max Rows Returned	Integer		Optional	For the cases where data are produced as a result of the SQL script execution, limit the rows of data returned. Filtering is applied after the SQL script is executed, on the client side, so this is not the same as using the “LIMIT” keyword in your SQL statements. Visible only when “Advanced“ is selected.	1.0.0
Connection Timeout	Integer	0	Optional	Define the number of seconds to wait for establishing the connection to the database server. Value zero (0) disables this timer, with the connection attempt waiting indefinitely. Not supported for all ODBC Drivers. Visible only when “Advanced“ is selected.	1.0.0
SQL Statement Timeout	Integer	0	Optional	Define the number of seconds to wait for the result on an SQL statement executed to the database server. Value zero (0) disables this timer, with the extension waiting for a database server response indefinitely. Visible only when “Advanced“ is selected.	1.0.0

Output Fields

Field	Type	Description	Introduced in Version
Execution Time	Float	The execution time of the query is in seconds.	1.0.0

Exit Codes

Extension Exit Code	Status	Status Description	Meaning
0	SUCCESS	“SUCCESS: Task executed successfully.“	Successful Execution
1	FAIL	“FAIL: <<Error Description>>”	This extension error is mapped to the following cases: Any UAC error not described in the rest of the error code. Any unexpected error during execution.
20	DATA_VALIDATION_ERROR	“DATA_VALIDATION_ERROR: <<Error Description>>“	Input fields validation error. ** See STDERR for more detailed error descriptions.
29	FILE_WRITE_ERROR	“FILE_WRITE_ERROR: <<Error Description>>“	Failed to write SQL script results to the target file.

STDOUT and STDERR

STDOUT and STDERR provide additional information to User. The populated content can be changed in future versions of this extension without notice. Backward compatibility is not guaranteed.

How To

Import Universal Template

To use the 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 these instructions.
When the files have been imported successfully, refresh the Universal Templates list; the Universal Template will appear on the list.

Modifications of this integration, applied by users or customers, before or after import, might affect the supportability of this integration. For more information refer to Integration Modifications.

Configure Universal Task

For a new Universal Task, create a new task, and enter the required input fields.

Integration Modifications

Modifications applied by users or customers, before or after import, might affect the supportability of this integration. The following modifications are discouraged to retain the support level as applied for this integration.

Python code modifications should not be done.
Template Modifications
- General Section
  - "Name", "Extension", "Variable Prefix", and "Icon" should not be changed.
- Universal Template Details Section
  - "Template Type", "Agent Type", "Send Extension Variables", and "Always Cancel on Force Finish" should not be changed.
- Result Processing Defaults Section
  - Success and Failure Exit codes should not be changed.
  - Success and Failure Output processing should not be changed.
- Fields Restriction Section
  The setup of the template does not impose any restrictions, However with respect to the "Exit Code Processing Fields" section.
  1. Success/Failure exit codes need to be respected.
  2. In principle, as STDERR and STDOUT outputs can change in follow-up releases of this integration, they should not be considered as a reliable source for determining the success or failure of a task.

Users and customers are encouraged to report defects, or feature requests at Stonebranch Support Desk.

Document References

This document references the following documents:

Document Link	Description
Universal Templates	User documentation for creating, working with, and understanding Universal Templates and Integrations.
Universal Tasks	User documentation for creating Universal Tasks in the Universal Controller user interface.

Known Issues

The use of FILE Data Source Names (FILEDSN) for connecting to an Oracle database is not supported in this version. Attempting to establish a connection using a FILE DSN may result in unexpected behavior or errors, as it is not proven not to be reliable with the latest ODBC Driver.

Changelog

ue-sql-odbc 1.0.0 (2023-06-29)

Initial Version