ODBC Connector

The LeanXcale ODBC driver is available for Windows and Unix-class systems, including Linux.

1. Installation

1.1. ODBC Linux Installation

The next steps show how to install the LeanXcale ODBC Driver for Linux. This procedure was tested on Ubuntu 20.04 LTS and is expected to run on other distributions.

Install the prerequisite packages:
```
sudo apt-get install unixodbc unixodbc-dev libprotobuf-c-dev
```
The unixodbc-dev package is used by some extensions that need to be compiled before being installed.

If you don’t have the Universe repository enabled in your Ubuntu installation, you can install the required libprotobuf-c-dev package by hand in the following way:
```
wget http://archive.ubuntu.com/ubuntu/pool/universe/p/protobuf-c/libprotobuf-c-dev_1.3.3-1_amd64.deb
sudo dpkg -i libprotobuf-c-dev_1.3.3-1_amd64.deb
```
Download and unpack the ODBC Connector package for your operative system from the Drivers page.
Make sure that all required dependencies are met:
```
ldd libodbclx.so
```

Copy driver files into the /opt/leanxcale/odbc/client directory:

sudo mkdir -p /opt/leanxcale/odbc/client
sudo cp libodbclx.so /opt/leanxcale/odbc/client
sudo cp libodbclxS.so /opt/leanxcale/odbc/client
sudo cp pdo_lxodbc.so.7.4 /opt/leanxcale/odbc/client

Make sure that the PHP user has read and execute permissions for libodbclx.so.

For example:

sudo chmod +rx www-data /opt/leanxcale/odbc/client/libodbclx.so

Create the file $HOME/.odbcinst.ini with the following details:

[LeanXcaleODBCDriver]
Description = LeanXcale ODBC Driver for Linux
Driver = /opt/leanxcale/odbc/client/libodbclx.so
UsageCount = 1

Create the file $HOME/.odbc.ini with these details:

[LeanXcale]
Driver = LeanXcaleODBCDriver
Description = Sample Database to be used for tests
Trace = No
Server = 123.45.67.89       # IP / hostname of your LeanXcale instance
Port = 1529
Database = db
UserName = APP
Password = APP
Encoding = WCHAR_T

If the LeanXcale Database you are connecting to is configured with Security, you must add the secure entry to the $HOME/.odbc.ini file.

[LeanXcale]
Driver = LeanXcaleODBCDriver
Description = Sample Database to be used for tests
Trace = No
Server = 123.45.67.89       # IP / hostname of your LeanXcale instance
Port = 1529
Database = db
UserName = APP
Password = APP
Encoding = WCHAR_T
Secure = True

Previously, you must have dowloaded and installed your TLS certificate, please check how to do this here.

By default, ODBC driver managers are configured to use hidden versions of the odbc.ini and odbcinst.ini configuration files (named .odbc.ini and .odbcinst.ini) located in the home directory.

If you store these configuration files elsewhere, then you must set the following environment variables:

Set ODBCINI to the full path and file name of the odbc.ini file.
Set ODBCSYSINI to the full path of the directory that contains the odbcinst.ini file.

The driver manager then must be able to locate these files.

1.2. ODBC Windows Installation

1.2.1. Installing the ODBC Connector

Download the ODBC Connector for Windows from the Drivers page
Run the installer lx-odbc-installer-1.9.9.msi
The default installation directory is C:\Program Files\Leanxcale\lx-odbc-driver
If asked to install the Visual C++ redistributable, then follow the default setup guide by simply clicking Next as needed.

Note that you would need to have Microsoft Visual C++ Redistributable package installed. Download the vcredist_x64.exe installer from an official site for Visual Studio 2019.

1.2.2. Registry Keys

The setup adds the following relevant keys to the registry:

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\ODBC Drivers]
"LX DRIVER(x64)"="Installed"

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\LX DRIVER(x64)]
"Driver"="[TARGETDIR]LXODBC.DLL"
"Setup"="[TARGETDIR]LXODBCSETUP.DLL"
"APILevel"="1"
"ConnectFunctions"="YYN"
"DriverODBCVer"="03.00"
"FileUsage"="0"
"SQLLevel"="3"

1.2.3. ODBC Data Source Administrator

After the driver is installed, add the data source with the ODBC Data Source Administrator from the User DSN tab:.

Click Add
Select LX DRIVER(x64) from the driver list

With the correct driver selected, you can now configure the Data Source Name properties:

Property Description Sample Values Comment

Property	Description	Sample Values	Comment
Data Source	Identifier for the datasource		ODBC logical name
Description	User friendly description of the database
Database name	Identifier for the database
SSL mode	SSL mode to use for connection	`True/False`	True to connect in secure mode
Host	LeanXcale server DNS or IP
Port	LeanXcale server access port
User	Login to database
Password	Database password
Trace	Error reporting level	`no`
TraceFile	Location of error log file	`%temp%/lxodbc`
Encoding		`unicode`
ANSI Encoding		`auto-search`

Data Source

Identifier for the datasource

ODBC logical name

Description

User friendly description of the database

Database name

Identifier for the database

SSL mode

SSL mode to use for connection

True/False

True to connect in secure mode

Host

LeanXcale server DNS or IP

Port

LeanXcale server access port

User

Password

Database password

Trace

Error reporting level

no

TraceFile

Location of error log file

%temp%/lxodbc

Encoding

unicode

ANSI Encoding

auto-search

When using connecting to a LeanXcale instance with Security, the ODBC Driver Setup, SSL Mode must be set to True.

The Windows ODBC driver is an ANSI driver that also supports Unicode data.

1.3. macOS Installation

The next steps show how to install the LeanXcale ODBC Driver for macOS.

Install the prerequisite packages. You can use Homebrew package manager tha can be installed executing the following command:
```
/bin/bash -c "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/HEAD/install.sh)"
```

Install the following dependencies:

brew install --cask odbc-manager
brew install python
brew install libtool
brew install pbc
brew install protobuf
brew install protobuf-c
brew install libiconv
brew install automake
brew install pkg-config
brew install ossp-uuid

Install pyodbc module using Python’s PIP tool (It comes by default with the Python installation from the previous step).
```
python3 -m pip install pyodbc --user
```

Then you need to find the ODBC configuration files, you can do this executing:

ec2-user@ip-172-31-25-164 odbc % odbcinst -j

unixODBC 2.3.11
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/ec2-user/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

In /usr/local/etc/odbc.ini, you need to set up your database connection details:

[lxodbcdsn]
Description     = test odbc
Driver          = Lxodbc
Trace           = INFO
TraceFile       = /tmp/sql.log
Host            = localhost
Database        = db
Port            = 1529
UserName        = APP
Password        = APP
NoNoTotal       = 65536

And check that in /usr/local/etc/odbcinst.ini the driver name matches the driver property in /usr/local/etc/odbc.ini.

[Lxodbc]
Description     = LX driver
Driver          = /Users/ec2-user/odbc/bin/libodbclx.so
Setup           = /Users/ec2-user/odbc/bin/libodbclxS.so

Modify permissions of libodbclx.so:

sudo chmod +rx /Users/ec2-user/odbc/bin/libodbclx.so

2. Examples of use

Review the following Quick Start guides for the languages used with the ODBC connector: