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.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 based queries will return no data if the records returned by the RDBMS ODBC driver contain unsupported data types. This includes ìnt,bit and uniqueidentifier. In case tables or views contain such fields, a call like

SELECT * FROM [dbo].[TableName]

will not work. The worst result is that no fields are returned (In case the rowset contains uniqueidentifier columns). In order to work around this issue, you could either create a view ommitting the fields, or, in case this can not be done, query the table with distinct field names ommitting the unsupported ones.

The inmation team is currently investigating options to fix the bugs in the luasql.odbc library.

Available functions

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

Documentation

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

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 100,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 avaiable 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= DB:QUERYDATA({DSN='DemoData',USER='sa',PWD='*********',UTF8=false},sql,true,mycallback)
  return "Callback was called " .. counter .. " times, sample=" .. STR:STRING(sample)
end

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