29. ODBC driver

MIRAJ ODBC Driver is Miraj's native ODBC driver. It speaks to miraj-server (port 7007 by default) the protocol of the instance: legacy protocol on an instance in the MIRAJ language, PostgreSQL v3 protocol on an instance in the PostgreSQL language (chapter 30), recognized at connection time (29.5). It needs no other component (no third-party client library). It targets the Core level and level 1 of the ODBC 3.8 API (functions in 29.8), in 64-bit, for applications that read Miraj through ODBC: Excel, Power BI, Access (linked tables), LibreOffice Base, pyodbc, FireDAC (Delphi), DBeaver.

The driver name, to be written as is in connection strings, is MIRAJ ODBC Driver. The server needs no configuration for it: an ordinary Miraj account is enough (chapter 15).

29.1 Installation#

Windows#

The setup wizard (2.2) copies miraj_odbc.dll next to miraj-server.exe and registers the driver with Windows, in the 64-bit view of the registry:

• the key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI\MIRAJ ODBC Driver, with the values Driver and Setup (path of the DLL), APILevel = 1, ConnectFunctions = YYY, DriverODBCVer = 03.80, SQLLevel = 1 and FileUsage = 0;
• the value MIRAJ ODBC Driver = Installed in the key ...\ODBCINST.INI\ODBC Drivers.

The driver then appears in the Drivers tab of the 64-bit ODBC Data Source Administrator (odbcad32.exe in the System32 folder). It does not exist in 32-bit: a 32-bit application (32-bit Access or Excel, old 32-bit Delphi) does not see it, and a 64-bit edition of the application is required. The Express and Enterprise editions register the same name; if both are installed, the last one installed wins.

Without the wizard (zip): unzip the folder wherever you like, then run, in a 64-bit PowerShell opened as administrator:

.\Declarer-ODBC.ps1                              # miraj_odbc.dll next to the script
.\Declarer-ODBC.ps1 -Dll C:\MIRAJ\miraj_odbc.dll

The DLL's folder must not be moved after registration (the registry keeps its path); to move it, run the script again.

macOS#

The package MIRAJ-<version>-Express-macos-<arch>.pkg (2.2, 21.14) installs /usr/local/miraj-express/lib/libmiraj_odbc.dylib, and its post-install script adds the [MIRAJ ODBC Driver] section to /Library/ODBC/odbcinst.ini (file created if needed), and also to /usr/local/etc/odbcinst.ini and /opt/homebrew/etc/odbcinst.ini if they exist (unixODBC installed by Homebrew). The other drivers in these files are not modified; a reinstallation replaces only Miraj's section.

[MIRAJ ODBC Driver]
Description=MIRAJ ODBC Driver (vers miraj-server)
Driver=/usr/local/miraj-express/lib/libmiraj_odbc.dylib
Setup=/usr/local/miraj-express/lib/libmiraj_odbc.dylib

macOS does not provide a Driver Manager: install unixODBC (brew install unixodbc) or use the one built into an application (Excel for Mac bundles iODBC, which reads the same odbcinst.ini). Check:

odbcinst -q -d                  # lists the drivers: "MIRAJ ODBC Driver" must appear

Linux#

The zip MIRAJ-<version>-Express-linux-x64.zip contains libmiraj_odbc.so and, in odbc/, two scripts. unixODBC is required (unixodbc on Debian and Ubuntu, unixODBC on Fedora and RHEL). After unzipping, for example in /opt/miraj:

sudo /opt/miraj/odbc/register.sh                 # registers the driver in odbcinst.ini
sudo /opt/miraj/odbc/register.sh --lib /opt/miraj/libmiraj_odbc.so   # explicit path

The script looks for the file reported by odbcinst -j (the DRIVERS line, usually /etc/odbcinst.ini), removes any old [MIRAJ ODBC Driver] section then writes the new one, leaving the rest intact. It can be run repeatedly. --ini <file> targets another file (can be given several times), for example a test odbcinst.ini or one from a specific ODBCSYSINI. The same section can also be written by hand, or registered with odbcinst -i -d -f template.ini using a file containing the section above.

29.2 Connection string#

An ODBC string is a series of KEYWORD=value;KEYWORD=value. Keywords are case-insensitive, a repeated keyword keeps its first value, and an unknown keyword is ignored (tools add some). A value that contains ; is written in braces: PWD={a;b}; a } is written }} there. An unclosed brace, text after }, an empty keyword or a control character in a host, database or source name are refused (HY000), without the message quoting the value.

DRIVER={MIRAJ ODBC Driver};SERVER=127.0.0.1;PORT=7007;UID=root;PWD=mot de passe;DATABASE=ventes

