The SQL Relay configuration file (usually /usr/local/firstworks/etc/sqlrelay.conf) may be edited by hand.

<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">

<instances>

        <!-- Regular SQL Relay Instance -->
        <instance id="example" port="9000" socket="/tmp/example.socket" dbase="oracle8" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no" timequeriessec="-1" timequeriesusec="-1" fakeinputbindvariables="no" translatebindvariables="no" isolationlevel="read committed" ignoreselectdatabase="no">
                <users>
                        <user user="user1" password="password1"/>
                        <user user="user2" password="password2"/>
                        <user user="user3" password="password3"/>
                </users>
                <session>
                        <start>
                                <runquery>alter session set nls_date_format='MM-DD-YYYY HH24:MI:SS'</runquery>
                        </start>
                        <end>
                                <runquery>alter session set nls_date_format='DD-MON-YYYY'</runquery>
                        </end>
                </session>
                <connections>
                        <connection connectionid="db1" string="user=exampleuser1;password=examplepassword1;oracle_sid=EXAMPLE1;" metric="1" behindloadbalancer="no"/>
                        <connection connectionid="db2" string="user=exampleuser2;password=examplepassword2;oracle_sid=EXAMPLE2;" metric="3" behindloadbalancer="no"/>
                        <connection connectionid="db3" string="user=exampleuser3;password=examplepassword3;oracle_sid=EXAMPLE3;" metric="5" behindloadbalancer="no"/>
                        <connection connectionid="db4" string="user=exampleuser4;password=examplepassword4;oracle_sid=EXAMPLE4;" metric="6" behindloadbalancer="no"/>
                </connections>
        </instance>


        <!-- Query Router/Filter -->
        <instance id="routerexample" port="9001" socket="/tmp/example.socket" dbase="router" connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" maxsessioncount="1000" endofsession="commit" sessiontimeout="600" runasuser="nobody" runasgroup="nobody" cursors="5" authtier="listener" handoff="pass" deniedips="" allowedips="" debug="none" maxquerysize="65536" maxstringbindvaluelength="4000" maxlobbindvaluelength="71680" idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no">
                <users>
                        <user user="user1" password="password1"/>
                        <user user="user2" password="password2"/>
                        <user user="user3" password="password3"/>
                </users>
                <router>
                        <!-- send all queries for table1 to host1 -->
                        <route host="host1" port="9000" socket="" user="host1user" password="host1password">
                                <query pattern="^\s*select\s+.*\s+from\s+table1"/>
                                <query pattern="^\s*insert\s+into\s+table1"/>
                                <query pattern="^\s*update\s+table1"/>
                                <query pattern="^\s*delete\s+from\s+table1"/>
                                <query pattern="^\s*drop\s+table\s+table1"/>
                                <query pattern="^\s*create\s+table\s+table1"/>
                        </route>
                        <!-- filter out any queries for table2 -->
                        <filter>
                                <query pattern="^\s*select\s+.*\s+from\s+table2"/>
                                <query pattern="^\s*insert\s+into\s+table2"/>
                                <query pattern="^\s*update\s+table2"/>
                                <query pattern="^\s*delete\s+from\s+table2"/>
                                <query pattern="^\s*drop\s+table\s+table2"/>
                                <query pattern="^\s*create\s+table\s+table2"/>
                        </filter>
                        <!-- send any other queries to host2 -->
                        <route host="host2" port="9000" socket="" user="host2user" password="host2password">
                                <query pattern=".*"/>
                        </route>
                </router>
        </instance>

</instances>

The file format is complicated and is best explained with an example.

As you can see, it's an XML file. Below is its DTD.

