SQLI VARA Objects

SQLI or SQL Internal variable objects retrieve values from the Automation Engine database using SQL statements. You do not have to specify a Connection or a Login object because the database access remains internal. There is no limit to the length of the values. If you set one, this value applies to the first value column only. Items exceeding this limit are excluded.

To determine the maximum number of returned lines, use the setting SQLVAR_MAX_ROWS in the variable UC_SYSTEM_SETTINGS - Systemwide Settings.

Prerequisites

• Set SQLVAR_INTERNAL to YES in the UC_SYSTEM_SETTINGS - Systemwide Settings.
• Your use definition has the right to Create and modify SQL-internal variables.

Defining SQLI VARA Objects

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

• General Page

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

  • Variable Settings, see below
  • Parameters
  • Data Types and Formatting
  • Script Access
  • Client 0 VARA Objects in Other Clients
  • Preview, see below
• Version Management Page
• Documentation Page

Important Considerations

When using SEC_SQLI VARA objects take the following into account:

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

  Important! A conversion error that occurs while running SQL commands on MS SQL server databases can cause a deadlock or an inconsistent transaction with an unforeseen result. It is recommendable to verify the commands in SQLI variables very carefully because the Automation Engine cannot intercept such an error.

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

• You can include PromptSet variables here when using the Dynamic Reload (Combobox Prompts) feature for Combobox 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.

Variable Settings Section

• Type

  Read-only, always SQL.

• Oracle Statement / SQL Server Statement / DB2 Statement / PostgreSQL Statements

  The SQL statements used to select specific database entries that serve as variable values. For example, select * from OH.

  Maximum length: 4096 characters.

  To open a cell editor to enter your SQL statement, click the icon on the right side of the field.

  Important! SQL statements are not checked. You can change or delete database entries.

  Notes:

  • The Automation Engine does not verify whether these SQL commands include errors or are dangerous. They are passed on to the database without being verified. This means that an execution error will only occur during database access, which is when the variables are being resolved.
  • Using Inserting Variables in Objects and Scripts or other VARA objects requires you to specify the setting VAR_SECURITY_LEVEL in the UC_SYSTEM_SETTINGS - Systemwide Settings variable accordingly. Otherwise, the variables are not replaced (standard) and a database error occurs.

  • You can include PromptSet variables here when using the Dynamic Reload (Combobox Prompts) feature for Combobox 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.

Preview section

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

Example:

select oh_name from oh

where oh_otype = 'JOBI'

and oh_deleteflag = 0

and oh_client = &$CLIENT#

order by oh_name asc

See also:

• VARA Objects
• Variables and VARA Objects