SQL VARA Objects

SQL VARA objects retrieve values from external databases using SQL statements.

Prerequisites

To be able to define SQL VARA objects, the following must be true:

A DB Service Agent for variables must be available that is individually configured and started. This Agent cannot be used to execute database jobs and events. For more information, see Installing the Agent for Database Variables.
A database-type Connection (CONN) object is defined that contains the database connection parameters
A Login object includes the database user and the password

Defining SQL VARA Objects

An SQL VARA object definition is made up of the following pages:

General Page

Common to all object types, here you define basic object information and apply custom values to it
Variables

Here you define the object-specific settings. Some sections on this page are common to all VARA objects and are described for all of them in dedicated topics. This topic describes the SQL-specific settings on the Variables page.
Version Management Page

Common to all object types, here you find a list of all versions of the object
Documentation Page

Common to all object types, here you enter information that you consider important to understand the configuration and behavior of the object

Important Considerations

There is no limit to the lengths of values. If a maximum text limit has been selected in the Attributes Page, this only applies to the first value column. Items exceeding this limit will be excluded.
The maximum number of lines that is returned is specified in the SQLVAR_MAX_ROWS setting (UC_SYSTEM_SETTINGS - Systemwide Settings.
To change the maximum time taken for resolving SQL variables, use VAR_TIMEOUT (UC_HOSTCHAR_DEFAULT - Host Characteristics*).
Non-printable characters are deleted automatically (ASCII characters 0x00 to 0x20 and 0x7F).
The Automation Engine does not truncate trailing space characters from the end SQL variable values.
Database VARA objects (SEC_SQLI, SEC_SQL, SQLI, and SQL types) do not support binary fields. Binary data that result from an SQL query causes an error in the preview or when the values are retrieved at runtime.
In database VARA objects (SEC_SQLI, SEC_SQL, SQLI, and SQL types), the system does not check the SQL statements that are processed for technical reasons. You can change or delete data records by using VARA objects. For security reasons, consider restricting user access accordingly. If possible, deploy a database user with read-only rights for VARA objects that can access an external database (SQL, SQL SECURE). For internal SQL variables (SQLI, SQLI SECURE), you can limit object access via a privilege (create and modify SQL-internal variables).
Depending on the data type, database VARA objects (SEC_SQLI, SEC_SQL, SQLI, and SQL types) return the value "" (string, date, time, timestamp) or 0 (number) for NULL values.
PromptSet variables are supported in select fields for BACKEND, EXEC, FILELIST, SEC_SQLI, SEC_SQL, SQLI, SQL, and XML variable types. Using PromptSet variables is necessary when using the Dynamic Reload (Combobox Prompts) feature.

Defining the Variables Page

In the Variable Settings section specify the following:
- Type
  
  Read-only, always SQL
- Connection
  
  Connection (CONN) object of type Database that contains the connection parameters for the selected database
- Login
  
  Login object that stores the name and password of the database user
  
  If you neither specify a Login object nor set the option Default login, the login data for accessing the database is retrieved from the Connection object.
- Default Login
  
  Default Login object that is assigned to your Users (USER) definition.
  
  If you select this option, the Login object that is specified in the VARA object is ignored.
  
  An error occurs when resolving the VARA object if no login is defined in the User object.
  
  PromptSet variables cannot be used here.
- SQL Statement
  
  SQL statements used to select specific database entries that serve as variable values.
  
  Variables that are directly specified in SQL are replaced. To include variables, use bind parameters.
  
  Maximum length: 4096 characters
You can include PromptSet variables here when using the Dynamic Reload (Combobox Prompts) feature for combo box prompts. When you use PromptSet variables in a dynamic VARA object, the Preview feature returns an error. This is because the PromptSet variables do not exist in the dynamic VARA object. However, the PromptSet variables will be resolved when objects including the PromptSet are executed.
Define the Data Types and Formatting.
Define the Script Access.
If you are logged in to Client 0, an additional section is available. For more information, see Client 0 VARA Objects in Other Clients.
To run a command, select it and click Preview. The statements are executed and their results displayed. The preview function serves to check for potential errors in the database statements (for example, it the data type of the VARA object and of the database do not match).

The database Agent sends the preview data to the Automation Engine in the form of a message. If this message exceeds the maximum message length that a server process accepts, an error occurs.

Maximum length: Set in the MaxMsgSize= parameter of the Automation Engine UCSRV.INI file.

Default value: 3145 728 bytes. You can limit the result either by using a maximum number of lines or through SQL statements.