SQL Relay - 0.50 is out

SQL Relay version 0.50 is now available.

This release mainly fixes a few issues relevant to users of Microsoft SQL Server.

The following client C++ API methods, equivalent methods and functions in other API's and equivalent commands in sqlrsh now work properly with SQL Server via FreeTDS:

• getDbVersion()
• getDatabaseList()
• getTableList()
• getColumnList()

The FreeTDS code has been updated as well. FreeTDS (and the TDS protocol in general) doesn't support fetching multiple result sets simultaneously through the same connection. Any attempt to do so results in the error: "Attempt to initiate a new Adaptive Server operation with results pending." SQL Relay has always attempted to work around this by caching the entire result set on the client by default but there was a subtle bug that could cause the error to still occur under certain circumstances. That bug has been fixed and everything now works as expected. I added an entry to the FAQ about it as well.

Parameters have been added to sqlrelay.conf for reformatting dates in the result set as well. Many databases provide limited or no control over the formatting of dates and times in the result set. This is problematic when migrating from one database to another if the databases have different date/time formats and the application expects a particular format. To help remedy this the datetimeformat, dateformat, timeformat and dateddmm parameters have been added to allow dates in the result set to be reformatted by SQL Relay before they are sent to the client.

Various other bug fixes and updates have also been made:

• fixed the sqlrserver-config script to return data for libsqlrserver rather than libsqlrconnection
• added a test for SQLROWSETSIZE for odbc that doesn't define it
• fixed a NULL dereference in custom_nw logger
• fixed an uninitialized variable (sqltr) in sqlrcontroller class
• fixed a memory leak in the freetds version-checking code
• added new search paths and default prefix for Syllable OS
• added support for null-terminated result bind lists to the mysql drop-in library so apps can optionally bind fewer columns than come back in the result set
• added configure tests for php-config-"version"

SQL Relay - 0.49.1 is out

This release fixes two bugs:

• sqlrserver-config returns the proper library now
• there was a NULL-dereference in the custom_nw logger module

If you don't use either of those things then there's no need to upgrade.

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.

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.

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

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:

stmtcachesize=20

(Or some size other than 20)

...to 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

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!