SQL/R A.03.00 Release Notes

SQL/R A.03.00 Changes

Changes in SQL/R version A.03.35:

Fixed COUNT(DISTINCT) with GROUP BY.
Fixed aggregate function and COUNT(DISTINCT) with GROUP BY.
Fixed COUNT(DISTINCT) in HAVING condition.
Fixed ODBC 3 driver compatibility with unixODBC.
Please note: Using the ODBC 3 driver requires that the SQL/R server is version A.03.17 or newer.
Fixed a minor ODBC driver memory leak. On disconnect, the allocated user name memory was not released.
Fixed MATCHES operator argument passed as ODBC execute parameter.

Changes in SQL/R version A.03.34 (beta test):

This version introduces the ODBC 3.0 driver.
ODBC 3 is an enhanced version of the ODBC standard that is used by current ODBC client applications. Typically, the ODBC driver manager transparently translates between different ODBC versions for applications and drivers as necessary. However, in some cases this translation is not sufficient, so an additional ODBC 3 driver was added. The previous ODBC driver (ODBC 2.0) remains in SQL/R.
On Windows, the new "SQL/R ODBC3 Driver" (32-bit and 64-bit) is installed in addition to the previous "SQL/R Driver". The driver DLLs are named "sqlr3o32.dll" (32-bit) and "sqlr3o64.dll" (64-bit).
On Linux and HP-UX, the new ODBC 3 driver shared libraries are named "libsqlr3odbc*" and are installed in addition to the previous driver shared libraries named "libsqlrodbc*".
Please note: Using the ODBC 3 driver requires that the SQL/R server is version A.03.17 or newer.
Please note: The ODBC 3 driver is currently in beta test, it implements new functionality which has not been widely tested in production.
A.03.34 rev. 2: Fixed ODBC 3 driver transaction handling issue on Linux.

Changes in SQL/R version A.03.33:

IPv6 (Internet Protocol version 6) is now supported.
Please note that IPv6 addresses are specified in square brackets, for example [::1] (localhost), [fe80::9656:d028:8652:66b6] (link-local address in the local network).
The [config] Service and HttpService configuration now allow an optional suffix /4, or /6, or /46, to establish IPv4 only, or IPv6 with implied IPv4, or separate IPv4 and IPv6 listening sockets. By default, separate IPv4 and IPv6 listening sockets are established.
The [access] allow/deny and [encryption] on/off configuration now accept host names as well as IPv6 and/or IPv4 addresses.
sqlrexec and Sqlr.DLL: The default server address and service/port may now be specified by setting the SQLR_SERVER environment variable.
For example, to address an SQL/R server instance on the local system listening on a port different from the default sqlrodbc service (both SQLR_SERVER settings below are equivalent):
```
SQLR_SERVER=localhost:6789
SQLR_SERVER=:6789
```
For example, to connect to an SQL/R server instance on a remote system in the local network listening on the default sqlrodbc service using an IPv6 link-local address (both SQLR_SERVER settings below are equivalent):
```
SQLR_SERVER=[fe80::9656:d028:8652:66b6]:sqlrodbc
SQLR_SERVER=[fe80::9656:d028:8652:66b6]
```
This affects the CONNECT TO and OPEN DATABASE statements.
SQL/R server: The HTTP status hyperlinks are now relative so that they work in a NAT (Network Address Translation) environment.
SQL/R server: Log messages related to the HTTP status now use the 'H' log flag.
SQL/R server: The new LogTimeFmt configuration item allows to configure the log message timestamp format.
Windows, ODBC driver: The SQLR_LOG_FLAGS and SQLR_LOG_FILE environment variables are now recognized, as an alternative to configuring the logging in the sqlrodbc.ini file.
A.03.33 rev. 2, sqlrexec and Sqlr.DLL: Fixed unexpected failure when reconnecting using the CONNECT TO or OPEN DATABASE statement.
A.03.33 rev. 2, sqlrexec and Sqlr.DLL: Fixed ODBC driver logging not functional.

Changes in SQL/R version A.03.32 (beta test):

Added support for multiple sqlrodbcd instances.
Added support for start/stop without root access (HP-UX and Linux system/V startup).
Ensure any config file issues are logged to syslog.
A license key revision of A.03.00 is accepted.

Changes in SQL/R version A.03.31 (beta test):

ODBC driver: Fixed the SQLGetData ODBC function where retrieving variable-length data in parts did not work as expected.
Fixed SQL/R server panic caused by wrong month or day in literal date or timestamp (#4308).
A "communication failure" error was issued on the client while a message like below was written to the server log:
```
panic: nexpr ...
```
A license key revision of A.03.30 is required.
Please note: This has been reversed since SQL/R version A.03.32 which again accepts a license key revision of A.03.00.

Changes in SQL/R version A.03.22:

ODBC driver: Fixed the SQLGetData ODBC function where retrieving variable-length data in parts did not work as expected.
Increased parser limits to allow for tables with a large number of columns.
A.03.22 rev. 2: Fixed SQL/R server panic caused by wrong month or day in literal date or timestamp (#4308).
A "communication failure" error was issued on the client while a message like below was written to the server log:
```
panic: nexpr ...
```

Changes in SQL/R version A.03.30 (beta test):

Introduce support to use FTS indexes.

The MATCHES clause may be used to specify FTS searches on the related field. Multiple conditions may be combined with the relational operators AND/OR/NOT.

# fts search on field

select custno,name1 from customers 
where name matches "keller";

select custno,name1 from customers 
where name matches "keller or ketterer";

select orderno,orderid,orderdate from orders
where orderno matches "24630:24639";

# fts search on multiple fields

select custno,name1,zipcity from customers 
where name matches "keller" and zipcity matches "esslingen";

select custno,name1,zipcity from customers 
where name matches "keller" and not zipcity matches "esslingen";

The LIKE clause was extended to also make use of fts indexes. Please keep in mind that fts searches by default are case insensitive but LIKE is case sensitive.

select custno,matchcode,name1
from customers 
where lcase(name1) like "kel%"
order by 1;

select custno,matchcode,name1
from customers 
where name1 matches "kel@"
order by 1;

The LIMIT n clause may be used to limit the number of results.

select custno,matchcode,name1 from customers
order by custno desc
limit 3;

Increased parser limits to allow for tables with a large number of columns.
A license key revision of A.03.30 is required.
Please note: This has been reversed since SQL/R version A.03.32 which again accepts a license key revision of A.03.00.

Changes in SQL/R version A.03.21:

The SQL/R server now uses the Eloquence B.08.30 database client library which provides improved database password security.
HP-UX, Linux: The encrypted communication functionality now supports recent OpenSSL crypto library versions.
Linux: systemd support has been added to manage the SQL/R server daemon process on contemporary Linux systems.
Windows: The SQL/R executables now use a recent Microsoft Runtime. The mimimum supported Windows versions are: Windows XP SP3, Windows Server 2003 SP2. Please refer to the Windows installation instructions for details.
Windows: Fixed a potential GPF when the ODBC driver DLL is unloaded after encrypted communication has been used.

Changes in SQL/R version A.03.20:

Fixed a potential HTTP request buffer overflow which under certain conditions could unexpectedly abort the SQL/R server master process with a segment violation.
A.03.20 rev. 2: Fixed SQL syntax errors caused by negative numeric literals (#4239).
A.03.20 rev. 2, Linux: Work around wrong function key terminal configuration which could cause the editor to not wait for the RETURN key being pressed after the last page of a query result is output (#4256).
A.03.20 rev. 3, Windows: The 64-bit ODBC driver could unexpectedly abort when connecting to a data source if the driver tries to open the connection dialog or the password dialog (#4269).

Changes in SQL/R version A.03.19:

Fixed a regression introduced with SQL/R A.03.18 which under certain conditions could cause a wrong join result if the WHERE condition uses the IFNULL() function (#4215).
Note: The fix delivered with SQL/R A.03.18 was incomplete.
Fixed a regression introduced with SQL/R A.03.18 where under certain conditions the IF() function could return an unexpected NULL result.
Improved the performance of join queries in certain cases if the WHERE condition uses the IFNULL() function or the IS NULL condition.
Fixed an ODBC compatibility issue causing the Microsoft(TM) SQL Server DELETE OPENQUERY statement to fail with error 7345.

Changes in SQL/R version A.03.18:

Fixed a problem which under certain conditions could cause a wrong join result if the WHERE condition uses the IF() or IFNULL() function or the IS NULL condition (#4215).
The WHERE condition could prematurely evaluate to FALSE when an IF() or IFNULL() or IS NULL condition is based on field values which have not yet been fetched.

Changes in SQL/R version A.03.17:

Improved ODBC 3.x application compatibility:
The ODBC 3.x standard defines new column names returned by the functions to query properties of the underlying database such as the database schema information.
These ODBC functions are: SQLColumns, SQLForeignKeys, SQLGetTypeInfo, SQLPrimaryKeys SQLSpecialColumns, SQLStatistics, SQLTablePrivileges, SQLTables
If ODBC-aware programs expect the ODBC 3.x column names and don't provide ODBC 2.0 backward-compatibility (as recently observed with Microsoft SQL Server 2012), SQL/R can now be configured to deliver the ODBC 3.x column names by setting the SQL/R ODBC3_SCHEMA connection option.
This option may be set for a specific connection by adding Options=O3SC to a connection string, for example:
```
DSN=My Data Source;UID=public;Options=O3SC
```
Alternatively, this option may be set in the odbc.dsn data source configuration for a specific DSN, for example:
```
[My Data Source]
...
Options = O3SC
```
The Options parameter, either in the connection string or in the odbc.dsn data source configuration, accepts a comma-separated list of connection options.
For example, Options=O3DT,O3SC sets both the ODBC3_SCHEMA and ODBC3_DATE_TIME options (see notes about SQL/R version A.03.13 below).
This requires that the SQL/R server is version A.03.17 or newer and the ODBC driver is version A.03.13 or newer.
A.03.17 rev. 2: Provide a workaround for a problem observed with Microsoft Access, refusing to link a data set having more than 32 indexes (#4214).
Microsoft Access might issue an error message like below:
```
The operation failed. There are too many indexes on table ... Delete some of the indexes on the table and try the operation again.
```
This is caused by an internal Microsoft Access limitation. To work around the problem, an SQL/R ODBC driver connection option may be set to filter the indexes reported by the SQLStatistics ODBC function:
Options = SI_ALL sets the STAT_IDX_ALL connection option causing SQLStatistics to report all available indexes for a data set. This is the default.
Options = SI_SEC sets the STAT_IDX_SEC connection option causing SQLStatistics to report primary indexes and those secondary indexes consisting of a single index segment, i.e., multi-segment secondary indexes are not reported.
Options = SI_PRI sets the STAT_IDX_PRI connection option causing SQLStatistics to report primary indexes only.
One of these options may be set for a specific connection by adding it to a connection string, for example:
```
DSN=My Data Source;UID=public;Options=SI_SEC
```
Alternatively, it may be set in the odbc.dsn data source configuration for a specific DSN, for example:
```
[My Data Source]
...
Options = SI_SEC
```
The Options parameter, either in the connection string or in the odbc.dsn data source configuration, accepts a comma- separated list of connection options.
For example, Options=O3DT,O3SC,SI_SEC sets the ODBC3_SCHEMA and ODBC3_DATE_TIME and STAT_IDX_SEC options.
A.03.17 rev. 2, Windows: Fixed an internal race condition that in some cases could have the effect that SQL/R client handler processes were not removed from internal bookkeeping after terminating (#4122).
Note: The fix delivered with SQL/R A.03.16 was incomplete.

Changes in SQL/R version A.03.16:

Improved mapping of characters in data set and item names which do not conform to the SQL syntax.
Eloquence data set and item names may be invalid SQL identifiers, resulting in SQL syntax errors.
SQL/R now maintains an additional alias name where invalid characters are mapped to the underscore character (_).
For example, an item name like ZIP/CITY contains the / character which SQL interprets as the division operator.
In previous SQL/R versions, referring such items required to use the configured identifier quote character. In the example above: |ZIP/CITY| (using the default | quote character).
Now an additional alias name allows to alternatively refer this item without using the identifier quote character. In the example above: ZIP_CITY
Improved SQL data type compatibility for bound parameters.
The SQLBindParameter ODBC function was enhanced to support the SQL data types BIGINT, DECIMAL, NUMERIC and REAL. These types are transparently converted to DOUBLE.
This improves compatibility with the Oracle(TM) DG4ODBC Database Gateway for ODBC.
Windows: Fixed an internal race condition that in some cases could have the effect that SQL/R client handler processes were not removed from internal bookkeeping after terminating (#4122).

Changes in SQL/R version A.03.15:

Improved compatibility with the Oracle(TM) DG4ODBC Database Gateway for ODBC.
SQL/R client (ODBC driver): Implemented the SQLGet/SetStmtAttr functions along with the SQL_ATTR_PARAMSET_SIZE attribute which are in some cases expected by DG4ODBC for parameter binding.

Changes in SQL/R version A.03.14:

Improved compatibility with the Oracle(TM) DG4ODBC Database Gateway for ODBC.
The SQL/R server SQLColumns implementation was enhanced to return additional ODBC 3.x columns which are in some cases expected by DG4ODBC to calculate character column sizes.

Changes in SQL/R version A.03.13:

HP-UX, Linux: Fixed a regression introduced with SQL/R A.03.08 which could cause a syntax error like below:
```
syntax error near "|"
```
If not explicitly configured with the 'Quoting' parameter in the connection string, the pipe character (|) was no longer recognized as the default identifier quote.
Fixed a date formatting problem which could cause unexpected results if an invalid formatting token was used.
Fixed a date formatting problem where the values generated by the tokens %d, %H, %I, %j, %m, %M, %S and %y were not zero-prefixed as expected.
Fixed SQL function problems:
The argument order of the ATAN2() function was reversed.
The random generator for the RAND() function was not always seeded.
Improved ODBC 3.x application compatibility:
The ODBC 3.x standard defines new identifiers for the date, time and timestamp data types. These are different from the ODBC 2.0 data type identifiers. The SQL/R server and client components were enhanced to recognize both the ODBC 2.0 and the ODBC 3.x identifiers.
If ODBC-aware programs expect the ODBC 3.x identifiers and don't provide ODBC 2.0 backward-compatibility (as recently observed with Oracle(TM) version 11.2), SQL/R can now be configured to deliver the ODBC 3.x identifiers by setting the SQL/R ODBC3_DATE_TIME connection option.
This option may be set for a specific connection by adding Options=O3DT to a connection string, for example:
```
DSN=My Data Source;UID=public;Options=O3DT
```
Alternatively, this option may be set in the odbc.dsn data source configuration for a specific DSN, for example:
```
[My Data Source]
...
Options = O3DT
```
The Options parameter, either in the connection string or in the odbc.dsn data source configuration, accepts a comma-separated list of connection options.
This requires that both the SQL/R server and the ODBC driver are version A.03.13 or newer.
Improved SQL function compatibility:
The BITAND() function is recognized as an alias for the BAND() standard ODBC function.
The CEIL() function is recognized as an alias for the CEILING() standard ODBC function.

Changes in SQL/R version A.03.12:

Fixed a problem where array member column names could not be resolved if they match the name of an array item (#4193).
For example, if the SQL/R server ArraySep configuration is set to a single underscore and a data set contains array items named ARRAY and ARRAY-2, the column name "ARRAY_2" is potentially ambiguous because it refers to the second ARRAY element as well as to the ARRAY-2 item. This could cause an error like below:
```
[Marxmeier][SQL/R ODBC Server]37000 - Undefined view (#32)
Unable to resolve ARRAY_2
```
Fixed a problem where the SQL/R server could abort because an internal limit of the indexing subsystem was reached if a high number of similar values are processed by ORDER BY or GROUP BY (#4194).
A "communication failure" error was issued on the client while a message like below was written to the server log:
```
panic: Fatal isam error #240. ...
```

Changes in SQL/R version A.03.11:

Fixed a problem in the access filter list. Only the first and last access entry was effective.
Fixed a problem in the encryption list. Only the first and last encryption entry was effective.
Linux: Fixed a problem where executing a script in the SQL/R editor could result in an "Unable to open terminal" error message (#4031).
Linux: SQL/R server now recognizes network interfaces not named ethx.
Windows: Fix permission denied error on Windows Server 2003 when using the SQLR.DLL (#4168).

Changes in SQL/R version A.03.10:

Literal string values in SQL statements were limited to 1023 characters on the SQL/R server (#4156). This was changed to allow for a maximum string length of 4095 characters.
Changed the SQL/R client to match the above limit (4095 characters) for string literals and row output.
HP-UX, Linux: Fixed a regression where the PS command line of connected SQL/R server child processes did no longer indicate the connected client address.
Windows: Fixed a problem where the ODBC driver could fail to initialize the cryptographic subsystem if the invoking user's profile was not set up, for example if invoked from an IIS web session (#4159).
The SQL/R server could abort in certain cases where the last character of an audit item value is a backslash or a curly brace and therefore must be escaped (#4160).

Changes in SQL/R version A.03.09:

The SQL/R server uses the KEEP ALIVE socket option to check for inactive client connections, configurable through the UseKeepAlive configuration item (#3962).
The OPEN DATABASE implementation was enhanced to support network port forwarding / address redirection (#4147).
On the client, OPEN DATABASE uses the host address of the first provided database name to connect to the SQL/R server. If this is the address of a forwarding router/firewall device, then connecting to the SQL/R server works but opening the database(s) does not.
The new implementation trims the host address of the database name so that the localhost address is used to open the database.
If OPEN DATABASE specifies multiple databases, all host addresses equal to the address of the first database name are trimmed, any other host address remains unchanged.

Changes in SQL/R version A.03.08:

Fixed an incompatibility with the OLEDB data connection method in recent Microsoft Office versions and other ODBC-aware programs which expect that the ODBC catalog functionality is supported.
This requires that both the SQL/R server and the ODBC driver are version A.03.08 or newer.
Added support for the linked server SQL syntax to be used with the Microsoft SQL Server. This allows to optionally qualify SQL table and column identifiers with the linked server name.
For more information, please refer to:
http://msdn.microsoft.com/en-us/library/ms187879.aspx
odbc.dsn data source configuration: Added the 'Qualifier' item to specify the DSN qualifier (aka. catalog name) of a data source. If omitted, the qualifier is derived from the data source name as specified in the DSN section title.
This requires that both the SQL/R server and the ODBC driver are version A.03.08 or newer.
ODBC connection string: Enhanced the 'Quoting' parameter so that a connection-specific identifier quotation character may be defined.
The default identifier quote is the pipe character (|). To define a different quotation character, the respective character or its ASCII code may be specified. For example, Quoting=" or Quoting=34 both enable identifier quoting using the double quotation mark.
This requires that both the SQL/R server and the ODBC driver are version A.03.08 or newer.

Changes in SQL/R version A.03.07:

HP-UX, Linux: Fixed a problem where restarting the SQL/R server could fail with a message like below:
```
X0: Unable to bind address. [98] Address already in use
D0: SQL/R ODBC server terminating
```
ODBC driver, HP-UX, Linux: Fixed a problem where the configuration of a system DSN was not used if the .odbc.ini file is present in the home directory of the invoking user.

Changes in SQL/R version A.03.06:

Fixed a problem where a SELECT statement could in certain cases return empty values for a view column (#4124).
If a view is joined with other table(s) or view(s), empty values may be returned for view columns that are specified more than once, for example, if a view column is specified in the SELECT column list as well as in the WHERE condition.
Fixed a problem specific to 64-bit SQL/R versions where large literal integer values in an SQL statement were not correctly converted (#4123).
This problem did not affect 32-bit SQL/R versions.
A literal integer value exceeding the 32-bit integer range (less than -2,147,483,648 or greater than 2,147,483,647) was wrongly truncated to 32-bit instead of being converted to a floating point value.
Fixed a problem on the Windows platform where in rare cases a new ODBC connection could not be established (#4122).
The SQL/R server log messages below were issued:
```
D0: Inter-process-communication failed: child process not found (sockfd=...)
P0: User ... failed to logon at ...: no more user licenses available
```
This was caused by an internal race condition where a newly started SQL/R child process was not registered in time by the SQL/R master process.

Changes in SQL/R version A.03.05:

ODBC driver: Improved compatibility with multi-threaded programs.

Changes in SQL/R version A.03.04:

To improve performance in certain cases, the SQL/R optimizer now favors an EQUAL or IN relation over a LIKE relation (#4014).

Changes in SQL/R version A.03.03:

Added support for date fields stored in string database items.
Date fields may now be stored in X/U/B database items. The item size must be appropriate to store the encoded date value, for example X8 for a YYMD encoding. When submitting a date value to the database, it is left aligned if the item size is greater than the length of the encoded value.
Fixed a problem when rounding single-precision floating point database values to the internal double-precision format (#3699).
The precision of single-precision floating point database values was reduced to 6 digits to avoid rounding problems in certain cases.
Windows ODBC driver: The setup and connect dialogs now provide an option to use the backward-compatible A02 network protocol.
Windows ODBC driver: The setup dialog now always suggests default values for the server, service and user name entries.

Changes in SQL/R version A.03.02:

Added the LdapBindDn, LdapUserMatch and LdapGroupAttr configuration items to enhance compatibility with typical Unix LDAP and Windows Active Directory implementations.
Fixed a problem where switching to a DSN-specific LogFile/LogFlags configuration did not work in all cases.

Changes in SQL/R version A.03.01:

Support for access groups in the DSN configuration was added.
The new GroupReadList and GroupWriteList DSN configuration items may be used to restrict access by group membership.
If Security = odbc is configured, the GroupList user configuration item is used. If Security = ldap is configured and the LdapBASE configuration item is present, the LDAP group membership is used.
Support for encrypted passwords was added.
Encrypted passwords are maintained with the sqlrpasswd utility and kept in the odbc.pswd configuration file.
The PassKey DSN and user configuration items are used to refer to encrypted passwords stored in the odbc.pswd file.
Please note: While Passwords in the odbc.pswd file are encrypted with the 256-bit AES encryption algorithm and are protected against accidential disclosure, a fixed internal encryption key is used which cannot be changed by the user.
The LDAP user authentication does no longer accept an anonymous login, i.e., an empty user name or password is rejected.
The default RSA key renewal interval, used for encrypted connections, was changed from 10 to 60 minutes. The RSA key is never renewed if encrypted connections are not used.
On the HP-UX IA64 platform, shared libraries are now using the .so file extension to follow platform conventions. Symbolic links from the .sl to the .so library file names are installed.
A.03.01 rev. 2: Fixed a compatibility problem of the Windows ODBC driver when used with Apache/PHP. On Windows, Apache/PHP affect the runtime libraries of shared libraries such as the ODBC driver. For compatibility with Apache/PHP, the SQL/R ODBC driver must use the WINSOCK version 2 runtime library. Other applications using the ODBC driver are not affected.