Sunday, December 28, 2014

SQL Relay 0.58 is out

SQL Relay version 0.58 is now available!

Windows Support

The most significant feature of this release is preliminary support for the SQL Relay server on Windows.  The client API's and command line programs have long supported Windows, but in this release, it's possible to run the SQL Relay server on Windows as well.

This is a preliminary release,it currently only supports Oracle, and there are some quirks, but it's a good first step.

Running the SQL Relay server on Windows is much the same as on Linux or Unix, just run sqlr-start as described in the documentation.  The biggest quirk is that it doesn't run as a service yet.  You must currently run it from a console window and if you close the console window, it kills the processes.  Other quirks: sqlr-status doesn't work yet and the various loadable modules such as password encryption, authentication and logging don't work yet.  Also, only Oracle databases are currently supported.  You can't run SQL Relay on Windows, against Sybase, DB2, MySQL, PostgreSQL, SQLite, MS Access or MS SQL Server yet.

Building SQL Relay on Windows is tricky and currently requires the right versions of things to be in the right places (as described in the documentation) so if you want to try SQL Relay on Windows, I recommend the SQL Relay Binary Distribution for Windows, available on the downloads page for a small fee.

Also, if you are running a 64-bit OS then use the 64-bit version of SQL Relay.  The 32-bit version doesn't perform well on 64-bit systems.  The issues it has appear to affect other applications as well though, and don't appear to be specific to SQL Relay.

Configuration and Init Improvements

Another significant new feature is support for an sqlrelay.conf.d directory.  As of this release, in addition to the sqlrelay.conf file, all configuration files in the sqlrelay.conf.d directory are also processed.  This makes it possible to keep instance configurations separate.

The init script and sqlr-start program have also been updated.  Rather than running sqlr-start with each instance listed in /etc/sqlrelay or /etc/sysconfig/sqlrelay, the init script now just runs sqlr-start without specifying an instance.  If run without specifying an instance, sqlr-start now digs through the configuration files and starts all instances with enabled="yes" attributes.

Together, these updates make configuration much cleaner, but in particular make it much easier for third party applications to install a configuration and cause an instance to start at boot.

Threaded Listener

The sqlr-listener can run in either multi-process or multi-thread mode and now runs in multi-thread mode by default, which is much faster and lighter weight.

PHP Improvements

PHP ini files are now installed to enable the PHP and PHP PDO modules.  It's no longer necessary (on modern platforms, at least) to manually enable them.

PHP PDO Improvements

The PHP PDO driver features many improvements.

The connect-string now includes resultsetbuffersize, dontgetcolumninfo and nullsasnulls parameters.  The debug connect-string parameter can be set to a file name to send debug information to a file instead of to the browser.

Output bind variables can be bound to streams now.  It is, for example, possible to fetch a blob directly into a file, using an output bind variable.

Samat Yusupov contributed a set of driver-methods for ending, suspending and resuming sessions.  They have been incorporated and improved a little, as described in the documentation.

Perl DBI Improvements

The Perl DBI driver is much improved as well.

Support for the maxrows parameter to fetchall_arrayref has been added.  The begin_work method works now.  The get_info method works properly now.

