Working with variables in scripts

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)
• By assigning another variable or a database control to a variable that does not have a data type yet – the variable takes over the type of the source.

Important behavior of declaration:

• A new declaration always resets the value of the variable to “null”, even with the same data type, and removes the link of the variable to the database query it was loaded by.
• The variable that controls a running cycle cannot be declared.
• An undeclared variable used in a condition, a cycle or a calculation raises an error. Substituted into text as “#A#”, it raises no error and is replaced with an empty string.

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) – an array of the numbers 1 to number_of_elements
• LongArray(value1;value2;...)
• DoubleArray(value1;value2;...)
• DateArray(value1;value2;...)
• EmailArray(address1;address2;...) – an array of text values in which each element is one e-mail address from the given list

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. If the second operand is an array too, the calculation is done element by element only when both arrays have the same number of elements; otherwise the first element of the second operand is used, and an empty array leaves the result unchanged.
• 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 by one script line (the line type “Assign more/multiple values from query”), they are iterated synchronously in the cycle. Variables loaded by two lines are not iterated synchronously, even if they use the same query. If one of them has fewer elements than the controlling variable, the cycle ends with an error.
• 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

• Assigning a variable to a variable of a different data type ends with an error; the exceptions are the pair Integer and Long and the difference of two Date values assigned to a variable of type Integer.
• The conversion is performed by writing the variable into the value field, for example “#A#” – the value is first substituted as text and only then converted to the data type of the target variable. The same applies to the expressions “String(#A#)”, “Integer(#A#)” etc.
• Converting text to a number evaluates the text as an arithmetic expression: an empty text gives 0, text after the number is ignored and for the Integer type the decimal part is truncated. An error is raised by text starting with a letter, by text containing only spaces and by text in which the number is followed by the letter “e” or “E” (for example “12eur”), because it is read as an exponent.
• Converting a Long number to Integer ends with an error if the number is outside the range of the Integer type (-2147483648 to 2147483647).

Example of converting a number to text:

• the value “#A#” is assigned to a variable of type String, where “A” is a variable of type Integer

Example of converting text to a number:

• the value “#A#” is assigned to a variable of type Integer, where “A” is a variable of type String

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.