<!ELEMENT instances (instance*)>
<!ELEMENT instance (users,connections?,router?)>
        <!ATTLIST instance id CDATA "defaultid">
        <!ATTLIST instance addresses CDATA "0.0.0.0">
        <!ATTLIST instance port CDATA "9000">
        <!ATTLIST instance socket CDATA "/tmp/sqlrelay.socket">
        <!ATTLIST instance mysqladdresses CDATA "0.0.0.0">
        <!ATTLIST instance mysqlport CDATA "9001">
        <!ATTLIST instance mysqlsocket CDATA "/tmp/mysqlsqlrelay.socket">
        <!ATTLIST instance dbase (oracle8|mysql|postgresql|sqlite|freetds|sybase|odbc|db2|firebird|mdbtools|router) "oracle8">
        <!ATTLIST instance connections CDATA "1">
        <!ATTLIST instance maxconnections CDATA "1">
        <!ATTLIST instance maxqueuelength CDATA "0">
        <!ATTLIST instance growby CDATA "1">
        <!ATTLIST instance ttl CDATA "60">
        <!ATTLIST instance maxsessioncount CDATA "0">
        <!ATTLIST instance endofsession (commit|rollback) "commit">
        <!ATTLIST instance sessiontimeout CDATA "60">
        <!ATTLIST instance runasuser CDATA "nobody">
        <!ATTLIST instance runasgroup CDATA "nobody">
        <!ATTLIST instance cursors CDATA "5">
        <!ATTLIST instance maxcursors CDATA "1300">
        <!ATTLIST instance cursors_growby CDATA "5">
        <!ATTLIST instance authtier (listener|connection|listener_and_connection|database) "listener">
        <!ATTLIST instance handoff (pass|reconnect) "reconnect">
        <!ATTLIST instance deniedips CDATA "">
        <!ATTLIST instance allowedips CDATA "">
        <!ATTLIST instance debug (none|listener|connection|sqltranslation|listener_and_connection|listener_and_connection_and_sqltranslation|connection_and_sqltranslation) "none">
        <!ATTLIST instance maxquerysize CDATA "65536">
        <!ATTLIST instance maxstringbindvaluelength CDATA "4000">
        <!ATTLIST instance maxlobbindvaluelength CDATA "71680">
        <!ATTLIST instance idleclienttimeout CDATA "-1">
        <!ATTLIST instance maxlisteners CDATA "-1">
        <!ATTLIST instance listenertimeout CDATA "0">
        <!ATTLIST instance reloginatstart (yes|no) "no">
        <!ATTLIST instance timequeriessec CDATA "-1">
        <!ATTLIST instance timequeriesusec CDATA "-1">
        <!ATTLIST instance fakeinputbindvariables (yes|no) "no">
        <!ATTLIST instance translatebindvariables (yes|no) "no">
        <!ATTLIST instance isolationlevel CDATA "read committed">
        <!ATTLIST instance ignoreselectdatabase (yes|no) "no">

<!ELEMENT users (user*)>
<!ELEMENT user EMPTY>
        <!ATTLIST user user CDATA "user">
        <!ATTLIST user password CDATA "password">

