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:
|
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:
|
Provide SQL execution metadata | The following metadata is provided as part of the Task Instance Extension output.
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 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 | 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:
Result fields
| { "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
Result fields
| { "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:
| 1.0.0 |
Database Type | Choice | ODBC Compatible Database | Yes | The database type. The following options are available:
| 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:
| 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. | 1.0.0 |
Database Host | Text | Optional | The host of the database server. | 1.0.0 | |
Database Port | Integer | Optional | The port is used to connect to the database server. | 1.0.0 | |
Database Name | Text | Optional | The name of the database in the target database server. | 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. | 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. | 1.0.0 | |
File Data Source Name (FILEDSN) | Text | Optional | The path of the FILEDSN file. | 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:
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:
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:
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:
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. | 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:
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. | 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:
|
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.Success/Failure exit codes need to be respected.
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 |
---|---|
User documentation for creating, working with, and understanding Universal Templates and Integrations. | |
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