Libzdb

This version is the result of a systematic audit of the database drivers. It adds no new features; most fixes concern error paths and edge cases that are rarely reached in normal use.

• Fixed: PostgreSQL: PreparedStatement_setSString() ignored the caller-supplied length. Since libpq reads text parameters up to the NUL terminator, the whole string was sent, and if the buffer was not NUL-terminated it could be read past its end. Large text values are now sent by reference in binary format with the exact length (zero-copy). Other values are copied NUL-terminated and kept in text format.
• Fixed: SQLite: if Connection_execute() was called with several statements in one string, and one of them hit SQLITE_BUSY or SQLITE_LOCKED, the whole string was retried. Any earlier statement had already committed as its own autocommit transaction, so it was executed a second time. Each statement is now prepared and stepped separately, and only the blocked statement is retried.
• Fixed: MySQL and Oracle: PreparedStatement_setBlob() bound a non-NULL, zero-length value as SQL NULL. A zero-length blob is now stored as an empty blob, consistent with PreparedStatement_setString(). Only a NULL pointer binds SQL NULL.
• Fixed: PostgreSQL: ResultSet_getBlob() decoded the bytea value in place without recording it, so a second call to getBlob() on the same cell re-decoded the bytes and returned corrupt data. The decoded state is now tracked per row and column, so repeated calls to ResultSet_getBlob() on the same row returns the same bytes.
• Fixed: MySQL: Connection_beginTransactionType() with a non-default isolation level sent two statements but drained only the first result, leaving the connection out of sync so the next command failed with CR_COMMANDS_OUT_OF_SYNC. All pending results of a multi-statement query are now consumed.
• Fixed: Connection_commit() cleared the in-transaction flag before the underlying commit had returned. If the commit itself failed, for example because the server had gone away, the connection was left marked as idle and the rollback was skipped when it was closed or returned to the pool. The flag is now cleared only after a successful commit.
• Fixed: PostgreSQL: PreparedStatement_setDouble() formatted the value with %lf, which writes six decimal places. Values needing more precision were rounded, and very small or very large magnitudes lost significance (1e-10 became 0, 1e300 was truncated). It now uses %.17g, which round-trips an IEEE-754 double exactly.
• Fixed: SQLite: PreparedStatement setters raised an exception only on SQLITE_RANGE. If a bind failed for another reason, such as SQLITE_TOOBIG for an oversized value, the error was not reported and the statement ran with that parameter unbound, storing NULL. A failed bind now raises an SQLException.
• Fixed: Oracle: ResultSet_getString() formatted date and timestamp columns incorrectly. It used '.' as the time separator (YYYY-MM-DD HH.MI.SS) and IYYY (ISO week-numbering year) instead of YYYY, which returned the wrong year near year boundaries. Both now match libzdb's canonical YYYY-MM-DD HH:MM:SS format.
• Fixed: PostgreSQL: connection URL values (user, password, host, dbname, SSL paths and application-name) were interpolated into the libpq conninfo string without escaping, so a value containing a quote or a backslash produced a malformed conninfo string. Such characters are now escaped.
• Fixed: C++: PreparedStatement::bind() and bindValues() handled integer parameters incorrectly:
  • Integers were narrowed by size only, ignoring signedness, so an unsigned value above INT_MAX wrapped to a negative number. Unsigned values now promote to long long, and a 64-bit unsigned value exceeding LLONG_MAX throws sql_exception instead of wrapping.
  • Every long value was bound as a SQL timestamp. Integral values (including time_t) now bind as integers.
    Note: to store a timestamp, use std::chrono::system_clock::time_point.
• Fixed: PostgreSQL: server NOTICE and WARNING messages that libpq's default notice processor wrote to stderr are now routed to libzdb's debug output instead, visible only if ZBDEBUG is set.
• Fixed: Additional internal hardening, none of which affect documented behavior for correct usage.

