27. Client libraries and C API

In addition to the network server and the miraj-cli console, MIRAJ can be embedded directly in an application through its native library miraj.dll, which exposes the engine through a stable C API (53 functions, described in miraj.h). No network connection, no separate process: the engine runs inside the application's process.

The same C API is exported under the three names; the Python (ctypes), PHP and Node.js wrappers look for the name specific to the system (miraj.dll, libmiraj.dylib, libmiraj.so).

On top of this C API, idiomatic wrappers are provided for several languages, so that you do not have to write the low-level calls yourself: Delphi (unit Miraj.pas, the product's historical API), C#, C++, Python, Java, PHP, Node.js and Go. Each wrapper follows the same general organization (Server / Session / Result) and the same conventions as the original Delphi unit.

Important: the embedded engine lives in the process that opened it. It is suitable for desktop applications, command-line scripts and scheduled tasks. For a web server with several processes or worker threads (PHP-FPM, Java thread pool, etc.) that must share the same data, use the miraj-server network server with a standard SQL client rather than the embedded library.

27.1 What each wrapper exposes#

All the wrappers revolve around three objects:

• Server (TMiraj in Delphi): opens a root folder of databases, owns the root session and lets you create others.
• Session: executes SQL (execute, query, positional ? or named :nom parameters), and gives access to the last auto-increment identifier, the number of affected rows, the warnings and the language of error messages.
• Result (ResultSet): describes the columns (name, type, scale) and gives access to the cells, either by position (row, column) or by sequential traversal (next / field).

An SQL error raises an exception specific to each language (code, SQLSTATE, line/column for a syntax error), built from the last error stored by the DLL.

Parameters are written ? (positional, list of values) or :nom (named, associated names and values, names without the colon). The Python, PHP, Node.js and Go wrappers look for the library first at the location given by the program, then in the MIRAJ_DLL environment variable (file path), then through the system loader; C# looks for it under the name miraj (executable folder, system paths), Java under the same name in the jna.library.path property then the system paths; Delphi and C++ link it at link time.

The following sections give, for each language, a minimal complete example — connection, query, reading the result, closing — in the actual syntax of the wrapper. They assume the following shop database, whose example query returns two rows:

CREATE DATABASE shop;
USE shop;
CREATE TABLE articles (id INT PRIMARY KEY, nom VARCHAR(30), prix DECIMAL(8,2));
INSERT INTO articles VALUES (1, 'Stylo', 2.50), (2, 'Agenda', 14.90), (3, 'Sac', 39.00);
SELECT nom, prix FROM articles WHERE prix > 10;

+--------+-------+
| nom    | prix  |
+--------+-------+
| Agenda | 14.90 |
| Sac    | 39.00 |
+--------+-------+
2 ligne(s)

A query on a missing table (SELECT * FROM absente) fails with code 1146, SQLSTATE 42S02 and the message Table 'shop.absente' doesn't exist: this is the error shown by the error-handling examples.

27.2 C# (.NET)#

Wrapper: Miraj.cs (P/Invoke, .NET 8, namespace Miraj). Reference Miraj.cs in your project and copy miraj.dll (64-bit) next to the executable.

using Miraj;

using var server = MirajServer.Open("data");          // loads the databases in the folder
using var session = server.CreateSession();
session.Execute("USE shop");

using var rs = session.Query("SELECT nom, prix FROM articles WHERE prix > ?", 10);
while (rs.Next())
    Console.WriteLine($"{rs.Field("nom")} : {rs.Field("prix")}");

Error handling: MirajException (Code, SqlState, Line, Column); misuse (null handle, invalid UTF-8) raises the standard .NET exceptions (ArgumentException, ArgumentOutOfRangeException).

try
{
    session.Execute("SELECT * FROM absente");
}
catch (MirajException e)
{
    Console.WriteLine($"Erreur {e.Code} ({e.SqlState}) : {e.Message}");
}

MirajServer, MirajSession and MirajResultSet implement IDisposable (using). The wrapper also covers named parameters, QueryValue, ExecSql, warnings, multiple sessions and reopening an existing folder.

27.3 Python#

Wrapper: miraj.py (pure ctypes module, no dependency to install). Copy miraj.py next to your script, or add its folder to PYTHONPATH. The DLL is searched for in this order: path passed to miraj.load_library(path) (file or folder, before the first use), MIRAJ_DLL environment variable (file or folder), folder of miraj.py, then the system search.

import miraj

with miraj.Server.open("data") as server:      # loads the databases in the folder
    session = server.root_session
    session.execute("USE shop")

    with session.query("SELECT nom, prix FROM articles WHERE prix > ?", (10,)) as rs:
        for nom, prix in rs:                     # one row = one tuple
            print(nom, prix)                     # Agenda 14.90, then Sac 39.00

Parameters: a sequence for ?, a dictionary for :nom (keys without the colon). A Result can also be read with rs.row(i) (tuple), rs.row_dict(i) or rs.dicts() (dictionaries column name → value), rs.fetchall(), rs.columns, or by sequential traversal rs.next() / rs.field("nom"). Server.open(path, save_policy=…, save_interval_ms=…, language=…) loads the databases in the folder, Server.create(path) loads none; server.create_session() opens another session; session.query_value(sql) returns the first cell; session.exec_sql(script, results=list) runs a script without raising an exception (error code returned). A DECIMAL is read as a decimal.Decimal (Decimal('26.90')).

Error handling: miraj.MirajError (code, sqlstate, line, column); misuse detected by the DLL raises miraj.MirajMisuseError (also derives from ValueError) or miraj.MirajRangeError (also derives from IndexError).

try:
    session.execute("SELECT * FROM absente")
except miraj.MirajError as e:
    print(f"Erreur {e.code} ({e.sqlstate}) : {e}")

A minimal DB-API 2.0 (PEP 249) compliant layer is provided in addition to the object-oriented API: miraj.connect(root, database=...) returns a Connection with cursor(), commit() and rollback() (which run COMMIT and ROLLBACK), and the resulting Cursor supports execute(), executemany(), fetchone()/fetchmany()/fetchall() and iteration, to integrate with code already written against the standard Python driver interface (paramstyle = "qmark", :nom accepted with a dictionary). root can also be an already open Server: the connection shares it without closing it.

conn = miraj.connect("data", database="shop")
cur = conn.cursor()
cur.execute("SELECT nom FROM articles WHERE prix > ?", (10,))
for (nom,) in cur.fetchall():
    print(nom)
conn.close()

27.4 Java (JNA)#

Wrapper: package com.cirtait.miraj (JNA 5.19.1, compatible with Java 8, Maven group com.cirtait:miraj-java). MirajLibrary declares the C API as is; Server, Session and Result form the AutoCloseable object wrapper. The DLL is searched for under the name miraj via the jna.library.path system property, then in the system paths; a 64-bit JVM is required.

import com.cirtait.miraj.Server;
import com.cirtait.miraj.Session;
import com.cirtait.miraj.Result;

try (Server server = Server.open("data");          // loads the databases in the folder
     Session session = server.createSession()) {
    session.execute("USE shop");
    try (Result rs = session.query("SELECT nom, prix FROM articles WHERE prix > ?", 10)) {
        while (rs.next()) {
            System.out.println(rs.getString("nom") + " : " + rs.getBigDecimal("prix"));
        }
    }
}

Error handling: MirajException (inherited for any failure returned by the DLL, SQL code included). Parameter types are converted automatically (BigInteger, BigDecimal, LocalDateTime, byte[], etc. — see the Javadoc of the package).

try {
    session.execute("SELECT * FROM absente");
} catch (com.cirtait.miraj.MirajException e) {
    System.out.println("Erreur " + e.getCode() + " : " + e.getMessage());
}

27.5 PHP (FFI)#

Wrapper: PHP library Miraj (namespace Miraj, PSR-4 loading via composer.json, PHP 8.1+ 64-bit, ext-ffi extension).

use Miraj\Server;

$server  = Server::open('data');                      // loads the databases in the folder
$session = $server->rootSession();
$session->useDatabase('shop');

foreach ($session->query('SELECT nom, prix FROM articles WHERE prix > ?', [10]) as $ligne) {
    echo $ligne['nom'], ' : ', $ligne['prix'], "\n";
}
$server->close();                                      // or let __destruct do it

Parameters: a list for ?, an associative array for :nom (['s' => 5, 'id' => 3]). A string is sent as UTF-8 text, new Miraj\Bytes($octets) as binary, new Miraj\Decimal('12.50') as an exact decimal, a DateTimeInterface as a date-time. A row traversed by foreach is an associative array; fetchAll(), fetchNumeric($i), fetchColumn($col), queryValue() and the sequential traversal next() / field() are also available. Dates are read as DateTimeImmutable, DECIMAL and TIME as an exact string.

DLL location, in order of priority: Miraj\Native::setLibraryPath('C:/.../miraj.dll') before the first call, the MIRAJ_DLL environment variable, otherwise miraj.dll searched by the Windows loader (folder of php.exe, PATH…).

Error handling: Miraj\MirajException (getCode(), getSqlState(), getSqlLine(), getSqlColumn()).

try {
    $session->execute('SELECT * FROM absente');
} catch (\Miraj\MirajException $e) {
    echo "Erreur {$e->getCode()} ({$e->getSqlState()}) : {$e->getMessage()}\n";
}

The FFI extension ships with PHP but is not always enabled: on the command line, without touching php.ini, use php -d extension=ffi -d ffi.enable=1 script.php. In web production, ffi.enable=preload with a preload script is recommended — the embedded MIRAJ server only lives for the duration of the PHP process that opened it, so PHP-FPM and Apache/mod_php are not suitable: prefer miraj-server for a website, and reserve the PHP wrapper for command-line scripts, scheduled tasks and single-process desktop applications.

27.6 Node.js#

Wrapper: module miraj (index.js + index.d.ts, TypeScript declarations), through koffi 3.3.1, synchronous calls only (the last error and the strings returned by the DLL are specific to the calling thread).

const { Server } = require('miraj');

const server = Server.open('data');                  // loads the databases in the folder
const session = server.createSession();
session.execute('USE shop');

const rs = session.query('SELECT nom, prix FROM articles WHERE prix > ?', [10]);
for (const ligne of rs) {
    console.log(ligne.nom, ligne.prix);
}
rs.close();
session.close();
server.close();

With using support (Symbol.dispose, recent Node 18+ / TypeScript 5.2+), closing is automatic:

using server = Server.open('data');
using session = server.createSession();
using rs = session.query('SELECT nom FROM articles WHERE prix > ?', [10]);
for (const ligne of rs) console.log(ligne.nom);

Error handling: MirajError (SQL code if positive, -1 misuse, -2 row/column out of the result); an unsupported parameter type raises TypeError.

try {
    session.execute('SELECT * FROM absente');
} catch (e) {
    console.error(`Erreur ${e.code} (${e.sqlState}) : ${e.message}`);
}

64-bit integers: returned as a number if they fit within Number.MAX_SAFE_INTEGER, as a BigInt otherwise (or always as a BigInt for 64-bit integer columns with the option Server.open(path, { bigInt: true }); Result.getBigInt always reads a BigInt). Decimals: exact string ('26.90'); as a parameter, new Decimal('12.50') is bound as an exact decimal. Dates: Date in local time. DLL location: setLibraryPath(path) before the first use, otherwise the MIRAJ_DLL variable, otherwise miraj.dll next to the module, otherwise the system search. To avoid blocking the event loop, run long queries in a Worker (worker_threads) with its own Server or Session.

27.7 Go#

Wrapper: module github.com/cirtait/miraj-go, without cgo: the DLL is loaded dynamically via golang.org/x/sys/windows (neither gcc nor an import library required), 64-bit Windows only for now. A database/sql driver is provided in the mirajsql subpackage, registered under the name "miraj".

import "github.com/cirtait/miraj-go"

miraj.Load(`C:\...\miraj.dll`)               // otherwise MIRAJ_DLL, otherwise miraj.dll
srv, err := miraj.Open(`C:\donnees`, nil)    // loads the databases in the folder
defer srv.Close()

s, _ := srv.NewSession()
defer s.Close()
s.UseDatabase("shop")

rs, err := s.Query("SELECT nom FROM articles WHERE prix > :p", miraj.Named("p", 10))
defer rs.Close()
for rs.Next() {
    nom, _ := rs.Field("nom")
    fmt.Println(nom)
}

Or via database/sql:

import _ "github.com/cirtait/miraj-go/mirajsql"

db, err := sql.Open("miraj", `C:\donnees?database=shop&language=fr`)

The data source is the root folder, followed if needed by ?key=value&…:

An unknown key is rejected. Each pool connection is a session of the embedded server; data sources that designate the same folder share a single server (options of the first opener), closed with the last sql.DB that uses it. sql.OpenDB(mirajsql.NewConnector(folder, options, database)) avoids parsing the string. Parameters ? or sql.Named("name", v) (:nom); a DECIMAL is returned as an exact decimal string; database/sql transactions become START TRANSACTION (READ ONLY according to sql.TxOptions, preceded by SET TRANSACTION ISOLATION LEVEL for a level other than the default), COMMIT and ROLLBACK.

Error handling: *miraj.Error (Code, SQLState, Line, Column, Message), in the idiomatic Go way (an error value is returned, no exceptions).

Note: this Go wrapper is provided without having yet been validated at runtime, unlike the other wrappers: test it on your application before relying on it. Linux and macOS are not yet supported.

27.8 C / C++#

Two levels:

• Pure C: direct calls to the C API of miraj.h (see § 27.9) — useful for a language without a dedicated wrapper or a minimal integration.
• Header-only C++17 (miraj.hpp): RAII classes that are non-copyable but movable (Server, Session, Result, ResultList), which release their handle in the destructor or via close().

#include "miraj.hpp"
#include <iostream>

auto server = miraj::Server::open("data");         // loads the databases in the folder
miraj::Session session = server.create_session();
session.execute("USE shop");

miraj::Result rs = session.query("SELECT nom FROM articles WHERE prix > ?", 10);
while (rs.next())
    std::cout << rs.field<std::string>("nom") << '\n';

Error handling: every failure raises miraj::Error (SQL code, SQLSTATE, line/column of a syntax error); miraj::MisuseError and miraj::RangeError derive from it for codes -1 and -2. Only Session::exec_sql / exec_script return the code instead of throwing, in order to process a multi-statement script in which some statements may fail.

try {
    session.execute("SELECT * FROM absente");
} catch (const miraj::Error& e) {
    std::cerr << "Erreur " << e.code() << " (" << e.sql_state() << ") : " << e.what() << '\n';
}

miraj::Server::open_encrypted(folder, passphrase, create_encrypted = false, options) opens an encrypted data folder (Enterprise and Cluster editions; see miraj_server_open_encrypted, § 27.9). Server::create(folder) opens without loading the databases; Session::wait_changes(timeout_ms, limit) waits for change events.

Linking: miraj.dll.lib (import library, MSVC) against the executable, miraj.dll next to it.

27.9 Low-level C API (miraj.h)#

To integrate MIRAJ into a language without a dedicated wrapper, or for fine-grained control over calls, the C API declared in miraj.h can be used directly. It comprises 53 functions exported by miraj.dll, organized into families:

Main signatures:

void    miraj_options_init(MirajOptions *options);
int32_t miraj_server_open(const char *root, const MirajOptions *options, MirajServer **out);
int32_t miraj_server_open_encrypted(const char *root, const MirajOptions *options,
                                    const char *passphrase, int32_t create_encrypted,
                                    MirajServer **out);
int32_t miraj_session_execute(MirajSession *session, const char *sql, const MirajValue *params,
                              size_t param_count, int64_t *affected);
int32_t miraj_session_query_named(MirajSession *session, const char *sql,
                                  const char *const *names, const MirajValue *params,
                                  size_t param_count, MirajResult **out);
int32_t miraj_session_exec_sql(MirajSession *session, const char *sql, const MirajValue *params,
                               size_t param_count, MirajResultList **results);
int32_t miraj_session_wait_changes(MirajSession *session, uint32_t timeout_ms, size_t limit,
                                   MirajResult **out);
int32_t miraj_result_get_value(const MirajResult *result, int64_t row, int32_t col,
                               MirajValue *out);

Open options (MirajOptions, filled by miraj_options_init):

miraj_server_open_encrypted opens an encrypted data folder (Enterprise and Cluster editions): passphrase (UTF-8) unlocks its keyring; a non-zero create_encrypted initializes a new folder as encrypted. With a null passphrase, the effect is that of miraj_server_open.

Values (MirajValue: kind, int_value, float_value, data, len), parameter or cell:

Column types (miraj_result_column_type): MIRAJ_DT_INT8 (0), _INT16 (1), _INT32 (2), _INT64 (3), _UINT64 (4), _SINGLE (5), _DOUBLE (6), _DECIMAL (7), _BOOLEAN (8), _STRING (9), _BYTES (10), _DATE (11), _TIME (12), _DATETIME (13), _YEAR (14), _VECTOR (15), _GEOMETRY (16, read as bytes: 4-byte little-endian SRID followed by the WKB).

Concepts common to the whole API:

• Opaque handles (MirajServer*, MirajSession*, MirajResult*, MirajResultList*), explicitly released by the corresponding _close / _free function (a null pointer is accepted and has no effect). The root session belongs to the server: do not close it yourself; close the other sessions before the server. A result (MirajResult) can outlive its session and its server.
• Return codes: MIRAJ_OK (0) on success; a positive integer is an SQL error code (for example 1146 unknown table, 1105 internal error or recovered panic); MIRAJ_ERR_MISUSE (-1) signals misuse of the API (null handle, invalid UTF-8); MIRAJ_ERR_RANGE (-2) signals an out-of-bounds index (row or column). A failure is stored as the last error of the calling thread, which can be queried via miraj_last_error_*; a success clears it.
• Execution: miraj_session_execute runs a script and returns in affected (optional) the rows affected by the last statement; miraj_session_query returns the result of the last statement that has columns (otherwise that of the last statement); miraj_session_exec_sql does not stop at results: it returns 0 or the code of the error that interrupted the script (the previous statements remain applied) and fills the list with one result per successful statement, even in case of error. miraj_result_list_take removes a result from the list (to be freed with miraj_result_free).
• Reading cells: miraj_result_get_int (rounded, 0 for NULL), _get_float, _get_datetime (TDateTime, 0 for NULL), _get_string (empty string for NULL), _get_text (displayed text, NULL for an absent value), _get_bytes (UTF-8 text for a non-binary column) and _get_value (typed MirajValue). miraj_result_column_index returns -1 for an absent name, miraj_result_column the error 1054; the search by name ignores case. miraj_result_column_type and _column_scale return -1 for a nonexistent column.
• UTF-8 strings: as input, null-terminated (SQL, names) or data given as pointer + length for parameter values; as output, pointer + length in bytes followed by a null byte, valid until the next call from the same thread that returns data — to be copied immediately on the caller side, never to be freed.
• Dates: TDateTime type (number of days since 12/30/1899, as a double), the same convention as the historical Delphi API.
• Decimals: type MIRAJ_TYPE_DECIMAL (8), exact text ("12.50") in data / len. miraj_result_get_value returns it for any DECIMAL value (including SUM, AVG and the / division), with the nearest floating-point value in float_value; as a parameter, the text ([-]digits[.digits], exponent allowed) is bound as an exact decimal. Until October 2026, decimals were returned as MIRAJ_TYPE_FLOAT. On the wrapper side: C# decimal (string beyond 28-29 digits), Delphi TBcd in a Variant (varFMTBcd; string beyond 64 digits; varFMTBcd and varCurrency accepted as a parameter), Java BigDecimal, PHP numeric string (parameter new Miraj\Decimal('12.50')), Node.js string (parameter new Decimal('12.50')), Go miraj.Decimal (string in database/sql), C++ miraj::Decimal (std::string text), Python ctypes decimal.Decimal.
• Threads: the server can be used from several threads; a session is serialized by an internal lock (one session per thread is advised to avoid waits); a result can be read from several threads; a result list is restricted to a single thread. A session command runs on an internal thread with a large stack (the calling thread waits): a statement and the definitions it evaluates (view, trigger, CHECK, default value, generated column), up to 1000 levels of nesting, do not overflow the stack of an ordinary application thread (1 MB), at a cost of about 1 µs per command.
• Panics: none crosses the library boundary; it becomes error 1105.

Each of the 53 functions is declared in miraj.h, in the Delphi unit and in each language wrapper.

27.10 Delphi (Miraj.pas)#

Miraj.pas is the Delphi wrapper of miraj.dll: it reproduces the historical public API of MIRAJ in Delphi (TMiraj, TMirajSession, IMirajResultSet, EMirajError), so that an existing application can switch to the new engine by replacing its units Miraj, Miraj.Session, Miraj.ResultSet, Miraj.Errors with this single unit in its uses clause.

uses
  Miraj;

var
  Server: TMiraj;
  Session: TMirajSession;
  RS: IMirajResultSet;
begin
  Server := TMiraj.Open('data');              // loads the databases in the folder
  try
    Session := Server.CreateSession;
    Session.Execute('USE shop');
    RS := Session.Query('SELECT nom, prix FROM articles WHERE prix > ?', [10]);
    while RS.Next do
      WriteLn(RS.Field('nom'), ' : ', RS.Field('prix'));
  finally
    Server.Free;                              // closes the sessions, then the server
  end;
end;

Error handling: EMirajError (Code, SqlState, Line, Column), raised by all methods of the "with exceptions" API (Execute, Query, QueryNamed, UseDatabase, ...). A "no-exception" API also exists on TMirajSession: ExecSQL returns 0 or the error code and fills a list of IMirajResultSet, one per statement of the script — useful for running a multi-statement script without interrupting the program at the first error.

try
  Session.Execute('SELECT * FROM absente');
except
  on E: EMirajError do
    WriteLn(Format('Erreur %d (%s) : %s', [E.Code, E.SqlState, E.Message]));
end;

IMirajResultSet releases itself (reference-counted interface); the sessions created by TMiraj.CreateSession belong to it and are closed with Server.Free or explicitly by Server.CloseSession. Main differences from the original Delphi API, documented at the top of the file: TMiraj no longer derives from TMirajServer (the internal catalog is not exposed by the DLL — FindDatabase, GetTable are absent, CurrentDatabase becomes CurrentDatabaseName), IMirajResultSet.ColumnVector (TVector) is absent, and EMirajError no longer has a constructor with catalog arguments (the message already arrives translated into the session language).

27.11 Summary of the wrappers#

27.12 The server's miraj Python library (DB-API, psycopg2 style)#

Alongside the DLL's ctypes wrapper (27.3), the miraj Python library (wheel miraj-<version>-cp39-abi3-<system>.whl, Python 3.9 and later) provides the psycopg2 DB-API 2.0 interface to miraj-server (network mode) or to a folder of databases opened in the process (path=…, embedded mode):

import miraj

conn = miraj.connect(host="127.0.0.1", port=7007, user="app", password="...", dbname="ventes",
                     sslmode="verify-full", sslrootcert="ca.pem")
cur = conn.cursor()
cur.execute("SELECT nom, prix FROM articles WHERE prix > %s", (10,))
for nom, prix in cur.fetchall():
    print(nom, prix)
conn.commit()

Installation: pip install miraj-<version>-cp39-abi3-<system>.whl (a single wheel per system for all Python versions from 3.9). The library depends on no other package; miraj.EMBEDDED is True if the wheel contains the embedded mode.

Connection string. connect() accepts a keyword string ("host=localhost port=7007 dbname=ventes user=root password='a b'"; value in single quotes for spaces, \' and \\ escaped, a Windows path C:\certs\ca.pem is passed as is), or a URI miraj://account:password@host:7007/database?sslmode=required&connect_timeout=5 (components percent-encoded, [::1] for an IPv6 address; only the miraj:// scheme is accepted), and named parameters that complete it and take precedence. An unknown parameter raises ProgrammingError. miraj.extensions.parse_dsn(string) returns the dictionary of parameters.

The TLS modes have the same meaning as for the ODBC driver (27.2). An elapsed connection timeout raises OperationalError with SQLSTATE HYT00, like a server that no longer answers in time once connected ("timeout expired: the server did not respond in time"); a link that is actually broken returns 08S01. In embedded mode, the connections opened on the same folder share the engine, which is closed with the last one (connection.embedded is True):

conn = miraj.connect(path="/srv/donnees", dbname="ventes", create=True)

Connection (Connection): cursor(cursor_factory=…), commit(), rollback(), close() (a transaction in progress is rolled back), closed (0 open, 1 closed), autocommit, isolation_level (constant miraj.extensions.ISOLATION_LEVEL_* or name "READ COMMITTED"…), readonly, set_session(isolation_level, readonly, deferrable, autocommit), set_isolation_level(level), dsn and get_dsn_parameters() (without the password), notices (warnings of the last statements, 50 at most), insert_rows(table, columns, rows) (bulk insert) and wait_changes(timeout=None, limit=100), which waits at most timeout seconds for the change events of the connection's LISTEN subscriptions and returns at most limit of them (miraj.extensions.ChangeEvent: seq, commit_seq, event, database, name, key, columns, payload, session_id). In a with block, the connection commits on exit, or rolls back on exception, and stays open. Without autocommit, a transaction opens at the first statement and lasts until commit or rollback. A connection can be shared between threads (threadsafety = 2), its calls being serialized.

Cursor (Cursor): execute(query, values) (sequence for %s, dictionary for %(name)s; without values, no % is interpreted), executemany() (in a single pass, total rowcount), mogrify() (query with the values written as literals), callproc(name, parameters) (CALL), fetchone(), fetchmany(size) (arraysize by default), fetchall(), iteration, scroll(value, mode="relative" | "absolute"), nextset(), copy_from(), copy_to(), copy_expert(), close(), and the attributes description (Column tuples of seven fields: name, type_code, display_size, internal_size, precision, scale, null_ok), rowcount (-1 before any execution), rownumber, lastrowid (AUTO_INCREMENT identifier of the last INSERT), query and statusmessage (SELECT 3, INSERT 1…).

Types. Integers as int, FLOAT and DOUBLE as float, DECIMAL as decimal.Decimal, text as str, binary as bytes, DATE as datetime.date, DATETIME as datetime.datetime (microseconds included), TIME as datetime.timedelta (negative or beyond 24 h included), YEAR as int, NULL as None. As a parameter: these same types, datetime.time, bytearray and memoryview, uuid.UUID (its text). The DB-API type objects (miraj.STRING, BINARY, NUMBER, DATETIME, ROWID) compare with type_code; miraj.Date, Time, Timestamp, Binary and the …FromTicks are provided.

Helper modules, modeled on psycopg2:

Errors. DB-API hierarchy (Warning, Error, InterfaceError, DatabaseError, DataError, OperationalError, IntegrityError, InternalError, ProgrammingError, NotSupportedError), plus TransactionRollbackError (SQLSTATE 40…, to be replayed) and QueryCanceledError (70100 after KILL QUERY, 57014). An exception carries errno (MIRAJ code), sqlstate (and pgcode), pgerror, diag and cursor. Classification by SQLSTATE: 23 → IntegrityError, 42, 3D, 21, 24, 44, 07 → ProgrammingError, 22 → DataError, 0A → NotSupportedError, 25, 2F, XX → InternalError, the others (HY000, 08…, 28……) → OperationalError. The server message is in the session language (Duplicate entry '1' for key 'PRIMARY' below).

from decimal import Decimal

try:
    cur.execute("INSERT INTO articles VALUES (%s, %s, %s)", (1, "Stylo", Decimal("2.50")))
except miraj.errors.DupEntry as e:
    print(e.errno, e.sqlstate)          # 1062 23000

Decimals: a DECIMAL column or computation (including SUM and AVG of an integer and the / division) is returned as an exact decimal.Decimal, like psycopg2, in network mode as in embedded mode (SUM(quantite) gives Decimal('100'), AVG gives Decimal('33.3333'), with no loss beyond 2^53 nor beyond 38 digits). A decimal.Decimal (or an int wider than 64 bits) passed as a parameter is written without loss. In embedded mode, it is bound as an engine decimal (%s * 3 with Decimal('0.1') gives Decimal('0.3')); in network mode, it is sent as text, converted exactly by the column that receives it, but a computation on it is done in floating point.

Instance in PostgreSQL language#

On an instance started in PostgreSQL language (chapter 30), the same library uses the PostgreSQL v3 protocol, with no change of API or paramstyle (pyformat: %s, %(nom)s):

• Detection: with protocol="auto", the connection starts with the PostgreSQL protocol's TLS request; an instance in MIRAJ language is reached through a new connection using the legacy protocol (once per host and port and per process). dbname names the current MIRAJ database (a schema); an unknown database is refused.
• Parameters: the %s become $1, $2… bound to a named prepared statement, kept per connection (64 at most) and reused: executemany prepares once. cursor.query shows the text sent. Values are sent as text with their type (int8, float8, numeric, bool, bytea, date, timestamp); mogrify writes the literals of this language ('\x01ff'::bytea, true, '2024-01-02'::date).
• Types: as in MIRAJ language (Decimal, date, datetime, timedelta for time, bytes, None), and bool for a boolean. cursor.description keeps the type codes of MIRAJ mode (miraj.NUMBER, STRING…), with precision and scale of numeric and nullability read from pg_attribute.
• Results: statusmessage is the server's tag (INSERT 0 1, SELECT 3); lastrowid is None (use INSERT … RETURNING).
• Copies: copy_from, copy_to and copy_expert go through the server's COPY … FROM STDIN / TO STDOUT (text and CSV formats, 27.11); connection.insert_rows too.
• Transactions: autocommit, commit, rollback, isolation_level, readonly and set_session as in MIRAJ language; callproc calls a procedure (CALL), whose INOUT parameters come back as a result row.
• Errors: PostgreSQL SQLSTATE (e.sqlstate, e.pgcode), with no MIRAJ code (errno 0); 42xxx → ProgrammingError, 23xxx → IntegrityError, 57014 → QueryCanceledError. Server notices (RAISE NOTICE) in connection.notices.

psycopg2 itself also works on such an instance (chapter 30).