Monday, December 31, 2012

SQL Relay - 0.49 is out

SQL Relay 0.49 is now available.

This release features the addition of extension modules for password encryption and logging. Logging was actually added in the previous release but it has been expanded and documented in this release.

See Password Encryption Modules and Logger Modules for more information.

The build process has been improved as well.

Lots of code was reorganized to be easier to maintain and to compile faster. For many years the configure script would erroneously include "-pthread" in the compile commands on platforms that don't support it. That has been fixed.

-Werror has been added to default build for most components as well and various issues revealed by it have been fixed.

The windows make.bat script has been refactored too and now supports both regular and CLR builds.

The client-server protocol has been refactored to improve performance. Two client-server round trips have been removed. One after authentication and another after the listener-connection handoff. All client-server commands can run in a single round trip now.

The handoff="reconnect" parameter has been replaced with handoff="proxy". In the past, if handoff="reconnect" then the client was told to disconnect from the listener and reconnect to an available connection daemon. Now, if handoff="pass" is unsupported, the listener just proxies the client, ferrying data back and forth between it and the server. This causes the listener/connection relationship to be completely transparent to the client and removes a client-server round-trip.

An very primitive ODBC driver has long been included in the source tree. It has been improved significantly in this release and can now be used with the isql program included with unixODBC and iODBC and with the henplus JDBC client using the JDBC-ODBC bridge. The driver still lacks many features, hasn't been tested much beyond those clients, and doesn't work on Windows without manually adding registry entries, but it is usable and should eventually make it possible to use SQL Relay with a much wider range of applications.

A few bugs were fixed as well including a bug that could cause problems when fetching from an output bind cursor under a very specific set of circumstances and a bug that could cause a hang if the database login failed.

Client API methods for getting the database's host name and IP address have been added as well.

This version of SQL Relay requires Rudiments - 0.40. Both are available from the SQL Relay web site.

Rudiments - 0.40 is out

Rudiments 0.40 is now available.

This release fixes several obscure bugs and problems with the build process.

For many years the configure script would erroneously include "-pthread" in the compile commands on platforms that don't support it. That has been fixed.

-Werror has been added to default build as well and various issues revealed by it have been fixed.

The windows make.bat script has been refactored too and now supports both regular and CLR builds.

The following bugs have been fixed:

  • fixed hostentry::getAddressString() for numbers greater than 127
  • fixed bugs that could lead to a crash when using the hostentry class with NULL hostnames
  • fixed charstring::safeCopy to copy the NULL terminator
  • made charstring::copy NULL-safe
  • made crypt class salt NULL-safe
  • reads that error out with EAGAIN are treated as successful reads of 0 bytes now

It appears that some platforms (OpenBSD 4.9 for i386 at least) create sockets in non-blocking mode. This is not what a developer would generally expect, so some changes have been made to standardize this behavior.

  • all sockets are manually set to blocking mode after creation now
  • sockets created by accept() are set to the blocking/non-blocking mode of the socket that was accepted on

Monday, December 17, 2012

SQL Relay - 0.48.1 is out

SQL Relay version 0.48.1 has just been released.

This release fixes an obscure bug that can occur with output bind cursors.

Basically if a server-side cursor is used with a query that returns a bind variable, and then later that same cursor ends up being used AS an output bind cursor, the bind variables from the previous use of that cursor end up getting sent back to the client and this wreaks all kinds of havoc.

This bug has been fixed.

If your app doesn't use output bind cursors then there's no need to update.

Saturday, December 8, 2012

SQL Relay - 0.48 is out

SQL Relay version 0.48 is now available.

This release features some major internal reorganization of the server-side software and integration of a bunch of contributed code.

Outwardly, not much has changed. The most noticeable change is that the sqlr-connection-XXX programs have been replaced with a single sqlr-connection program which now loads a plugin for whichever database it needs to connect to. So, if you have monitoring programs that look for that process, you may need to update them.

The next-most noticeable change is the addition of a stmtcachesize parameter to the connectstring when configuring an instance of SQL Relay to talk to Oracle. Setting this parameter to a value other than 0 enables use of Oracle's Statement Cache which can improve performance significantly. See Configuring SQL Relay for more info on this parameter and Google "Oracle Statement Cache" for more info on that.

Two changes that should improve performance somewhat have been made.

There were cases where the client would tell the server to abort the result set, then wait for a response, unnecessarily, incurring the cost of an additional client-server round-trip. It no longer does this.

