Version 0.64 of SQL Relay, the powerful connection pool, load balancer, query router, and database proxy, is now available.
This release features support for Informix databases, support for remote and centralized configuration files, a better configure script for building from source on Windows, improvements to the drop-in replacement library for PostgreSQL, and a "softttl" parameter for dynamically spawned database connections.
Informix
SQL Relay now supports Informix databases. It can compile against fairly modern versions (probably 10.X and up) and access versions older than that. Informix may not be the most modern database, but it has a large user base and free developer versions have been available from IBM for a while now. It's high time SQL Relay supported it.
To configure SQL Relay to talk to an Informix database, use an sqlrelay.conf file like this:
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
<instance id="informixtest" dbase="informix">
<users>
<user user="test" password="test"/>
</users>
<connections>
<connection
string="informixdir=/opt/informix;servername=ol_informix1210;
db=testdb;user=testdbuser;password=testdbpass"/>
</connections>
</instance>
</instances>
(Actually, the connection tag should be all-on-one-line, I just had to split it up to it would fit on the page)
Then you can access it with sqlrsh like:
[dmuse@windows7 ~]$ sqlrsh -id informixtest
SQLRShell - Version 0.64
Connected to: localhost:9000 as test
type help; for help.
0> use sysmaster;
0> show tables;
tabname
=================
flags_text
logmessage
smi_build_status
sysactptnhdr
sysadtinfo
sysaqt
...
systxptab
sysuserthreads
sysvplst
sysvpprof
sysxatab
Rows Returned : 196
Fields Returned : 196
System time : 8651
0>
Informix!
Remote and Centralized Configuration FilesThe -config option that all of SQL Relay's command line programs take can now be a local file, directory, or URL.
For example:
sqlr-start -id oracleexample -config \ /home/myuser/mysqlrelay.conf sqlr-start -id oracleexample -config \ file:///home/myuser/mysqlrelay.conf sqlr-start -id oracleexample -config \ dir:///home/myuser/mysqlrelay.conf.d sqlr-start -id oracleexample -config \ http://configserver.mydomain.com/sqlrconfig/sqlrelay.conf sqlr-start -id oracleexample -config \ http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/sqlrelay.conf sqlr-start -id oracleexample -config \ http://[/usr/local/firstworks/etc/sqlruserpwd.txt]@configserver.mydomain.com/sqlrconfig/sqlrelay.conf
(of course, the commands can be all-on-one-line, I just split them up here for readability)
In the case of a dir:// URL, all files under the given directory will be parsed as config files.
In the last http:// URL, the file between the square brackets is a user-password file, which can contain a single line like:
myuser:mypassword
Using a file rather than embedding the user/password in the URL helps keep the user/password out of process listings and init scripts.
If Rudiments (the library upon which SQL Relay is built) was built with support for libcurl, the https, ftp, scp, and sftp urls can be used as well.
The -config option can now take a comma-separated list of URL's too. For example:
sqlr-start -id oracleexample -config \ /home/myuser/mysqlrelay-1.conf,\ /home/myuser/mysqlrelay-2.conf sqlr-start -id oracleexample -config \ /home/myuser/mysqlrelay-1.conf,\ /home/myuser/mysqlrelay-2.conf,\ dir:///home/myuser/mysqrelay.conf.d sqlr-start -id oracleexample \ -config /home/myuser/mysqlrelay-1.conf,\ /home/myuser/mysqlrelay-2.conf,\ dir:///home/myuser/mysqrelay-1.conf.d,\ dir:///home/myuser/mysqrelay-2.conf.d sqlr-start -id oracleexample \ -config http://configserver1.mydomain.com/sqlrconfig/sqlrelay.conf,\ http://configserver2.mydomain.com/sqlrconfig/sqlrelay.conf,\ http://configserver3.mydomain.com/sqlrconfig/sqlrelay.conf
(again, I just split the commands over multiple lines for readability)
Any combination of local files, directories and remote URL's can be specified. Files will be processed in the order that they are specified.
SQL Relay now supports "link files" too. Instead of containing an XML configuration, a "link file" just contains the URL's of other configuration files. For example:
# oracle configuration http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/oracle.conf # sap/sybase configuration http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/sap.conf # db2 configuration http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/db2.conf
Any url that would be valid on the command line is valid in a link file. Link files may simply refer to other link files too.
Link files can be used to centralize configuration. For example, if you have several SQL Relay servers, rather than distributing configuration files across the servers, you could create an identical sqlrelay.conf file on each of them like:
http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/sqlrelay.conf
And then, on configserver.mydomain.com, host an sqlrelay.conf file like:
http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/oracle.conf http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/sap.conf http://myuser:mypassword@configserver.mydomain.com/sqlrconfig/db2.conf
The files oracle.conf, sap.conf, and db2.conf could then be hosted by and maintained on that server as well.
See the SQL Relay Configuration Guide for more information. In particular, the sections on Remote Configuration Files and Link Files.
A Better Configure Script for WindowsIn the past, the Windows configure.vbs script didn't do much configuring. It basically detected what compiler and version of Windows you were using, and set all the right options for those, but didn't do much to detect different versions of databases or languages.
It does a lot more now. It's not as full featured as its linux/unix counterpart yet, but it's a lot better than it was in the past. It can now detect various versions of databases and languages, provided they're installed in reasonably standard locations. It also has options allowing you to specify locations and versions, in case you did install something in an odd location, and it provides feedback about what it could and couldn't find, like its linux/unix counterpart.
For example:
C:\Users\dmuse\src\sqlrelay>cscript /nologo configure.vbs
***** Platform ***************
Visual C++ Version: 16
Visual C++ Architecture: x64
Windows Version: 0x0601
******************************
***** Oracle *****************
Oracle includes... /I "C:\Program Files\Oracle\instantclient_12_1\sdk\include"
Oracle libs... /LIBPATH:"C:\Program Files\Oracle\instantclient_12_1\sdk\lib\msvc
" oci.lib
******************************
***** MySQL ******************
MySQL includes... /I "C:\Program Files\MySQL\MySQL Connector.C 6.1\include"
MySQL libs... /LIBPATH:"C:\Program Files\MySQL\MySQL Connector.C 6.1\lib" libmys
ql.lib
******************************
***** PostgreSQL *************
PostgreSQL includes... /I "C:\Program Files\PostgreSQL\9.4\include"
PostgreSQL libs... /LIBPATH:"C:\Program Files\PostgreSQL\9.4\lib" libpq.lib
******************************
***** SAP/Sybase *************
SAP/SYBASE includes... /I "C:\SAP\OCS-16_0\include"
SAP/SYBASE libs... /LIBPATH:"C:\SAP\OCS-16_0\lib" libsybct64.lib libsybblk64.lib
libsybcs64.lib
******************************
***** ODBC *******************
ODBC includes...
ODBC libs... user32.lib gdi32.lib odbc32.lib odbccp32.lib
******************************
***** DB2 ********************
DB2 includes... /I "C:\Program Files\IBM\SQLLIB\include"
DB2 libs... /LIBPATH:"C:\Program Files\IBM\SQLLIB\lib" db2api.lib
******************************
***** Firebird ***************
Firebird includes... /I "C:\Program Files\Firebird\Firebird_2_5\include"
Firebird libs... /LIBPATH:"C:\Program Files\Firebird\Firebird_2_5\lib" fbclient_
ms.lib
******************************
***** Informix ***************
Informix includes... /I "C:\Program Files\IBM Informix Software Bundle\incl\cli"
Informix libs... /LIBPATH:"C:\Program Files\IBM Informix Software Bundle\lib" ic
lit09b.lib
******************************
***** C# *********************
******************************
***** Perl *******************
Perl prefix... C:\Perl64
Perl version... 520
******************************
***** Python *****************
Python prefix... C:\Python27
Python version... 27
******************************
***** Ruby *******************
Ruby prefix... C:\Ruby
Ruby version... 2.2.0
******************************
***** PHP ********************
PHP prefix... C:\PHP
******************************
***** Java *******************
Java prefix... C:\Program Files\Java\jdk1.8.0_25
******************************
***** TCL ********************
TCL prefix... C:\Tcl
******************************
***** node.js ****************
node.js prefix... C:\Program Files\nodejs
******************************
***** Summary ***********************************************
Version : 0.64
APIs : C/C++ yes Perl yes
Python yes Ruby yes
PHP yes Java yes
PHP PDO yes ODBC yes
TCL yes C# yes
node.js yes
Databases : Oracle8 yes MySQL yes
PostgreSQL yes SAP/Sybase yes
ODBC yes DB2 yes
Firebird yes Informix yes
*************************************************************
If you expected a Database or API that doesn't show up in the Summary
then the configure script probably couldn't find a package it needed to
build it. You can manually specify package locations using command line
options.
Type: cscript /nologo configure.vbs --help for a list of options.
Drop-In Replacement Library for PostgreSQL
The drop-in replacement library for PostgreSQL has been improved somewhat. PQprepare/PQexecPrepared and bind variables are now supported.
"softttl"There's also a new, "softttl" parameter for dynamically spawned connections.
From the Configuration Reference:
softttl - The total number of seconds that a dynamically spawned connection intends to live. When the connection notices that it has been alive for this number of seconds, it voluntarily shuts down, but it only checks after each client session. Thus, the connection will ignore this parameter until it has handled at least one client session, and it could live longer than this time if a client session takes a long time, or if it sits idle for a long time between client sessions. Setting this parameter to 0 disables it. Defaults to 0 (disabled).
It's just another way to prevent dynamically spawned connections from living too long. It can be used as an alternative to maxsessioncount.
Of course, this release features a few bug fixes too.
Full ChangeLog follows:
- fixed bug in sqlrsh that caused it to filter out non-ascii characters
- added informix connection module
- added shutdown loop detection in sqlr-listener
- fixed dbhostname/dbipaddress requests
- added bind variable format validation to php pdo driver
- added softttl parameter
- added db/language detection to configure.vbs
- added passwordfunction parameter to "sqlrelay" authentication module
- added -disable-crash-handler command line option to server processes
- postgresql connection module PQclear()'s the result of PQprepare() now, fixing a memory leak
- updated postgresql connection module to handle invalid bind variables properly, and without crash
- added various missing postgresql functions to the postgresql drop-in library, most notably PQprepare, PQexecPrepared, PQexecParams
- simplified sqlrpaths/sqlrcmdline classes a bit
- renamed sqlrconfigfile to sqlrconfig
- implemented a modular configuration framework
- config files can be loaded over protocols supported by libcurl now or http if rudiments compiled withougt libcurl support
- config files can now be "link files" which just refer to other local config files, local config directories, or remote config files
- updated sqlr-start and sqlr-scaler not to pass the default -config value if sqlr-start wasn't explicitly started with -config
- fixed a bug that could cause sqlrsh to act funny if the last character prior to the command delimiter was a single or double quote
- fixed a stacked result-set translation module bug
- applied George Carrette's patch to fix potential PDO null-dereferences
