28. ODBC driver

MIRAJ ODBC Driver is Miraj's native ODBC driver. It speaks the MySQL protocol to miraj-server (port 7007 by default) and needs no other component: neither MariaDB Connector/ODBC nor a MySQL client library. It targets the Core level and level 1 of the ODBC 3.8 API, in 64-bit, for applications that read Miraj through ODBC: Excel, Power BI, Access (linked tables), LibreOffice Base, pyodbc, FireDAC (Delphi), DBeaver.

System	Library	Installed by
Windows 64-bit	`miraj_odbc.dll`	the setup wizard (program folder) or the zip
macOS (Apple silicon)	`/usr/local/miraj-express/lib/libmiraj_odbc.dylib`	the `.pkg` package
Linux 64-bit	`libmiraj_odbc.so`	the Linux zip

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 14).

28.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.13) 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 (protocole MySQL 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.

28.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}.

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

Keyword	Purpose	Default
`DRIVER`	driver name (`{MIRAJ ODBC Driver}`); selects the driver with the Driver Manager
`DSN`	name of a data source (28.3); keywords in the string override those of the source
`SERVER` (or `HOST`)	server name or address	`127.0.0.1`
`PORT`	TCP port	`7007`
`UID` (or `USER`)	Miraj account
`PWD` (or `PASSWORD`)	password; never written in an error message or in the string returned to the application
`DATABASE` (or `DB`)	current database at connection	none
`SSLMODE`	TLS: `disabled`, `preferred`, `required`, `verify-ca`, `verify-full`	`preferred`
`SSLCA`	certificate authority file (PEM or DER) for `verify-ca` and `verify-full`	public authorities
`SSLCERT`, `SSLKEY`	client certificate and private key, together, unencrypted key	none
`COMPRESS`	compressed protocol (`1`, `yes`, `true`, `on`)	`0`
`PREPONCLIENT`	`1`: the `?` parameters are substituted into the SQL text by the driver; `0`: statements prepared by the server	`0`
`CONNECTTIMEOUT`, `READTIMEOUT`, `WRITETIMEOUT`	timeouts, in seconds	none
`INITSTMT`	statement executed after connecting	none
`LOG`	path of a driver log (or environment variable `MIRAJ_ODBC_LOG`); no passwords or values, file mode 0600, limited to 64 MiB	disabled

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.

28.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 26.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=:

[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).

28.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()

The script tools/validation/odbc.py (40 checks) runs against this driver: python3 tools/validation/odbc.py --port 7007 --password '...' --driver "MIRAJ ODBC Driver".

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.

28.5 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 (28.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.
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: mysql_native_password and caching_sha2_password; not client_ed25519.
TLS: no password-protected client private key.
Configuration window: no Miraj dialog box in odbcad32.exe (28.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.
Transactions: AUTOCOMMIT and isolation levels are those of Miraj (chapter 13).

28.6 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.13.

Linux#

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

then delete the unzipped folder.

28. ODBC driver

28.1 Installation#

Windows#

macOS#

Linux#

28.2 Connection string#

28.3 Data sources (DSN)#

Windows#

macOS and Linux: odbc.ini#

28.4 Examples#

Python (pyodbc)#

Excel#

Power BI Desktop#

Access (linked tables)#

LibreOffice Base#

FireDAC, DBeaver, others#

28.5 Limits#

28.6 Uninstallation#

Windows#

macOS#

Linux#

macOS and Linux: `odbc.ini`#