SQL Variables

SQL variable objects retrieve values from external databases.

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

Overview

Variable values that use SQL source are loaded from an external database using SQL statements. In this case, the AE system's database cannot be used.
The variable source "SQL - internal" can be specified to make use of the AE database.

To retrieve values from a database, you need:

A DB Service Agent for variables.
A database-type Connection object which contains the database connection parameters.
A Login object includes the database user and the password.

Note that SQL variables require the database agent to be individually configured and started. Consequently, this cannot be used to execute database jobs and events. Related information is included in Installing the Agent for Database Variables.

To change the maximum time taken for resolving SQL variables, use the setting VAR_TIMEOUT (UC_HOSTCHAR_*).

There is no limit to the lengths 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.

Non-printable characters will be deleted automatically (ASCII characters 0x00 to 0x20 and 0x7F).

The maximum number of lines that is returned is specified in the SQLVAR_MAX_ROWS setting ( UC_SYSTEM_SETTINGS - System-Wide Settings.

The Automation Engine does not truncate trailing space characters from the end SQL variable values.

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.

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 as the variable type. You determine the variable type when you create the object. It cannot be changed once the variable is defined.
Connection	The database-type Connection object that contains the connection parameters for the selected database. 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.
Login	The Login object that stores the name of the database user and the corresponding password. The login data for accessing the database is retrieved from the Connection object if you neither specify a Login object nor set the option Default login. An error occurs if the variable should be resolved and this object does not include any login information. 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.
Default login	You can also select a Login object for SQL variables in the User object (default login), see Users (USER). With the option Apply user's login activated, the Login object that has been assigned to the current user is applied. The Login object that is specified in the variable is ignored in this case. An error occurs if the variable is accessed and no login is defined in the User object.
SQL statement	Text field for specifying SQL statements. These SQL statements are used to select data records in the database and supply them as variable values. For example: select * from col Note that all SQL statements are allowed in this text field. You can even change or delete database entries. AE does not verify whether these SQL commands include errors or are harmful. They are passed on to the database without being verified. This means that an execution error can only occur during database access, which is when the variables are being resolved. Note that you must use the setting VAR_SECURITY_LEVEL in the UC_SYSTEM_SETTINGS - System-Wide Settings variable in the SQL statements if you use Predefined Variables or other Variable objects. Otherwise, no value replacement takes place (default) 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

Specifying Data & Formatting Settings

Column	Description
Data Type	The data type decides the format of the variable's value. Text: An alphanumeric string. Blanks at the end are removed, leading blanks are kept. If the Limit Text Length checkbox is not checked, there will be no limit to the length of the character string. You can format the string further: Select Limit text length to display the Max. text length input box allowing you to limit the text length. Possible values: 1 to 1024 Select Force upper case to convert the variable's text to upper case. Number: Singed integer, i.e. a number between 0 and 2147483647. You can format the string further: Select from the Output format drop-down list, how the number will be displayed. Select Set Min. value to display the Min. value input box. Possible values: max. 16 digits before and 16 digits after the decimal point Select Set max. value to display the Max. value input box. Time: The default time format is hh:mm:ss. You can: Select another format from the drop-down list. Define a minimum and maximum value. Date: The default date format is yyyy-mm-dd. You can: Select another format from the drop-down list. Define a minimum and maximum value. Note that you cannot define a date before 01.01.1980 as the minimum value. Timestamp: The default timestamp format is yyyy-mm-dd hh:mm:ss. You can: Select another format from the drop-down list. Define a minimum and maximum value. Note that you cannot define a date before 01.01.1980 as the minimum 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: You can easily change "Number", "Timestamp", "Time", and "Date" to "Text". The values are converted without any changes. An output format must be selected if "Text" has been changed to "Number", "Timestamp", "Time", or "Date". When the Variable object is saved, the system verifies that the values match the specified format. You cannot directly modify the data types "Date", "Time", "Number", and "Timestamp". They must be converted to the data type "Text" before you can modify them. 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. For Number Singed integer, possible values: any number from 0 to 2147483647 Default format: 16 digits For Time The following formats are possible: HHMMSS HH:MM:SS HHMM HH:MM MMSS MM:SS Default format: HHMMSS The value for this data type must be a four or a six digit number. Decimal places are removed. Four-digit numbers are converted to hours and minutes. Example: Output Format: HH:MM:SS Value of the user or the data source: 1234, 1234.5, 123400, 12:34, 12:34:00 Resulting variable value: 12:34:00 For Date The following formats are possible: DD-MON-YYYY DD-MON-RR MM-DD-YYYY YYYYMMDD MMDDRR DDMMRR MM-DD-RR MM/DD/RR YYMMDD YY.MM.DD YY-MM-DD YYYYMMDD YYYY.MM.DD YYYY-MM-DD DDMMYY DD.MM.YY DD-MM-YY DDMMYYYY DD.MM.YYYY DD-MM-YYYY MMDDYY MMDDYYYY MM/DD/YY MM/DD/YYYY Default format: YYMMDD Note: Some date formats include RR as a placeholder for the abbreviated year. Having both YY and RR accommodates different century-counting conventions, which are based on the two digits of the abbreviated year value. YY - The current century applies for numbers from 00 to 80. The previous century applies for 81 - 99. RR - The current century applies for numbers from 00 to 49. The previous century applies for 50 - 99. Examples: Date format DDMMYY and the resulting complete dates: 010305 - corresponds to 01 March 2005 010365 - corresponds to 01 March 2065 010380 - corresponds to 01 March 2080 010385 - corresponds to 01 March 1985 Date format DDMMRR and the resulting complete dates: 010305 - corresponds to 01 March 2005 010365 - corresponds to 01 March 1965 010380 - corresponds to 01 March 1980 010385 - corresponds to 01 March 1985 For Timestamp Possible formats: YYYYMMDDHH24MISS YYYY-MM-DD HH:MM:SS Default format: YYYY-MM-DD HH:MM:SS The value for this data type must be a four or a six digit number. Decimal places are removed. Four-digit numbers are converted to hours and minutes. Example: Output Format: YYYY-MM-DD HH:MM:SS Value of the user or the data source: 20110325 2201, 20110325 2201.5, 20110325 220100 Resulting variable value: 2011-03-25 22:01:00 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. YY - The current century applies for numbers between 00 and 80. The previous century applies for 81 - 99. RR - The current century applies for numbers between 00 and 49. The previous century applies for 50 - 99. For example: Date format DDMMYY and the resulting complete dates: 010305 - corresponds to 01 March 2005 010365 - corresponds to 01 March 2065 010380 - corresponds to 01 March 2080 010385 - corresponds to 01 March 1985 For example: Date format DDMMRR and the resulting complete dates: 010305 - corresponds to 01 March 2005 010365 - corresponds to 01 March 1965 010380 - corresponds to 01 March 1980 010385 - corresponds to 01 March 1985 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: Time Output Format: HH:MM:SS Value of the user or the data source: 1234, 1234.5, 123400, 12:34, 12:34:00 Resulting variable value: 12:34:00 Data type: Timestamp Output Format: YYYY-MM-DD HH:MM:SS Value of the user or the data source: 20110325 2201, 20110325 2201.5, 20110325 220100 Resulting variable value: 2011-03-25 22:01:00
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	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 that 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 using {} curly brackets in the Result Format field will be replaced by the value of the corresponding column. For example: Value column 1: JOB1 Value column 2: WIN01 Return Format: {1}_{2} Result column: JOB01_WIN01 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 value of this column exceeds the allowed range, the line is skipped and is not available via the Variable object.

Column

Description

Data Type

The data type decides the format of the variable's value.

Text: An alphanumeric string. Blanks at the end are removed, leading blanks are kept. If the Limit Text Length checkbox is not checked, there will be no limit to the length of the character string.
You can format the string further:
- Select Limit text length to display the Max. text length input box allowing you to limit the text length.
  Possible values: 1 to 1024
- Select Force upper case to convert the variable's text to upper case.
Number: Singed integer, i.e. a number between 0 and 2147483647.
You can format the string further:
- Select from the Output format drop-down list, how the number will be displayed.
- Select Set Min. value to display the Min. value input box.
  Possible values: max. 16 digits before and 16 digits after the decimal point
- Select Set max. value to display the Max. value input box.
Time: The default time format is hh:mm:ss. You can:
- Select another format from the drop-down list.
- Define a minimum and maximum value.
Date: The default date format is yyyy-mm-dd. You can:
- Select another format from the drop-down list.
- Define a minimum and maximum value. Note that you cannot define a date before 01.01.1980 as the minimum value.
Timestamp: The default timestamp format is yyyy-mm-dd hh:mm:ss. You can:
- Select another format from the drop-down list.
- Define a minimum and maximum value. Note that you cannot define a date before 01.01.1980 as the minimum 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:

You can easily change "Number", "Timestamp", "Time", and "Date" to "Text". The values are converted without any changes.
An output format must be selected if "Text" has been changed to "Number", "Timestamp", "Time", or "Date". When the Variable object is saved, the system verifies that the values match the specified format.
You cannot directly modify the data types "Date", "Time", "Number", and "Timestamp". They must be converted to the data type "Text" before you can modify them.

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.

For Number

Singed integer, possible values: any number from 0 to 2147483647

Default format: 16 digits
For Time

The following formats are possible:
- HHMMSS
- HH:MM:SS
- HHMM
- HH:MM
- MMSS
- MM:SS
Default format: HHMMSS

The value for this data type must be a four or a six digit number. Decimal places are removed. Four-digit numbers are converted to hours and minutes.

Example:

Output Format: HH:MM:SS

Value of the user or the data source: 1234, 1234.5, 123400, 12:34, 12:34:00

Resulting variable value: 12:34:00
For Date

The following formats are possible:
- DD-MON-YYYY
- DD-MON-RR
- MM-DD-YYYY
- YYYYMMDD
- MMDDRR
- DDMMRR
- MM-DD-RR
- MM/DD/RR
- YYMMDD
- YY.MM.DD
- YY-MM-DD
- YYYYMMDD
- YYYY.MM.DD
- YYYY-MM-DD
- DDMMYY
- DD.MM.YY
- DD-MM-YY
- DDMMYYYY
- DD.MM.YYYY
- DD-MM-YYYY
- MMDDYY
- MMDDYYYY
- MM/DD/YY
- MM/DD/YYYY
Default format: YYMMDD

Note: Some date formats include RR as a placeholder for the abbreviated year. Having both YY and RR accommodates different century-counting conventions, which are based on the two digits of the abbreviated year value.
- YY - The current century applies for numbers from 00 to 80. The previous century applies for 81 - 99.
- RR - The current century applies for numbers from 00 to 49. The previous century applies for 50 - 99.
Examples:
- Date format DDMMYY and the resulting complete dates:
  
  010305 - corresponds to 01 March 2005
  010365 - corresponds to 01 March 2065
  010380 - corresponds to 01 March 2080
  010385 - corresponds to 01 March 1985
- Date format DDMMRR and the resulting complete dates:
  
  010305 - corresponds to 01 March 2005
  010365 - corresponds to 01 March 1965
  010380 - corresponds to 01 March 1980
  010385 - corresponds to 01 March 1985
For Timestamp

Possible formats:
- YYYYMMDDHH24MISS
- YYYY-MM-DD HH:MM:SS
Default format: YYYY-MM-DD HH:MM:SS

The value for this data type must be a four or a six digit number. Decimal places are removed. Four-digit numbers are converted to hours and minutes.

Example:

Output Format: YYYY-MM-DD HH:MM:SS
Value of the user or the data source: 20110325 2201, 20110325 2201.5, 20110325 220100
Resulting variable value: 2011-03-25 22:01:00

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.
YY - The current century applies for numbers between 00 and 80. The previous century applies for 81 - 99.
RR - The current century applies for numbers between 00 and 49. The previous century applies for 50 - 99.

For example:
Date format DDMMYY and the resulting complete dates:
010305 - corresponds to 01 March 2005
010365 - corresponds to 01 March 2065
010380 - corresponds to 01 March 2080
010385 - corresponds to 01 March 1985

For example:
Date format DDMMRR and the resulting complete dates:
010305 - corresponds to 01 March 2005
010365 - corresponds to 01 March 1965
010380 - corresponds to 01 March 1980
010385 - corresponds to 01 March 1985

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: Time
Output Format: HH:MM:SS
Value of the user or the data source: 1234, 1234.5, 123400, 12:34, 12:34:00
Resulting variable value: 12:34:00

Data type: Timestamp
Output Format: YYYY-MM-DD HH:MM:SS
Value of the user or the data source: 20110325 2201, 20110325 2201.5, 20110325 220100
Resulting variable value: 2011-03-25 22:01:00

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

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 that 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 using {} curly brackets in the Result Format field will be replaced by the value of the corresponding column.

For example:
Value column 1: JOB1
Value column 2: WIN01
Return Format: {1}_{2}
Result column: JOB01_WIN01

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 value of this column exceeds the allowed range, the 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.

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.

Previewing Commands

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

The database agent sends the preview data to the Automation Engine in the form of a message. An error occurs if this message exceeds the maximum message length that a server process accepts (see parameter MaxMsgSize= in the Automation Engine's UCSRV.INI file ( see Automation Engine), default value: 3145 728 Bytes). In this case, you can limit the result either by using a maximum number of lines or through SQL statements.