General Script Language

Lexical Conventions

Unlike the SQL language, the script language is case-sensitive, that means upper and lower case has to be considered (for example, variable definitions). Furthermore, there is a constraint that variable and function identifiers should contain only ASCII characters. However, the ending semicolon (;) after a script statement is optional.

Comments

The two types of comments in Exasol are:

  • Line Comment: It begins with the character -- and indicate that the remaining part of the current line is a comment.
  • Block Comment: It is indicated by the characters /* and */ and can be spread across several lines. All of the characters between the delimiters are ignored.
Example
-- This is a single line comment

/*
This is
a multiline comment
*/

Types and Values

The following table describes the types that are distinguished in the script language.

Type Range of Values
nil

nil is the "unknown type".

null and NULL

null and NULL represent the SQL NULL. This constant is not included in the Lua language and was added by Exasol to allow comparisons with result data and returning NULL values.

boolean

Boolean values true and false.

string

String values are specified in single or double quotes ('my_string' or "my_string") and consist of any 8-bit characters. Alternatively, you can enclose string values in double square brackets ([[my_string]]). This notation is especially useful if quotes are used in the string and if you don't want to use escape characters ('SELECT * FROM "t" WHERE v=\'abc\';' equates to "SELECT * FROM \"t\" WHERE v='abc';" or simply [[SELECT * FROM "t" WHERE v='abc';]]).

number

Integers or floating point numbers (for example, 3, 3.1416, 314.16e-2, 0.31416E1)

decimal

Decimal values (for example, 3,3.1416)

The type decimal is not a standard Lua type, it is a user-defined Exasol type (userdata). It is similar to the special value NULL.

The decimal type supports the following operators and methods for mathematical calculations and conversions.

Constructor

decimal(value [,precision [, scale]])

value can be of type string, number or decimal.

The default for precision and scale is (18,0), i.e. decimal(5.1) is rounded to the value 5.

Operators

+, -, *, /, and %

Addition, subtraction, multiplication, division and modulo calculation of two numerical values. The return type is determined dynamically: decimal or number

== , <, <=, >, and >=

Comparison operators for numerical values. Return type: boolean

Methods

var:add(), var:sub(), var:mul(), var:mod()

Addition, subtraction, multiplication and modulo calculation of two numerical values.

No new variable is created in this case.

var:scale(), var:prec()

Scale and precision of a decimal. Return type: number

var:tonumber()

Conversion into a number value. Return type: number

var:tostring(), tostring(var)

Conversion into a string value. Return type: string

Example

Here are some examples of using decimal values.

d1 = decimal(10)
d2 = decimal(5.9, 2, 1)
s = d1:scale()     -- s=0
str = tostring(d2)     -- str='5.9'
d1:add(d2)         -- result is 16

Simple Variables

Script variables are typed dynamically. They do not have variable type but they have values assigned to them. You can use the operator = to assign the value.

By default, the scope of a variable is global. However, you can limit it to the current execution block by using the keyword local.

Exasol recommends to use local variables to explicitly show the variable declaration.

Example
local a = nil     -- nil 
local b = false     -- boolean 
local c = 1.0     -- number 
local d = 'xyz'     -- string
d = 3             -- same variable can be used for different types 
local e,f = 1,2     -- two variable assignments at once
g = 0             -- global variable

Arrays

An array consists of a list of values (my_array={2,3,1}) that can be heterogeneous (with different types).

You can access an element of an array through its position beginning from 1 (my_array[position]). The size of the array is determined by the # operator (#my_array). If the value is nil, you will get an exception.

The elements of an array can also be an array. You can use this to create multidimensional arrays.

Example
local my_array = {'xyz', 1, false}    -- array
local first = my_array[1]               -- accessing first entry
local size = #my_array                   -- size=3

Dictionary Tables

Besides simple variables and arrays, you can also use dictionary tables that consist of a collection of key/value pairs. These keys and values can be heterogeneous (with different types).

You can access to a specific value by a key, either by using the array notation (variable[key]) or by using the point notation (variable.key)

You can iterate through all the entries of the dictionary (for k,v in pairs(t) do end) by using the function pairs(t).

In Lua documentation, there is no difference between arrays and the dictionary tables. They are named as table.

Example
local my_contact = {name='support',      -- define key/value pairs
             phone='unknown'}
local n = my_contact['phone']              -- access method 1
n = my_contact.phone                      -- access method 2
my_contact.phone = '0049911239910'          -- setting single value

-- listing all entries in dictionary
for n,p in pairs(my_contact) do
  output(n..":"..p)
end

-- defining a 2 dimensional table
local my_cube = {{10, 11, 12}, {10.99, 6.99, 100.00}}
local my_value = my_cube[2][3]       -- -> 100.00


-- defining "column names" for the table
local my_prices = {product_id={10, 11, 12}, price={10.99, 6.99, 100.00}}
local my_product_position = 3
local my_product_id = my_prices.price[my_product_position] -- -> 100.00
local my_price = my_prices.product_id[my_product_position] -- -> 12

Execution Blocks

Execution blocks are the elements that limit the scope of local variables. The script itself is the outermost execution block. Other blocks are defined through Control Structures or Functions declarations.