RowCacheSize and RowsInCache attributes work correctly now.  The entire result set is now buffered by default (as is the case with other SQL Relay client API's) unless the RowCacheSize is set.

A custom ext_SQLR_Debug database handle attribute now allows debug to be turned on or off or sent to a file.

Type, length, precision and scale attributes can now be set for bind variables.  Output bind BLOBs and CLOBs  work now.

The ParamValues, ParamTypes and ParamArrays attributes work correctly now.

The driver works correctly with very old versions of Perl DBI now too.

Lots of fixes.

SAP ASE Support

SAP/Sybase ASE 16.0 is supported now.  The configure script detects it properly.  Version 16.0 revealed a few bugs that have been fixed too.

PostgreSQL Improvements

It is now possible to connect to PostgreSQL databases over SSL using the sslmode option.

JMeter  Support

The ODBC driver has been improved sufficiently to work with JMeter via the JDBC-ODBC bridge.  JMeter can now be used to benchmark all tiers of the application directly.  This is helpful in determining whether a performance problem lies in the database, SQL Relay, or the app.

Changes Under the Hood

Internally, quite a few things were restructured.  A lot of tightly coupled code has been decoupled and refactored into frameworks with plugins.  This ought to be invisible to the end user, but should enable much more modular development in the future.

Bug Fixes

A slew of bugs have been fixed.

Some are significant:
  • Oracle JDK 7 and 8 are now detected correctly on Debian/Ubuntu systems.
  • Blobs work when faking input binds now.
  • Everything ought to compile cleanly with clang now.
  • DB2 output bind BLOBs aren't truncated any more.
Others only occurred in obscure circumstances, but I guess they're still significant if they happened to you:
  • sqlrsh doesn't lose the timezone when binding dates any more.
  • The PostgreSQL drop-in replacement library had buggy implementations of PQreset, PQresetStart, PQresetPoll.  Those are fixed.
  • The MySQL drop-in replacement library had implementations of mysql_row_seek, mysql_row_tell., mysql_stmt_row_seek and mysql_stmt_row_tell that didn't work on platforms with 32-bit void pointers.  Those are fixed.
  • Subtle bugs related to keeping track of the total row count in the sybase and freetds connections have been fixed.
  • Old versions of Perl (5.00x) are supported now.
  • An obscure bug that caused DB2 output bind integers to be copied out incorrectly sometimes has been fixed.
  • A very obscure bug that could cause Oracle connections to crash sometime later if an output bind cursor was used to fetch blob data has been fixed.

Rudiments 0.49 is out

Rudiments version 0.49 is now available.

Plenty of good stuff in this release: much improved support for Windows, some refactoring of the signal-handling classes, unification of the passwdentry and shadowentry classes into a single userentry class, improvements when compiling with clang, and a slew of subtle bug fixes.

Full changelog follows:
  • combined passswdentry and shadowentry classes into userentry class
  • updated file::sync() to use FlushFileBuffers directly
  • implemented sys::sync(), reboot(), halt() and shutdown() for windows
  • implemented various sys::getXXX() methods for windows
  • added "detached" option to process::spawn()
  • crash-related methods in process class catch SIGABRT, SIGFPE, SIGILL, SIGBUS, SIGIOT, SIGEMT and SIGSYS in addition to SIGSEGV now
  • shutdown-related methods in process class catch SIGQUIT and SIGHUP in addition to SIGINT and SIGTERM now
  • on windows, signal classes now support catching, sending/raising SIGABRT, SIGFPE, SIGILL, SIGINT, SIGSEGV and SIGTERM and sending/raising SIGKILL
  • disabled threads outright on OSR5
  • fixed threads detection for solaris 2.6
  • codetree bails properly at end of string now
  • fixed a break-related condition that could cause codetree to loop indefinitely
  • added support for a beginning-of-line character to codetree
  • added support for recursive breaks to codetree
  • made charstring::compareIgnoringCase null-safe for platforms that don't have str(n)casecmp
  • improved Windows support in signal classes
  • -Wno-overloaded-virtual is now set when clang is used
  • fixed some subtle kqueue-related bugs
  • added sys/byteorder.h and htonll/ntohll for solaris 11
  • fixed endian-detection macros for solaris 10 and older
  • added support for SIGLARM and signalmanager::alarm() on windows
  • improved windows implementation of semaphoreset
  • added sys::signalsInterruptSystemCalls()
  • added process::supportsFork()

Wednesday, November 12, 2014

Windows Progress...

SQL Relay has classically run on Unix and Linux but not Windows. A few years ago I got the C/C++ API working on Windows and added a C# API and .NET data provider. I got a few of the command-line programs working too, but that was about all you could do on Windows for quite a while. A few months ago when I got the Perl, Python and PHP API's working on Windows along with the rest of the command line clients, but still, only the client side software worked.

Since then I've made quite a bit of progress getting the server working too. And tonight things really started to come together...

That's the SQL Relay server running on Windows, connected to a database running on Linux, accessed by sqlrsh, also running on Windows.

And it can be accessed remotely too.

That's the same SQL Relay server running on Windows, accessed by sqlrsh running in a separate Linux VM.

There are a number of things that don't work yet like sqlr-stop and various plugins, and there are several things that just haven't been tested yet, but the big things are coming along nicely. I expect to be able to announce preliminary support for the SQL Relay server software on Windows in the next release.

Tuesday, October 28, 2014

oracle-jvm error...

I'm not sure how long ago this happened, but there are now lots of different JVM's available for Debian and Ubuntu systems: openjdk-6, openjdk-7, openjdk-8, oracle-jvm-7 and oracle-jvm-8. Possibly more...

SQL Relay 0.57 has no trouble detecting the openjdk's, but doesn't properly detect oracle-jvm's and the build fails. I ran into this error trying to build on a Raspberry PI running Raspbian, but a few people have reported the same error on Debian and Ubuntu sytems.

There are 3 workarounds:

Disable Java

If you're not using SQL Relay with java then you can add --disable-java to the configure command and SQL Relay won't even try to build against oracle-jvm.

Replace Oracle JVM with OpenJDK

This may be tricky. I'm not sure which packages have a hard dependency on oracle-jvm as opposed to just needing some version of Java. Your mileage may vary.

Patch the Configure Script

Edit the configure script in the SQL Relay source distribution and look for a line like:

   for i in `ls -d /usr/java/jdk* /usr/java/j2sdk* /usr/local/jdk* 2> /dev/null` /usr/java 
/usr/local/java `ls -d /usr/local/openjdk* /usr/pkg/java/openjdk* 2> /dev/null` `ls -d /usr/lib64/jvm/java 2> /dev/null` 
`ls -d /usr/lib64/jvm/java-1.8* 2> /dev/null` `ls -d /usr/lib64/jvm/java-1.7* 2> /dev/null` `ls -d /usr/lib64
/jvm/java-1.6* 2> /dev/null` `ls -d /usr/lib/jvm/java 2> /dev/null` `ls -d /usr/lib/jvm/java-1.8* 2> /dev/null` `ls -d 
/usr/lib/jvm/java-1.7* 2> /dev/null` `ls -d /usr/lib/jvm/java-1.6* 2> /dev/null` /System/Library/Frameworks
/JavaVM.framework/Versions/Current /usr /usr/local

and replace it with these lines:

   for i in `ls -d /usr/java/jdk* /usr/java/j2sdk* /usr/local/jdk* 2> /dev/null` \
    /usr/java \
    /usr/local/java \
    `ls -d /usr/local/openjdk* /usr/pkg/java/openjdk* 2> /dev/null` \
    `ls -d /usr/lib64/jvm/java 2> /dev/null` \
    `ls -d /usr/lib64/jvm/java-1.8* 2> /dev/null` \
    `ls -d /usr/lib64/jvm/java-1.7* 2> /dev/null` \
    `ls -d /usr/lib64/jvm/java-1.6* 2> /dev/null` \
    `ls -d /usr/lib64/jvm/jdk-7-* 2> /dev/null` \
    `ls -d /usr/lib64/jvm/jdk-8-* 2> /dev/null` \
    `ls -d /usr/lib/jvm/java 2> /dev/null` \
    `ls -d /usr/lib/jvm/java-1.8* 2> /dev/null` \
    `ls -d /usr/lib/jvm/java-1.7* 2> /dev/null` \
    `ls -d /usr/lib/jvm/java-1.6* 2> /dev/null` \
    `ls -d /usr/lib/jvm/jdk-7-* 2> /dev/null` \
    `ls -d /usr/lib/jvm/jdk-8-* 2> /dev/null` \
    /System/Library/Frameworks/JavaVM.framework/Versions/Current \
    /usr \

One of those three solutions should work.

The source has been updated and it's fixed in CVS. The next release will include the fix too.

Saturday, October 11, 2014


It appears that I forgot to run "make distclean" before building that last round of tar.gz and zip files for both Rudiments and SQL Relay. So, if you downloaded last week (the week of October 6th, 2014), or the week before, then you may have had trouble building. Configure probably ran, but make might not have made anything.

Who knows what platform the binaries that were in there ran on? Fedora 20 x64 maybe... Maybe not though.


I just updated the tar.gz and zip files with clean versions. I also downloaded and verified them this time. They ought to be correct.

Thanks Florin... If you hadn't pointed it out, I might never have noticed.

Saturday, October 4, 2014

SQL Relay - 0.57 is out

SQL Relay 0.57 is now available!

Windows Support

Support for Windows is much improved in this release. The command line clients all work now - sqlrsh, sqlr-export, sqlr-import, etc. The C/C++, C# and ADO.NET API's have worked for a while, but are now joined by native API's for Perl, Python, PHP and Java as well as drivers for Perl DBI, Python DB and PHP PDO.

It is possible to build all of this on Windows using MS Visual Studio or MS Visual Studio Express but it is currently quite difficult and requires specific versions of things to be installed in the right places. PHP is especially difficult to work with. In fact, I'm honestly not sure how 3rd party developers are expected to develop drivers for Windows. I had to shoehorn the headers in manually from the source code.

However... The SQL Relay Binary Distribution for Windows is now available (for a few bucks) and provides installers for binary versions of the above-mentioned software.

So... in addition to just accessing SQL Relay from command line clients, SQL Relay applications, including web-based applications, can now be developed for Windows in C, C++, C# (and other .NET languages), Perl, Python, PHP and Java.

Give it a try, let me know if you run into trouble.

Oracle "describe table" Improvements

In sqlrsh if you run "describe tablename" it returns info about the columns of the table - name, type, size, etc. This is accomplished by a call to the getColumnList() method of the C++ API. All SQL Relay API's support this method, and it has been improved.

For oracle tables, if a column is a primary, unique or foreign key, that information is now given. In previous releases it was not. This is a fairly expensive operation, as it turns out, so there is a config parameter to disable looking up keys. See disablekeylookup=yes/no in the oracle connection string in the SQL Relay Configuration Reference for more info.

Also, if you run "describe" on a synonym to a table in another schema, it will now return information about that table. In previous releases, only tables could be described.

Rudiments Improvements

The Rudiments library that SQL Relay depends on has been updated to randomize the list of hosts returned by DNS server when using round-robin DNS. If you are using round robin DNS to distribute SQL Relay clients over a pool of SQL Relay servers, then the behavior may different than in previous releases. In particular, if one host is down, then rather than slamming the next host in the list with all of the downed host's traffic, load will be distributed over the remaining hosts. This feature was added to work around an issue with many recent-ish implementations of getaddrinfo().

Rudiments has also been updated to support kqueue, epoll, port_create, /dev/poll, poll and select in its listener class. There should be no concerns now about large numbers of clients queuing up on the listener, or with listener performance with regard to using select internally.

See the rudiments release announcement for more information on these updates.

Bug Fixes and Minor Improvements

This release features a few bug fixes and minor improvements too.

The Python API's getRowDictionary method had a bug where None's were being returned as 0's when getNullsAsNones was set. That's fixed now.

Sensible errors are now returned when the format of a numeric bind variable is incorrect when using mysql, firebird or oracle. An error was fixed that could cause mysql connection to loop up if alphanumeric bind variable names were used instead of numbers. A bind-variable translation bug was fixed that could cause variables not to be translated when multiple formats are used in the same query and one of them is the correct format too.

The --with-system-libtool configure option was broken but it's fixed now.

Minix 3.3.0 is now supported. At least the client side is. Ie. you can access Oracle from Minix 3.3.0 using sqlrsh and SQL Relay running on another system. The server-side stuff doens't work yet. The Minix kernel doesn't support semaphores by default and Minix 3.3.0 ships without kernel source. I'm not sure if it supports semaphores at all though, so maybe that's moot.

The drop-in replacement library for MySQL had bugs with its implementations of mysql_row_seek() and mysql_row_tell(). Those are fixed now.

The configure script properly detects Maria DB on Ubuntu 14.04.1 now.

Give it a try and report any bugs that you find!

Friday, October 3, 2014

Rudiments - 0.48 is out

Rudiments 0.48 is now available!

This release has a few fairly important features.

Random Number Generation

The randomnumber class has been updated to support CryptGenRandom (on Windows), arc4random, random_r, rand_r, lrand48_r, random, rand and lrand48. It will use whichever of those it can find, in that order. The class has static and non-static members now. When the non-static members are used, the class can be seeded once and numbers (or scaled numbers) can be generated over and over without explicitly re-seeding.

Inet Socket Clients

Several improvements have been made to the inetsocketclient class.

Internally, inetsocketclient uses either getaddrinfo() or some variant of gethostbyname(), depending on what's available.

First, a bug was fixed that caused timeouts not to work on systems that don't have getaddrinfo().

Second, it seems that the behavior of getaddrinfo() has changed over the years and a workaround has been introduced to deal with some issues that the new behavior causes...

When round-robin DNS is used, gethostbyname() and getaddrinfo() fetch the entire list of IP addresses that the host name resolves to. The inetsocketclient's connect() method gets this list and tries to connect to each of these IP's, in the order that they were returned by gethostbyname()/getaddrinfo(). When round-robin DNS is used, the order of the IP's in the list is rotated with each DNS request. This can be used to implement a simple load-balancing scheme.

"Recent" (I'm not sure how recent) implementations of getaddrinfo() have begun sorting the IP's, defeating round-robin DNS entirely. Some implementations have a /etc/gai.conf file that allow a sortv4=no directive, but many do not. Some implementations randomize the list of IP's. Some implementations return them in the order that the DNS server returned them in.

Rudiments has no way of knowing what the behavior of getaddrinfo() is, so now, by default, the list of IP addresses is randomized. For the sake of consistency, it's randomized whether it uses getaddrinfo() internally or some variant of gethostbyname(). Methods have been added to the class to enable or disable this behavior too.

Randomization provides a benefit over round-robin DNS as well. With round-robin DNS, when a host goes down, the next host in the list will receive all of the downed host's traffic. With randomization, traffic is distributed evenly over the hosts that are still up.

listner class

The listener class has been refactored entirely to match the paradigms established by kqueue, epoll, /dev/poll, etc. It now supports kqueue, epoll, port_create, /dev/poll, poll and select internally as well, and will use whichever is available, in that order.

In the previous release, support was added for kqueue, epoll and poll but the implementation still did some inefficient things like rebuilding the list of file descriptors to listen on before each iteration. The list is only rebuilt if a file descriptor is added or removed now.

Minix 3.3.0

Minix 3.3.0 came out since the last release and it's now supported.

General Bug Fixes and Improvements

Some general bug fixes and improvements were implemented as well. Cygwin builds ought to work correctly now. Setting file permissions ought to work natively on Windows now too.

Monday, July 14, 2014

SQL Relay - 0.56 is out

SQL Relay version 0.56 is now available!

You might want to get this one. A subtle memory leak is fixed in this release. If you really can't upgrade and need a fix for a previous release, contact me and we can work something out.

Amazing new features:

Perl, PHP, Python and Java API's all build and work on Windows. You still have to build them from source though, and it's a pain. If you need Windows binaries, contact me and we can work something out.

Intelligent defaults have been added for a lot of the configuration parameters. The sqlrelay.conf file can be much more stripped down now. I also added a Configuration Guide that walks you through various configurations from minimal to complex. Enjoy.

The PHP PDO API now has a custom statement attribute PDO::SQLRELAY_ATTR_GET_NULLS_AS_EMPTY_STRINGS that can be set to true or false to cause it to return NULL values as NULLs or empty strings.

New parameters have been added for connecting to MySQL via SSL. See the Configuration Reference for more detail.

Amazing bug fixes:

The PHP PDO driver handles float values correctly now. Previously it was converting them to strings. It also handles NULL integers correctly now rather than converting them to 0's.

There was a bug in the sqlr-scaler that could cause it to use a noticeable amount of the CPU when sitting idle. That's fixed now.

Some installations of Oracle instant client might not have been properly detected before, but they are now.

There was a bug that caused problems with DB2 7.X. It's fixed now.

sessionhandler="thread" and listenertimeout!="0" ought to work now. This is still sketchy and experimental though.

Internal stuff:

A lot of code has been reorganized internally. Many things have been modularized and simplified. Frameworks have been built and some things have been re-implemented using them. This is ongoing. More of this will be done over the next few releases.

Full ChangeLog follows:

  • removed VERSION from perl api bootstrap
  • reorganized and simplified perl api code
  • got perl api working on windows with ActivePerl
  • perl api uninstall cleans up better now
  • got python api working on windows with ActivePython
  • consolidated php include tweaks
  • got java api working on windows
  • updated pdo driver to convert float fields to strings rather than ints
  • updated pthread test to match rudiments pthread test
  • moved everything done by children of sqlwriter interface into individual translations
  • added plugin-based authentication framework
  • reorganized code tree a bit
  • reworked default values to enable more minimal configurations
  • updated configuration docs and added configuration guide
  • fixed cast issue with firebird 1.5
  • added result set translation framework and re-implemented date translation using it
  • fixed millisecond->nanosecond confusion in sqlr-scaler
  • fixed subtle oracle instantclient version detection bug
  • added null input bind support to sqlrsh
  • fixed php pdo driver to return null rather than 0 for null integer fields and bind null rather than 0 for null integer input binds
  • added attribute to return nulls as nulls or empty strings to php pdo
  • added parameters for connecting to mysql via ssl
  • added a few tweaks to support DB2 < 8.0
  • fixed bad return value type in python getConnectionPort method
  • fixed sessionhandler="thread" with listenertimeout!="0"

Rudiments - 0.47 is out

For immediate release!

Rudiments 0.47 is now available.

This update to rudiments fixes a few obscure things including a memory leak in the datetime class that could cause SQL Relay to chew up memory (very slowly) and a potential null-dereference in the xmldomnode class. Not sure how I ever missed those bugs before, given how often I use those classes, but at least they're fixed now.

Another significant update in this release is internal support for poll, epoll and kqueue. In the past, the listener class used select exclusively, but select has some issues. Most notably, attempting to listen on "large numbers" (more than 1024 on most systems) of file descriptors could cause undefined problems (references beyond the end of a 1024-member array). Now, if the platform supports epoll (like most modern linux systems) or kqueue (like most modern BSD systems) then those will be used in favor of select. If the platform supports neither epoll nor kqueue, but supports regular poll, then poll will also be used in favor of select. /dev/poll is not yet supported on solaris yet, but it's on the TODO list. Also, the poll/epoll/kqueue implementations aren't optimized yet. They still do inefficient things like rebuild the list of file descriptors every time, which has to be done with select, but they are a bit faster than select, and safe for large numbers of file descriptors.

Two classes have been renamed to be more consistent - variablebuffer is now bytebuffer and rawbuffer is now bytestring. Hey, it's pre-1.0 software. Things get renamed.

The linkedlist class has been refactored and a singlylinkedlist class has been added. Both now have sort methods now too: selectionSort() and heapSort(). Writing them took me back to my data structures class in college. selectionSort() is slow but uses no additional storage. heapSort() is much faster but uses N additional storage. Choose!

I also fixed an obscure bug on windows involving file::open() with O_CREAT but not O_EXCL.

Solaris 2.5.1 is also supported, just in case anyone still uses that!

Full ChangeLog follows:

  • fixed possible null-dereference in xmldomnode::safeAppend
  • added charstring::inSetIgnoringCase
  • xmldomnode::setAttributeValue does nothing when the null node is referenced now
  • fixed a codetree bug that could cause indentation to attempt to go negative when using an unsigned number
  • added a configure test to see if -Wno-format is needed
  • configure tests for mlockall/munlockall attempt link now
  • added sys/types.h before sys/un.h in sys/un.h test for older freebsd
  • implemented stubs for pure virtual methods of client and server classes so instances of them could be created to attach already-open file descriptors to
  • unified usage of select/poll and prefer poll
  • pushed all select/poll-related code into listener class
  • removed problematic and unused useListener-related methods in filedescriptor class
  • epoll is used in place of select/poll for systems that support it
  • kqueue is used in place of select/poll for systems that support it
  • consolidated getpagesize() calls to use sys::getPageSize()
  • added detection and support for __vsnprintf for platforms that have that instead of vsnprintf
  • added cancel and raiseSignal methods to the thread class
  • fixed datetime memory leak
  • added xmldomnode::clone method to clone a node
  • renamed variablebuffer to bytebuffer
  • renamed rawbuffer to bytestring
  • refactored linkedlist
  • added singlylinkedlist class
  • added sort, detach, move and insert methods to linkedlist classes
  • fixed file::open using O_CREAT without O_EXCL on windows

Friday, June 27, 2014

Retro: SQL Relay/DB2 6.1

Keeping the Retro theme going, I recently got SQL Relay running against DB2 6.1 Personal Edition through a nearly equally contrived path as getting it running against Personal Oracle 7.

Again, I did it for fun, but the exercise demonstrates the versatility of both SQL Relay and Rudiments.

DB2 6.1 PE - 8. Access From Fedora 20 x64

In this case, I accessed DB2 6.1 running on Windows NT from Fedora 20 via an instance of SQL Relay running against an old instance of DB2 7.2 on Redhat 6.2 (that is, pre-Fedora Redhat 6.2).

Saturday, April 12, 2014

Retro: SQL Relay/Oracle7

I recently got SQL Relay running against Personal Oracle 7.2.2. I did it for fun, but it demonstrates a valuable feature of SQL Relay: Proxying - accessing databases from unsupported systems.

fedora 20 x64 - Oracle 7.2.2

Exercises like that often fall under the category of hobbyist retrocomputing but the reality is... old systems refuse to die.

Maybe you have an old app still lurking around, running against Oracle7 and you can't upgrade the DB for some reason. Your modern apps all run against Oracle 12c, but it would sure be great if they could pull data out of that old database. Unfortunately OCI stopped supporting Oracle7 about 10 years ago. You might even have an old copy of Oracle 8i lying around. If it wasn't so expensive, you'd have chucked it for all the good it does you today.

Relay to the rescue!

In my experiment, I accessed Oracle7 from Fedora 20 via an instance of SQL Relay running on Redhat 6.2 (not RHEL, but old, pre-Fedora Redhat 6.2) using OCI from Oracle 8.1.7. Of course, I wouldn't recommend using Redhat 6.2 in production, but you could run a still-supported operating system like Solaris 8 or 9 x86 in a VM and get the same result.

Monday, March 31, 2014

SQL Relay Binary Distro

In case anyone hasn't noticed yet...

A binary distribution of SQL Relay is now available alongside the source distribution.

SQL Relay has lots of dependencies, and building it from source can be a good bit of work. Now, for a couple of bucks, you can now download pre-built RPMs for Fedora, CentOS and OpenSUSE systems.

The distro comes with a README detailing how to install the RPMs. Source code is included as well, for good measure.

The CentOS binaries were built on CentOS, but ought to run on any RHEL derivative, including RHEL proper, CentOS, Scientific Linux, and other distros.

Binaries aren't available for Windows yet, but they will be in the next release.

Sunday, March 30, 2014

SQL Relay - 0.55 is out

SQL Relay 0.55 is now available for download.


This release focused a lot on improved LOB support. Fetching LOB columns and binding LOBS for input and output are supported way better than they used to be. LOB support didn't exist at all for Firebird before, but LOBs are fully supported now. inputBindClob/Blob works on all databases now. On DB2, LOB columns have no size limitations any more and the maximum size of an output bind LOB is now configurable.

Oracle has long had parameters for configuring the size of buffers for fetching result sets. These parameters now exist for Sybase, FreeTDS, DB2 and ODBC connections now as well. In the past, you had to modify the source code to change them for those DB's. Now all you have to do is update the sqlrelay.conf file.

On that note, I made the default fetch buffer sizes consistent across Oracle, Sybase, FreeTDS, DB2 and ODBC. In particular, the maximum size of a column defaulted to 32K for Oracle and 4K for other DB's. 4K was once the maximum size of a varchar/varbinary on those DB's, but at some point (probably long ago) they were increased to 32K. So, 32K is the default size when using those DB's now too.

I also added a subtle but important update that helps out a lot when using a replicated database or database cluster with nodes behind a load balancer, such as Oracle RAC, or MySQL behind Ultramonkey. In the past, if a node went down, SQL Relay connections would detect this, re-log, and get distributed over other nodes. SQL Relay wouldn't know when the node came back up though, and would ignore it until restarted. Now, as of this release, connections to nodes behind a load balancer re-login periodically, giving them the opportunity to be re-distributed over all available nodes.

Some bugs have been fixed too. Probably the most significant was a cursor leak. If cursors were configured to scale dynamically, then with some databases, they wouldn't be freed when scaling back down. Some other bugs were fixed too though. If you were having some odd kind of trouble, give this release a try.

The complete ChangeLog follows:

  • fixed a sql translation bug related to exists clauses
  • added deployment projects for windows
  • tweaked make.batch install target to install 32-bit files under C:\Program Files (x86)
  • added a timeout parameter to db2 and odbc connect strings
  • fixed a bug that could cause a connection's ttl to fail because a semaphore was left signalled when another connection's ttl expired
  • updated handoff="proxy" code to work with sessionhandler="thread"
  • replaced waitpid call in sqlrscaler with rudiments equivalent
  • got the server-side stuff to compile on windows
  • added configurable fetch buffer sizes to sybase, freetds and db2
  • connections re-distribute themselves periodically if the database is behind a load balancer now
  • fixed a bug that caused sqlrsh to incorrectly interpret internal commands with leading whitespace when run from a script or on the command line
  • fixed a bug that caused cursors not to be completely cleaned up after when dynamic scaling is used
  • fixed a bug that caused the response timeout to be handled improperly
  • added response timeout command to sqlrsh
  • added support for clobs/blobs with firebird
  • fixed a blob-related bug with sqlite
  • added support for blob input binds with db2
  • added support for clob/blob output binds with db2
  • improved clob/blob fetching with db2
  • inputBindClob/Blob methods work with all db's now
  • added -Wno-unknown-pragmas flag (if it it supported) to java build for solaris 8
  • updated default item buffer sizes to 32768 for sybase, freetds, db2 and odbc
  • applied Gerhard Lausser's patch to improve Oracle instantclient detection

Rudiments - 0.46 is out

Rudiments 0.46 is now available for download.

This release continues the march toward good support for Windows - lots of updates and fixes. I also made a minor change to one of the logger class helpers. ...and added a waitpid() wrapper. ...and some general cleanup. ...and maybe a few other minor changes. But that's all there is new in this release.

Full ChangeLog below:

  • Updated filedestination::open to take a permissions parameter.
  • Added deployment projects for windows.
  • Tweaked make.batch install target to install 32-bit files under C:\Program Files (x86).
  • Wrapped waitpid() in process class.
  • Added #define _WINSOCKAPI_ before windows.h includes to prevent redefinition of winsock defs later.
  • Fixed O_RDONLY detection on windows in file::openInternal.
  • Fixed shared memory segment sizing error on windows.
  • Fixed O_CREAT without O_EXCL bug on windows.
  • Fixed backwards loaddependencies flag in dynamiclib for windows.
  • Added file::eightDotThree.
  • Added charstring::stripSet and updated charstring::strip to return true/false if stripping occurred or not.
  • Removed stubs for unimplemented classes.

Thursday, January 30, 2014

SQL Relay - 0.54 is out

SQL Relay version 0.54 is now available!

This release mainly fixes a lot of subtle or obscure bugs. It also fixes problems detecting a bunch of things on a bunch of odd platforms - for example TCL is now properly detected on multi-arch systems like Debian GNU/Linux and 64-bit Sybase and DB2 are properly detected now too. The most significant new features though are a much improved PHP PDO driver and experimental support for a multi-thread (as opposed to multi-process) listener.

To use the new multi-threaded listener, set sessionhandler="thread" (as opposed to sessionhandler="process", which is the default) in the sqlrelay.conf file. There are a few caveats to using this feature though. Currently listenertimeout="0" and handoff="proxy" are not supported when using threads. Those issues will be remedied in a future release.

A good bit of behind-the-scenes work has been done as well. One of the long term goals is multi-protocol support. Ie. the ability to aim a MySQL (or other) app directly at SQL Relay rather than having to port the app to the SQL Relay API or use the drop-in replacement library. The groundwork for this has been laid in this release. The client-server communication has been separated and abstracted and the SQL Relay native API has been refactored as an implementation of the interface.

I'm also working towards complete support on Windows. Currently the C++, C, C# and ADO.NET client API's work on Windows but none of the rest of the API's, nor any of the server components work yet. A lot of the Rudiments library has been ported to Windows in it's most recent release though, and virtually all of the Posix API calls have been replaced with Rudiments calls. There are a few left in the ODBC connection class, but that's all. So, basically when the rest of Rudiments is ported, SQL Relay should work on Windows, and there are only a few things left to port. Some of them are tricky though, like signals, so it could still be a little while.

The full ChangeLog for this release follows:

  • fixed bugs that caused problems when unopened result sets were closed
  • added protocol identification phase to client/server protocol
  • split client protocol handling out into its own class and abstracted it
  • updated oracle code not to relogin when dropping tables unless a temp table with an "on commit preserve rows" was run
  • updated server to run table-drop and session-end queries after the session-end commit/rollback, as some queries on some platforms (including drop-table on oracle) cause an implicit commit and the rollback needs to be run first
  • replaced gettimeofday calls with calls to datetime::getSystemDateAndTime
  • fixed a bug in slow query logger that caused it to display the wrong query time
  • a few mingw32 tweaks
  • fixed a few PDO driver bugs related to affected row counts and last insert id
  • implemented PDO exceptions
  • implemented support for PDO::ATTR_EMULATE_PREPARES by using substitution variables
  • added foundrows and ignorespace options to mysql connect string
  • fixed a bug where affectedRows() would return -1 for MySQL selects instead of the same value as rowCount()
  • updated sqlr-connection code not to get db host name and ip address at startup unless logging is enabled to work around issues where people fail to put the db host name in dns
  • fixed a subtle bind format translation bug that caused errors when translating from oracle-style binds to mysql-style if the varaibles were out of order with the placeholders in the query
  • fixed bugs in the mysql and postgresql connection code that assumed that the bind variables were bound in order, independent of their names
  • resolved a paradox involved in detecting whether the current mysql query needs to have its bind variables faked or not
  • added support for client-side debug-to-file
  • added debug-to-file support to command line clients
  • tweaked server processes to use process::spawn instead of system() and process::fork()/process::exec()
  • added another error string to detect down mysql database
  • added support for a multi-threaded listener
  • fixed a bug that could cause a crash when logging connection-level errors
  • added back x64-detection so sybase and db2 libraries will be detected properly
  • added support for forward-only cursors to the PDO driver
  • fixed a bug that could cause one (and only one) dynamically spawned connection daemon to ignore its ttl
  • added support for get/set result set buffer size to the PDO driver
  • updated configure test for xsubpp
  • updated configure test for tcl to support multi-arch
  • updated configure test for python to support multi-arch
  • added custom PDO driver attributes to access db type, version, host name, ip address and bind format
  • added custom PDO driver attribute to get/set current database
  • added custom PDO driver attribute to set whether or not to get column info when fetching the result set
  • updated db2 to re-login when it sees error -30081

Rudiments - 0.45 is out

Rudiments version 0.45 is now available!

Support for microseconds has been added to the datetime class. There's a new thread class too. A significant bug that caused all socket connect() calls with timeouts to fail has been also been fixed.

The most significant feature of this release though is that most of the classes have been ported to Windows. There are still some that haven't but a lot have.

Here's the full Changelog.

  • got the directory class working on windows
  • got the semaphoreset class working on windows
  • implemented passwd/groupentry::getSid and an internal id-name-sid map for windows
  • removed meaningless groupentry::getPassword
  • improved filesystem class implementation for Windows significantly
  • added a test for the memorymap class
  • got the memorymap class implementation working on Windows
  • got the sharedmemory class working on windows
  • added support for microseconds to the datetime class
  • build-tweaks for mingw32
  • added process::spawn that maps more cleanly on windows than fork/exec
  • added thread class
  • added send/receiveSocket methods to filedescriptor class for systems like Windows that distinguish between sockets and other file descriptors
  • improved file class implementation for Windows
  • fixed a bug that caused all connect-with-timeout calls to fail
  • fixed a bug that caused permissions::setFilePermissions to fail on directories