SQLI Variables
SQLI or SQL Internal variable objects retrieve values from the Automation Engine database.
This topic provides information on the following:
- Overview
- Defining the Variable Settings
- Specifying Data & Formatting Settings
- Specifying the Script Access Setting
- Defining the Variable Usage in Other Clients
- Previewing Commands
If a variable's source is "SQL - internal", its values are retrieved from the AE database via SQL statements. It is not necessary to specify a connection or a login because the database access remains internal.
The administrator can activate this function by using the setting SQLVAR_INTERNAL in the variable UC_SYSTEM_SETTINGS. The user privilege "Create and modify SQL- internal variables" is also required.
For security reasons, you cannot create or modify SEC_SQLI or SQLI variables by default. You can change this setting via SQLVAR_INTERNAL in the system variable UC_SYSTEM_SETTINGS
There is no limit to the length of values. If a maximum text limit has been selected in the "Attributes" tab, this only applies to the first value column. Items exceeding this limit will be excluded.
To determine the maximum number of returned lines, use the setting SQLVAR_MAX_ROWS in the variable UC_SYSTEM_SETTINGS.
When you use database variables (SEC_SQLI, SEC_SQL, SQLI, and SQL types), binary fields are not supported. Binary data that is the result of an SQL query will cause an error in the preview or when the values are retrieved at runtime.
Note that in database variables (SEC_SQLI, SEC_SQL, SQLI, and SQL types), the system will not check the SQL statements that are processed for technical reasons. This also means that you can change or delete data records by using Variable objects. For security reasons, Automic recommends restricting user access accordingly. If possible, deploy a database user with read-only rights for variables that 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 variables (SEC_SQLI, SEC_SQL, SQLI, and SQL types) return the value "" (string, date, time, timestamp) or 0 (number) for NULL values.
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). Automic recommends verifying the commands in SQLI variables of users very carefully because the Automation Engine cannot intercept such an error for technical reasons.
PromptSet variables are supported in select fields for BACKEND, EXEC, FILELIST, SEC_SQLI, SEC_SQL, SQLI, SQL, and XML variable types. Using PromptSet variables are necessary when using the Dynamic Reload feature.
Defining the Variable Settings
|
Field |
Description |
|---|---|
| Type | This read-only field shows SQL - internal as the variable type. You determine the variable type when you create the object. It cannot be changed once the variable is defined. |
|
The SQL statements used to select specific database entries that serve as variable values. For example: select * from OH To open a cell editor to enter your SQL statement, click the Note that SQL statements are not checked. You can even change or delete database entries. AE 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. Note that using Predefined Variables or other Variable objects requires you to specify the setting VAR_SECURITY_LEVEL in the UC_SYSTEM_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 feature for Combobox prompts. When PromptSet variables are used in dynamic Variable objects, and you click Preview, you will get an error. This is because the PromptSet variables do not exist in the dynamic Variable object. However, the PromptSet variables will be resolved when objects including the PromptSet are executed. Maximum length: 4096 characters |
icon on the right side of the field.Specifying Data & Formatting Settings
|
Column |
Description |
|---|---|
| Data Type |
The data type decides the format of the variable's value.
The exact format of the selected data type (with the exception of "String") can be determined in the Output Format field. The data type of the values that are available in the first column is checked. The variable's data type must match the data type of the first column returned by the SQL statements when you use the sources "SQL" or "SQL internally". Data can also be processed using specific SQL statements. An error occurs if static variables should be stored or dynamic variables are accessed and the data types do not match. Note the following if you change the data type of static Variable objects that already include values:
The data type of variables with the type "Multi", "SQL", or "SQL internally" can be changed at any time. The data type of the script variable in question ("float", "signed", "unsigned", or "string") is irrelevant in values that have been retrieved from Variable objects with the data type "Number" when using the script element GET_VAR. The value can always be stored. Algebraic signs and decimal places are removed if they are not supported by the script variable's data type. The values are always passed on to the script as a "string" if a different data type has been specified in the Variable object. |
| Output Format |
This setting defines the format that should be used to store values in the Variable object (source: static variables) or to retrieve values from the data source (dynamic variables). The contents of this selection menu depend on the data type. Several different formats are available for each data type (except for the String type). The output format only refers to the first column. In static variables, this is the column Value 1; in Database and Multi variables, it is the first basic column. Note that Multi variables use the first basic column as their reference column. This means that the source variable's output format can affect the retrieved values. The output format of static variables for a particular datatype can be changed at any time. In doing so, the values are converted to the new format. If the values do not match the old format, they cannot be converted to a new format. The format that is used to enter value in static variables is irrelevant unless it is an AE-supported format of the data type in question. The values are then converted to the relevant output format. The data type "Text" does not show this combo box. Timestamp
Date
Time
Number
Some date formats include RR as a placeholder for the abbreviated year. Having both YY and RR accommodates different century-counting conventions based on the two digits of the abbreviated year value. For example: For example: The time value for the data type "Time" or "Timestamp" must be a 4 or 6-digit number. Decimal places are removed. 4-digit numbers are converted to hours and minutes. Examples: Data type: Timestamp |
|
Set min. value Min. value |
Enforces a minimum value. The data type "Text" does not show these fields. |
|
Set max. value Max value |
Enforces a maximum value. The data type "Text" does not show these fields. |
|
Limit text length Max. Text Length |
Enforces a maximum character length. Allowed values: 1 to 1024 If this option is deactivated, there is no limit. The Key column of VARA objects has a maximum limit of 200 characters. This limit always exists and cannot be increased or deactivated. The result column of dynamic variables has no limit. The length depends on the value columns. In static and dynamic variables, this limit refers to the first value column. The Key or result column is not checked. Values that exceed the maximum length are excluded. Only the data type "Text" shows these fields. |
| Force upper case |
Lower case letters that are used in variable values are automatically converted to upper case. This only affects the first value column. Only the data type "Text" shows this field. |
| Result Format |
The definition of the result column's contents. The first column that is displayed in the preview is the result column. The other columns include the individual values of the database columns which have been returned by the SQL query. The result column can show a combination of value columns and any characters. This is defined via the Result Format. Column numbers that were specified by using { } curly brackets in the field Result Format will be replaced by the value of the corresponding column. For example: The result column corresponds to the first value column if no Result format is specified. The limitations for variables (min. value, max. value, limit text to) do not affect the result column but the first returned column. If the values of this column exceed the maximum range, this line is skipped and is not available via the Variable object. |
Specifying the Script Access Setting
You can specify what happens if a key is not found with the If key not found radio buttons.
|
To: |
Choose: |
|---|---|
| Have an error message displayed if an object attempts to access an item that is not available at runtime via script | Return error |
| Not use the result of output format of when it is " " | Return initial values |
Defining the Variable Usage in Other Clients
In client 0 you specify how the VARA object will behave in other clients.
When you define Variable objects in system Client 0, they can be used in other clients, either because they refer to the values set in the Variable in Client 0 or because you copy the original object from Client 0 to other clients and, if necessary, edit its values in the target clients.
With the Variable Usage in Other Clients options you define the behavior of this Variable object in clients other than 0:
| Option |
Description |
|---|---|
| Don't share but allow duplicates |
If you select this option, this VARA object is available in clients other than 0 and you can use it there. You can modify all of its parameters in the non-0 clients, which can then be different per client. The name of the variable can be kept in non-0 clients. This means that if there is already a VARA object in the non-0 client with this name, two independent VARA objects with the same name will be available. You can modify all the parameters of both variables independently per client. |
| Share and allow read-only access |
If you select this option, this VARA object is available in clients other than 0 and you can use it there but you cannot modify any of its parameters. Additionally, the name of this VARA object must be unique system-wide. |
| Share but keep attribute settings from system client |
If you select this option, this VARA object can be copied to non-0 clients. If you do so, it keeps all the definitions made on the Attributes page but you can edit the Key and Value settings in the non-0 clients. Each client can have its own Key/Value definitions. Only available for STATIC variables. |
To run command, select the command and click Preview. This executes the SQL statements and displays the results. In doing so, the SQL statements are checked and possible errors can be identified (for example, the data type of the Variable object and of the database do not match).
In the example above, the following SQL statement is entered in the SQL Server Statement field.
select oh_name from oh
where oh_otype = 'JOBI'
and oh_deleteflag = 0
and oh_client = &$CLIENT#
order by oh_name asc