<!ELEMENT session (start,end)>
<!ELEMENT start (runquery*)>
<!ELEMENT end (runquery*)>
<!ELEMENT runquery (#PCDATA)>

<!ELEMENT connections (connection*)>
<!ELEMENT connection EMPTY>
        <!ATTLIST connection connectionid CDATA "defaultid">
        <!ATTLIST connection string CDATA "user=scott;password=tiger">
        <!ATTLIST connection metric CDATA "1">
        <!ATTLIST connection behindloadbalancer (yes|no) "no">

<!ELEMENT router ((route*|filter*)*)>
<!ELEMENT route (query*)>
        <!ATTLIST route host CDATA "">
        <!ATTLIST route port CDATA "">
        <!ATTLIST route socket CDATA "">
        <!ATTLIST route user CDATA "">
        <!ATTLIST route password CDATA "">

<!ELEMENT filter (query*)>

<!ELEMENT query EMPTY>
        <!ATTLIST query pattern CDATA "">

So what do all these tags do?

The instances tag is just the root tag. The sqlrelay.conf file should have only one of these tags surrounding all other tags.

Each instance tag defines an instance of sqlrelay. An instance consists of one sqlr-listener and one or more sqlr-connection daemons. You can define as many of these as you like.

The users tag surrounds the list of users that may connect to the instance.

Each user tag defines a user/password combination that may be used to connect to the instance.

The session tag surrounds a set of queries to be run at the start and end of each session. The entire session block is optional.

The start tag surrounds a set of queries to be run at the the start of each session and the end tag surrounds a set of qeries to be run at the end of each session. Both are optional.

The runquery tag surrounds a query to be run at the beginning or end of a session. Any number (including 0) of these may be specified.

The connections tag surrounds the list of sqlr-connection daemon configurations used by the instance.

Each connection tag defines a sqlr-connection daemon configuration. In most cases, there will be only 1 of these tags. In cases where clustered or replicated databases are used, the sqlr-connection daemons may need to be able to connect to multiple machines. In that case, there would be more than 1 line here. See SQL Relay Load Balancing and Failover for more information.

The router tag sets up the instance to route and filter queries to other SQL Relay instances. If this tag is present, the instance should contain no connections tags. See Routing and Filtering Queries with SQL Relay for detailed information about configuring SQL Relay to route and filter quries.

The route tag designates an instance of SQL Relay to route queries to. It should contain a set of query tags defining which queries to route to the designated instance. This tag is only valid inside a router tag.

The filter tag defines a set of queries to be filtered out (ie. if a client sends one of these queries, the SQL Relay server won't run it against the database and will simply return an error). This tag is only valid inside a router tag.

The query tag defines a regular expression which the route and filter tags use to match queries.

Below is a description of the attributes for each tag.

• instance
  • id - the ID of this instance
  • addresses - A comma-delimited list of addresses that the sqlr-listener and sqlr-connection daemons should bind to. If absent or set to 0.0.0.0, they will bind to all addresses.
  • port - the port the listener should listen on (note, if multiple instances are configured with the same port or socket, they cannot be run at the same time)
  • socket - the unix socket (filename) the listener should listen on (note, if multiple instances are configured with the same port or socket, they cannot be run at the same time)
  • dbase - the type of database the connection daemon should connect to. Should be one of: oracle8, mysql, postgresql, sqlite, freetds, sybase, odbc, db2, firebird or mdbtools
  • connections - the number of sqlr-connection daemons to start up when using sqlr-start.
  • maxconnections - the maximum number of sqlr-connection daemons to scale to.
  • maxqueuelength - the size the queue of waiting clients has to grow to before more connections will be spawned. See the faq for a note about settng maxqueuelength to 0.
  • growby - the number of connections that will be started up when new connections are spawned.
  • ttl - the amount of time that an idle connection will stay alive after being spawned dynamically (does not apply to connections spawned by sqlr-start).
  • maxsessioncount - The number of client sessions that a connection will handle after being spawned dynamically before shutting down, even if it never goes idle. Setting this to 0 will cause the connection to only shut down if it does idle for longer than ttl seconds. This parameter does not apply to connections spawned by sqlr-start.
  • endofsession - the command to issue when a client ends its session or dies. Should be either commit or rollback.
  • sessiontimeout - if a client leaves a session open for another client to pick up but no client picks it up, the session will time out after this interval.
  • runasuser - the user to run the sqlr-listener, sqlr-connections and sqlr-scaler as.
  • runasgroup - the group to run the sqlr-listener and sqlr-connections and sqlr-scaler as.
  • cursors - the number of open database cursors to maintain
  • authtier - where to authenticate, see note below
  • handoff - method for handing off a client from listener to connection, can be one of: reconnect or pass, see note below
  • deinedips - a regular expression indicating which IP addresses will be denied access (for example, to deny access to all clients: deniedips=".*")
  • allowedips - a regular expression indicating which IP addresses will be allowed access, overriding deniedips (for example, to allow access to clients from the 192.168.2.0 and 64.45.22.0 networks: allowedips="(192\.168\.2\..*|64\.45\.22\..*)")
  • debug - instructs the listener and/or connection daemons to log debugging information when sqlr-start is run with the -debug option, see note below
  • maxquerysize - sets the maximum query length that the SQL Relay server will accept, if a client tries to send a longer query, the server will close the connection (defaults to 64k bytes)
  • maxstringbindvaluelength - sets the maximum length of a string bind value that the SQL Relay server will accept, if the client tries to send a longer string bind value, the server will close the connection (defaults to 32k bytes)
  • maxlobbindvaluelength - sets the maximum length of a LOB/CLOB bind value that the SQL Relay server will accept, if the client tries to send a longer LOB/CLOB bind value, the server will close the connection (defaults to 70k bytes)
  • idleclienttimeout - sets the number of seconds that a client can sit idle while logged into the SQL Relay server before it will be disconnected (defaults to -1 which means forever)
  • maxlisteners - When a client connects to the listener but no connections are available, a child listener is forked off to wait for an available connection. Since these can pile up and consume system resources, this parameter allows you to limit the number of child listeners that can be running simultaneously before an error will be returned to the client. Set to -1 (the default) to run without a limit.
  • listenertimeout - sets the number of seconds that a listener will wait for an avaialable connection before giving up, 0 (the default) means wait forever
  • reloginatstart - When SQL Relay starts up, it attempts to log into the database. If this parameter is set to yes, then if the login fails, SQL Relay will fork off into the background and attempt to log in over and until it succeeds or until it is shut down. If this parameter is set to no, then if the login fails, SQL Relay will print out an error and exit.
  • timequeriessec and timequeriesusec - These parameters allow you to log queries which take longer than a certain amount of time to run. If either is set to -1, the default, no logging will occur. If both are set to a number other than -1, then any query which takes longer than timequeriessec seconds and timequeriesusec milliseconds will be written out to a log file. The log file will be in the location: /usr/local/firstworks/var/sqlrelay/debug/sqlr-connection-id-querylog.pid where id is the ID of the instance and pid is the process ID of the connection daemon that ran the query. Setting both parameters to 0 will cause all queries to be logged. Setting either to -1 will cause no queries to be logged.
  • fakeinputbindvariables - Instead of binding variables using the native database API, SQL Relay can fake input bind variables by rewriting the query and substituting values directly into it. Setting this parameter to "yes" enables this functionality. This is useful if you are using an old version of a database that doesn't support bind variables natively or if your are using a modern version but your app was originally written when the database didn't support bind variables natively or when SQL Relay didn't support native bind variables with that database. If enabled, bind variables must be specified in the query as a colon, followed by a name or number (eg. :var1 or :1) unless the translatebindvariables parameter is also set to "yes".

  • translatebindvariables - There is no standaradized format for bind variables across databases. Some databases use question marks to identify bind variables, others use colon, dollar-sign or at-signs, followed by either names or numbers. Setting this parameter to "yes" causes SQL Relay to remap the bind variables in a query to the native format for whatever database the query is being run against. This is useful when migrating from one database to another or when using fake binds against a database that doesn't support colon-delimited bind variables.
  • isolationlevel - Sets the transaction isolation level to the specified value. At the end of each client session, the isolation level will be reset to this value as well.
  • ignoreselectdatabase - Intructs sqlrelay to ignore selectDatabase() calls from the client. If you want to point an instance at a test database and a program at the instance but the program manually selects the database to use, effectively aiming itself back at production, this is useful in preventing it from doing so.
• user
  • user - the user name required to connect to the listener
  • password - the password required to connect to the listener
• connection
  • connectionid - the ID of this connection
  • string - the database connect string the connection daemon should use
  • For oracle8 databases, the connect string syntax is "user=USER;password=PASSWORD;oracle_sid=ORACLE_SID;oracle_home=ORACLE_HOME;nls_lang=NLS_LANG;autocommit=yes/no;fetchatonce=FETCHATONCE;maxselectlistsize=MAXSELECTLISTSIZE;maxitembuffersize=MAXITEMBUFFERSIZE;faketransactionblocks=YESORNO;droptemptables=YESORNO;lastinsertidfunction=LASTINSERTIDFUNCTION"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • oracle_sid: Which Oracle SID to use. Optional if the ORACLE_SID environment variable is set. Overrides the ORACLE_SID environment variable. This parameter can either be an SID name corresponding to an entry in the tnsnames.ora file such as:

      oracle_sid=ora1

      or a tnsnames-style expression such as:

      oracle_sid=(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost.localdomain)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = ora1)))

    • oracle_home: The base directory of the oracle installation to use. Optional if the ORACLE_HOME environment variable is set. The SID will be looked up in $ORACLE_HOME/network/admin/tnsnames.ora
    • nls_lang: The NLS_LANG to use. Optional if the NLS_LANG environment variable is set. Overrides the NLS_LANG environment variable.
    • autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
    • fetchatonce: The number of rows that SQL Relay fetches from the database in each round trip. Defaults to 10. (see here for more info on this parameter)
    • maxselectlistsize: The maximum number of columns that can be fetched in a query. Defaults to 256. (see here for more info on this parameter)
    • maxitembuffersize: The maximum size of a non-lob field. Fields longer than that will be truncated. Defaults to 32768. (see here for more info on this parameter)
    • faketransactionblocks: Some databases are in autocommit mode until you explicitly start a transaction with a "start" or "begin" statement. Oracle is always in a transaction though, unless autocommit is turned on. Setting this parameter to "yes" causes SQL Relay to put the database in autocommit mode until a "start" or "begin" statement is issued, and then put it back in autocommit mode when a commit or rollback is issued. In effect, emulating the behavior of databases which require an explicit "start" or "begin".
    • droptemptables: In most databases, temporary tables are dropped at the end of the client session. In Oracle however, the rows may be deleted but the table itself remains. Setting this parameter to "yes" causes any temporary tables that were created during an SQL Relay client session, to be dropped when the session is over, in effect emulating the behavior or other databases.
    • lastinsertidfunction: Many databases support auto-increment columns (also called serial or identity columns) and after an insert into a table containing one, the id that was generated may be retrieved via some stored procedure call, api call or special variable. Oracle doesn't support auto-increment columns but they can be simulated using triggers and sequences. Trigger-sequence packages are often developed when migrating from a database that supports auto-increment columns to Oracle. When implementing a trigger-sequence package, it is possible to store the value that was most-recently fetched from the sequence in a package-local variable and provide a function to access it. This parameter allows you to specify that function so that a call to getLastInsertId() by a SQL Relay client will return whatever value is returned by that function.
  • For mysql databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;socket=SOCKET;fakebinds=FAKEBINDS;charset=CHARSET"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • db: The database to log into. Required.
    • host: The host to connect to. Required.
    • port: The port to connect to on a remote host. Optional, defaults to 3306.
    • socket: The unix socket to connect to. Optional if host and/or port are specified. Overrides host/port. host and port are optional if socket is specified.
    • fakebinds: MySQL 4.1.2 supports bind variables natively. Versions prior to 4.1.2 did not, and SQL Relay had to substitute the values of bind variables into the query itself (fake binds). In MySQL 4.1.2, bind variables are identified by ?'s in the query, while in versions prior to 4.1.2, bind variables are identified by :var1, :var2, :var3, etc. Converting queries and code which was written to use the older syntax to use MySQL 4.1.2 syntax can be a lot of work, so this parameter is provided to make code which was written to run against older versions of MySQL work against MySQL 4.1.2. This parameter may be set to "yes" (meaning allow only older syntax and fake binds) or "no" (meaning only allow MySQL 4.1.2 syntax and use native binds).
    • charset: The character set to translate data coming out of the database into. Excluding this parameter causes the data to be translated to whatever character set the MySQL client library was configured to use at compile time, probably latin1. Setting this to "binary" will cause the data not to be translated at all. This parameter is only supported when SQL Relay is compiled against version 5.0.7 or newer of the MySQL client library.
  • For postgresql databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;options=OPTIONS;typemangling=MANGLING;fakebinds=FAKEBINDS;charset=CHARSET;lastinsertidfunction=LASTINSERTDFUNCTION"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • db: The database to log into. Required.
    • host: The host to connect to. Required. If host begins with a slash (/) then it is assumed to be the directory containing a unix socket starting with .s.PGSQL.
    • port: The port to connect to on a remote host. Optional, defaults to 5432. If host refers to a unix socket, then port is the extension on the socket name (eg. /tmp/.s.PGSQL.5432).
    • options: Command line options to be sent to the server. Optional.
    • typemangling: If set to "yes" then column types are translated to standard types. If set to "lookup" then the pg_type table is queried at startup and column type names are returned as they appear in that table. By default (or if set to "no"), the type number is returned.
    • fakebinds: Postgresql 8 supports bind variables natively. Versions prior to 8 did not, and SQL Relay had to substitute the values of bind variables into the query itself (fake binds). In Postgresql 8, bind variables are identified by $1, $2, $3, etc. in the query, while in versions prior to 8, bind variables are identified by :var1, :var2, :var3, etc. Converting queries and code which was written to use Postgresql 7 syntax to use Postgresql 8 syntax can be a lot of work, so this parameter is provided to make code which was written to run against Postgresql 7 work against Postgresql 8. This parameter may be set to "yes" (meaning allow only Postgresql 7 syntax and fake binds) or "no" (meaning only allow Postgresql 8 syntax and use native binds).
    • charset: The character set to translate data coming out of the database into. Optional.
    • lastinsertidfunction: Many databases support auto-increment columns (also called serial or identity columns) and after an insert into a table containing one, the id that was generated may be retrieved via some stored procedure call, api call or special variable. Postgresql doesn't support auto-increment columns but they can be simulated using triggers and sequences. Trigger-sequence packages are often developed when migrating from a database that supports auto-increment columns to Postgresql. When implementing a trigger-sequence package, it is possible to store the value that was most-recently fetched from the sequence in a package-local variable and provide a function to access it. This parameter allows you to specify that function so that a call to getLastInsertId() by a SQL Relay client will return whatever value is returned by that function.
  • For sqlite databases, the connect string syntax is "db=DB;"
    • db: The filename of the database open. Required.
  • For freetds databases, the connect string syntax is "sybase=SYBASE;user=USER;password=PASSWORD;server=SERVER;db=DATABASE;charset=CHARSET;language=LANGUAGE;hostname=HOSTNAME;packetsize=PACKETSIZE;"
    • sybase: The directory containing the "interfaces" or "freetds.conf" file. Optional if the SYBASE environment variable is set. Overrides the SYBASE environment variable.
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • server: The entry in the "interfaces" or "freetds.conf" file which specifies host, port and other database connection parameters.
    • db: The database to log into. Required.
    • charset: The character set to use. Optional.
    • language: The language to use. Optional.
    • hostname: The host to connect to. Optional, overrides the host in the "interfaces" or "freetds.conf" file.
    • packetsize: The packetsize to use. Optional.
  • For sybase databases, the connect string syntax is "sybase=SYBASE;lang=LANG;user=USER;password=PASSWORD;server=SERVER;db=DATABASE;charset=CHARSET;language=LANGUAGE;hostname=HOSTNAME;packetsize=PACKETSIZE;"
    • sybase: The directory containing the "interfaces" or "freetds.conf" file. Optional if the SYBASE environment variable is set. Overrides the SYBASE environment variable.
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • server: The entry in the "interfaces" or "freetds.conf" file which specifies host, port and other database connection parameters.
    • db: The database to log into. Required.
    • charset: The character set to translate data coming out of the database into. Optional.
    • language: The language to use. Optional.
    • hostname: The host to connect to. Optional, overrides the host in the "interfaces" or "freetds.conf" file.
    • packetsize: The packetsize to use. Optional.
  • For odbc databases, the connect string syntax is "user=USER;password=PASSWORD;dsn=DSN;autocommit=yes/no;"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • dsn: The entry in the the odbcinst.ini which specifies host, port and other database connection parameters.
    • autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
  • For db2 databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;autocommit=yes/no;faketransactionblocks=YESORNO"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • db: The server instance to connect to.
    • autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
    • lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
    • faketransactionblocks: Some databases are in autocommit mode until you explicitly start a transaction with a "start" or "begin" statement. DB2 is always in a transaction though, unless autocommit is turned on. Setting this parameter to "yes" causes SQL Relay to put the database in autocommit mode until a "start" or "begin" statement is issued, and then put it back in autocommit mode when a commit or rollback is issued. In effect, emulating the behavior of databases which require an explicit "start" or "begin".
  • For firebird databases, the connect string syntax is "user=USER;password=PASSWORD;db=DATABASE;dialect=DIALECT;autocommit=yes/no;charset=CHARSET;faketransactionblocks=YESORNO;lastinsertidfunction=LASTINSERTIDFUNCTION"
    • user: The username SQL Relay should use to log into the database. Required.
    • password: The password SQL Relay should use to log into the database. Required.
    • db: The filename of the database open. Required.
    • dialect: The database dialect to use. Optional, defaults to 3.
    • autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
    • charset: The character set to translate data coming out of the database into. Optional.
    • faketransactionblocks: Some databases are in autocommit mode until you explicitly start a transaction with a "start" or "begin" statement. Firebird is always in a transaction though, unless autocommit is turned on. Setting this parameter to "yes" causes SQL Relay to put the database in autocommit mode until a "start" or "begin" statement is issued, and then put it back in autocommit mode when a commit or rollback is issued. In effect, emulating the behavior of databases which require an explicit "start" or "begin".
    • lastinsertidfunction: Many databases support auto-increment columns (also called serial or identity columns) and after an insert into a table containing one, the id that was generated may be retrieved via some stored procedure call, api call or special variable. Firebird doesn't support auto-increment columns but they can be simulated using triggers and sequences. Trigger-sequence packages are often developed when migrating from a database that supports auto-increment columns to Firebird. When implementing a trigger-sequence package, it is possible to store the value that was most-recently fetched from the sequence in a package-local variable and provide a function to access it. This parameter allows you to specify that function so that a call to getLastInsertId() by a SQL Relay client will return whatever value is returned by that function.
  • For mdbtools databases, the connect string syntax is "db=DATABASE;"
    • db: The filename of the database open. Required.
  • metric - a number that influences how many of this connection should be started
  • behindloadbalancer - Whether the database is really a pool of databases behind a load balancer or not. If the database is not a pool of databases behind a load balancer, then when a connection daemon determines that the database has gone down, it will cause all connection daemons connected to that database to log out and re-login. However, if the database is really a pool of databases behind a load balancer, and a connection daemon determines that the database has gone down, only that connection will log out and re-login as some or all of the remaining connections may not actually be connected to the same physical database machine, but rather to a clone which has not actually gone down.
