Script Language

This article describes the script language used for scripting programming in Exasol.

Lexical conventions

• The script language used in Exasol (Lua) is case sensitive. This means that although SQL is not case sensitive, you must consider the use of uppercase and lowercase characters in variable definitions, etc. in your scripts.

• Variable and function identifiers must only contain characters in the ASCII (7-bit) character set.

• The use of a semicolon ; after a script statement is optional.

Comments

Line comment:

A line beginning with a double dash --. The remaining part of the line will be ignored.

Block comment:

One or more lines delimited by /* and */. All characters between the delimiters will be ignored.

Examples:

-- This is a single line comment

/*
This is
a multiline comment
*/

Types and values

Decimal type

The type decimal is not a standard Lua type but a user-defined Exasol type. It supports the following operators and methods for mathematical calculations and conversions:

Examples:

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 dynamically typed. 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.

We recommend that you use local variables to explicitly show the variable declaration.

Lua supports a maximum of 200 local variables.

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 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 using array notation (variable[key]) or 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 the Lua documentation there is no difference between arrays and dictionary tables, they are both called 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 blocks explicitly with do ... end. This is useful to limit the scope of 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 following priority order:

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 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

A script normally terminates when an error occurs. However, in some cases you may need to handle special errors with specific actions. For such scenarios you can use the following functions:

pcall() (protected call)

You can use this to protect a 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).

pcall() returns 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 that 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