SQL ODBC

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

2.2.0

2.0.0 was 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 2.0.0

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.

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.

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

Python Libraries

pyodbc 4.0.39. 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.2.0.0.

  • Universal Agent for Linux Version >= 7.2.0.0.

Universal Controller

Universal Controller Version >= 7.2.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.

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 the 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

  • Redshift

1.0.0

SQL Source

Choice

SQL as UAC Script

Yes

Defines the source of the SQL Script:

  • SQL as UAC Script

  • SQL as Text

2.1.0

Script

Script field


Yes

UAC script that contains the SQL command(s).

Required & visible only when field SQL Source= “SQL as UAC Script”.

1.0.0

SQL Text

Large Text


Optional

The SQL commands to be executed.

Required & visible only when field SQL Source= “SQL as Text”.

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

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

Enable autocommit

Checkbox

Unchecked

No

When checked the autocommit mechanism is enabled.

When this flag is activated, transactions are automatically committed by the DB server. Consequently, the Universal Task lacks transaction control capability, preventing any rollback action initiated by the task in case of errors from reversing transactions already committed by the server.



Visible only when field Database Type = “MS SQL Server”.

2.2.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. 
UID and PWD are being specified with [credential_name].user and [credential_name].password notation for enhanced security.

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

User Defined Metric

Choice

--None--

Optional

Enables the creation of a user-defined metric. The metric value is calculated based on the result set returned as a result of the SQL Script execution. Available options are as follows:

  • --None--

  • Counter

2.0.0

Metric Name Suffix

Text


Optional

The metric name suffix. The resulting metric name is ue.sqlodbc.user.<metric_suffix>


Visible only and mandatory when User Defined Metric = “Counter”.

2.0.0

Column For Metric Value Calculation

Text


Optional

The result set’s column from which the metric value shall be retrieved. If provided, the metric value is incremented by the value of this column. Otherwise, the metric value is incremented by one for each row of the result set. Note that the configured column should represent always a number.


Visible only when User Defined Metric = “Counter”.

2.0.0

Metric Attributes

Choice

--None--

Optional

Define the metric attributes selection method. Available options are as follows:

  • --None--

  • Column Based

When --None-- is selected, no attributes are sent when metric values are published.

When “Column Based” is selected, the metric attribute names are taken from the result set’s columns, and their values are taken from the row values of the result set.

Visible only when User Defined Metric = “Counter”.

2.0.0

Column List

Text


Optional

Comma-separated list of result set’s columns to be used as attributes.

Visible only and mandatory when Metric Attributes = “Column Based“. At least one column name should be selected

2.0.0

Output Fields


FieldTypeDescriptionIntroduced in Version
Execution TimeFloatThe execution time of the query is in seconds.1.0.0


Environment Variables

Variable: UE_EXTENSION_OUTPUT_MAX_SQL_ROWS


This environment variable expects an integer value and controls the maximum number of SQL rows presented as a result.result_sets.data JSON attribute in the Task's Extension Output. If the number of rows exceeds the value provided to this variable, the number of rows is truncated to the value of this variable, and a representative message is appended to the Status Description. This truncation only affects data printed to the Extension Output and does not affect the rest of the outputs (STDOUT, File Output).If a value is not provided, the default value of 100 is used.


Exit Codes



Extension Exit Code
Status
Status Description
 Meaning
0
Success“Task executed successfully.“Successful Execution
1
Failed“Execution Failed: <<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
Failed“Data Validation Error: <<Error Description>>“

Input fields validation error.** See STDERR for more detailed error descriptions.
29
Failed“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 the User. The populated content can be changed in future versions of this extension without notice. Backward compatibility is not guaranteed.

Observability


Metrics are generated and exported by this integration, contributing to the UAC data observability, delivered since Universal Automation Center 7.5.0.0.

Metric: ue.sqlodbc.sql.rows

This metric is created based on the respective Event Template.

Name

Instrument Type

Unit (UCUM)

Attributes

Instrumented By

Description

ue.sqlodbc.sql.rows

Histogram

{row}

As defined in metric attributes.

Universal Controller

Measures the total rows affected by the executed SQL statement(s). The metric value is aggregated across different task instances

Note: When row counts are reported as a -1 from ODBC drivers, from a metric point of view they are considered equal to zero.

Metric: ue.sqlodbc.sql.duration

This metric is created based on the respective Event Template.

Name

Instrument Type

Unit (UCUM)

Attributes

Instrumented By

Description

ue.sqlodbc.sql.duration

Histogram

s

As defined in metric attributes.

Universal Controller

Duration of the given SQL statements to be executed. This is measured from the client side and includes network delays. The metric value is aggregated across different task instances

Metric Attributes

The following attributes are applicable for ue.sqlodbc.sql.rows and ue.sqlodbc.sql.duration metrics.

Attribute Name

Enabled

Description

action

By Default

The selected action of the Universal Task.

script

Optionally

The name of the SQL script that is executed.

dsn

By Default

The DSN is used for the database.

dbname

By Default

The name of the database.

dbaddress

By Default

The host address of the database.

dbsystem

By Default

The system type of the database.

Optional Metric Attributes

In the table above some Attributes are optionally enabled. Those attributes are not activated by default, cause it might lead to high cardinality scenarios in case the distinct values of those attributes used for a customer installation are too many. Administrators can activate them with caution as follows:

  1. Go to Administration → Universal Templates

  2. Select the Universal Template

  3. Click “Event Templates” Tab

  4. Select the Name of the Event Template you want to update, which represents the metric you are interested in.

  5. Update the “Metric Label Attributes” Field by adding the required Attribute Name.

User Defined Metrics

This integration enables users to create their own metrics based on the result set’s data.

Name

Instrument Type

Unit (UCUM)

Description