• route
• host - The host that the SQL Relay server that queries will be routed to is running on.
• port - The port that the SQL Relay server that queries will be routed to is listening on.
• socket - The socket that the SQL Relay server that queries will be routed to is listening on. Only use this if the SQL Relay server that queries will be routed to is running on the same machine as the router.
• user - The user name to use to log into the SQL Relay server that queries will be routed to is running on.
• password - The password to use to log into the SQL Relay server that queries will be routed to is running on.
• query
• pattern - A regular expression. Any query matching this regular expression will be routed to the SQL Relay server specified in the enclosing route tag.

Most of the tag attributes are straightforward. The most complicated one are the metric attribute of the connection tag and the authtier attribute of the instance tag.

The metric attribute doesn't define how many connections are started, the connections attribute of the instance tag defines that. The metric attribute influences how many of the total connections will be of that connection type. The higher the metric relative to the other metrics, the more of that connection type will be started.

The client will send a user and password to the sqlr-listener and sqlr-connection daemons when it connects to them. The authtier attribute indicates which daemon will actually pay attention to that user and password.

If the authtier attribute is set to "listener", the sqlr-listener will compare the user/password to the list of user/passwords in the sqlrelay.conf file defined in the users tag and accept or reject the client connection. If the client is accepted, it is handed off to the sqlr-connection-database daemon which assumes that it has already been authenticated and does not perform its own authentication.

