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

Sunday, November 10, 2013

Consulting and Development Services

Just a reminder...

Consulting, Support and Integration Services for SQL Relay or other firstworks technologies are available and General Consulting and Software Development Services are also available.
  • I have expertise in:
    • C/C++ on Linux/Unix
    • Embedded Linux systems
    • Database-Driven Applications
    • System and platform migration
    • Systems and applications for small businesses
  • And experience with:
    • Java, PHP, Python, C#, Visual Basic and other languages.
    • Windows and Mac OS X
So how can I help you?
  • Need help converting your existing apps to use SQL Relay?
  • Need help developing new apps that use SQL Relay?
  • Need a feature that SQL Relay doesn't currently provide?
  • Need SQL Relay to run on a new platform?
I'm your guy.
  • Need a custom app for your small business?
  • Need a legacy app migrated to a modern platform?
  • Need to squeeze some life out of an old app or older Unix platform?
  • Need to integrate some odd set of dissimilar applications?
  • Need help with an Embedded Linux app?
I've done plenty of that, all of that, for clients all over the world.  I might be your guy.

Send me an email or give me a ring.  For contact info, click here.