CS

Working with variables in scripts

Home / Support / Manuals / Administrator Guide / Script designer /

Working with variables in scripts

CS

What a script variable is

  • A script variable is a temporary storage used during script execution.
  • Variables are written in the form “#A#”, “#B#”, “#C#”, etc.
  • Script variables are not database columns – they exist only for the duration of the script execution.
  • Each variable always has exactly one data type.

Supported variable data types:

  • String – text string
  • Integer – integer number
  • Long – large integer number
  • Double – decimal number
  • Date – date

Database null value

  • The script uses the database value “null”.
  • The value “null” means that the variable has no defined value.
  • A newly declared variable that has not been assigned a value always has the value “null”.
  • The value “null” can be assigned explicitly using the variable “#null#”.

Variable declaration

A variable must always be declared before it is used. Declaration defines the variable’s data type.

A variable can be declared in several ways:

  • Using a script command for variable declaration.
  • By loading a value from a database query – the variable’s data type is automatically taken from the database column type.
  • Using expressions:
    • String(text)
    • Integer(number)
    • Long(number)
    • Double(number)
    • Date(date)

Important behavior of declaration:

  • If a variable already exists and is redeclared with a different type, its value is reset to “null”.

Single value vs. array of values

  • A variable loaded from a database query automatically becomes an array of values.
  • Each row in the query result corresponds to one element of the array.
  • If the database query returns no records, the result is an array with zero elements.

Arrays of values can also be created manually using server functions:

  • StringArray(value1;value2;…)
  • IntegerArray(number_of_elements)
  • LongArray(number_of_elements)
  • DoubleArray(value1;value2;…)
  • DateArray(value1;value2;…)

Assigning values

  • A variable value can be assigned to another variable or to a database control.
  • Assignment can be:
    • simple (a single value), or
    • based on a mathematical expression (addition, subtraction, multiplication, division).
  • If a variable is an array of values, arithmetic operations are applied to all elements of the array.
  • When assigning an array of values to a database control, only the first value of the array is used.
  • If the array contains no elements or the value is “null”, the database value “null” is assigned to the control.

Cycle

  • A cycle is used to sequentially process elements of an array of values.
  • The number of cycle iterations corresponds to the number of elements in the controlling variable.
  • If the array contains no elements, the cycle is not executed at all.

Variable behavior inside a cycle:

  • During a single iteration, the variable behaves as a single-value variable.
  • After the cycle finishes, the variable again behaves as an array of values.
  • Elements with the value “null” are automatically skipped in the cycle.
  • If multiple variables were loaded using the same database query, they are iterated synchronously in the cycle.
  • It is recommended to use the primary key “id” as the controlling variable of the cycle.

Comparing values

  • Script values of two variables can be compared.
  • If one of the variables is an array of values, only its first value is used for comparison.

The “contains” operator:

  • When used on an array of values, it tests whether the array contains a specific value.
  • When used on a text string, it tests whether the string contains a given substring.

Testing for an empty (unfilled) value

An empty (unfilled) value exists in the database in two forms – as the database value “null” and, for text columns, also as a zero-length text string (empty string). The “is not defined” operator alone catches only the “null” value – it does not catch an empty string. A condition testing whether a value is (not) filled in must therefore take both variants into account.

The condition “value is not filled in” by data type or control type:

  • String (TextBox, TextArea, RichTextBox, ComboBox, ListBox, MultiListBox, Radio, CheckBox) – a composed condition: value equals “” or value is not defined. In SQL: ng_tb = '' OR ng_tb IS NULL.
  • ForeignKey, File, Image – a composed condition: value equals “0” or value is not defined. In SQL: ng_idfk = 0 OR ng_idfk IS NULL.
  • Integer, Long, Double, Date – the “is not defined” operator is sufficient. These data types cannot contain an empty string; an unfilled value is always “null”.

In a script, the composed condition is written using a line of type “If (composed expression) …” with two conditions joined by the logical operator “or”. Example of interrupting a script when the user has no e-mail address filled in – the variable “#A#” of type String contains the user’s e-mail address loaded by a database query, and a line of type “14. If (composed expression) throw exception” contains two conditions:

  • First condition: “#A#” | operator “equals” | the value field is intentionally left empty (the comparison is made against an empty string) | joined by “or”.
  • Second condition: “#A#” | operator “is not defined”.
  • Exception text: “The user has no e-mail address filled in.”

A simple condition (“If (expression) …”) and a composed condition (“If (composed expression) …”) treat the “null” value differently:

  • A simple condition is evaluated directly on the server and takes the “null” value into account:
    • The “equals” operator evaluates two “null” values as equal – a test for “null” can be written by comparing with the “#null#” variable.
    • The “does not equal” operator evaluates a “null” value as satisfied against any filled-in value, including an empty string.
  • A composed condition is evaluated as an SQL condition with three-valued logic:
    • A “null” value satisfies neither the “equals” nor the “does not equal” operator – regardless of the compared value.
    • A “null” value is caught by the “is not defined” operator or by comparing with the “#null#” variable using the “equals” operator.

This has a practical consequence for the opposite test – “the value is filled in” – for the String data type:

  • In a composed condition, a single “does not equal” condition with an empty value field is sufficient – under SQL logic, a “null” value does not pass the condition.
  • In a simple condition, “does not equal” with an empty value field lets the “null” value through (null satisfies the condition) – the “is filled in” test therefore cannot be written as a single simple condition.

The same rule also applies to query filtering conditions. A detailed description of conditions is provided in the separate guide Database query designer.

Data type conversion

  • A value’s data type can be changed by assigning it to a variable of a different data type.
  • Writing a value into a variable of the target type performs the conversion.

Example of converting a number to text:

  • a variable of type Integer is assigned to a variable of type String

Example of converting text to a number:

  • a variable of type String is assigned to a variable of type Integer

Typical mistakes when working with variables

  • A variable is used without being declared.
  • The script author expects a single value, but the variable is actually an array of values.
  • The value “null” is not handled in subsequent processing.
  • A cycle uses a controlling variable that contains “null” values.