What’s new in MariaDB Connector/ODBC 3.2

spacer

We are pleased to announce the general availability (GA) of MariaDB Connector/ODBC 3.2. MariaDB Connector/ODBC 3.2 is our newest interface for building С applications on top of MariaDB Community Server and MariaDB Enterprise Server.

The MariaDB Connector/ODBC 3.2 release series replaces the maintenance releases for the 3.1 release series, as the new release series is fully compatible with 3.1.

MariaDB Connector/ODBC 3.2 includes the following enhancements:

  • ODBC 3.8 compliance
  • Enhanced text protocol usage
  • Prepared statement caching
  • Improved resilience during resultset streaming

ODBC 3.8 compliance

The driver’s compliance with the ODBC standard version 3.8 is a significant update. This ensures compatibility and enhances the driver’s functionality, particularly for applications primarily using the SQLCancelHandle API function and SQL_ATTR_RESET_CONNECTION connection attribute support (ODBC-66).

Enhanced text protocol usage

The use of the text protocol has been extended, offering more flexibility. In 3.1, it was used for queries without parameters and results, i.e. for upserts etc, without parameters. The SQLExecDirect ODBC API function now uses text protocol and client-side prepared statements (CSPS). If necessary, this can be automatically switched to binary protocol and server-side prepared statements (SSPS), such as CALL statements. The ODBC API function SQLPrepare uses SSPS by default, but it can switch to CSPS, for example, in case of a multistatement query. The connection string option PREPONCLIENT to use CSPS by default in SQLPrepare has also been added. Sometimes, the driver will still revert to using SSPS even if this option is selected. One example of such a case is a request for resultset metadata before the SQLExecute API function call. (ODBC-222). The text protocol usage is a key feature that allows for more efficient and flexible query execution, improving the overall performance of your applications.

The certificate verification option (SSLVERIFY) is now ‘on’ by default

The driver has been upgraded to use the Connector/C 3.4 series. This change aims to simplify TLS (often referred to as SSL) configuration. Now, with MariaDB Server 11.4, the application may use self-signed certificates, provided the database password is not empty. Setting the FORCETLS connection string option is all that is needed to configure a secure connection (but please make sure you are not explicitly resetting the SSLVERIFY option).

Please also note that the certificate verification option (SSLVERIFY) is now enabled by default for encrypted connections (ODBC-421). This enhances security by ensuring only trusted certificates are accepted but may cause connection failures for applications relying on older (<11.4) servers and self-signed certificates unless this option is explicitly disabled.

Prepared statement caching

Prepared Statement LRU Cache has been added to the Connector. This cache allows the driver to reuse server-side prepared statements, leading to performance improvements that can be as significant as 2x or more, especially for complex queries involving numerous schemas and tables. An application can configure the cache with PSCACHESIZE and MAXCACHEKEY connection string options. The former sets the size of the cache, i.e. the number of statements that will be cached. The latter is the maximal size of the cache key, which effectively limits the maximum query length, which will be the value of MAXCACHEKEY - length of the current schema name - 1. The cache is enabled by default. Setting any of these options to 0 will disable cache use. Default values are 250 statements and 2112, i.e.~2048, max statement length. (ODBC-397). The Prepared Statement LRU Cache is a powerful feature that can significantly improve the performance of your applications by reducing the need to re-prepare statements, especially in scenarios with frequent query execution.

Improved resilience during resultset streaming

The 3.2.1 driver introduces improved protocol resilience in resultset streaming. In version 3.1, if resultset streaming is used, it will block the connection, and the driver will return an error on any new query. However, the 3.2.1 driver, in this case, will cache the rest of the currently streamed result set and execute the new query without error. This enhancement ensures uninterrupted connection and enhances the driver’s reliability, giving you peace of mind in your operations.

Data type conversion using Connector/C callbacks

There are two types of callbacks – for parameters and results. Parameter callbacks are used for bulk operations and allow minimizing memory use required for data type conversions. Their use is controlled by the connection string option PCALLBACK, which is on by default. The option to turn on result callbacks is RCALLBACK, which is off by default. Result callbacks are expected to make certain types of conversions faster and reduce memory use, as they can write directly to application buffers and not allocate intermediate buffers required for conversion.(ODBC-163)

Support for connection attributes

For setting connection attributes, the ATTR connection string option can be used in the following format: ATTR={<attrname1>=<attrvalue1>[,<attrname2=attrvalue2,…]}  (ODBC-388).

SQLForeignKeys performance optimization

Experience a significant performance improvement with the SQLForeignKeys function. Previously, it could be considerably slow when dealing with numerous database schemas. However, our optimization efforts have made it noticeably faster, even in such scenarios. (ODBC-410).

Optimized parameter array operations

Parameter array operations are now optimized for text protocol and CSPS use. i.e., the driver can construct a batch of queries based on values in parameter arrays to send via text protocol. Sending parameter arrays via binary protocol is already supported in the 3.1 version and is preferable for this operation. (ODBC-86). Parameter array operations are a feature that allows you to execute multiple similar queries with different parameter values in a single call, reducing the number of round trips to the server and improving performance. The driver’s optimization for text protocol and CSPS use further enhances the performance of parameter array operations, making them even more efficient.

Important defaults have changed

In this version, we have changed some of the driver defaults. The cursor type now defaults to SQL_CURSOR_FORWARD_ONLY as required by the ODBC specs. 3.1.x series has the default cursor type SQL_CURSOR_STATIC (ODBC-290). The NULL value of the catalog name parameter of the SQLTables ODBC API function is now treated as ‘current schema’ by default and not as ‘any schema’ (ODBC-298). These changes in driver defaults can significantly impact the execution of your queries. The new default cursor type can improve the performance of your applications by reducing the memory footprint and network traffic.

Bug fixes for a smoother experience

Rest assured, we’ve diligently addressed the issues reported after the 3.1.20 release. Our bug fixes ensure a smoother and more reliable experience with Connector/ODBC 3.2.

Download Connector/ODBC 3.2

We encourage you to upgrade to the latest version and take advantage of the new features and improvements. Your feedback is valuable to us, and we look forward to hearing about your experience with Connector/ODBC 3.2.

Download Connector/ODBC 3.2 here.