esi-odbc

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.

Changes

version date description

1.0.11

2022-05-13

Fixed error returned order in EXECUTE function

1.0.10

2020-12-08

TFS code synchronisation

1.0.9

2020-10-03

Fix links and section titles in the docs

1.0.4

2018-06-19

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

1.0.0

2018-06-11

First inmation release

0.1.3

2018-06-03

Extensions to manage connection state for named connections

0.1.2

2018-05-27

Some cleanup

0.1.1

2018-05-24

Initial release

Dependencies

library version inmation core library

luasql.odbc

2.3.4

yes

dkjson

2.5

yes

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,
       SomeColumn2,
       CAST(UniqueIDColumn AS varchar(64)) AS [UniqueIdString],
       SomeColumn3
FROM [dbo].[TableName]

Available functions

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

QUERYDATA

EXECUTE

CONNECT

CONNECTED

CLOSE

GETSTATISTICS

Documentation

QUERYDATA

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.

DSN

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

DSN

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

DSN, USER, PWD

Parameters

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

NAME

string

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.

DSN

string

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

USER

string

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.

PWD

string

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.

UTF8

boolean

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.

CP

number

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.

MAXRECORDS

number

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

Returns

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.

Limits

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.

Usage

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

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

return main

Result in inmation:

image

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

image

EXECUTE

EXECUTE(conn, sql, json, cb)

Executes a single or multiple SQL commands. If the commands return data then the result can be returned as a Lua table (default) or as a JSON document, or transferred to an optional callback.

Parameters

conn (string or table, required)

Fields are the same as for conn parameter of QUERYDATA function

sql (string or table, mandatory)

Any valid SQL statement or an array of valid SQL statements.

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

Returns

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.

CONNECT

CONNECT(conn)

Allows to connect to an ODBC data source without querying data. Note: If no NAME is provided, the connection will be immediately closed.

Parameters

conn (string or table, required)

Fields are the same as for conn parameter of QUERYDATA function

Returns

result (boolean)

Returned result. True if the connection could be established, false otherwise.

err (string or nil)

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

CONNECTED

CONNECTED(name)

Retrieves the connection status for a named connection.

Parameters

name (string required)

String reference to the specific ODBC connection.

Returns

result (boolean)

Returned result. True if the connection is established, false otherwise.

CLOSE

CLOSE(name)

Closes a named connection.

Parameters

name (string required)

String reference to the specific ODBC connection.

GETSTATISTICS

GETSTATISTICS(recent, json)

Parameters

recent (boolean required)

If recent flag is true, returns recent statistics, otherwise returns all statictics

json (boolean)

If json flag is true, returns result that will be encoded in json format.

Returns

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 .