Monday, May 21, 2018

SQL Relay 1.3.0 Release Announcement

Version 1.3.0 of SQL Relay, the powerful connection pool, load balancer, query router, and database proxy, is now available.

This release is mainly focused on integrating recent improvements and customizations, contributed by or developed for various clients into the official distribution.

ODBC Improvements

As with the last release, the ODBC front and backend have both been improved significantly. Apps like Toad Data Point and RazorSQL should work as expected with SQL Relay now.

PDO Improvements

The PDO driver has been wrung out and updated to be more consistent with other PDO drivers. So, now, if you're migrating a PHP PDO app from connecting directly to the database to using SQL Relay, you should encounter fewer, if any differences in behavior.

"patterns" Query Translation Module

There is now a new "patterns" query translation module. It allows you to match a pattern in a query and replace it with another pattern. Match-patterns can be strings, case-insensitive strings, or regular expressions. It also allows you to nest matches. See patterns for a detailed description.

Column Tables

Many, but not all, databases can return the table name of a column in the result set header. The client-side supports this now, as do the connection modules for various databases.

In classic PostgreSQL fashion, PostgreSQL databases return a table OID by default, and require a "tablemangling" parameter in the connect string to indicate whether to return the OID's, or to map them to actual table names. This is similar to the "typemangling" parameter.

SQL Server only returns table names when a "server cursor" is in use. FreeTDS doesn't support returning the table name at all, so it's just not an option when FreeTDS is being used. The ODBC driver for SQL Server can return the table name, but again, only when a "server cursor" is in use. Unfortunately, server cursors cause results to come back incredibly slowly. So, the ODBC connection module has a getcolumntables="yes/no" parameter. When set to "yes" it basically prepares the query with server cursors enabled, gets the column info, then prepares it again with server cursors disabled, so that when it's executed, results can be fetched quickly. There is a cost to the double-prepare, but it's nothing like the cost of fetching rows. Since there's a cost at all though, it can be disabled.

Modern Platforms

Current versions of Ubuntu and Fedora Linux revealed various subtle compiler problems. These have been remedied and the code should compile cleanly on these systems.

There are a lot of other changes too, but everything else is subtle or behind-the-scenes.

Full ChangeLog follows:

  • updated systemd init file to use SIGINT to kill sqlrelay
  • fixed several "ocurred" -> "occurred" typo's
  • client debug hides password now
  • passing -disable-crash-handler to the scaler passes it to connections spawned by it now
  • passing -nodetach to the sqlr-scaler or sqlr-listener works now
  • added get*Timeout methods to the C++ API
  • various pdo tweaks to make it more consistent with other pdo drivers
  • added rudiments version an compile date/time to output of --version
  • exposed timeouts, client info via custom pdo options
  • timeouts configurable in pdo connect string now
  • dsn parameters can be overriden (or supplied outright) in the connect string now
  • added \r and ; as possible after-variable characters for fake-binds
  • added -short, -connection-detail, and -query options to sqlr-status
  • added initial support for queries that return multiple result sets
  • lots of various odbc connection module improvements
  • added parse failed event
  • updated normalize translation to remove spaces around :=
  • added noop query framework
  • added "patterns" query translation
  • added debug="sql"
  • fixed subtle bug in normalize translation that could cause problems with empty strings
  • moved the memorypool used by the sqlrclient protocol module to allocate memory for bind variables into the sqlrservercontroller and exposed it to modules
  • added docs for tls/ssl backend enc/auth
  • added directives framework and custom_wf directive
  • pushed connect timeout parameter up
  • implemented underpinnings for input/output binds
  • made SQLGetConnectAttr(SQL_AUTOCOMMIT) not return an error
  • fixed configure bug causing --disable-server to disable the odbc api
  • configure.vbs sets versions in vdproj files now
  • fixed some missing code in the documentation
  • replaced toFloat() calls with toFloatC()
  • mysql connection module handles /* ... */ comments correctly when detecting which API (stmt vs. traditional) to use now
  • added php conf type for netbsd
  • added freetds parameter to freetds connection module that sets the FREETDS/FREETDSCONF environment variables
  • fixed some subtle SQLGetData bugs in ODBC driver
  • updated sqlrserver API to support getting the column tables
  • updated odbc, mysql, postgresql, and sqlite connection modules to
  • return column table names
  • updated sqlrclient protocol to send/receive column table names
  • added result set header translation framework
  • added getcolumntables option to odbc connection module
  • ODBC SQLTables switches to the specified catalog before getting the table list now (and switches back)
  • fixed ODBC wchar truncation problem
  • fixed "show columns odbc" bug in mysql connection module
  • mysql connection module uses MYSQL_FIELD.name_length if it can now
  • fixed char * -> const char * bug in python API
  • added support for openjdk-11 to configure script
  • removed some unnecessary parentheses in mysql drop-in library that gcc 8.1.1 doesn't like