The code for talking to Oracle used to re-prepare the query before each execution, if the bind variables had been modified. This was done to work around an issue that could come up when using OCI 8.0 or when running against 8.0 or 8i databases. Oracle has long resolved the issue though, and now the re-prepare is only done in those specific cases.

Other updates and fixes include:

  • Integrated patches from Neowiz for:
    • handling for oracle errors ora-01033, ora-02067 and ora-04068
    • bind validation when using the statement cache
    • optionally rejecting oracle queries with duplicate bind variables
    • sqlrconnecton::setClientInfo/getClientInfo
    • query logging
    • separate authentication and response timeouts on the client-side
    • environment variables for setting timeouts
    • sqlrelay-level errors for exceeding various bounds
    • improved statistics gathering
  • created a query logging framework
  • implemented the current slow query log as a plugin
  • implemented the neowiz query log format as a plugin
  • created a custom query framework
  • implemented the neowiz statistics gathering commands as custom queries
  • added a test program for triggers, translations and other extensions and obscure features
  • fixed several bugs in the informixtooracledates translation
  • added a droplocalizedtemptables trigger
  • added support for "global temporary" to temptableslocalize translation
  • removed oracletemptablespreserverowsbydefault translation - temptableslocalize is much more effective
  • fixed a bug that caused a "no server-side cursors" error to occur when a new session is started if cursors="0" in sqlrelay.conf

Rudiments - 0.39 is out

Rudiments Version 0.39 is now available.

This release features a few bug fixes and a few new methods that I'm not sure how I ever managed to live without:

Bug Fixes:

  • filedescriptor::getPeerAddress returns NULL now for non-inet sockets
  • regularexpression::getSubstring* methods return const char *'s now
  • calling xmldomnode::deleteAttribute() on a non-existent attribute name no longer crashes
  • fixed a long standing memory leak in dictionary::clear()

New Features:

  • added charstring::safeCopy methods
  • added xmldomnode::getTree and xmldomnode::print methods

Thursday, November 1, 2012

SQL Relay - 0.47 is out

SQL Relay 0.47 is now available.

This release mainly features the ability to use Oracle's Statement Cache feature. Code for this was contributed by Neowiz.

To enable the statement cache, simply add:


(Or some size other than 20) the connect string for your instance of SQL Relay that talks to Oracle in your sqlrelay.conf file. Note also that there are a few quirks when using the statement cache which appear to be either bugs in OCI or issues with the way SQL Relay uses OCI. They may be resolved in a future release of OCI or SQL Relay. See Configuring SQL Relay for more information.

Note also that I haven't finished integrating all of the contributed code yet, so if something doesn't work as expected, that could be why.

Also in this release, I updated the SQL Relay command line clients to have a consistent command-line parameter interface and renamed a few of them.

All command line programs now have the following parameters:

  • -host
  • -port
  • -socket
  • -user
  • -password

So, for example, to connect to localhost, port 9000 using user test with password test, you now use:

sqlrsh -host localhost -port 9000 -user test -password test

Whereas in the past, you would have used:

sqlrsh localhost 9000 "" test test

In addition, sqlrsh now has -script and -command arguments to run scripts or commands non-interactively.

I also renamed the query and fields commands sqlr-query and sqlr-fields.

In addition to those updates and changes, the following new features and bug fixes have also been implemented:

  • fixed bug that caused sqlr-export to output negative xml-entity values
  • fixed single-quote-escaping in sqlr-export
  • forced handoff="reconnect" for Cygwin and Linux<2.2
  • added a "fields" command to sqlrsh
  • improved readline detection in the configure script
  • added getDatabase/Table/ColumnList support to odbc and mdbtools connections
  • added detection and support for openjdk
  • integrated patches from Neowiz for:
    • using -g3 if available when --enable-debug is specified
    • fixing an oracle column-resource-related memory leak
    • eliminating sqlr-start delay
    • "using namespace rudiments" declaration fixes
  • added a faq entry about common OCIEnvCreate errors
  • removed default id warning from sqlr-cachemanager
  • removed coloration from sqlrsh
  • updated begin, commit and rollback to return errors if they fail
  • updated firebird docs
  • added parsing of standalone constraints in a create table query

Rudiments - 0.38 is out

Rudiments 0.38 is now available!