TLS. disabled never uses TLS. preferred encrypts if the server offers it and otherwise falls back to plaintext, without verifying the certificate: it does not protect against an active attacker. required demands TLS (the connection fails before the password is sent if the server does not offer it) without verifying the certificate. verify-ca and verify-full verify the chain with SSLCA and that the server name appears in the certificate (a client library limitation: verify-ca cannot ignore the name, so it is as strict as verify-full). Without SSLCA, only public authorities are trusted: a self-signed certificate requires SSLCA. For an untrusted network, use verify-full with the server's authority certificate:

DRIVER={MIRAJ ODBC Driver};SERVER=miraj.exemple.fr;UID=app;PWD=...;SSLMODE=verify-full;SSLCA=C:\certs\ca.pem

A server started with --require-tls refuses SSLMODE=disabled (error 3159).

PREPONCLIENT. By default, SQLPrepare uses the server's prepared statements (binary protocol: exact types, BLOBs sent in chunks). Set PREPONCLIENT=1 if a tool prepares statements that the server does not accept in that form: the driver then escapes the values itself and sends the complete text.

COMPRESS. Useful on a slow link (large results); on a local network, there is no gain and the processor works harder. No effect on an instance in the PostgreSQL language.

Timeouts. The connection never waits forever: once the connection timeout has elapsed, it fails with HYT00. A read or write that exceeds READTIMEOUT or WRITETIMEOUT after connecting fails with HYT01; without these timeouts, a long query is not interrupted (a statement's own timeout is set with SQL_ATTR_QUERY_TIMEOUT, 29.8). A timeout cannot exceed one week. A refused or impossible connection fails with 08001, a broken link with 08S01.

SQLDriverConnect. The driver opens no dialog box: with SQL_DRIVER_PROMPT or SQL_DRIVER_COMPLETE, it tries with the string received. The string returned to the application is the complete effective string, without the password. SQLConnect (source name, account, password) goes through the same parameters.

29.3 Data sources (DSN)#

A data source (DSN) stores the connection string keywords under a name; the application then only writes DSN=name (and, if needed, UID and PWD).

Windows#

A source is stored in the registry: HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\<name> for a System source (visible to all accounts), HKEY_CURRENT_USER\SOFTWARE\ODBC\ODBC.INI\<name> for a User source, with a Driver value (path of miraj_odbc.dll) and one keyword from table 29.2 per value, and the name in the key ...\ODBC.INI\ODBC Data Sources (value MIRAJ ODBC Driver). Example for a User source, in PowerShell:

$k = "HKCU:\SOFTWARE\ODBC\ODBC.INI\MIRAJ ventes"
New-Item $k -Force | Out-Null
Set-ItemProperty $k Driver   "C:\Program Files\Miraj Express\miraj_odbc.dll"
Set-ItemProperty $k SERVER   "127.0.0.1"
Set-ItemProperty $k PORT     "7007"
Set-ItemProperty $k DATABASE "ventes"
New-Item "HKCU:\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources" -Force | Out-Null
Set-ItemProperty "HKCU:\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources" "MIRAJ ventes" "MIRAJ ODBC Driver"

The source then appears in odbcad32.exe (64-bit), under the User DSN or System DSN tab. The password should not be stored in the source: the application asks for it and passes it to the connection. The driver provides no Miraj-specific input window in odbcad32.exe (Configure button): sources are created through the registry as above.

macOS and Linux: odbc.ini#

unixODBC reads sources from ~/.odbc.ini (user) and from odbc.ini in the configuration folder (/etc/odbc.ini on Linux, /Library/ODBC/odbc.ini on macOS; odbcinst -j gives the paths). The driver reads these files itself when the string contains DSN=, in this order: $ODBCINI, ~/.odbc.ini, $ODBCSYSINI/odbc.ini, /etc/odbc.ini, /usr/local/etc/odbc.ini, /opt/homebrew/etc/odbc.ini, ~/Library/ODBC/odbc.ini, /Library/ODBC/odbc.ini; the first file that contains the section wins (name case ignored, ; and # comments allowed). Example:

[MIRAJ ventes]
Driver     = MIRAJ ODBC Driver
Description = Base des ventes
SERVER     = 127.0.0.1
PORT       = 7007
UID        = lecteur
DATABASE   = ventes
SSLMODE    = verify-full
SSLCA      = /etc/ssl/miraj-ca.pem

Check, without writing a program (isql ships with unixODBC):

isql -v "MIRAJ ventes" lecteur 'mot de passe'

A password written in odbc.ini (PWD = ...) is stored there in plaintext: reserve this for trusted workstations and protect the file (chmod 600 ~/.odbc.ini).

29.4 Examples#

Python (pyodbc)#

import pyodbc

cn = pyodbc.connect(
    "DRIVER={MIRAJ ODBC Driver};SERVER=127.0.0.1;PORT=7007;"
    "UID=root;PWD=mot de passe;DATABASE=ventes")
cur = cn.cursor()
cur.execute("SELECT id, nom FROM client WHERE ville = ?", "Alger")
for ligne in cur.fetchall():
    print(ligne.id, ligne.nom)
cn.close()

Excel#

Data → Get Data → From Other Sources → From ODBC, then choose a data source (DSN) or Connection String and enter the string (DRIVER={MIRAJ ODBC Driver};SERVER=...), then the credentials, the table and Load. Excel must be 64-bit (File → Account → About Excel). On macOS, Excel goes through iODBC: the driver must be in /Library/ODBC/odbcinst.ini (done by the package), and the source in ~/Library/ODBC/odbc.ini or /Library/ODBC/odbc.ini.

Power BI Desktop#

Get Data → ODBC, choose the DSN or paste the connection string in Advanced options, then the credentials (Database). Import mode is the one to use (the driver has no DirectQuery mode of its own). An on-premises data gateway (refresh in the online service) must have the driver installed on the gateway machine, and the source as a system DSN.

Access (linked tables)#

64-bit Access only. External Data → ODBC Database → Link to the data source, Machine Data Source tab (system or user DSN), choose the tables. For a table with no declared primary key, Access asks for the columns that identify a row: choose them to be able to edit the linked table.

LibreOffice Base#

File → New → Database → Connect to an existing database → ODBC, enter the DSN name (LibreOffice relies on unixODBC on Linux and macOS, and on the Driver Manager on Windows); the user name is requested at the next step.

FireDAC, DBeaver, others#

FireDAC: DriverID=ODBC, ODBCDriver=MIRAJ ODBC Driver (or DataSource=<DSN>). DBeaver (generic ODBC driver) and any tool that accepts a DSN or an ODBC string work the same way.

29.5 Instance in the PostgreSQL language#

The same driver connects to an instance started in the PostgreSQL language (--sql-mode POSTGRESQL,..., chapter 30), on the same port, with no other setting:

DRIVER={MIRAJ ODBC Driver};SERVER=127.0.0.1;PORT=7007;UID=app;PWD=...;DATABASE=ventes

Choice of protocol. With PROTOCOL=auto (default), the driver first sends the TLS request of the PostgreSQL protocol and reads one byte: an instance in the PostgreSQL language continues on that connection; an instance in the MIRAJ language is reached by a new connection using the legacy protocol. The recognized language is remembered per host and port for the lifetime of the process: the extra connection happens only once. PROTOCOL=miraj or PROTOCOL=postgresql forces the protocol (a protocol forced wrongly fails at once, without waiting).

Connection. SCRAM-SHA-256 authentication, or clear-text password under TLS and on the loopback (16.10). SSLMODE, SSLCA, SSLCERT and SSLKEY have the same meaning as in the MIRAJ language (verify-ca verifies the chain without the host name). DATABASE names the current MIRAJ database (a schema, in the PostgreSQL language); an unknown database is refused (3D000). INITSTMT is written in the instance's language.

What changes for the application.

A third-party PostgreSQL driver (psqlODBC) also works on such an instance (chapter 30).

29.6 Limits#

• No 32-bit: the driver is shipped in 64-bit only (Windows x64, macOS Apple silicon, Linux 64-bit). 32-bit applications do not see it (29.1).
• Forward-only cursor: no scrolling or updatable cursor. SQLFetchScroll accepts only SQL_FETCH_NEXT. Any other cursor type requested by the application is replaced by the forward-only cursor, with the warning 01S02 (option value changed).
• Levels: ODBC Core and level 1 functions; no level 2 (no SQLSetPos, SQLBulkOperations, SQLBrowseConnect). Updates are done in SQL (INSERT, UPDATE, DELETE).
• A single network stream per connection: a second statement executed while a SELECT is half read causes the rest of the first result to be buffered in memory; interleaved cursors and COMMIT with an open cursor work, but a very large result left half read uses memory.
• Not supported: output parameters (SQL_PARAM_OUTPUT, {? = call}), data sent at execution time with PARAMSET_SIZE > 1, bookmarks, explicit application descriptors bound to a statement.
• Times: a negative TIME value, or one greater than 24 h, read as SQL_C_TYPE_TIME is returned without its sign; reading it as SQL_C_CHAR gives the exact value.
• Cancellation: catalog functions (SQLTables, SQLColumns…) cannot be cancelled.
• Instance in the PostgreSQL language (29.5): results in text (no binary format); no protocol lastrowid (use INSERT ... RETURNING); SQLDescribeParam does not describe the parameter types; the nullability of a computed column is reported as SQL_NULLABLE; COPY is not available through ODBC (simple query only, see 30.11).
• Catalog: the ODBC schema is always empty; the server's indexes are all of hash type; AUTO_INCREMENT columns are not reported by SQLColumns.
• Authentication: legacy protocol, native password and SHA-2 (not ed25519); PostgreSQL protocol, SCRAM-SHA-256 and clear-text password (29.5).
• TLS: no password-protected client private key.
• Configuration window: no Miraj dialog box in odbcad32.exe (29.3).
• Dependencies: cancelling a query (SQLCancel) opens a second connection and sends KILL QUERY: the account must have the right to kill its own queries (instance in the PostgreSQL language: protocol cancel request, no particular right).
• Transactions: AUTOCOMMIT and isolation levels are those of Miraj (chapter 14).
• The server's limits also apply: unsupported types, unrecognized statements.

29.7 Uninstallation#

Windows#

Uninstall Miraj from Settings → Apps (2.2): the wizard removes miraj_odbc.dll and the driver registration (the MIRAJ ODBC Driver key and the ODBC Drivers value). Data sources created by the user remain in the registry (they no longer work); delete them in odbcad32.exe. After a zip installation, remove the registration before deleting the folder:

.\Retirer-ODBC.ps1

macOS#

Uninstalling a .pkg is not handled by macOS: remove the driver by hand, in a terminal.

sudo /usr/local/miraj-express/bin/miraj-odbc-unregister.sh    # removes the section from the odbcinst.ini files
sudo rm /usr/local/miraj-express/lib/libmiraj_odbc.dylib

The script only touches the [MIRAJ ODBC Driver] section; other drivers and data sources (odbc.ini) remain. The rest of Miraj is uninstalled as described in 21.14.

Linux#

sudo /opt/miraj/odbc/unregister.sh               # removes the section from odbcinst.ini

then delete the unzipped folder.

29.8 Reference: functions, attributes and information#

ODBC functions#

The driver exports each function in two variants when it takes text: without a suffix (UTF-8, for input and output) and W (UTF-16). SQLGetFunctions reports exactly the functions below.

Execution. SQLExecDirect without parameters sends the text as is; with parameters, or through SQLPrepare / SQLExecute, the statement is prepared by the server (typed values); a statement that the server cannot prepare automatically goes through the substitution of the values into the text, as with PREPONCLIENT=1. Parameter arrays (SQL_ATTR_PARAMSET_SIZE, column-wise or row-wise binding, status vector, SQL_ATTR_PARAMS_PROCESSED_PTR) and data sent at execution time (SQLParamData / SQLPutData) are accepted. A SELECT is read row by row as SQLFetch proceeds; a text of several statements or a CALL returns one result per statement (SQLMoreResults). Block reading (SQL_ATTR_ROW_ARRAY_SIZE, SQL_ATTR_ROWS_FETCHED_PTR, SQL_ATTR_ROW_STATUS_PTR, column-wise or row-wise binding, SQL_ATTR_ROW_BIND_OFFSET_PTR) and SQLGetData on any column, in any order, including bound columns, are available.

Catalog. The catalog functions read the server's information_schema. The ODBC catalog is the MIRAJ database (TABLE_CAT); with no catalog given, the current database is the default, and all the databases when there is no current database. With SQL_ATTR_METADATA_ID off (default), table, column and procedure names are patterns (%, _, escape \); when on, they are identifiers. SQLPrimaryKeys returns the key under the name PRIMARY.

Escape sequences#

Literals and comments are never modified. SQL_ATTR_NOSCAN set to SQL_NOSCAN_ON disables the translation.

Data types#

Texts are reported as Unicode (the server sends UTF-8); their length is counted in characters. Under ODBC 2 (SQL_ATTR_ODBC_VERSION), dates are SQL_DATE, SQL_TIME and SQL_TIMESTAMP. A binary value read as SQL_C_CHAR gives its hexadecimal representation; SQL_C_NUMERIC takes the precision and scale of the application descriptor when they are set there, otherwise those of the column.

Attributes#

SQLEndTran sends COMMIT or ROLLBACK to the connection, or to all those of an environment. Attributes set before connecting are applied as soon as it is established. An invalid value returns HY024.

Information (SQLGetInfo)#

Main values on an instance in the MIRAJ language (those of an instance in the PostgreSQL language are in 29.5):

Diagnostics#

SQLGetDiagRec and SQLGetDiagField return the SQLSTATE, the native code and the message. A server error keeps its SQLSTATE and its MIRAJ code (42S02 and 1146 for an unknown table) and its message is prefixed [MIRAJ][ODBC Driver][MIRAJ Server]; a driver error is prefixed [MIRAJ][ODBC Driver] (native code 0). SQLSTATEs specific to the driver: