SQL Relay Configuration Reference

• Example
• Tags
• Attributes
• Advanced Configuration

Example

The SQL Relay configuration file is usually located at /usr/local/firstworks/etc/sqlrelay.conf

Additional configuration files may be created under the configuration directory, usually /usr/local/firstworks/etc/sqlrelay.conf.d

Files in the configuration directory may be given any name, but it is conventional to give them a .conf suffix.

Each configuration file may define any number of SQL Relay instances.

A fairly comprehensive example follows.

<?xml version="1.0"?>
<instances>
	<instance id="example" enabled="yes" dbase="oracle"
		connections="3" maxconnections="15" maxqueuelength="5" growby="1" ttl="60" softttl="0"
		maxsessioncount="1000" endofsession="commit" sessiontimeout="600"
		runasuser="nobody" runasgroup="nobody" cursors="5" maxcursors="10" cursors_growby="1"
		sessionhandler="process" handoff="pass" deniedips="" allowedips=""
		maxquerysize="65536" maxbindvars="256" maxbindnamesize="64" maxstringbindvaluesize="4000" maxlobbindvaluesize="71680"
		maxclientinfosize="512" maxerrorsize="2048"
		idleclienttimeout="-1" maxlisteners="-1" listenertimeout="0" reloginatstart="no"
		fakeinputbindvariables="no" translatebindvariables="no" isolationlevel="read committed"
		ignoreselectdatabase="no" waitfordowndatabase="yes">
		<listeners>
			<listener protocol="sqlrclient" port="9000" socket="/tmp/sqlrclient1.socket" addresses="0.0.0.0"/>
			<listener protocol="sqlrclient" port="9001" socket="/tmp/sqlrclient2.socket" addresses="0.0.0.0"/>
			<listener protocol="mysql" port="3306" socket="/tmp/mysql.socket" addresses="0.0.0.0"/>
			<listener protocol="postgresql" port="5432" socket="/tmp/postgresql.socket" addresses="0.0.0.0"/>
		</listeners>
		<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=ora1;"
				metric="1" behindloadbalancer="no"/>
			<connection connectionid="db2"
				string="user=exampleuser2;password=examplepassword2;oracle_sid=ora2;"
				metric="3" behindloadbalancer="no"/>
			<connection connectionid="db3"
				string="user=exampleuser3;password=examplepassword3;oracle_sid=ora3;"
				metric="5" behindloadbalancer="no"/>
			<connection connectionid="db4"
				string="user=exampleuser4;password=examplepassword4;oracle_sid=ora4;"
				metric="6" behindloadbalancer="no"/>
		</connections>
	</instance>
</instances>

Tags

• The instances tag is just the root tag. Each configuration file should have only one of these tags surrounding all other tags.
• Each instance tag defines an instance of SQL Relay. You can define as many of these as you like in each configuration file.
• The listeners tag surrounds the list of listener configurations used by the instance.
• The listener tag defines a listener configuration. In most cases, there will be only one of these tags. However, there could be more than one in cases where the instance is configured to listen on more than one port/socket/address combination, or more than one protocol.
• 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 database connection configurations used by the instance.
• Each connection tag defines a database connection configuration. In most cases, there will be only one of these tags. In cases where clustered or replicated databases are used, there would likely be more than one line here. See Load Balancing and Failover for more information.

Attributes

Below is a description of the attributes for each tag.

• instance
• id - The ID of this instance.
• enabled - Whether to auto-start this instance. Defaults to "no". If set to "yes", then this instance will be started when sqlr-start is run with no arguments (as it is in the init scripts which run at boot).
• dbase - The type of database the connection daemon should connect to. Should be one of: "oracle", "freetds", "sap", "db2", "informix", "mysql", "postgresql", "firebird", "sqlite", or "odbc". Defaults to "oracle". Note: For historical reasons, "oracle8" and "sybase" are also supported, and are synonymous with "oracle" and "sap". "mariadb" is also supported, and is synonymous with "mysql".
• connections - The number of database connections to open at startup. This may be set to any positive number or 0. Defaults to 5. (Note: implicitly limited to a compile time limit, which is configurable, but defaults to 4096.)
• maxconnections - The maximum number of database connections to scale up to. If this is set to a number lower than the value for the connections parameter then it will be automatically bumped up to the value set for connections. Defaults to the same value as connections. (Note: implicitly limited to a compile time limit, which is configurable, but defaults to 4096.)
• maxqueuesize - The size the queue of waiting clients has to grow to before more connections will be spawned. Defaults to 0.
• growby - The number of connections that will be started at a time when new connections are spawned. Defaults to 1.
• ttl - The number of seconds that a dynamically spawned connection will sit idle, waiting for a client, before giving up and shutting down. Setting this parameter to 0 causes each dynamically spawned connection to die immediately after handling one client session. Defaults to 60 (one minute).
• 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).
• maxsessioncount - The number of client sessions that a dynmically spawned connection will handle before voluntarily shutting down. Setting this to 0 disables it. Defaults to 0 (disabled).
• endofsession - The command to issue when a client ends its session or dies. Should be either "commit" or "rollback". Defaults to "commit".
• 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 number of seconds. Defaults to 600 (10 minutes).
• transactionmodel - Sets the transaction model that the connection daemon will use, overriding the database's native model. May be set to native (the database's native transaction model), none (no transactions), implicit (in a transaction when the session begins; commit/rollback implicitly starts a new transaction; autocommit on/off take effect immediately), explicit (not in a transaction when the session begins; begin required to start a new transaction; commit/rollback does not start a new transaction; autocommit on/off take effect immediately), explicit-deferred (like explicit, but while in a begin-initiated transaction, autocommit on takes effect at the next commit/rollback (deferred); while in an autocommit-off-initiated transaction, autocommit on takes effect immediately), or explicit-error (like explicit, but while in a transaction, autocommit on/off throw an error). Defaults to native. (Note: Prior to version 2.2.0, this attribute was named faketransactionblocks. faketransactionblocks="no" was equivalent to transactionmodel="native", and faketransactionblocks="yes" was equivalent to transactionmodel="explicit".)
• runasuser - The user to run the instance as. Note that a configuration file must be readable by this user and the various "run" directories (usually under /usr/local/firstworks/var/run/sqlrelay or /usr/local/firstworks/var/sqlrelay) must be writable by this user. If this parameter is set to a user other than the user who runs sqlr-start, then unless sqlr-start is run as root, it will not be possible to switch to this user and the following warning will be displayed: Warning: could not change user to user. Usually defaults to "nobody" when built from source and "sqlrelay" when installed from packages.
• runasgroup - The group to run the instance as. Note that a configuration file must be readable by this group and the various "run" directories (usually under /usr/local/firstworks/var/run/sqlrelay or /usr/local/firstworks/var/sqlrelay) must be writable by this group. If this parameter is set to a group other than a group that the user who runs sqlr-start belongs to, then unless sqlr-start is run as root, it will not be possible to switch to this group and the following warning will be displayed: Warning: could not change group to group. Usually defaults to "nobody" when built from source and "sqlrelay" when installed from packages.
• cursors - The number of database cursors to open and maintain for each database connection. More cursors may be opened if needed, but the pool of cursors will shrink back down to this size at the end of each client session. This may be set to any positive number or 0. Defaults to 1.
• maxcursors - The maximum number of database cursors that may be opened for a single client session. If this is set to a number lower than the value for the cursors parameter then it will be automatically bumped up to the value set for cursors. Defaults to 5, or the same value as cursors, whichever is greater.
• cursors_growby - The number of new cursors that will be opened at a time when new cursors are required. Defaults to 1.
• sessionhandler - Method used by the listener to handle a client session. Options are either "thread" (the default as of version 0.58) or "process". When a client connects to the listener, a child is forked to handle the connection. The child can be either a process or a thread. Threads should perform better but aren't supported on all platforms. Defaults to "thread" (as of version 0.58).
• handoff - Method for handing off a client from listener to connection, can be one of: "pass" or "proxy". 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 file descriptor of the connected client can be passed from the listener to the connection daemon, or the listener can proxy the client, ferrying data back and forth between it and the connection daemon. These two methods are referred to as "pass" and "proxy". "proxy" works on every platform. "pass" works on most platforms but not all. "pass" is faster and lighter than "proxy" and should be used if possible. Cygwin and Linux kernels prior to 2.2 don't support "pass" though, and on those platforms, even if you specify "pass", "proxy" will be used instead and a warning will be displayed. Other platforms may not support "pass" as well but those are the only known ones and the only ones where "proxy" is forced.
• deniedips - A regular expression indicating which IP addresses will be denied access (for example, to deny access to all clients: deniedips=".*") By default, no IP addresses are denied.
• 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\..*)") By default, all IP addresses are allowed.
• maxquerysize - Sets the maximum query size, in bytes, that the SQL Relay server will accept, if a client tries to send a longer query, the server will close the connection. Defaults to 65536 (64k) bytes.
• maxbindvars - Sets the maximum number of input and output bind variables that the server will accept in a single query, if a client tries to send more input bind variables or more output bind variables than this number, in a single query, the server will close the connection. Defaults to 256 bind variables. Note that this parameter controls both input and output bind variables independently. For example, setting it to 512 would allow both 512 input bind variables and 512 output bind variables.
• maxbindnamesize - Sets the maximum number of bytes that the SQL Relay server will accept for a bind variable name. Defaults to 64.
• maxstringbindvaluesize - Sets the maximum number of bytes that the SQL Relay server will accept for a string bind value, if the client tries to send a longer string bind value, the server will close the connection. Defaults to 32768 (32k) bytes.
• maxlobbindvaluesize - Sets the maximum number of bytes that the SQL Relay server will accept for a LOB/CLOB bind value, if the client tries to send a longer LOB/CLOB bind value, the server will close the connection. Defaults to 71680 (70k) bytes.
• maxclientinfosize - Sets the maximum number of bytes that the SQL Relay server will accept for a client info string. Defaults to 512.
• maxerrorsize - Sets the maximum number of bytes that the SQL Relay server will accept from the database for an error string. Defaults to 2048.
• 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 to wait 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. Defaults to -1, which means to run without a limit.
• listenertimeout - Sets the number of seconds that a listener will wait for an avaialable connection before giving up. Defaults to 0, which means to 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. This parameter is useful in situations where it is difficult or impossible to guarantee that SQL Relay will start after the databases it needs to connect to are up. Defaults to "no".
• 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". Defaults to "no".
• fakeinputbindvariablesdateformat - Defines the date format to use when faking bind variables by rewriting the query and substituting values directly into it. Defaults to "YYYY-MM-DD HH24:MI:SS" Supports the following format strings:
• DD - 2 digit day, left padded with 0
• D - 1 or 2 digit day, not left padded with 0
• MM - 2 digit month, left padded with 0
• MON - uppercase, 3-character month abbreviation
• Month - mixed-case full name of the month
• YYYY - 4 digit year, left padded with 0
• YY - 2 digit year, left padded with 0
• HH24 - 2 digit hour, in 24-hour format, left padded with 0
• HH - 2 digit hour, in 12-hour format, left padded with 0
• MI - 2 digit minute, left padded with 0
• SS - 2 digit second, left padded with 0
• FFFFFF - 6 digit fractional second, left padded with 0
• FFFFF - 5 digit fractional second, left padded with 0
• FFFF - 4 digit fractional second, left padded with 0
• FFF - 3 digit fractional second, left padded with 0
• FF - 2 digit fractional second, left padded with 0
• F - 1 digit fractional second
• AM - AM or PM
• 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. Defaults to "no".
• 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. If this is left blank or omitted entirely then nothing will be done to set or reset the isolation level at the beginning or end of each session.
• ignoreselectdatabase - Instructs SQL Relay 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. Defaults to "no".
• waitfordowndatabase - If this is set to "yes" then, if the database goes down while a client is connected, the server will not return an error but rather wait until the database comes back up and then resume the client session. If this is set to "no" and a down database is detected during a client session, then SQL Relay will return the native database error and if a new client connection is made and all databases are down then SQL Relay will generate an error and return it. Defaults to "yes".
• listener
• protocol - The client-server protocol to expect on this port/socket/address. Currently, supported protocols include sqlrclient (the native SQL Relay client-server protocol), mysql, and postgresql. Defaults to sqlrclient.
• port - The port to listen for client connections on (note, if multiple instances are configured with the same port or socket, they cannot be run at the same time). Defaults to 9000.
• socket - The unix socket (filename) to listen for client connections on (note, if multiple instances are configured with the same port or socket, they cannot be run at the same time). Defaults to /tmp/sqlrelay.socket
• addresses - A comma-delimited list of addresses that the instance should bind to. If absent or set to 0.0.0.0, it will bind to all addresses. Defaults to 0.0.0.0.
• Kerberos options - Use these options to configure Kerberos encryption and authentication.
• krb - Set to "yes" to enable Kerberos encryption and authentication. Set to "no" to disable Kerberos encryption and authentication. Defaults to "no".
• krbservice - Specifies the kerberos service to run this instance as. Defaults to "sqlrelay".
• krbkeytab - Sets the Kerberos keytab file to acquire the service key from. Defaults to the system keytab, usually /etc/krb.keytab. Has no effect on Windows systems.
• krbmech - Sets the Kerberos mechanism to use. On Linux and Unix systems this should ba an OID, like: "{ 1 2 840 113554 1 2 2 }". On Windows systems this should be the string name of the mechanism, like: "Kerberos". Defaults to the default mechanism of the Kerberos implementation. Only set this if you know that you have a good reason to.
• krbflags - Sets the Kerberos flags to use. Multiple flags may be specified, separated by commas. Valid flags include: GSS_C_MUTUAL_FLAG, GSS_C_REPLAY_FLAG, GSS_C_SEQUENCE_FLAG, GSS_C_CONF_FLAG, and GSS_C_INTEG_FLAG. For a full list of flags, consult the GSSAPI documentation, though note that only the previously listed flags are supported on Windows. Only set this if you know that you have a good reason to.
• TLS/SSL options - Use these options to configure TLS/SSL encryption and authentication.
• tls - Set to "yes" to enable TLS/SSL encryption and authentication. Set to "no" to disable TLS/SSL encryption and authentication. Defaults to "no".
• tlsversion - Sets the TLS/SSL version to use. Valid values include SSL2, SSL3, TLS1, TLS1.1, TLS1.2 or any more recent version of TLS, as supported by and enabled in the underlying TLS/SSL library. If left blank or omitted, the highest supported version will be negotiated.
• tlscert - Specifies the TLS/SSL certificate chain to use. On non-Windows platforms, it must specify a certificate chain file. On Windows platforms it may specify a certificate chain file or a certificate in a Windows Certificate Store. (See The tlscert Parameter for more information.)
• tlspassword - Specifies the password to use if the certificate contains a password-protected private key.
• tlsvalidate - Set to "yes" to instruct the server to request a certificate from the client and only allow a session to proceed if it can be validatated against known certificate authorities, including any supplied by the tlsca parameter. Set to "no" not to request a certificate from the client. Defaults to "no.
• tlsca - Specifies the location of a certificate store containing the certificate of the certificate authority to use, in addition to the system's root certificates, when validating the client's certificate. This is useful if the client's certificate is self-signed, or signed in-house. On non-Windows platforms, this parameter can be either a certificate store file or the name of a directory containing multiple certificate store files. On Windows platforms it may specify a certificate store file or a Windows Certificate Store. (See The tlsca Parameter for more information.)
• tlsciphers - Specifies a list of ciphers to allow. Ciphers may be separated by spaces, commas, or colons. If omitted or left empty then a default set of ciphers is used. For a list of valid ciphers on Linux/Unix platforms, see: man ciphers. For a list of valid ciphers on Windows platforms, see: this link. On Windows platforms, the ciphers (alg_id's) should omit CALG_ and may be given with underscores or dashes. For example: 3DES_112. Only set this if you know that you have a good reason to.
• tlsdepth - Sets the maximum certificate chain validation depth. The absolute maximum depth is 9. Defaults to "9".

• connection
• connectionid - The ID of this connection. This parameter defaults to id-number where "id" is the id of the instance snd "number" is the index of the connection tag. It's not necessary to set this parameter if you use sqlr-start. It's only necessary if you plan on starting the daemons individually and want friendlier connection ids.
• metric - A number that influences how many of this connection should be started. The metric attribute doesn't actually 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.
• behindloadbalancer - Can be set to "yes" or "no". Indicates 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.
• string - The database connect string the connection daemon should use. Connect string options vary depending on the database. See Connect String Options below.

Connect String Options

For oracle databases, the connect string syntax is "user=USER;password=PASSWORD;oracle_sid=ORACLE_SID;tns_admin=TNS_ADMIN;oracle_home=ORACLE_HOME;nls_lang=NLS_LANG;lang=LANG;autocommit=yes/no/default;fetchatonce=FETCHATONCE;maxcolumncount=MAXCOLUMNCOUNT;maxfieldsize=MAXFIELDSIZE;droptemptables=yes/no;globaltemptables=TABLELIST;lastinsertidfunction=LASTINSERTIDFUNCTION;stmtcachesize=0;rejectduplicatebinds=yes/no;disablekeylookup=yes/no;dbtype=ID"

• 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. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption
• 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)))

• tns_admin: The directory to look for oracle configuration files in. Optional if the TNS_ADMIN environment variable is set, if the ORACLE_HOME environment varialbe is set, or if the oracle_home parameter is set. The SID will be looked up in $TNS_ADMIN/tnsnames.ora.
• 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. If SQL Relay was built to load the Oracle client libraries at runtime, then the lib subdirectory of this directory will be searched for the client libraries. As such it might be necessary to specify both oracle_home and tns_admin (or both environment variables).
• nls_lang: The NLS_LANG to use. Optional if the NLS_LANG environment variable is set. Overrides the NLS_LANG environment variable.
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior (autocommit-off) is observed. Optional, defaults to default.
• 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)
• maxcolumncount: The maximum number of columns that can be fetched in a query. Defaults to 256. Setting this parameter to -1 causes result set buffers to be dynamically allocated, allowing any number of columns to be fetched. Using -1 is flexible and conserves memory but there is a performance penalty. (see here for more info on this parameter)
• maxfieldsize: The maximum size, in bytes, of a non-lob field. Non-lob fields longer than this will be truncated. Defaults to 32768. (see here for more info on this parameter)
• 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. Note that temporary tables created outside of the session will not be dropped.
• globaltemptables: Since SQL Relay doesn't log out of the database at the end of each client session, global temporary tables aren't automatically truncated by the database. SQL Relay tracks the creation of global temporary tables and truncates any table created during the session, or drops them if droptemptables=yes is configured. But, SQL Relay isn't aware of tables created outside of the current session, or outside of SQL Relay altogether. To work around this issue, this parameter can be set to either a comma-separated list of global temporary tables that SQL Relay should truncate at the end of each session. Alternatively, it can be set to % and SQL Relay will truncate all global temporary tables that it has access to at the end of each session. If this parameter is omitted, only tables that were created during the session are truncated. Providing a list of tables performs better but is less flexible than using %.
• 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.
• stmtcachesize: Set the size of the local statement cache. Using a local statement cache can improve performance significantly. This parameter defaults to 0, which disables using the cache. This parameter also has no effect when SQL Relay is compiled against a version of Oracle prior to 9i. There is one known issue with using the statement cache. There is either a bug in OCI or a bug in the way SQL Relay uses it. Running a query that uses a stored procedure that returns a result set in an output bind cursor while using the statement cache causes a segmentation fault in the OCIStmtExecute function. To prevent this, SQL Relay prevents any attempt to run such a query if stmtcachesize is non-zero. It is possible to run such queries when stmtcachesize is set to 0 or defaulted to 0 though.
• rejectduplicatebinds: Setting this to yes causes SQL Relay to reject queries which contain more than one instance of the same bind variable. If you're binding by position and using PL/SQL and your query contains duplicate bind variables, it may not work as expected and it might be convenient to just have SQL Relay reject all queries containing duplicate bind variables. This parameter is defaulted to no. By default, queries containing duplicate bind variables are not rejected.
• disablekeylookup: It is possible to get the list of columns in a table by running "describe table" or "show columns of table like '...'" in sqlrsh, or calling getColumnList() in one of the native API's, or by other methods using non-native API's. When doing this, whether each column is a primary, unique or foreign key is returned. Looking up this key information takes a noticeably long time though. This parameter makes it possible to disable key lookup by setting disablekeylookup=yes.
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).

For freetds databases, the connect string syntax is "sybase=SYBASE;lang=LANG;user=USER;password=PASSWORD;server=SERVER;db=DATABASE;autocommit=yes/no/default;charset=CHARSET;language=LANGUAGE;hostname=HOSTNAME;packetsize=PACKETSIZE;csversion=CSVERSION;maxcolumncount=MAXCOLUMNCOUNT;maxfieldsize=MAXFIELDSIZE;decodeblobs=yes/no;dbtype=ID"

• sybase: The directory containing the "freetds.conf" file. Optional if the SYBASE environment variable is set. Overrides the SYBASE environment variable.
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• 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. Required. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption
• 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.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior (autocommit-on) is observed. Optional, defaults to default.
• 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.
• csversion: The highest Client-Library (ctlib) protocol version to attempt, one of 100, 110, 120, 125, or 150, or "current" for the newest version the client library supports. SQL Relay tries the requested version first and falls back to lower versions until one is accepted by the client library, logging a warning if a numeric version was requested but could not be used. "current" - also the default, and the meaning of any non-numeric value - starts from the newest version and never warns. Older versions support fewer features; eg. version 100 (the protocol for ASE 10.0) limits blobs to 255 bytes. Optional.
• maxcolumncount: The maximum number of columns that can be fetched in a query. Defaults to 256. Setting this parameter to -1 causes result set buffers to be dynamically allocated, allowing any number of columns to be fetched. Using -1 is flexible and conserves memory but there is a performance penalty. (see here for more info on this parameter)
• maxfieldsize: The maximum size, in bytes, of a non-lob field. Non-lob fields longer than this will be truncated. Defaults to 32768. (see here for more info on this parameter)
• decodeblobs: Blob field values are returned by the SAP/Sybase client library to SQL Relay in a text-encoded form. By default SQL Relay decodes these to raw binary before passing them to the client. Set to "no" to leave the values text-encoded and let the client decode them itself. Optional, defaults to yes.
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).

For sap (or sybase) databases, the connect string syntax is "sybase=SYBASE;lang=LANG;user=USER;password=PASSWORD;server=SERVER;db=DATABASE;autocommit=yes/no/default;charset=CHARSET;language=LANGUAGE;hostname=HOSTNAME;packetsize=PACKETSIZE;csversion=CSVERSION;fetchatonce=FETCHATONCE;maxcolumncount=MAXCOLUMNCOUNT;maxfieldsize=MAXFIELDSIZE;decodeblobs=yes/no;dbtype=ID"

• sybase: The directory containing the "interfaces" file. Optional if the SYBASE environment variable is set. Overrides the SYBASE environment variable. If SQL Relay was built to load the SAP/Sybase libraries at runtime, then the lib subdirectory of this directory will be searched for the SAP/Sybase client libraries.
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• 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. Required. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption
• 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.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior (autocommit-on) is observed. Optional, defaults to default.
• 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.
• csversion: The highest Client-Library (ctlib) protocol version to attempt, one of 100, 110, 120, 125, 150, 155, 157, or 160, or "current" for the newest version the client library supports. SQL Relay tries the requested version first and falls back to lower versions until one is accepted by the client library, logging a warning if a numeric version was requested but could not be used. "current" - also the default, and the meaning of any non-numeric value - starts from the newest version and never warns. Older versions support fewer features; eg. version 100 (the protocol for ASE 10.0) limits blobs to 255 bytes. Optional.
• 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)
• maxcolumncount: The maximum number of columns that can be fetched in a query. Defaults to 256. Setting this parameter to -1 causes result set buffers to be dynamically allocated, allowing any number of columns to be fetched. Using -1 is flexible and conserves memory but there is a performance penalty. (see here for more info on this parameter)
• maxfieldsize: The maximum size, in bytes, of a non-lob field. Non-lob fields longer than this will be truncated. Defaults to 32768. (see here for more info on this parameter)
• decodeblobs: Blob field values are returned by the SAP/Sybase client library to SQL Relay in a text-encoded form. By default SQL Relay decodes these to raw binary before passing them to the client. Set to "no" to leave the values text-encoded and let the client decode them itself. Optional, defaults to yes.
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).

For db2 databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;lang=LANG;autocommit=yes/no/default;connecttimeout=CONNECTTIMEOUT;fetchatonce=FETCHATONCE;maxcolumncount=MAXCOLUMNCOUNT;maxfieldsize=MAXFIELDSIZE;maxoutlobbindsize=MAXOUTLOBBINDSIZE;dbtype=ID"

• 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. Required. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption
• db: The server instance to connect to.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior (autocommit-off) is observed. Optional, defaults to default.
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• connecttimeout: Specifies the number of seconds to wait for a successful connection to the database. Defaults to 5 seconds if omitted. Setting a timeout of 0 means to wait forever.
• 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)
• maxcolumncount: The maximum number of columns that can be fetched in a query. Defaults to 256. Setting this parameter to -1 causes result set buffers to be dynamically allocated, allowing any number of columns to be fetched. Using -1 is flexible and conserves memory but there is a performance penalty. (see here for more info on this parameter)
• maxfieldsize: The maximum size, in bytes, of a non-lob field. Non-lob fields longer than this will be truncated. Defaults to 32768. (see here for more info on this parameter)
• maxoutlobbindsize: The maximum size of a lob output bind variable. Defaults to 2 megabytes. Has has no effect on lob columns.
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).
• db2: The base directory of the DB2 installation. Only used if SQL Relay was built to load the DB2 client libraries at runtime. Then the lib, lib32, and lib64 subdirectories of this directory will be searched, as appropriate, for the client libraries.

For informix databases, the connect string syntax is "user=USER;password=PASSWORD;informixdir=INFORMIXDIR;servername=SERVERNAME;db=DB;lang=LANG;autocommit=yes/no/default;connecttimeout=CONNECTTIMEOUT;fetchatonce=FETCHATONCE;maxcolumncount=MAXCOLUMNCOUNT;maxfieldsize=MAXFIELDSIZE;maxoutlobbindsize=MAXOUTLOBBINDSIZE;dbtype=ID"

• 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. Required. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption
• informixdir: Specifies the path to the Informix instance that should be used. This is used for locating the sqlhosts file and other dependencies. If not specified, this defaults to the value of the INFORMIXDIR environment variable, if set. If SQL Relay was built to load the Informix client libraries at runtime, then the lib/cli subdirectory of this directory will be searched for the client libraries.
• servername: The server to connect to, as defined in sqlhosts. If not specified, this defaults to the value of the INFORMIXSERVER environment variable, if set.
• db: The database instance to connect to, on the specified server.
• informixdir: The server instance to connect to.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior (autocommit-off) is observed. Optional, defaults to default.
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• connecttimeout: Specifies the number of seconds to wait for a successful connection to the database. Defaults to 5 seconds if omitted. Setting a timeout of 0 means to wait forever.
• maxcolumncount: The maximum number of columns that can be fetched in a query. Defaults to 256. Setting this parameter to -1 causes result set buffers to be dynamically allocated, allowing any number of columns to be fetched. Using -1 is flexible and conserves memory but there is a performance penalty. (see here for more info on this parameter)
• maxfieldsize: The maximum size, in bytes, of a non-lob field. Non-lob fields longer than this will be truncated. Defaults to 32768. (see here for more info on this parameter)
• maxoutlobbindsize: The maximum size of a lob output bind variable. Defaults to 2 megabytes. Has has no effect on lob columns.
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).

For mysql databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;socket=SOCKET;lang=LANG;autocommit=yes/no/default;fakebinds=FAKEBINDS;maxcolumncount=MAXCOLUMNCOUNT;maxfieldsize=MAXFIELDSIZE;charset=CHARSET;sslmode=sslmode;tlsversion=tlsversion;sslkey=keyfile;sslcert=certfile;sslcipher=cipherlist;sslca=cafile;sslcapath=cafilepath;sslcrl=crlfile;sslcrlpath=crlfilepath;foundrows=yes/no;ignorespace=yes/no;dbtype=ID"

• 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. Required. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption
• 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.
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior (autocommit-on) is observed. Optional, defaults to default.
• fakebinds: MySQL versions 4.1.2 and higher, and all versions of MariaDB, support bind variables natively. Older versions of MySQL did not and SQL Relay had to substitute the values of bind variables into the query itself (fake binds). As such, in modern MySQL/MariaDB, bind variables are identified by ?'s in the query, while in old versions, bind variables are identified by :var1, :var2, :var3, etc. Converting queries and code which was written to use the old syntax to use the modern syntax can be a lot of work, so this parameter is provided to make code which was written to run against old versions of MySQL work against modern MySQL/MariaDB This parameter may be set to "yes" (meaning allow only old syntax and fake binds) or "no" (meaning only allow modern syntax and use native binds).
• maxcolumncount: The maximum number of columns that can be fetched in a query. Defaults to 256. Setting this parameter to -1 causes result set buffers to be dynamically allocated, allowing any number of columns to be fetched. Using -1 is flexible and conserves memory but there is a performance penalty. (see here for more info on this parameter)
• maxfieldsize: The maximum size, in bytes, of a non-lob field. Non-lob fields longer than this will be truncated. Defaults to 32768. (see here for more info on this parameter)
• 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/MariaDB 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 or any version of the MariaDB client library.
• sslmode: The ssl mode of the connection. Optional. Valid values include disable, prefer, require, verify-ca (validate the server's certificate) and verify-full (validate the server's certificate and common name). Defaults to prefer with modern versions of MySQL and all versions of MariaDB and disable with older versions. Ignored when SQL Relay was compiled against MySQL < 5.7.3. Only necessary when connecting to MySQL using SSL.
• tlsversion - Sets the TLS version to use. Valid values include TLSv1, TLSv1.1, TLSv1.2 or any more recent version of TLS, as supported by and enabled in the underlying MySQL/MariaDB client/server. Ignored when SQL Relay was compiled against MySQL < 5.7.10. In that case, or if the parameter is left blank or omitted, then the highest supported version will be negotiated. Only necessary when connecting to MySQL using SSL.
• sslkey: The full path name of the ssl key file. (eg. /etc/certs/key.pem) Only necessary when connecting to MySQL using SSL.
• sslcert: The full path name of the ssl certificate file (eg. /etc/certs/cert.pem) Only necessary when connecting to MySQL using SSL.
• sslcipher: The list of permissable ciphers to use for SSL encryption. Only necessary if you want to restrict which cipers to use when connecting to MySQL using SSL.
• sslca: The full path name of an SSL CA certificate to add to the list of trusted CA certificates to validate the host's certificate against. (eg. /etc/certs/ca.pem) Only necessary when connecting to MySQL using SSL, and then usually only when the host certificate is self-signed.
• sslcapath: The full path name to a directory that contains a set of trusted SSL CA certificates to add to the list of trusted CA certificates to validate the host's certificate against. (eg. /etc/certs/ca) Only necessary when connecting to MySQL using SSL, and then usually only when the host certificate is self-signed.
• sslcrl: The full path name of an SSL certificate revocation list. (eg. /etc/certs/crl.pem) Only used when connecting to MySQL using SSL and then only when certificate signing authorities which may have signed the server's certificate may have been compromised.
• sslcrlpath: The full path name of a directory that contains a set of SSL certificate revocation lists. (eg. /etc/certs/crl) Only used when connecting to MySQL using SSL and then only when certificate signing authorities which may have signed the server's certificate may have been compromised.
• foundrows: Ordinarily, the MySQL/MariaDB client library returns the number of rows that were modified by an insert, update or delete command are returned as the "affected rows" of the query. Setting foundrows to "yes" passes a flag to the MySQL/MariaDB client library, telling it to return the number of rows that matched the where clause of the query rather than the number that were modified. This can be a different number with certain queries. This parameter defaults to no.
• ignorespace: Tells MySQL/MariaDB to allow spaces after function names. Ie. "select count (*) from mytable" should be valid, with the space between count and (*).
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).

For postgresql databases, the connect string syntax is "user=USER;password=PASSWORD;db=DB;host=HOST;port=PORT;sslmode=SSLMODE;options=OPTIONS;lang=LANG;autocommit=yes/no/default;typemangling=MANGLING;tablemangling=MANGLING;enablecolumnisnullable=yes/no;fakebinds=FAKEBINDS;charset=CHARSET;lastinsertidfunction=LASTINSERTDFUNCTION;fetchatonce=FETCHATONCE;decodeblobs=yes/no;dbtype=ID"

• 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. Required. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption
• 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).
• sslmode: The ssl mode of the connection. Optional, defaults to disable (ie. not to use ssl). Valid values include disable, allow, prefer, require, verify-ca and verify-full. See SSL Mode Descriptions for more information. If a mode other than disable is used, certificate and key files must be present in ~/.postgresql (in the home directory of the user that sqlrelay is running as). See SSL Client File Usage for more information.
• options: Command line options to be sent to the server. Optional.
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior (autocommit-on) is observed. Optional, defaults to default.
• 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.
• tablemangling: If set to "lookup" then the pg_class table is queried at startup and table names are returned as they appear in that table for the table associated with a column. By default (or if set to "no"), then the table number is returned.
• enablecolumnisnullable: If set to "yes" then column nullability is looked up via pg_attribute for each column of each result set. By default (or if set to "no"), all columns are reported as nullable and no lookup is performed. Optional, defaults to no. The lookup requires an extra round trip per column, so enable it only if your application relies on accurate nullability information.
• 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: Postgresql 7+ supports serial or identity columns, and after an insert into a table containing one, the id that was generated may be retrieved via lastval(). Older Postgresql doesn't support auto-increment columns of any kind, but they can be simulated using triggers and sequences. 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. Defaults to lastval().
• fetchatonce: May be set to 0 or 1. Defaults to 0. 0 is interpreted as "fetch all rows at once" and 1 is interpreted as "fetch one row at a time". Use caution when setting this to 1. Due to the quirky way that PostgreSQL implements fetching one row at a time, if you run a set of nested queries, rows will be truncated in the outer query, if the client uses a non-zero result set buffer size. See the faq for more details.
• decodeblobs: PostgreSQL returns blob field values to the client in a text-encoded form (either hex format "\xDEADBEEF" or escape format with octal escapes). By default SQL Relay decodes these to raw binary before passing them to the client. Set to "no" to leave the values text-encoded and let the client decode them itself. Optional, defaults to yes.
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).

For firebird databases, the connect string syntax is "user=USER;password=PASSWORD;db=DATABASE;dialect=DIALECT;lang=LANG;autocommit=yes/no/default;maxcolumncount=MAXCOLUMNCOUNT;maxfieldsize=MAXFIELDSIZE;charset=CHARSET;droptemptables=yes/no;globaltemptables=TABLELIST;lastinsertidfunction=LASTINSERTIDFUNCTION;dbtype=ID"

