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

Rudiments 1.0.7 Release Announcement

Version 1.0.7 of Rudiments, the C++ class library for developing systems and applications, is now available.

This is mainly a bug-fix release.

The most significant new feature is migration of the templateengine class from an older project into Rudiments. Also, there are 2 new "interface" classes: input and output. The filedescriptor class now implements both. The bytebuffer class now implements output.

VS2017 is also formally supported now as well.

ChangeLog follows:

  • updated threadmutex, filedescriptor, file, signalhandler, signalset, and dynamciclib destructors to be less disatsterous in the event of a double-free
  • added sys::getRudimentsVersion()
  • added empty-string detection to charstring::isInteger()/isNumber()
  • added charstring::toFloatC()
  • wrapped _access_s call with null/empty-string detection
  • added to codetree
  • added various replaceFirst/replaceAll methods to charstring
  • fixed some subtle codetree bugs that could cause parsing of a concatenation to succeed at eof inappropriately
  • updated xmldomevents to support multiple events per xmldomnode
  • moved codetree nonterminal-build to codetreegrammar
  • added nonterminal definition dictionary to codetree
  • added templateengine from stencil
  • added input/output interfaces
  • made filedescriptor an input/output
  • made bytebuffer an output
  • configure.vbs sets versions in vdproj files now
  • added config_vs2017.h and v2015 detection to configure.vbs
  • added keepalive to http urls
  • added # to characters that must be http-escaped
  • fixed debugprint macros to only print if the file could be opened

Wednesday, April 25, 2018

Microsoft ODBC Driver 17 for SQL Server on Linux

The Microsoft ODBC Driver 17 for SQL Server has been available for Linux for a while now, but I recently had a bit of trouble getting it to work with SQL Relay on CentOS 6.9. Just in case anyone else has had similar trouble, here's what I ran into, and here's how I fixed it.

The CentOS/RHEL 6 RPM's are available at https://packages.microsoft.com/rhel/6.8/prod/.

The driver itself is msodbcsql17-17.1.0.1-1.x86_64.rpm but if you try to install it using yum localinstall or rpm -i then it will complain that it needs a newer version of unixODBC than what is available for CentOS/RHEL 6. Version 2.3.1, in particular.

Fortunately, there are some unixODBC-2.3.1 rpms available in the same directory. Sort of...

Actually, there's a unixODBC-devel-2.3.1 rpm and some odd unixODBC-utf16 rpms, but the requested unixODBC-2.3.1 rpm is missing.

It turns out that a unixODBC-2.3.1 package was there at one point, but has since been removed. It's kind-of good that it was removed because it didn't always work. It worked with isql, but any calls to SQLConnect() by SQL Relay (and presumably by other apps) would reliably hang. What can we do though? The msodbcsql17 rpm requires unixODBC-2.3.1.

Well, those utf16 packages actually do work with msodbcsql17, and don't cause SQLConnect() to hang, but getting everything to install requires a little finesse.

I was able to get everything to install by running:

yum remove unixODBC unixODBC-devel
yum localinstall unixODBC-utf16-*
rpm -i --nodeps msodbcsql17-17.1.0.1-1.x86_64.rpm

The --nodeps option is important because it enables the rpm to install without checking dependencies.

After that I reconfigured, rebuild, and reinstalled SQL Relay and everything worked for me.

Wednesday, April 11, 2018

Updating Ubuntu 17.04

I recently attempted to apt-get upgrade an Ubuntu 17.04 VM that I hadn't upgraded in a while, and got all kinds of errors like:

N: Updating from such a repository can't be done securely, and is therefore disabled by default.

It took me a surprising amount of time to discover the solution. Apparently this typically happens when a mirror has some problem that makes the updates unreadable, and I ran into lots of forums explaining how to fix that. Unfortunately, that wasn't my problem. My problem was a lot more legitimate.

Ubuntu 17.04 reached EOL in mid January of 2018, and so us.archive.ubuntu.com and security.ubuntu.com don't host updates any longer.

Ha!

Well, the solution is simple. Edit /etc/apt/sources.list and replace us.archive.ubuntu.com and security.ubuntu.com with old-releases.ubuntu.com. Then rerun your apt-get update and apt-get upgrade again.

