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.