The esi-odbc library provides simplified RDBMS transactions. It works with ODBC data sources. Please note

  • for database connections to work, the ODBC data source must be created in the bit-model (32bit or 64 bit) that matches the inmation Connector service installation on the particular machine

  • esi-odbc internally uses the open source luasql.odbc libary. As such, it has a few shortcomings inherited from the base library. Certain datatypes provided for example by MS SQL Server are not supported. See Known Issues.


version date description



TFS code synchronisation



Fix links and section titles in the docs



Bug fixes (fields other than string and number would not be loaded into the table)



First inmation release



Extensions to manage connection state for named connections



Some cleanup



Initial release


library version inmation core library







Known Issues

esi-odbc uses the luasql.odbc library that does not support mapping of all possible SQL data types to data types available in Lua. Queries can return no data if the records returned by the ODBC driver contain such unsupported data types. This includes bigint and uniqueidentifier. In case tables or views contain such fields, a call like

SELECT * FROM [dbo].[TableName]

will not work.

To work around this issue, create the SQL command in a way that it casts/converts the column in question to a supported data type instead, like

SELECT SomeColumn1,
       CAST(UniqueIDColumn AS varchar(64)) AS [UniqueIdString],
FROM [dbo].[TableName]

Available functions

All functions have to be called according to the ESI standard, using colons, e.g. ODBC:QUERYDATA("test")



QUERYDATA(conn, sql, json, cb)

Allows to fetch data from a table or view in the RDBMS into a Lua table with autocreation (and autoclose option) of the odbc connection. The result can be returned as a Lua table (default) or as a JSON document.

When the function is called for the first time for a particular ODBC connection, the connection parameters have to be supplied to that extend required to establish the connection.

For ODBC data sources, three different scenarios exist:

Scenario Mandatory Parameters

The ODBC data source has been configured to supply the credentials.


The ODBC data source has been configured to authenticate the Connector process using Windows Authentication (It might be required to change the Run as account of the Connector service in Windows Service Control Manager (SCM).


The ODBC data source has been configured to expect the credentials from the caller when opening a connection.



conn (string or table, required)

The first call in a particular Lua chunk in inmation must use the table form and provide the following parameters:

Field Data Type Meaning



a name to reference the ODBC connection for later calls. If no name is supplied by the caller, the library assumes that the connection shall be auto-closed after the call. The name-less call makes sense in use case scenarios where calls are executed in a slow fashion (e.g. in minute cycles). For faster data pulls the overhead of recreating database connections must be considered and the named version should be used to ensure the library keeps the connection open.



a valid DSN in the same bit-model as the executing Connector service.



The user name which shall be used in case authentication is required. In case the authentication is handled by the ODBC configuration or the impersonated user of the Connector service, this parameter can be ommitted.



The password which shall be used in case authentication is required. In case the authentication is handled by the ODBC configuration or the impersonated user of the Connector service, this parameter can be ommitted.



This flag must be set to true, in case the ODBC server returns strings in UTF-8 format. As a default, the library assumes ASCII-strings to be transferred and manages the conversion to and from UTF-8 which is the inmation system standard.



A codepage number to be used for string translation. Possible settings can be found here. In case this parameter is not specified, codepage 0 is used, which defaults to the standard codepage of the Connector service environment.



The maximum number of records to deliver back as table or JSON document. The default value is 100,000 records. In case the query results in more records only MAXRECORDS is delivered by the function. In case a certain use case requires to process larger rowsets, consider to use the QUERYDATA function with a user-defined callback function, which results in no data stored in memory, but user code to be executed on each row.

sql (string, mandatory)

Any valid SQL statement which returns rows.

json (boolean, default false)

If false, the data is returned as a Lua table. If true, the data is returned as a JSON document.

cb (function, default nil)

When a callback function is specified, the function will be invoked on each row returned by the RDBMS server. In this mode, the MAXRECORDS parameter is ignored. The callback function provided must have the following signature:

function callback(row)


data (table or string or nil)

Returned data. If the json input parameter is set to false, the output is a Lua table, otherwise a json string. If cb is specified, or no cursor is returned, the output is nil .

err (string or nil)

The last occurred error. nil if no errors occurred during the call.

NOTE: Under curtain conditions, the data table may be returned incomplete due to the internal ODBC driver exceptions. It is always recommended to check the call for possible errors before proceeding with data processing.


The table or JSON document returned by the function will hold a maximum of rows, which is depending on the internal MAXROWS setting. This setting has a default of 300,000 rows. Be careful, not to fetch enormous data volumes from the external server by accident, because the return will occupy memory in the inmation Connector memory space. This can be easily the source of problems, especially with Connectors running on very limited hardware, 32-bit OS versions or little available RAM in general.

You may use inmation Performance Counters in the service component executing the queries, to watch the impact on memory consumption of your queries.


The following code snippet demonstrates the usage invoking a callback function:

local DB = require("esi-odbc")
local STR = require("esi-string")

local counter = 0
local sample = nil

local function mycallback(data)
    if data then
        counter = counter + 1
        if counter == 1 then
            sample = data

local function main()
    local sql = "SELECT TOP 1000 * FROM [dbo].[DemoData]"
    local data, err = DB:QUERYDATA({ DSN = 'DemoData', USER = 'sa', PWD = '*********', UTF8 = false }, sql, true, mycallback)
    if err then error("An error occured during the call: " .. err)
    return "Callback was called " .. counter .. " times, sample = " .. STR:STRING(sample)

return main

Result in inmation:


In this case, all returned rowsets are handed over to the callback function, and a maximum of one rowset is kept in memory.

The next example shows the usage to return a Lua table:

local DB = require("esi-odbc")

local sql = "SELECT TOP 1000 * FROM [dbo].[DemoData]"
local rowset = DB:QUERYDATA({ DSN = 'DemoData', USER = 'sa', PWD = '********', UTF8 = false }, sql)
return "Rowset contains " .. #rowset .. " rows, pressure in last row: " .. rowset[#rowset].SepAPressure

return main