# Lua eXtremeSQL Interface

> ## Connections

> A connection is used to access most of the database features. When a UDF is called, the database connection is already established as predefined variable `con`.

> When a script or the console is used, it’s necessary to call function `connect()` to explicitly make a connection. The argument of the `connect()` function is a table with specified keys. This function can connect to local database, a remote RSQL database, or a distributed database depending on it’s arguments.

> ### Examples:

> The following code snippet creates a local in-memory (IM) database (in process ) and makes a connection to it.

>         db = create("luadb", devices, dbparams)
>         c = connect{db=db}

> The following command will create an IM database named `testdb` with size of 100Mb and page size 512 bytes. (Parameter `pagesize` could be omitted as it has default value of 256 bytes).

>         c = connect{dbname=”testdb”, dbsize=100*1024*1024, pagesize=512}

> The following command will connect to remote xSQL server located on the same machine on port 5000.

>         c = connect{host=”localhost”, port=5000}

> The following command will create a distributed connection to 2 shards.

>         c = connect{nodes={"localhost:5000", "localhost:5001"}}

> The following additional parameters are supported for connections:

> - `nReplicas`: The number of HA replics
> - `maxConnectAttempts`: The number of times to attempt to connect
> - `connectTimeout`: The timeout for connection attempts
> - `sql_login`: Security login name
> - `sql_password`: Security password

> ## Running SQL queries

> Once a connection is established, we can perform statements and queries using that connection. A statement does not return data from the database, while a query does.

> The following is an example of statement:

>         con:statement("create table MyTable (pk int, value string)")

> This can be run from a UDF or as a script.

> To make it possible to retrieve data from the query, the function `con:query()` returns a cursor object. Cursor objects allow iteration over their content, and return data represented as rows. The rows are Lua tables in the form `key=value` where `key` is the column name and `value` is the corresponding row value.

> The following is an example of cursor iteration:

>         for row in con:query("select pk,value from MyTable"):tuples() do
>             return print row.pk, row.value
>         end

> ## Passing Parameters to an SQL Query

> It is possible to pass parameters into a query or statement. The eXtremeSQL Lua interface is a thin layer on top of the eXtremeSQL C++ SQL API, and the LuaJIT FFI intreface is used to bind and pass parameters. All parameters of a query are passed "as is", without any checking or processing. Note the following rules regarding parameters:

> - Parameters in the query are specified in the "%" form, exactly as in the C++ SQL API
> - Parameter values are passed as FFI data types
> - Passing parameters is unsafe. No extra checking is performed in eXtremeSQL layer and FFI translates parameters into low level C-compatible data and data structures. So, an error specifying parameters or parameter types will result in undefined behavior. Most likely it will cause a program (server) crash, but not necessarily. It could cause data corruption, data loss, etc.

> The following is an example of passing parameters:

>         for row in con:query("select k from t where i=%i", ffi.new("int", i)):tuples() do
>             return row.k
>         end

> The supported format specifiers and corresponding FFI expressions are listed in the table below:

> Format specifier | eXtremeDB data type | FFI expression
> --- | --- | ---
> %b | `bool` | ffi.new("bool", true)
> %i | `int` | ffi.new("int", 1)
> %i8 | `int64_t` | ffi.new("int64_t", 1000000000000)
> %u | `unsigned` | ffi.new("unsigned", 2)
> %l | `int64_t` | ffi.new("int64_t", 1000000000000)
> %L | `integer` value, passed as `double` | Native Lua type, a lua value can be passed. 123456
> %I1 | Array of 1 byte integers. `size_t,int8_t*` | 2 values expected – size and an array. Array is passed as zero-based, while lua array is 1-based, so actual size of allocated array should be size+1 ffi.new('size_t', 3), ffi.new('int8_t[4]', {1,2,3})
> %I2 | Array of 2-byte integers.`size_t,int16_t*` | Same as above. ffi.new('size_t', 3), ffi.new('int16_t[4]', {1,2,3})
> %I4 | Array of 4-byte integers.`size_t,int32_t*` | Same as above. ffi.new('size_t', 3), ffi.new('int32_t[4]', {1,2,3})
> %I8 | Array of 8-byte integers. `size_t,int64_t*` | Same as above. ffi.new('size_t', 3), ffi.new('int64_t[4]', {1,2,3})
> %U1 | Array of 1-byte unsigned integers.`size_t,uint8_t*` | ffi.new('size_t', 3), ffi.new('uint8_t[4]', {1,2,3})
> %U2 | Array of 2-byte unsigned integers. `size_t,uint16_t*` | Same as above. ffi.new('size_t', 3), ffi.new('uint16_t[4]', {1,2,3})
> %U4 | Array of 4-byte unsigned integers. `size_t,uint32_t*` | Same as above. ffi.new('size_t', 3), ffi.new('uint32_t[4]', {1,2,3})
> %U8 | Array of 8-byte unsigned integers. `size_t,uint64_t*` | Same as above. ffi.new('size_t', 3), ffi.new('uint64_t[4]', {1,2,3})
> %F4 | Array of floats. `size_t,float*` | Same as above. ffi.new('size_t', 3), ffi.new('float[4]', {1,2,3})
> %F8 | Array of doubles. `size_t,double*` | Same as above. ffi.new('size_t', 3), ffi.new('double[4]', {1,2,3})
> %A | `size_t` nElems, `size_t` elemSize, `char*` body |
> %f | `double` | ffi.new("double", 10.1) or just Lua numeric value – 10.1
> %t | `mco_datetime`. This is an internal representation of `datetime`, represented as `uint64_t` | ffi.new("uint64_t", ticks)
> %s | `char*`, but Lua string is properly converted | 'hello world'

> ## How to query a row

> Function `query_row()` returns a table with a single row as a result set.

> ### Syntax

>         row = con:query_row (sql_query, params1, param2, ... paramN, no_data_hdl, too_many_rows_hdl)

> ### Example:

> Assume that we have following table and data:

>         create table Employee(
>             name char(25) primary key,
>             dept_no unsigned(2)
>         );

>         INSERT INTO Employee(name, dept_no) VALUES (['Luke Skywalker', 'Han Solo', 'Darth Vader'], [1,1,2])

> we can write the following Lua code:

>         local row = con:query_row("select name, dept_no from Employee order by name limit 1")
>         local name, dept_no = row.name, row.dept_no

>         print ("Values returned: ", name, dept_no)

> and this code will print:

>         Values returned:    Darth Vader	2

> Note: This function is designed specifically for situations when it is needed to fetch exactly one row. So, if the query returns more than one row or no rows `query_row()` will try to call the handler passed via the `too_many_rows_hdl` or `no_data_hdl` optional parameters respectively. If the corresponding handler is not provided then `lua_error` will be called (refer to pcall() for details).

>     	local row = con:query_row(
>     	"select name, dept_no from Employee order by name limit 1",
>     	-- No data
>     	function ( )
>     	.....
>     	end,
>     	-- TOO_MANY_ROWS
>     	function ()
>     	.....
>     	end
>     	)