General Script Language

Lexical Conventions

Unlike the SQL language, the script language is case-sensitive, that means upper and lowercase has to be considered (for example, variable definitions). There is a constraint that variable and function identifiers should contain only ASCII characters. 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 indicates 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.

Decimal type

The type decimal is not a standard Lua type but a user-defined Exasol type (userdata), similar to the special value NULL. The decimal type supports the following operators and methods for mathematical calculations and conversions.

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

Usage Notes

Lua supports a maximum of 200 local variables.

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 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 explicitly declare the 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

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

Control Structures

The following control structures are supported.

if <condition> then <block>
 [elseif <condition> then <block>]
 [else <block>] 
end

while <condition> do
 <block> 
end

repeat
 <block>
until <condition>

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

-- print all keys of table 't'
for k in pairs(t) do
    print(k)
end

Operators

Exasol supports the following operators in the scripting language.

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

1. ^
2. not, - (negation)
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. If you assign a completely 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 when 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=c 
    elseif c<min then min=c 
    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 of 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