This release features the following new features and bug fixes:

  • included some contributed updates to chat, charstring and modemclient
  • added several more wrappers to system class
  • refactored configure/make for non-gnu make compatibility
  • upated passFileDescriptor to dynamically allocate the control buffer for OS X 10.7 whos CMSG_LEN ultimately calls a function
  • updated msvc build to create Release code rather than Debug by default
  • added posix analogs (rewind, skip and read) to directory class
  • updated filedescriptor debug so debugging of the buffering can be enabled/disabled
  • integrated patches from Neowiz for:
    • using -g3 if available when --enable-debug is specified
    • testing the result of getaddrinfo for any non-zero result when checking for EINTR, not just EAI_SYSTEM, and also to reset errno between tries
    • clientsocket to use WSAConnect and friends on windows
    • disabling code that uses AF_UNIX sockets outright for windows
    • inetserversocket::listen() to reuse addresses
  • added process::exitImmediately
  • updated error::getErrorString() to be thread safe when it uses strerror_r or strerror_s internally
  • fixed charstring::isInteger/isNumber to return false when the string passed in is either - or . without any actual number
  • changed longs in snooze class to uint32_t's

Wednesday, August 15, 2012

SQL Relay - 0.46 is out

SQL Relay 0.46 is now available.

The biggest new feature in this release is a native windows ADO.NET data provider. Yes, you can now aim your Windows .NET applications at SQL Relay.

To accomplish this, I ported most of Rudiments and the client-side components of SQL Relay to MSVC, wrote a C# wrapper for the client API and wrote an ADO.NET data provider using the C# wrapper.

A binary distribution will be available soon, but for now, see the documentation for how to build, install and use the data provider.

In addition to the ADO.NET data provider, the following new features have also been added:

  • A begin() method has been added to the client API's to compliment the commit() and rollback() methods.
  • A waitfordowndatabase parameter has been added to sqlrelay.conf to control SQL Relay's behavior when the database it's talking to has gone down.

The following bugs have also been fixed:

  • Applied Georgiy Kirichenko's missing flushWriteBuffer() patch.
  • Some subtle bugs in the Ruby DBI adapter that modern versions of Ruby DBI reveal.
  • Fixed a race condition in the sqlr-scaler that could occur when connections="0" is set in the sqlrelay.conf file.
  • Applied some ODBC fixes from Chae Young-ku.
  • Fixed the debug option to the query command line client.
  • Fixed an outstanding TCL api bug that caused cursors not to get created.
  • Fixed several outstanding bugs regarding escaping of quotes.

Try it out and report any problems you have!

Rudiments - 0.37 is out

Rudiments 0.37 is now available!

This release features the following new features and bug fixes:

  • updated logger to exclude : if header isn't supplied
  • updated logger not to print double-returns after each log message
  • added file::createPipe()
  • fixed net-to-host and host-to-net byte order conversions for 64-bit integers on windows
  • fixed charstring::copy that I'd broken in 0.36
  • fixed missing [] in a delete in filedescriptor::read() with terminator that could cause leaks and corruption
  • fixed a leak in filedescriptor::read() with terminator that could occur when it fell through with an error, timeout or 0-byte read
  • applied Georgiy Kirichenko's fix to the safePoll call in filedescriptor::readFileDescriptor() - swapped true/false arguments so it would wait using POLLIN
  • added xmldomnode::getPosition()
  • fixed display of seconds from datetime::getString()
  • fixed optimum buffer size calculation in xmlsax
  • fixed several dlclose()-related bugs

Tuesday, June 12, 2012

SQL Relay Fast Start

Introducing: SQL Relay Fast Start...

SQL Relay has many dependencies and features. Building, installing and configuring it can be quite a project. If you're just trying out SQL Relay then SQL Relay Fast Start is a much faster and easier way to get started.

It's a VMware virtual appliance, pre-loaded with all of the redistributable databases and languages supported by SQL Relay.

The VM provides running instances of MySQL, PostgreSQL, Firebird, SQLite and MS Access databases and running instances of SQL Relay, pre-configured to talk to them. There are also instances of SQL Relay pre-configured to talk to Oracle and MS SQL Server or Sybase databases. All you have to do is plug in a hostname and start them up.

Just download the image, point VMWare Player or Workstation at it, log in as sqlrelay with password sqlrelay and follow the instructions in the browser window.

At $10, SQL Relay Fast Start is less expensive than the opportunity cost of building, installing and configuring SQL Relay and a test database from scratch. Use SQL Relay Fast Start to get a feel for how SQL Relay works and what you can do with it right away. If you like what you see, then move on to building and configuring it in your environment for yourself.