ue.sqlodbc.user.<user-defined metric suffix>

Counter

-

A counter representing an entity derived by the result set data.

Attributes are controlled by the field Column List when MetricAttributes = “Column Based“. Some representative configuration examples are listed below where it is assumed that an Oracle database is used and a database table named car_sales exists and holds car sales data.

Example: Add 1 for every row

Let’s assume that car sales data is being retrieved from an SQL statement. An example could be the following.

select * from car_sales
where sales_date between TRUNC(SYSDATE-1) and TRUNC(SYSDATE)

The task author wants to take the number of sales of the previous day and push them as a metric. For that, instrumentation is required to be added on top of the existing SQL statement as follows:




The above setup will create a metric called ue.sqlodbc.user.carsales and for each entry on the result set, the value of 1 will be added to the metric.

Example: Add a selected value for every row

Let’s assume that the same table is being selected to retrieve car sales amounts. An example could be the following:

select amount, sales_date, car_make, car_model  from car_sales
where sales_date between TRUNC(SYSDATE-1) and TRUNC(SYSDATE)


The task author wants to calculate the total sales amount of the previous day and push the value as a metric. For that instrumentation is required to be added on top of the existing SQL statement.




The above setup will create a metric called ue.sqlodbc.user.sales amount and for each entry on the result set, the value of the column amount will be added to the metric. A second example is shown below where the amount is manipulated on the select statement.


select amount*2 as double_amount, sales_date, car_make, car_model  from car_sales
where sales_date between TRUNC(SYSDATE-1) and TRUNC(SYSDATE)


In that case, the configuration of the metric could be the following. As shown, the virtual column named double_amount is used as a parameter of the field Column For Metric Value Calculation.




Example: Using attributes for labeling data

Attributes and their values put extra information with respect to the published metric. Based on the example before, if the task author would require to see the sales amount per car_make and car_model then the required configuration would be the following:



Other Observability Configuration Options

Administrators can update the Universal Event Template to control additional attributes that can be sent out for Universal Event-based Metrics. However, this configuration should be approached with caution. When the possible distinct values of those attributes are high, that might lead to high cardinality issues.

Administrators can activate them with caution as follows:

  1. Go to Administration → Universal Templates

  2. Select the Universal Template

  3. Click “Event Templates” Tab

  4. Select the Name of the Event Template you want to update, which represents the metric you are interested in.

  5. Update the “Optional Metric Labels” List the required Metric Labels.


How To

Import Universal Template

To use the Universal Template, you first must perform the following steps.

  1. This Universal Task requires the Resolvable Credentials feature. Check that the Resolvable Credentials Permitted system property has been set to true.

  2. To import the Universal Template into your Controller, follow these instructions.

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

Import Grafana Dashboard

Users can benefit from a ready-to-use sample dashboard that this downloadable integration offers when observability features are used. It is located under /observability/grafana/ directory inside the downloadable zip file from Stonebranch Integration Hub. Administrators should refer to the official Grafana User Guide on how to import a Grafana Dashboard.


Dashboard’s Prometheus data source is configured as a variable and thus needs to be mapped to an existing Data Source configured on the target Grafana instance.



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.

Event Template configuration when related to “Metric Label Attributes” & “Optional Metric Labels” is allowed. However, administrators should be cautious of high cardinality scenarios that might occur.

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 Overview

User documentation for creating, working with, and understanding Universal Templates and Integrations.

Universal Tasks Overview

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.

  • The use of environmental variables to determine the location of the DSN file during “Data Source Name” Choice Field Execution

Changelog

ue-sql-odbc 2.2.0 (2023-12-21)

Enhancements

  • Added: Provide the capability to enable or disable the auto-commit mechanism for MS SQL Server databases. (#35223).

ue-sql-odbc 2.1.0 (2023-11-09)

Enhancements

  • Added: Official support for the Amazon Redshift Database (#34719).

  • Added: New Choice Field “SQL Source” that gives the opportunity to provide the SQL Script either as a UAC Script or as plain text (#34606).

  • Added: Enhanced security when the Connection Method is “Connection String” by selecting the proper Credential and passing it via the connection string. This can be achieved using the following notation: [credential_name].user and [credential_name].password (#34605).

Fixes

  • Fixed: Authenticate with the system user connectivity issue when the Connection Method is “Connection String” and the Database Type is “MSSQL” (#34623).

ue-sql-odbc 2.0.0 (2023-10-07)

Enhancements

  • Added: Observability through Metrics related to SQL execution metadata (#34187).

  • Added: Observability through User-Defined Metrics based on SQL resultset (#34220).

  • Added: Feature added to protect task authors from pushing into Universal Controller unnecessary database data by providing the capability to control the number of lines from the SQL result set that will be available on extension output. Task Authors can use  UE_EXTENSION_OUTPUT_MAX_SQL_ROWS  environment variable to control the behavior (#33848)

Deprecations and Breaking Changes

  • Breaking Change: Updated the status descriptions to be more informative (#34335).

    Tasks or workflows evaluating the "Status Description" of the task Instance, either programmatically or within UAC, might be affected by it. In that case, they need to conform to the new "Status Description" Text

  • Breaking Change: Compatibility of this integration for Universal Controller and Universal Agent changed from ">=7.1" to ">=7.2". Template flag “Send Extension Variables” is used which is available starting from Universal Controller 7.2.0.0 (#34187).

  • Breaking Change: In this version, by default, the returned datasets displayed on the extension output are truncated to the first 100 rows. If more than 100 rows need to be pushed into the extension output, the environment variable UE_EXTENSION_OUTPUT_MAX_SQL_ROWS needs to be set appropriately on the task definition.(#33848).
  • Breaking Change: Extension Invocation Fields > Credential fields are displayed as an object for better readability

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


Initial Version