SQL/R A.03.00 Release Notes

SQL/R A.03.00 Changes

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 paramter 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.