SEC_SQL VARA Objects

SEC_SQL (Secure SQL) VARA objects retrieve values from external databases in a secure manner. SEC_SQL VARA objects are similar to SQL VARA objects because they also run SQL statements on an external database. As opposed to SQL VARA objects, with SEC_SQL you can use all supported variables within SQL statements without putting the database at risk of an SQL injection.

The variables are replaced regardless of the VAR_SECURITY_LEVEL settings in the UC_SYSTEM_SETTINGS - Systemwide Settings variable.

Recommendations

For technical reasons, the system cannot check whether only SELECT commands are used in the variable's SQL. To prevent that SQL commands other that SELECT are run, deploy a database user with read-only access rights.
SEC_SQL VARA objects require a DB Service Agent that was started in DB service mode and that is version v9 SP3 or later. Older Agents do not support this VARA type. For more information, see Installing the Agent for Database Variables.

Restriction

This type of VARA object does 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.

Notes

The maximum number of returned lines is specified in the SQLVAR_MAX_ROWS setting in the UC_SYSTEM_SETTINGS variable.
Depending on the data type, this VARA object returns "" (string, date, time, timestamp) or 0 (number) for NULL values.
Use PromptSet variables here when you use the Dynamic Reload feature for combo box prompts. The PromptSet variables are resolved when the object that includes the PromptSet is executed.
In the Variables Settings and Bind Parameters sections:

You can include PromptSet variables here when using the dynamic reload 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.

To Define a SEC_SQL VARA Object

In the Variable Settings section, specify how the VARA object is going to access the target system from which it retrieves the values:
- Connection
  
  Connection object of type Database that contains the connection parameters for the selected database.
  
  To be able to use PromptSet variables here, the VAR_SECURITY_LEVEL system variable must be set to 3. For more information, see VAR_SECURITY_LEVEL.
- 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.
  
  To be able to use PromptSet variables here, the VAR_SECURITY_LEVEL system variable must be set to 3. For more information, see VAR_SECURITY_LEVEL.
- Default Login
  
  Default Login object that is assigned to your user definition. If your user definition has no Login object assigned, an error occurs. If you select this option, the Login object that is specified in the VARA object is ignored.
  
  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.
  
  For technical reasons, the system does not check the SQL statements that are processed with database VARA objects (SEC_SQLI, SEC_SQL, SQLI, and SQL types). This means that 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).
  
  You cannot specify the variables here, but you can include them using bind parameters. These bind parameters are maintained in the related tables and can include predefined variables, placeholders for VARA objects and PromptSet variables. In the SQL script, use the "?" character in the locations where the bind parameters will be inserted.
  
  Maximum length: 4096 characters
You insert variable values within SQL statements in the Bind Parameters section. You can use variable or VARA object names as bind parameters with any values of your choice. If a variable or VARA object does not exist, no values are replaced but the variable or VARA object name is used. This can cause an error when the variable or VARA object is resolved.

You can enter the following type of variables and VARA objects:
- Script variables
- Object variables
- Predefined variables
- Predefined and user-defined VARA objects
Each line in the table represents one bind parameter.

Supported Data Types and Input Formats
- Date: yyyy-mm-dd
- Time: hh:mm:ss
- Timestamp: yyyy-mm-dd hh:mm:ss
- String: any string
- Boolean: possible values: 0 / 1 or true / false
- Number: no thousand separator, a decimal point as a decimal separator
Important!
- Data types are not converted (no conversion from timestamp to date).
- Inserting an invalid value causes an error when the SQL commands are processed.
- The VAR_SECURITY_LEVEL variable 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 should be inserted. A bind parameter must exist for each "?". Ensure that you maintain the correct order (table).
- You cannot use bind parameters multiple times.
- Do not use SQL functions as bind parameters because in some cases they do not match the data type and input format.
In the Data & Formatting section you specify the data format of the value that is rendered by the VARA object. Do the following:
- Data Type
  - For Text
    - Select Limit Text Length to set a limit to the length of the character string.
    - In Max. text length specify the maximum length. Possible values: 1 to 1024.
    - Select Force upper case if you want to convert the text of the first value column to upper case.
    - In Result format specify the format of the content in the RESULT column, which is the first column that is displayed in the preview. The result column can show a combination of value columns and any other characters.
      
      Column numbers that are specified in curly brackets { } in the Result Format field are replaced by the value of the relevant column.
      
      Example:
      - Value column 1: JOB1
      - Value column 2: WIN01
      - Result format: {1}_{2}
      - Result column: JOB01_WIN01
      If you do not specify a result format, the value in the Result or in the Key column is used.
      
      Important!
  - 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:
    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
- Set Min. value / Min. value and Set max. value / Max. value (All VARA types)
  
  Enforces a minimum/maximum value.
  
  Possible values for Number: max. 16 digits before and 16 digits after the decimal point
All VARA objects have a Script Access section on their definition pages that determines the error handling when scripts read the VARA object. In this section you decide what happens if scripts access the VARA object and the key to which they refer is not available.

You have the following options:
- Return error
  
  The script ends abnormally and a runtime error message is displayed in the Messages console
- Return initial values (Default)
  
  The script continues executing although the key is not available. No runtime error is displayed. The report contains an empty string indicating that no key has been found.
  
  Tip: Ensure that the keys of VARA objects always return a value if you want to use this option.
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 EngineUCSRV.INI file.

Default value: 3145 728 bytes.
To export the content of the preview table to a CSV file, click Export Table. All rows and column are exported. Use this function if you need to further process the content of the CSV file (paste it to a different application for further processing and so on).
To copy one or more rows in the Preview table, select the Copy button. The selected rows are copied to the clipboard and you can paste them to a third party tool (Google, Sheets, Excel, text editor, and so on).

Note: This function is available only if https is used on the Automic Web Interface .
Save your changes.