It guess I never ran into the problem before because other than 17.04, I'm only running LTS releases. 16.04 is still supported, and the other versions are so old that I had to make that change immediately after installation so apt-get update would work at all. I guess I never run into a case where it had been working and stopped.

You learn something new every day.

Monday, January 29, 2018

A Couple of SQL Relay on Ubuntu How-To's

A couple of people have recently asked for some step-by-step tutorials detailing SQL Relay installation and configuration on various platforms. So, there's a new Gadget on the right hand side of this blog called SQL Relay How-To's that will contain these tutorials.

I just added 2 for Ubuntu 16.04.

Check them out!

Friday, December 8, 2017

Firstworks GIT Migration

I'm fairly well known, among friends at least, for liking to get every last mile out of vehicles, equipment, gear, and just about everything else. Every last mile. The night before last, I discovered that I had gotten every last mile out of Sourceforge's CVS service.

I'd made a couple of changes, run a commit, and bam!

###############################################################################

CVS commits are NOW DISABLED! This was first announced on October 5th, 2017:

  https://sourceforge.net/blog/decommissioning-cvs-for-commits/

This project's data may be converted over to a new SCM. Use the project's
menu to see where the current code is kept:

  https://sourceforge.net/projects/PROJECT_NAME/

To access the CVS data, either use a pserver checkout or an rsync backup.
In the following commands, replace PROJ with the project name and MOD with
the module name:

  cvs -z3 -d:pserver:a.cvs.sourceforge.net:/cvsroot/PROJ co -P MOD

  rsync -aiv a.cvs.sourceforge.net::cvsroot/PROJ/ /dest/dir/

Conversion instructions for svn and git are available here:

  https://sourceforge.net/p/forge/documentation/CVS/

###############################################################################

Ha!

Well, I wasn't too upset. It's not like I didn't see it coming. A few years back I tried to get git working on all the old platforms in the compile farm. That ultimately failed, but I worked out a better solution last month and I've been trying, off and on to get my CVS repos converted to GIT ever since.

Good thing! I needed the experience of all of the failed attempts, and some advice from a buddy of mine to finally get it working today.

So, as of today, all Firstworks projects have been migrated from Sourceforge-hosted CVS to Sourceforge-hosted GIT.

Rudiments is available via:

git clone git://git.code.sf.net/p/rudiments/rudiments

SQL Relay is available via:

git clone git://git.code.sf.net/p/sqlrelay/sqlrelay

RIP CVS. Thanks for the memories!

Tuesday, November 7, 2017

SQL Relay 1.2.0 Release Announcement

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







ODBC Improvements

The main focus of this release is ODBC improvements.

ODBC is unimaginably complex. To illustrate this point, the ODBC Driver for SQL Relay consists of 11223 lines of code, while the SQL Relay Client Library that it wraps only consists of 8748 lines. The API also provides several different ways do to do the various things, and different apps use different ways. It's a daunting task to implement even the majority of commonly used features, and then there's always that one app that uses some particular feature. As such, the ODBC driver hasn't gotten the attention that it deserves.

This release brings many, many major and minor improvements to the ODBC Driver for SQL Relay. It is especially improved when used with an ODBC backend, and even more so when using ODBC on the backend to access MS SQL Server, a very common use case.

The improvements are far too numerous (and in most cases, subtle) to describe here. If you had an ODBC app that was having trouble with SQL Relay, try it again.

Windows Improvements

There were basically build problems with older versions of SQL Relay that prevented it from running on some systems, and prevented it from running correctly on others.

The vast majority of these problems have been vetted and solved.

As with ODBC, if you had a platform that had trouble running SQL Relay, try it again.

32-bit Output Bind Length

This release also remedies a significant oversight. On the client-side, and in the protocol, input and output bind variables have always had a 32-bit length. Until this release, though, due to an oversight, in the server API, output bind variables had 16-bit lengths. This basically made it impossible to fetch a string longer than 32678 bytes from the database. Some databases limit varchar fields to this length, so it's not a problem with those databases, but it created significant problems in some distinct cases.