You can declare the explicit blocks through do end. They are useful to limit the scope of the local variables.

Example
-- this is the outermost block
a = 1        -- global variable visible everywhere

if var == false then
  -- the if construct declares a new block
  local b = 2    -- b is only visible inside the if block 
  c = 3            -- global visible variable
end

-- explicit declared block 
do
  local d = 4; -- not visible outside this block
end

Control Structures

The following control structures are supported.

Element Syntax
if
if <condition> then <block>
 [elseif <condition> then <block>]
 [else <block>] 
end
while
while <condition> do
 <block> 
end
repeat
repeat
 <block>
until <condition>
for
for <var>=<start>,<end>[,<step>] do
 <block> 
end
for <var> in <expr> do
 <block> 
end

Usage Notes:

  • The condition <condition> is evaluated as false if its value is false or nil, otherwise it is evaluated as true. It means that the value 0 and an empty string is evaluated as true.
  • The control expressions <start>, <end>, and <step> of the for loop are evaluated only once, before the loop starts. They must all result in numbers. Within the loop, you may not assign a value to the loop variable <var>. If you do not specify a value for <step>, then the loop variable is incremented by 1.
  • The break statement can be used to terminate the execution of while, repeat, and for, skipping to the next statement after the loop. For syntactic reasons, the break statement can only be written as the last statement of a block. If it is necessary to break in the middle of a block, then an explicit block can be used (do break end)

Example
if var == false 
    then a = 1 
    else a = 2
end

while a <= 6 do 
    p = p*2
    a = a+1 
end

repeat
    p = p*2 
    b = b+1
until b == 6

for i=1,6 do
    if p< 0 then break end 
    p = p*2
end

Operators

Exasol supports the following operators in the scripting language.

Operator Description
+, -, *, /, %

Common arithmetic operators.

The float arithmetic is always used.

^

Power (2^3=8)

==, ~=

If the operands of the equality operator (==) are different, the condition is always evaluated as false! The inequality operator (~=) is exactly the opposite of the equality operator

<, <=, >, >=

Comparison operators

and, or, not
  • and returns the first operand, if it is nil or false, otherwise the second operand.
  • or returns the first operand, if it is not nil or false, otherwise the second one
  • Both operators use short-cut evaluation, that is, the second operand is evaluated only if required.
  • not returns true, if the operand is nil or false, otherwise it returns false.
..

Concatenation operator for strings and numerical values.

Operator precedence follows the below priority order (higher to lower):

  1. ^
  2. not, - (unary operand)
  3. *, /, %
  4. +, -
  5. ..
  6. <, >, <=, >=, ~=, ==
  7. and
  8. or

You can use parentheses to change the precedence in an expression.

Example
local x = 1+5        --> 6
x = 2^5             --> 32
x = 1==1            --> true
x = 1=='1'            --> false
x = 1~='1'            --> true
x = true and 10    --> 10
x = true and nil   --> nil
x = 10 and 20        --> 20
x = false and nil  --> false
x = nil and false  --> nil
x = true or false  --> true
x = 10 or 20        --> 10
x = false or nil   --> nil
x = nil or false   --> false
x = nil or 'a'        --> 'a'
x = not true        --> false
x = not false        --> true
x = not nil        --> true
x = not 10            --> false
x = not 0            --> false
x = 'abc'..'def'   --> 'abcdef'

Functions

You can structure scripts using the functions.

Syntax

function <name> ( [parameter-list] )
  <block> 
end

Usage Notes:

  • Simple variables are treated as per value parameters. They cannot be manipulated within the function. However, arrays and dictionary tables are treated as per reference parameters which means their entries are mutable. But if you assign a complete new object, then the original function parameter is not affected.
  • If you call a function with too many arguments, the supernumerous ones are ignored. If you call it with too few arguments, the rest of them are initialized with nil.
  • Through return, you can exit a function and return one or more return values. For syntactic reasons, the return statement can only be written as the last statement of a block, if it returns a value. If it is required to return in the middle of a block, then an explicit block can be used (do return end).
  • Functions are first-class values, they can be stored in a variable or passed in a parameter list.
Example
function min_max(a,b,c) 
    local min,max=a,b
    if a>b then min,max=b,a 
    end
    if c>max then max=
    elseif c<min then min=
    end
    return min,max
end

local lowest, highest = min_max(3,4,1)

Error Handling through pcall() and error()

Usually, a script terminates whenever any error occurs. However in some cases you may need to handle special errors and have to perform some actions. For such scenarios, you can use the following functions:

  • pcall (): It stands for protected call. You can use this to protect function call. The parameters are the function name and all parameters f the functions. For example, pcall(my_function,param1,param2) instead of my_function(param1,param2). The function pcall () returns following two values:
    • Success of the execution: false if any error occurred.
    • Result: the actual result if no error occurred, otherwise the exception text.
  • error (): Throws an error which terminates a function or the script.
Example
-- define a function which can throw an error 
function divide(v1, v2)
    if v2==0 then
        error()
    else
        return v1/v2
    end
end

-- this command will not abort the script 
local success, result=pcall(divide, 1, 0
if not success then
    result = 0
end