SQL Relay Fast Start is available today.

Wednesday, May 2, 2012

Rudiments Update - 0.36.1 (Urgent)

There's bug in Rudiments, version 0.36 in the charstring::copy method that can cause various problems including segmentation faults. Version 0.36.1 has just been released to fix the problem. Please update.

For anyone who just wants to fix version 0.36, the fix is simple... Just edit src/charstring.cpp and look for the copy() method around like 842. It currently calls rawbuffer::copy but should be modified to have the following implementation:

char *charstring::copy(char *dest, const char *source, size_t size) {
 return strncpy(dest,source,size);

I changed the code in 0.36 to fix a problem on windows but the new code doesn't work properly for what should have been obvious reasons when I made the change. strncpy() will stop when it hits a null-terminator but rawbuffer::copy will copy "size" bytes no matter what, even if that's past the end of the string and off into the weeds.

Tuesday, April 10, 2012

SQL Relay - 0.45 is out

SQL Relay 0.45 is now available.

This release has the following new features:

  • support for getting the native database error number
  • support for mapping native error numbers to MySQL error numbers for the mysql drop-in replacement library
  • MSVC projects for sqlrclient, sqlrclientwrapper, sqlrutil and sqlrsh (all work natively on windows now)
  • added autocommit on/off to sqlrsh

In addition, the following things have been fixed or updated:

  • ttl="0" works as expected
  • dynamic scaling of cursors works as expected now
  • fixed a few mdbtools errors
  • column aliases work as expected now with firebird
  • fixed a bug where if an app either enabled or disabled autocommit, that could become the default status of autocommit rather than what was set in the config file
  • fixed a bind-var-related memory leak in mysql drop-in lib
  • fixed return values in mysql drop-in lib
  • updated getTableList() to exclude system tables
  • applied Benno Lange's localstatedir patch to fix a case where the localstatedir command line parameter wasn't working properly
  • applied Renat's listener alarm handling patch to avoid race conditions and other issues with the listenertimeout feature


Stencil - 2.0.6 is out

An updated version of Stencil is now available. This is an extremely minor update with a few modifications to the configure script and a few modifications to support the latest version of Rudiments. This version requires Rudiments 0.36.

Rudiments - 0.36 is out

Rudiments 0.36 is available for general consumption.

This release features several compatibility-related updates, mostly under the hood.

  • Various things work on various old SCO systems as well as Ultrix now.
  • MSVC projects/solutions have been added and the classes that build cleanly have been included.
  • All .C files were renamed to .cpp for compatibility with MSVC.
  • Several changes were made to improve compatibility with MSVC.
  • Methods that gave access to internal structures (such as pthread_mutex_t's or statfs's) all take and return void pointers now.

SQL Relay 0.45 requires Rudiments 0.36, so if you're updating SQL Relay, make sure to update Rudiments as well.

Tuesday, January 17, 2012

Rudiments 0.35 is out

An updated version of Rudiments is now available.  This release features some minor bug fixes and a few new methods.  Nothing all that significant.  However, the new features are required by SQL Relay 0.44 so if you're upgrading SQL Relay, upgrade Rudiments as well.

SQL Relay 0.44 is out

This release introduces a host of new features.

  • The MySQL Drop-In Replacement Library has been greatly improved and updated to support MySQL 5.2 and the MySQL statement API.
  • The SQL Relay client API's support new methods to get databases, tables and columns, returning lists similar to those returned by "show databases", "show tables" and "describe table" queries.
  • Bind Variable Translation is now supported.  If your queries have ?'s in them but you want to aim them at a database that uses colon-delimited queries, or vice-versa, SQL Relay can remap the variables automatically.
  • SQL Relay can now fake transaction blocks.  If you are migrating from a DB that is in autocommit mode by default and requires "begin" or "start" queries to begin a transaction to a database that is always in a transaction, SQL Relay can fake the behavior of the first database and make the new database appear to support transaction blocks.
  • The sqlrsh command line client now supports bind variables.
  • selectDatabase()/getCurrentDatabase() methods have been added to all API's, providing a database agnostic method for switching schemas/db's.
  • The fakebinds connect string option has been replaced by a parameter at the instance level and all databases support faking of input binds now.
  • getLastInsertId() has been added to all API's.  If the database supports it natively then the native function is used.  If the database doesn't support it then a stored procedure can be specified to fake it.
  • A set of queries can be run at the beginning and end of each client session now.
This is all in addition to the standard number of bug fixes and optimizations.