If the authtier attribute is set to "connection", the sqlr-listener will ignore the user/password and just hand off the client to the sqlr-connection-database daemon which will compare the user/password to the list of user/passwords in the sqlrelay.conf file and accept or reject the client connection.

If the authtier attribute is set to "listener_and_connection" then both daemons will perform the authentication. This is the most secure method and the default but is slower than "listener" or "connection" alone.

If the authtier attribute is set to "database", the sqlr-listener daemon will ignore the user/password and the sqlr-connection-database daemon may use database-specific methods for authenticating and proxying the user.

When SQL Relay is used with Oracle 8i/9i, the sqlr-connection-database daemon switches users without logging out. The sqlr-connection-database daemon must be configured to log into the database as a user that can proxy other users and the client must attempt to log in to SQL Relay as one of the users that can be proxied. See this document for more information including instructions for configuring Oracle.

When SQL Relay is used with any other database, it simply logs out and logs back in as a different user. This is quite ineffecient and defeats the value of keeping persistent database connections.

NOTE: authtier="database" can't be used in an instance where dbase="router". It's OK for the instances that the router uses to use authtier="database" but not the router instance itself. If authtier="database" is set on that instance, it will be overridden to authier="listener_and_connection".

When an SQL Relay client needs to talk to the database, it connects to a listener process which queues it up until a database connection daemon is available. When a daemon is available, the client is "handed off" to it. This "handoff" can be done in one of two ways. The client can disconnect from the listener and reconnect to the connection daemon, or the existing connection to the client can be passed from the listener to the connection daemon. These two methods are referred to as "reconnect" and "pass". "Reconnect" works on every platform. "Pass" works on most platforms but not all. SCO OpenServer and Linux kernels prior to 2.2 don't support "pass". Other platforms may not support "pass" as well but those certainly don't. If SQL Relay is being run on a different machine than its clients, "pass" is substantially faster than "reconnect" and more lightweight in any case. It should be used if possible.

If the debug attribute is not set to "none", when sqlr-start is run, it runs special versions of the sqlr-listener and sqlr-connection daemons which log debug information to files in /usr/local/firstworks/var/sqlrelay/debug. When the daemons start up, they print out the exact filename that they are logging to. This filename will be listener.PID or connection.PID where PID is the process ID of the daemon. Note that the sqlr-listener forks each time it gets a client connection and generates a seperate file for each forked process as well as one for the main process.

The debug attribute can be one of: "none", "listener", "connection" or "listener_and_connection". If "none" is specified, no debugging information is logged. If "listener" is specified, then only the listener daemon logs debug information. If "connection" is specified, then only the connection daemons log debug information. If "listener_and_connection" is specified, all daemons log debug information.