SQL: ODBC Task
Disclaimer
Your use of this download is governed by Stonebranch’s Terms of Use, which are available at Stonebranch Integration Hub - Terms of Use.
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, SAP HANA, and Amazon Redshift 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.
Version Information
Template Name | Extension Name | Extension Version | Status |
---|---|---|---|
SQL ODBC | ue-sql-odbc | 3.0.1 | Fixes and new Features are introduced. |
SQL ODBC 3.0.0 is a major release update and introduces breaking changes that might affect some customers depending on their setup. Administrators are strongly advised to refer to Changelog for more information on the changes introduced if updating from a version earlier than 3.0.0
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 and serverless Amazon Redshift. |
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. |
Observability | Calculate and expose important metrics related to task executions. |
Software Requirements
This integration requires a Universal Agent and a Python runtime to execute the Universal Task.
Area | Details |
---|---|
Python Version | Requires Python 3.7 or later, tested with Python 3.7.16 and Python 3.11.6 |
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.4.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 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 B: 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. UID and PWD are being specified with [credential_name].user and [credential_name].password notation for enhanced security. |
Scenario C: A connection to an MSSQL Server Database can be established using the Trusted Connection feature. When this checkbox is enabled, the need to include the User ID (UID) and Password (PWD) in the connection string can be omitted. Connection String example: Driver=ODBC Driver 18 for SQL Server;Server=tcp:mssql-sample.org:1443;Database=mydb;TRUSTSERVERCERTIFICATE=yes; |