This oversight has been remedied. In fact, this is the tiny, but significant, ABI-breaking change that caused the bump to 1.2.0 from 1.1.0.

Bug Fixes and Improvements

This release also includes the standard array of random bug fixes and minor improvements. Full Changelog follows.

      fixed subtle error in sqlr-import that could cause it to skip empty/NULL fields
      implemented getCurrentDatabase for odbc connections
      implemented selectDatabaseQuery for odbc connections
      added NCHAR, NVARCHAR, NTEXT, XML, and DATETIMEOFFSET types, updated odbc connection module to recognize them
      added --with-windows-version to configure.vbs
      enabled "liveconnection" test for "Lost connection to MySQL server during query" error
      made server-side string outputBind() method's valuesize 32-bits
      added missing mapColumn() call to sqlrservercontroller::getField()
      fixed var directories in Windows deployment projects
      added run-as-Admin requirement to Windows installer
      implemented ODBC SQLProcedures, SQLProcedureColumns, SQLGetTypeInfo, SQLPrimaryKeys, and SQLStatistics, though currently they only work when using the odbc connection module
      implemented ODBC SQLTables to get schema and table type lists, though currently only works when using the odbc connection module
      ODBC SQLProcedures, SQLProcedureColumns, SQLColumns, SQLTables, SQLGetTypeInfo, SQLPrimaryKeys, and SQLStatistics properly set the error now if there was one
      ODBC SQLError properly cycles through the error records now
      updated ODBC SQLGetInfo(SQL_DRIVER_ODBC_VER) to return a value corresponding to the value set by SQLSetAttrInfo(SQL_ATTR_ODBC_VERSION)
      fixed string outputBind() signature in odbc connection module
      fixed a couple of short -> int16_t/uint16_t errors in odbc and mysql connection modules
      ODBC SQLBindParameter(in/out) calls outputBind() now, to work with apps like Delphi which broadly use in/out for out parameters
      refactored odbc connection module's methods to get object lists to support dot-separated object identifiers
      various odbc driver improvements
      updated odbc driver to interpret SQL_C_(X)LONG types as (u)int32_t's rather than long's
      updated odbc connection module to use SQL_C_SBIGINT/SQL_BIGINT for integer binds, rather than SQL_C_LONG/SQL_INTEGER
      fixed uninitialized null/blob flags in sqlrclientprotocol::returnRow()
      various variables are now properly typed (SQLLEN vs. SQLINTEGER) in the odbc connection module
      added "divider on/off" command to sqlrsh
      fixed several cases where microseconds were represented by an int16_t made them all int32_t's
      parsing/generation of fractional seconds pays attention to length of value/format-string now
      fixed a bug that caused fractional seconds to be dropped sometimes
      odbc lazy-fetches now
      odbc driver fakes SQLBindParameter with data-at-exec by deferring the execute and buffering the data locally now
      bumped odbc MAX_COLUMN_COUNT to 384
      added a mars=yes/no flag to enable MS SQL Server MARS when using the odbc driver for MS SQL Server with the odbc connection module
      ODBC SQLGetInfo(SQL_USER_NAME) gets the schema from the backend when used with the odbc connection module now
      increased listen backlogs to 128 from 15/5
      normalize translation doesn't remove spaces around _'s any more
      implemented output bind null indicators properly for db2/informix
      odbc connection supports dynamic maxcolumncount/maxfieldlength now
      fixed but that caused the results of non-tree-based query translations to be concatenated
      updated odbc connection to fetch blob columns in chunks and not be bound by maxitembuffersize
      fixed a bug that could cause a buffer overrun when using maxitembuffersize with odbc, db2, and informix
      configure script finagles the multiarch dir for platforms where gcc -print-multiarch doesn't return anything, but the multiarch dir is necessary to find python3
      configure script can find php-7 on openbsd now
      node is used to run node-gyp.js now
      configure script looks for /etc/php7 now
      configure script looks for node-gyp under /usr/lib*/node_modules/nmp[6789] now
      php pdo api uses zend_long in place of ssize_t now
      fixed uninitialized ncols in mysql connection module