C

Clang

The SQL interface in LeanXcale is exposed to the C language through the ODBC driver, so you’ll need to install it first:

1. ODBC Linux Installation

The next steps shows how to install the LeanXcale ODBC Driver for Linux. This procedure has been tested in Ubuntu 20.04 LTS, but it should work on other distributions too.

  1. Install prerequisite packages:

    sudo apt-get install unixodbc unixodbc-dev

    unixodbc-dev package is used by some extensions that needs to be compiled to be installed.

  2. Download the ODBC Connector from the Drivers page and unpack it.

  3. Copy the file libodbclx.so into /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

  4. Create the file $HOME/.odbcinst.ini with this content:

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

  5. Create the file $HOME/.odbc.ini with this content:

    [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 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, you must set these 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 will then be able to locate the files.

2. ODBC Windows Installation

2.1. Installing the ODBC Connector

  1. Download the ODBC Connector for Windows from the Drivers page

  2. Run the installer lx-odbc-setup-1.5.4.exe

  3. The default installation directory is C:\Program Files\Leanxcale\lx-odbc-driver

  4. If asked to install Visual C++ redistributable, follow the setup guide; just click Next as needed.

2.2. Registry Keys

The setup adds the relevant keys to the registry:

Registry Editor 
[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

When the driver has been installed, you can add the data source from the ODBC Data Source Administrator.

The datasource can be added from the User DSN tab:

  1. Click Add

    ODBC DSA

  2. Select LX DRIVER(x64) from the driver list

    ODBC DSA

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

ODBC DSA
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

disable

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

no

TraceFile

Location of error log file

%temp%/lxodbc

Encoding

unicode

ANSI Encoding

auto-search

The Windows ODBC driver is an ANSI driver, but it supports unicode data.

3. Quick Start

In this section you will find a set of code snippets to connect, read, insert and update information through the LeanXcale ODBC Driver. For more information about ODBC functions, please check ODBC doc.

3.1. Connecting and starting a session

Connecting to the SQL interface of requires some basic information:

  • If you are using LeanXcale as a Cloud Service, you can find the IP or hostname for your instance in the LeanXcale Cloud Console.

  • If you’re using LeanXcale on-premise, you can use the information from your installation.

In our examples we will use these parameters:

  • IP / hostname: 123.45.67.89

  • Port: 1529

  • DB name: dbtest

  • Username: user1

  • Password: pass4user1

Connecting to LeanXcale requires first to create an environment handle. From that environment, you can create as many connections as you need. Note that some connections attributes need to 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, connection is not included, but this is needed for any app’s session. Note that you will need to create statements from the connection handle to interact with the data, and the queries should be supported by Leanxcale’s SQL dialect.

3.2. Creating a table and its indexes

To create a new table in LeanXcale, you can use 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. Inserting, updating and deleting

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. Insert 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 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 Key

  rc = SQLExecDirect(hstmt, "DELETE FROM person WHERE dni = '123456789A'", SQL_NTS);
  CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);

3.3.5. Delete 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. Reading and scanning Data

3.4.1. Select with 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. Select 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.