• 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. Required. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption
• db: The filename of the database open. Required.
• dialect: The database dialect to use. Optional, defaults to 3.
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior (autocommit-off) is observed. Optional, defaults to default.
• maxcolumncount: The maximum number of columns that can be fetched in a query. Defaults to 256. Setting this parameter to -1 causes result set buffers to be dynamically allocated, allowing any number of columns to be fetched. Using -1 is flexible and conserves memory but there is a performance penalty. (see here for more info on this parameter)
• maxfieldsize: The maximum size, in bytes, of a non-lob field. Non-lob fields longer than this will be truncated. Defaults to 32768. (see here for more info on this parameter)
• charset: The character set to translate data coming out of the database into. Optional.
• droptemptables: In most databases, temporary tables are dropped at the end of the client session. In Firebird 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. Note that temporary tables created outside of the session will not be dropped.
• globaltemptables: Since SQL Relay doesn't log out of the database at the end of each client session, global temporary tables aren't automatically truncated by the database. SQL Relay tracks the creation of global temporary tables and truncates any table created during the session, or drops them if droptemptables=yes is configured. But, SQL Relay isn't aware of tables created outside of the current session, or outside of SQL Relay altogether. To work around this issue, this parameter can be set to either a comma-separated list of global temporary tables that SQL Relay should truncate at the end of each session. Alternatively, it can be set to % and SQL Relay will truncate all global temporary tables that it has access to at the end of each session. If this parameter is omitted, only tables that were created during the session are truncated. Providing a list of tables performs SUBSTANTIALLY better but is less flexible than using %.
• 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.
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).

For sqlite databases, the connect string syntax is "db=DB;dbtype=ID;lang=LANG;autocommit=yes/no/default;busytimeoutms=MILLISECONDS"

• db: The filename of the database open. Required.
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior (autocommit-on) is observed. Optional, defaults to default.
• busytimeoutms: The number of milliseconds to wait for a contended write lock to clear before returning an error. Set to 0 to fail immediately when a write lock is contended. Optional, defaults to 5000 (5 seconds).

For odbc databases, the connect string syntax is "user=USER;password=PASSWORD;dsn=DSN;lang=LANG;autocommit=yes/no/default;connecttimeout=CONNECTTIMEOUT;odbcversion=ODBCVERSION;ncharencoding=NCHARENCODING;maxcolumncount=MAXCOLUMNCOUNT;maxfieldsize=MAXFIELDSIZE;maxoutlobbindsize=MAXOUTLOBBINDSIZE;dbtype=ID;mars=yes/no;trace=yes/no/default;tracefile=TRACEFILE;detachbeforelogin=yes/no;overrideschema=SCHEMA"

• 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. Required. The password is generally stored in plain text but it is possible to encrypt the password using a loadable module. See Password Encryption
• dsn: The entry in the the odbcinst.ini which specifies host, port and other database connection parameters.
• lang: Sets/overrides the LANG environment variable and by extension, all locale-related variables. Optional if the LANG environment variable is set.
• autocommit: If set to yes, then each insert, update, or delete is committed immediately. If set to no, then each insert, update, or delete is only committed when a commit statement is executed. If set to default then the default behavior for the database is observed. Optional, defaults to default.
• autocommit: Whether to commit each insert, update or delete immediately or not. Optional, defaults to no.
• connecttimeout: Specifies the number of seconds to wait for a successful connection to the database. Defaults to 5 seconds if omitted. Setting a timeout of 0 means to wait forever.
• odbcversion: Tells the driver to exhibit behavior from the specified version of ODBC. Valid values include: 2, 3, and 3.8. Defaults to 3. It may be necessary to set this to 2 for some drivers that only support ODBC 2.
• ncharencoding: Whether to encode bind variables as UCS-2 or UTF-16 when inserting into a NCHAR/NVARCHAR field, and whether to interpret data from NCHAR/NVARCHAR fields as UCS-2 or UTF-16. Defaults to UCS-2.
• maxcolumncount: The maximum number of columns that can be fetched in a query. Defaults to 256. Setting this parameter to -1 causes result set buffers to be dynamically allocated, allowing any number of columns to be fetched. Using -1 is flexible and conserves memory but there is a performance penalty. (see here for more info on this parameter)
• maxfieldsize: The maximum size of a field. Fields longer than this will be truncated. Defaults to 32768. (see here for more info on this parameter)
• dbtype: Overrides the default value returned when a client request the database type using the identify() method (or similar method/function).
• mars: Whether to enable MS SQL Server MARS (Multiple Active Result Sets). Set to yes to enable MARS with MS SQL Server. Omit or set to no to disable MARS. Omit when using an ODBC driver for a database other than MS SQL Server. Optional, defaults to no.
• trace: Whether or not to enable ODBC tracing. Set to yes to enable tracing. Set to no to disable tracing. Set to default to accept whatever is configured in the DSN. Defaults to default.
• tracefile: Allows you to override the default ODBC trace file name, or the trace file name specified in the DSN. If omitted then the trace file name specified in the DSN will be used. If that is omitted then the default trace file name will be used.
• detachbeforelogin: Whether or not to detach from the controlling terminal before login. When set to yes, the process ID's in the ODBC trace file will match the process ID of the sqlr-connection process. Otherwise they will not. Defaults to no.
• overrideschema: Overrides the schema that the database believes that it is in when requesting lists of tables, columns, procedures, etc. Useful for specific databases (eg. Apache Hive) that misreport the schema.

Advanced Configuration

SQL Relay is highly modular, and there are additional tags and attributes that direct SQL Relay to load various filtering, translation, query routing, logging, notification, and other modules. These are described in detail in the SQL Relay Configuration Guide.

Copyright 2024 - David Muse - Contact