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 inte