SEC_SQLI Variables

SEC_SQLI or Secure SQL Internal variable objects securely retrieve values from the Automation Engine database.

This topic provides information on the following:

SQL Internal Secure - Overview

This variable type fulfills the same function as "SQL - internal", with the difference being that variables can always be inserted in the SQL statements - regardless of the VAR_SECURITY_LEVEL setting in the UC_SYSTEM_SETTINGS variable ( UC_SYSTEM_SETTINGS - System-Wide Settings). To include variables in the SQL, you need to use bind parameters. In doing so, you cannot compose SQL statements, and this protects the database from the risk of an SQL injection.

As in "SQLI internal", the variable values are directly retrieved from the AE database. This means that you do not need a database agent.

SQLI SECURE variables are bound to the SQLVAR_INTERNAL setting in the UC_SYSTEM_SETTINGS variable ( UC_SYSTEM_SETTINGS - System-Wide Settings) and to the privilege "Create and modify SQL-internal variables".

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 UC_SYSTEM_SETTINGS system variable ( UC_SYSTEM_SETTINGS - System-Wide Settings).

For technical reasons, the system cannot check whether only SELECT commands are used in the variable's SQL.

To determine the maximum number of returned lines, use the setting SQLVAR_MAX_ROWS in the UC_SYSTEM_SETTINGS system variable ( UC_SYSTEM_SETTINGS - System-Wide 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.

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.

Oracle Statement

SQL Server Statement

DB2 Statement

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 icon on the right side of the field.

Variables that are directly specified in SQL will not be replaced. To include variables, use bind parameters.

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

Defining Bind Parameters

The table in the Bind Parameters section contains a list of bind parameters. You use bind parameters in order to insert variable values within SQL statements. You can use script variables, object variables, predefined variables, and placeholders for VARA objects that you enter in this table. Each line represents one bind parameter.

The variable VAR_SECURITY_LEVEL (UC_SYSTEM_SETTINGS) does not impose any limitations.

In the SQL statements, you must use a "?" character at every location where the replaced value of a bind parameter is to be inserted. A bind parameter must exist for each "?". Make sure that you maintain the correct order (table). Note that you cannot use bind parameters multiple times.

No values will be replaced if a specified variable does not exist. In this case, the variable name is used, which can cause an error when the variable is to be resolved.

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.

You can use variable names as bind parameters, with any values of your choice.

The following field data types and input formats are supported for bind parameters:

Other field types of the database and other formats are not supported. Data types are not converted (no conversion from timestamp to date). Inserting an invalid value causes an error when the SQL commands are processed.

Working in the Bind Parameter Table

To: Do this:
Add a row to the table

Click in the appropriate section. Click the cells in the table to edit their values.
Edit a cell in table row

Click the cell in the table and edit its value.

Delete one of more rows to the table

Check the checkbox for the row(s) and click .

Delete all rows to the table

Check the checkbox column header and click .
Cut one or more rows and them to the AWI clipboard

Check the checkbox for the row(s) and click .
Copy one or more rows and them to the AWI clipboard

Check the checkbox for the row(s) and click .

Paste row(s) from the AWI clipboard to the bottom of the table

 Click .

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

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:
Value column 1: JOB1
Value column 2: WIN01
Result 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 values of this column exceed the maximum range, this line is skipped and is not available via the Variable object.

Scope

This setting can be used to simplify the script that writes values to variables or reads them. Usually, the key must be specified as a parameter in the script element :PUT_VAR or GET_VAR. However, this makes it difficult to ensure that values are stored with unique keys. By specifying a particular scope, you can simplify design of scripts that write to or read from the variable. For all values of scope except Freely selected, the key is optional, and the default key used to access the variable if no key is specified depends on the characteristics of the task that is accessing the variable.

  • No scope: (Optional) The AE system automatically uses "*" as the key. The variable contains only one entry.
  • Freely selected: (Required) N/A. If the key is not specified, the system returns the runtime error U00003712.
  • Host - each host name: (Optional) The name of the agent where the task is running
  • Task - each task name: (Optional) The name of the running executable object
  • Workflow name - each workflow name: (Optional) The name of the parent workflow of the task
  • Workflow session - each workflow session: (Optional) The run ID of the parent workflow in which the task is running
  • User - each user name: (Optional) The name of the executing user
  • User session - each user session: (Optional) The run ID of the user session

  • The entry (row) corresponding to the user session will be deleted when the user session ends.

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.

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

In the example above, the following SQL statement is entered in the SQL Server Statement field. The values of the bind variables replace the "?" characters.

select ah_name as ObjectName,ah_timestamp1 as ActivationTime,ah_timestamp2 as StartTime,ah_timestamp4 as EndTime from ah

where ah_client = ?

and ah_otype = ?

and ah_deleteflag = ?

and ah_hostdst = ?

order by ah_name asc