C
The SQL interface in LeanXcale is exposed to the C language through the ODBC driver, so you must install it first with the following before configuring.
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
The
unixodbc-dev
package is used by some extensions that need to be compiled before being installed. -
Download and unpack the ODBC Connector from the Drivers page.
-
Make sure that all required dependencies are met:
ldd libodbclx.so
For Ubuntu 20.04 the required libprotobuf_c can be installed if missing in the following way:
wget http://archive.ubuntu.com/ubuntu/pool/universe/p/protobuf-c/libprotobuf-c-dev_1.3.3-1_amd64.deb apt install -y ./libprotobuf-c-dev_1.3.3-1_amd64.deb
-
Copy the file
libodbclx.so
into the/opt/leanxcale/odbc/client
directory:mkdir -p /opt/leanxcale/odbc/client cp libodbclx.so /opt/leanxcale/odbc/client
Make sure that the PHP user has read and execute permissions for
libodbclx.so
.For example:
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:[LeanXcaleTests] 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 = dbtest UserName = user1 Password = pass4user1 Encoding = WCHAR_T
By default, ODBC driver managers are configured to use hidden versions of the If you store these configuration files elsewhere, then you must set the following environment variables:
The driver manager then must be able to locate these files. |
2. ODBC Windows Installation
2.1. Installing the ODBC Connector
-
Download the ODBC Connector for Windows from the Drivers page
-
Run the installer
lx-odbc-setup-1.6.6.exe
-
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.
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"
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 |
---|---|---|---|
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 |
|
Not yet supported |
Host |
LeanXcale server DNS or IP |
||
Port |
LeanXcale server access port |
||
User |
Login to database |
||
Password |
Database password |
||
Trace |
Error reporting level |
|
|
TraceFile |
Location of error log file |
|
|
Encoding |
|
||
ANSI Encoding |
|
The Windows ODBC driver is an ANSI driver that also supports Unicode data. |
3. Quick Start
This section includes a set of code snippets to connect, read, insert, and update information through the LeanXcale ODBC Driver. For more information about ODBC functions, please refer to the ODBC doc.
3.1. Connect and start a session
Connecting to the SQL interface requires some basic information:
-
If you use LeanXcale as a Cloud Service, then you can find the IP or hostname for your instance in the LeanXcale Cloud Console.
-
If you use LeanXcale on-premise, then provide the information from your installation.
In our examples, we include these parameters:
-
IP / hostname:
123.45.67.89
-
Port:
1529
-
DB name:
dbtest
-
Username:
user1
-
Password:
pass4user1
Connecting to LeanXcale requires the creation of an environment handle. From this environment, you create as many connections as you need. Several connection attributes must be set before connecting.
SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (void *)SQL_OV_ODBC3, 0);
SQLAllocHandle(SQL_HANDLE_DBC, env, &conn);
/* Disable autocommit */
SQLSetConnectAttr(conn, SQL_ATTR_AUTOCOMMIT, (SQLPOINTER)SQL_AUTOCOMMIT_OFF,
0);
rc = SQLDriverConnect(
conn, NULL,
"DSN=myodbc_test_dsn;USERNAME=user1;PASSWORD=pass4user1;"
"TRACE=debug;Host=123.45.67.89;"
"Database=dbtest;Port=1529;ENCODING=WCHAR_T;",
SQL_NTS, str, sizeof(str), &strl, SQL_DRIVER_COMPLETE);
CHECK_CONN_RESULT(rc, "SQLDriverConnect failed", conn);
In the next examples, the connection details are not included but are still needed for any application session. You create statements from the connection handle to interact with the data, and these queries should be supported by Leanxcale’s SQL dialect.
3.2. Create a table with indexes
To create a new table in LeanXcale, run the following code:
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
CHECK_CONN_RESULT(rc, "Failed to allocate stmt handle", conn);
rc = SQLExecDirect(hstmt,
"CREATE TABLE person (personId BIGINT AS IDENTITY START "
"WITH 1 INCREMENT BY 1"
", dni VARCHAR, name VARCHAR, lastName BINARY, address "
"VARCHAR, phone VARCHAR, email VARCHAR"
", comment VARCHAR, birthday date, weight float, height "
"float, primary key(personId))",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed while creating table", hstmt);
// Non unique index
rc = SQLExecDirect(hstmt, "CREATE INDEX personixbd ON person(birthday)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed while creating index", hstmt);
rc = SQLExecDirect(hstmt, "CREATE UNIQUE INDEX personixdni ON person(dni)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed while creating index", hstmt);
3.3. Insert, update and delete
3.3.1. Inserting multiple Records using a prepare statement
rc = SQLPrepare(hstmt, (SQLCHAR *) "INSERT INTO person VALUES (?,?,?"
",'UNKNOWN',?,?,'no comments',?,?,?)", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);
char dni[50];
wchar_t name[50];
SQLLEN namesz;
wchar_t lastName[50];
SQLLEN lastnamesz;
char phone[50];
char email[50];
long birthday;
float weight;
float height;
long refdate = 946684800;
rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR, /* value type */
SQL_CHAR, /* param type */
50, /* column size */
0, /* dec digits */
dni, /* param value ptr */
50, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_WCHAR, /* value type */
SQL_CHAR, /* param type */
50, /* column size */
0, /* dec digits */
name, /* param value ptr */
sizeof(name), /* buffer len */
&namesz /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_WCHAR, /* value type */
SQL_BINARY, /* param type */
50, /* column size */
0, /* dec digits */
lastName, /* param value ptr */
sizeof(lastName), /* buffer len */
&lastnamesz /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 4, SQL_PARAM_INPUT, SQL_C_CHAR, /* value type */
SQL_CHAR, /* param type */
50, /* column size */
0, /* dec digits */
phone, /* param value ptr */
50, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 5, SQL_PARAM_INPUT, SQL_C_CHAR, /* value type */
SQL_CHAR, /* param type */
50, /* column size */
0, /* dec digits */
email, /* param value ptr */
50, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 6, SQL_PARAM_INPUT, SQL_C_LONG, /* value type */
SQL_DATE, /* param type */
0, /* column size */
0, /* dec digits */
&birthday, /* param value ptr */
0, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 7, SQL_PARAM_INPUT, SQL_C_FLOAT, /* value type */
SQL_FLOAT, /* param type */
0, /* column size */
0, /* dec digits */
&weight, /* param value ptr */
0, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
rc = SQLBindParameter(hstmt, 8, SQL_PARAM_INPUT, SQL_C_FLOAT, /* value type */
SQL_FLOAT, /* param type */
0, /* column size */
0, /* dec digits */
&height, /* param value ptr */
0, /* buffer len */
0 /* StrLen_or_IndPtr */);
CHECK_STMT_RESULT(rc, "SQLBindParameter failed", hstmt);
for (size_t inx = 0; inx < 10000; inx++) {
snprintf(dni, 49, "%zd", inx);
swprintf(name, 49, L"%zd", inx % 1000);
namesz = (wcslen(name) + 1) * sizeof name[0];
swprintf(lastName, 49, L"%zd", inx % 3000);
lastnamesz = (wcslen(lastName) + 1) * sizeof lastName[0];
snprintf(phone, 49, "%zd-%zd", inx, inx % 1000);
snprintf(email, 49, "%zd@lx.com", inx % 1000);
birthday = refdate + ((inx % 3000) * 86400);
weight = (float)refdate / (float)(1 + (inx % 30000));
height = (float)refdate / (float)(1 + (inx % 90000));
rc = SQLExecute(hstmt);
CHECK_STMT_RESULT(rc, "SQLExecute failed", hstmt);
}
rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
CHECK_CONN_RESULT(rc, "Failed to commit", conn);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
CHECK_STMT_RESULT(rc, "SQLFreeHandle failed", hstmt);
3.3.2. Inserting using a Sequence
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
CHECK_CONN_RESULT(rc, "Failed to allocate stmt handle", conn);
rc = SQLExecDirect(
hstmt, "CREATE SEQUENCE seq START WITH 10001 increment by 1", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLExecDirect(
hstmt,
"INSERT INTO person(personId, dni, name, lastName, address, phone, "
"email, comment, birthday, weight, height) VALUES (NEXT VALUE FOR seq, "
"'123456789A','John',X'3537','Mulholland "
"Drive','0034698765432','johndoe@lx.com','secret',DATE "
"'1963-03-14','70','179')",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
3.3.3. Updating a Record by the key
rc = SQLExecDirect(
hstmt, "UPDATE person SET comment = NULL WHERE dni = '123456789A'", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
3.3.4. Deleting a Record by the key
rc = SQLExecDirect(hstmt, "DELETE FROM person WHERE dni = '123456789A'", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
3.3.5. Deleting a Record with a Filter
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
rc = SQLExecDirect(hstmt, "DELETE FROM person WHERE dni = '1000'", SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
CHECK_CONN_RESULT(rc, "Failed to commit", conn);
rc = SQLExecDirect(hstmt,
"DELETE FROM person WHERE (weight/power(height,2)) > 26.0",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLEndTran(SQL_HANDLE_DBC, conn, SQL_COMMIT);
CHECK_CONN_RESULT(rc, "Failed to commit", conn);
3.4. Read and scan Data
3.4.1. Selecting with a Group By clause
rc = SQLExecDirect(hstmt,
"SELECT name, count(*) as rep FROM person"
" WHERE name = '123' GROUP BY name ORDER BY rep",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLNumResultCols(hstmt, &numcols);
CHECK_STMT_RESULT(rc, "SQLNumResultCols failed", hstmt);
ASSERTEQUAL(numcols, 2);
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
rc = SQLGetData(hstmt, 1, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Basic scan name: %s\n", data);
ASSERTSTREQUAL(data, "123");
long ldata = 0;
rc = SQLGetData(hstmt, 2, SQL_C_LONG, &ldata, sizeof(ldata), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Basic scan count: %ld\n", ldata);
ASSERTEQUAL(ldata, 10);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
3.4.2. Selecting by joining two tables
rc = SQLExecDirect(
hstmt,
"SELECT a.dni, b.dni, a.birthday, a.lastname FROM person a JOIN "
"person b ON a.birthday = b.birthday WHERE a.personid <> "
"b.personid AND a.dni = '123'",
SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
rc = SQLGetData(hstmt, 1, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan dni: %s\n", data);
ASSERTSTREQUAL(data, "123");
rc = SQLGetData(hstmt, 2, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan dni: %s\n", data);
ASSERTSTREQUAL(data, "3123");
SQL_DATE_STRUCT cdate;
rc = SQLGetData(hstmt, 3, SQL_C_DATE, &cdate, sizeof(cdate), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan birthday: %d-%d-%d\n", cdate.year, cdate.month, cdate.day);
ASSERTEQUAL(cdate.day, 3);
ASSERTEQUAL(cdate.month, 5);
ASSERTEQUAL(cdate.year, 2000);
wchar_t wstr[50];
rc = SQLGetData(hstmt, 4, SQL_C_WCHAR, &wstr, sizeof(wstr), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan last name: %ls\n", wstr);
ASSERTWSTREQUAL(wstr, L"123");
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
rc = SQLGetData(hstmt, 2, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan dni: %s\n", data);
ASSERTSTREQUAL(data, "6123");
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch failed", hstmt);
rc = SQLGetData(hstmt, 2, SQL_C_CHAR, data, sizeof(data), 0);
CHECK_STMT_RESULT(rc, "SQLGetData failed", hstmt);
printf("Join scan dni: %s\n", data);
ASSERTSTREQUAL(data, "9123");
rc = SQLFetch(hstmt);
ASSERTEQUAL(rc, SQL_NO_DATA);
rc = SQLFreeStmt(hstmt, SQL_CLOSE);
CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);
Ths driver implements the ODBC 3.0 behavior, so Unicode’s ODBC functions are not supported. |