Result Set Translation modules allow the SQL Relay server programs to alter fields in the result set before returning the field to the client.
The resultsettranslations section of the sqlrelay.conf file indicates which result set translation modules to load and what parameters to use when executing them.
<?xml version="1.0"?>
<instances>
...
<instance id="example" ... >
...
<resultsettranslations>
<resultsettranslation
module="reformatdatetime"
datetimeformat="MM/DD/YYYY HH24:MI:SS"
dateformat="MM/DD/YYYY"
timeformat="HH24:MI:SS"
dateddmm="yes"
ignorenondatetime="yes"/>
</resultsettranslations>
...
</instance>
...
</instances>
The module attribute specifies which module to load.
Module configurations may have attributes and/or nested tags. How these elements are interpreted is module-specific.
Currently, all result set translation modules have an enabled parameter, allowing the module to be temporarily disabled. If enabled="no" is configured, then the module is disabled. If set to any other value, or omitted, then the module is enabled.
Translation modules can be "stacked". Multiple modules may be loaded and multiple instances of the same type of module, with different configurations, may also be loaded.
<?xml version="1.0"?>
<instances>
...
<instance id="example" ... >
...
<resultsettranslations>
<resultsettranslation
module="reformatdatetime"
datetimeformat="MM/DD/YYYY HH24:MI:SS"
dateformat="MM/DD/YYYY"
timeformat="HH24:MI:SS"
dateddmm="yes"
ignorenondatetime="yes"/>
<resultsettranslation module="anothermodule"/>
<resultsettranslation module="andanothermodule"/>
<resultsettranslation module="yetanothermodule"/>
</resultsettranslations>
...
</instance>
...
</instances>
At startup, the SQL Relay server creates instances of the specified result set translations modules and initializes them. As each field of the result set is returned, the server passes the field to each module, in the order that they were specified in the config file. If a module modifies a field, then that modified field is passed on to the next module.
Currently, the following result set translation module is available:
The reformatdatetime module examines the field, decides if it's a date/time field, and if so, reformats it based on the given parameters.
The following parameters are currently supported:
For example, the following configuration:
<?xml version="1.0"?>
<instances>
...
<instance id="example" ... >
...
<resultsettranslations>
<resultsettranslation
module="reformatdatetime"
datetimeformat="MM/DD/YYYY HH24:MI:SS"
dateformat="MM/DD/YYYY"
timeformat="HH24:MI:SS"
dateddmm="yes"
ignorenondatetime="yes"/>
</resultsettranslations>
...
</instance>
...
</instances>
Would translate the following date/time field:
Jul 10 2015 05:17:55:717PM
Into:
07/10/2015 17:18:55
Note that dateddmm and dateyyddmm should usually be set to the same thing. There are very specific cases where these two parameters need to be set differently from one another. You'll know if you need to.
Note also that date/time translation in general is especially problematic with MS SQL Server. See the FAQ for more info.
You might notice that the instance tag takes the exact same set of parameters as this module, and provides the exact same functionality. So, why would you want to use it instead of just using the built in parameters of the instance tag.
The instance tag parameters are evaluated prior to evaluating any modules. So, you must use the reformatdatetime module if you are stacking result set translation modules, and want to ensure that other translations are performed prior to reformatting dates and times.
Custom modules may also be developed.
For more information, please contact David Muse at david.muse@firstworks.com.