/
SQL: ODBC Task

SQL: ODBC Task

Owned by Stonebranch (Deactivated)
Last updated: Nov 18, 2024 by Alec Berger

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:

  • 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.

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 Agent for Windows x64 Version >= 7.4.0.0.

  • Universal Agent for Linux Version >= 7.4.0.0.

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;

Action Output