• New: Exception_frame.errorCode provides the numeric error code from the underlying database when available. This allows for more robust error handling.
• New: Added SQLState.h interface providing SQLSTATE error code constants for PostgreSQL.
• New: Added ResultSet_isnullByName() to complement ResultSet_isnull().
• New: Libzdb now automatically detects and blocks connections to MySQL Router, ProxySQL, MariaDB MaxScale, and other MySQL proxies due to fundamental protocol limitations. Connect directly to your MySQL/MariaDB server instead. Advanced users who understand these limitations can override this restriction using the allow-proxy=true URL parameter, though connection failures or unpredictable behavior may result.
• Enhancement: Improved documentation around Exception and error codes.
• Enhancement: Oracle: Optimized and cleaned up. Libzdb now supports Oracle version 18c and later. Fixed a bug in transaction handling.
• Fixed: PostgreSQL: If Connection_beginTransaction(), Connection_commit(), or Connection_rollback() functions failed (e.g., due to an offline database), and an exception was thrown, the exception description contained either "Unknown error" or "?".
• Fixed: PostgreSQL: Connection_ping() relied solely on local status and couldn't detect whether the server had failed or recovered.
• Fixed: MySQL: Explicitly disabled auto-reconnect due to its unsafe handling of transactions and session state. This also makes Connection_ping() fail fast. The connection pool handles server recovery by creating new connections when needed.
• Fixed: Memory corruption when libzdb was used in a multi-threaded application, the database was down, the connection pool was empty, and two threads were trying to call ConnectionPool_getConnection().
• Fixed: Eliminated potential lock contention in the connection pool reaper thread by removing periodic ping tests on idle connections. The connection pool performs the ping test before returning a connection.

• New: Enum CONNECTIONPOOL_TYPE and ConnectionPool_getType() function to identify the database type that the ConnectionPool is connected to (MySQL, PostgreSQL, SQLite, etc.).
• New: Reintroduced configurable shared cache support for SQLite. While shared cache is deprecated, it remains beneficial in multi-threaded scenarios. When used together with the unlock notify API, shared cache can significantly reduce database lock errors and improve concurrency. Note: For optimal performance, SQLite should be compiled with:
  • Threading support (--enable-threadsafe)
  • Unlock notify API (-DSQLITE_ENABLE_UNLOCK_NOTIFY)
  When unlock notify is available, libzdb uses efficient thread synchronization. Without it, libzdb automatically falls back to an exponential backoff strategy to handle database locks. Most package-distributed SQLite libraries include these features, but some distributions may disable them. To enable shared cache in libzdb, use the shared-cache=true URL parameter.
• New: Added SSL/TLS configuration options to connection URLs for MySQL and PostgreSQL:
  • ssl-cert - SSL client certificate file path
  • ssl-ca - Certificate Authority (CA) certificate file path
  • ssl-key - SSL client private key file path
  Example: mysql://user:pass@host/db?use-ssl=true&ssl-cert=/path/to/cert.pem&ssl-ca=/path/to/ca.pem&ssl-key=/path/to/key.pem
• Fixed: Removed heap_limit as a special URL parameter for SQLite connections. This change aligns with SQLite's introduction of the soft_heap_limit pragma alongside hard_heap_limit in SQLite version 3.7.3. Users should now use the standard SQLite pragma syntax instead. See SQLite pragma documentation for details.

• New: Connection_beginTransactionType() can be used to specify a transaction's isolation level and characteristics when beginning a new transaction.
• New: valueOr macro for safe handling of API return values:
  • Provides a default for NULL, 0, or negative returns.
  • Works with pointers, integers, and floats.
  • Safely evaluates expressions only once.
  • Compatible with GCC and Clang (uses GNU C extension).
  Example: const char* host = valueOr(URL_getHost(url), "localhost");
• New: Enabled multi-thread mode for SQLite connections by default. This can improve performance in multi-threaded applications by allowing concurrent access to the database. Works with stock libsqlite builds (SQLITE_THREADSAFE=1 or 2). The URL property, serialized=true, can be used to set the connection back in serialized thread mode.
• New: Added PreparedStatement_setSString() for setting a sized string value. If the string length is known, using this method is more efficient.
• Fixed: Disabled use of sqlite3_enable_shared_cache and SQLITE_OPEN_SHAREDCACHE with open_v2(). Although we did see concurrency benefits with shared cache, it is best not to use it due to potential side-effects. SQLite also discourages its use.
• Fixed: C++, added missing support for binding a floating point value in PreparedStatement::bind()
• Fixed: C++, Use PreparedStatement_setSString() when binding a std::string_view which is more efficient and safer.
• Fixed: Plus minor improvements and fixes