SQL/R ODBC A.03.11 Release Notes for Windows

Welcome to the SQL/R A.03.11 version for the Windows platform.

SQL/R A.03.xx supported platforms:

HP-UX PA-RISC based systems: HP-UX 11iv1 (11.11) or newer
HP-UX Itanium based systems: HP-UX 11iv2 (11.23) or newer
Linux glibc2.3 based systems or newer
Windows 2000, XP, 2003, Vista, 2008, Windows 7 and Windows 8

In addition to the Eloquence database since version A.06.xx, the Eloquence database version A.05.xx is supported on the HP-UX PA-RISC platform.

ODBC drivers are available for the Windows, Linux and HP-UX platforms.

1. New functionality and known issues
2. SQL/R ODBC Server Configuration
3. Additional information
4. SQL/R example database

1. New functionality and known issues

SQL/R A.03.xx adds a secure network protocol, using an RSA encrypted key exchange method and AES session encryption keys.
SQL/R A.03.xx adds the LDAP user authentication method.
SQL/R A.03.xx supports the Eloquence database auditing function. The ODBC driver submits login, user and process audit information through the SQL/R server to the Eloquence database server.
SQL/R A.03.xx adds support for storing passwords in an encrypted form.
SQL/R A.03.xx adds support for groups (Security = odbc/ldap).
The virtual column ROWID is included for each table which is maintained by an eloqdb6. It returns the record number.
Almost all of the standard SQL scalar functions and type conversions are available.
The SQLInfo function has been changed to indicate the new server functionality.
Type conversion in the client software has been fixed.
The SQL/R ODBC Server now includes a Web interface which provides a status display. It is enabled if the HttpService in the odbc.cfg configuration file is specified.
Support for views has been added to the ODBC server backend. A view is specified using the CREATE VIEW statement and can either be specified in the repository file or submitted dynamically.
TurboIMAGE item types (I,K,U,P,Z) are now supported.
Bulk fetch of results has been added as an option.
Support for case insensitive indexes has been added.
The SQL/R A.03.xx client / ODBC driver by default uses a new network protocol which supports the encryption and auditing functionality.
This protocol is no longer compatible with previous SQL/R A.02.xx server versions.
To communicate with a previous SQL/R A.02.xx server, the SQL/R client / ODBC driver must use the backward-compatible A02 network protocol.
The A02 network protocol is used:
- either if the environment variable SQLR_PROTOCOL=A02 is set
- or if the Protocol=A02; option in the ODBC connection string is specified
- or if the Protocol=A02 option is specified in the data source definition in the odbc.ini configuration file of the ODBC driver manager
- or, on the Windows platform, if the option to use the A02 network protocol is checked in the data source configuration dialog
- or, on the Windows platform, if Protocol=A02 is configured in the [Config] section of the ODBC driver configuration file (Start Menu - Programs - SQLR - ODBC Driver Configuration).

Limitations

Some features of the SQL kernel are not yet available.
This includes:
- Sub SELECT, UNION SELECT
  - not supported yet
These features are not required by the ODBC standard. We intend to include them in a subsequent release.
ASYNC mode is currently not implemented on the client side. This will result in your PC stay busy while transferring data.
This is an optional part of the ODBC standard. We plan to include it in a subsequent release.
SELECT DISTINCT has no effect.
SQLExtendedFetch() has been implemented for application compatibility reasons. Its functionality is currently limited as it returns one row per invocation only.
SQLDescribeParam() for a prepared statement may return the SQL_CHAR type instead of the correct type. This is compensated for internally by performing implicit data type conversions (CHAR -> NUMERIC and CHAR -> DATE/TIME).

Fixed Problems / Changes

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: The SQL/R server now recognizes network interfaces not named ethx.

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.
A.03.10 rev. 2: Changed the SQL/R client to match the above limit (4095 characters) for string literals and row output.
A.03.10 rev. 2, 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.
A.03.10 rev. 3, 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).
A.03.10 rev. 4: 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.

2. SQL/R ODBC Server Configuration

In order to run properly, the SQL/R ODBC Server must be configured prior to its first usage. The following steps should be taken and are described below:

2a. Registering the SQL/R ODBC Server with Windows
2b. Configuring your system
2c. Configuring the SQL/R ODBC Server
2d. Configuring the SQL/R ODBC Server startup
2e. Controlling the SQL/R ODBC Server from the command line
2f. The SQL/R ODBC Server HTTP status display

2a. Registering the SQL/R ODBC Server with Windows

The SQL/R ODBC Server is installed as a Windows service. During the installation process the sqlrodbcd.exe executable is installed in the Windows system directory (C:\Windows\System32). In addition, it is automatically registered with the Windows Service Control Manager.

NOTE: Manual registration of the SQL/R ODBC Server with the Windows Service Control Manager is usually not required since the installation program performs a default registration. However, before uninstallation the SQL/R ODBC Server should be manually unregistered.

In order to manually register the SQL/R ODBC Server with the Windows Service Control Manager you must execute sqlrodbcd.exe with the -install argument:

Log on as Administrator.
Open a Command Prompt window.
Execute the command: sqlrodbcd -install
This makes the SQL/R ODBC Server available to the Windows Service Control Manager. The service name is "SQLRODBC".

To unregister, use the command: sqlrodbcd -remove
Please make sure you unregister the SQL/R ODBC Server before you uninstall the SQL/R software.

2b. Configuring your System

For SQL/R, the installation program adds the following default TCP service to your local SERVICES file:

   #
   # SQL/R related services
   #
   sqlrodbc        8003/tcp    # SQL/R ODBC

where the first column specifies the service name ("sqlrodbc") and the second column the associated port number and protocol ("8003/tcp"). The installation program takes care that the selected port number is not already used otherwise.

In rare cases it might be necessary to modify this preset configuration, for example if the port number does not match the one configured on other systems.

The location of your SERVICES file depends on your Windows version:

Windows XP:
C:\Windows\System32\Drivers\etc\SERVICES
Windows 2000/NT:
C:\WINNT\System32\Drivers\etc\SERVICES

2c. Configuring the SQL/R ODBC Server

The SQL/R ODBC Server is configured by editing the odbc.cfg, odbc.user and odbc.dsn configuration files. They are located in the SQL/R configuration directory. All configuration files provide complete inline documentation.

The location of the SQL/R configuration directory depends on where SQL/R has been installed. All SQL/R configuration files are installed in the etc subdirectory of the SQL/R installation directory. This is usually C:\Programs\Marxmeier\SQLR\etc.

Each configuration file is responsible for a specific part of the SQL/R ODBC Server configuration:

odbc.cfg This is the general server configuration file.
odbc.user In this file all users are defined who are allowed to access the ODBC server.
odbc.dsn This file defines all accessible data sources on the server.

2d. Configuring the SQL/R ODBC Server startup

After the SQL/R ODBC Server has been registered with the Windows Service Control Manager it can be accessed using the Services applet.

Windows XP:

Open the Windows Control Panel
Select Performance and Maintenance
Select Administrative Tools
Open the Services applet

Windows 2000:

Open the Windows Control Panel
Select Administrative Tools
Open the Services applet

Windows NT:

Open the Windows Control Panel
Open the Services applet

Locate the SQLRODBC entry in the list and select it with the mouse. On Windows XP/2000, open the Properties dialog, either from the menu or using the context menu (right mouse button). On Windows NT, click the Startup... button to open the Startup Options dialog:

If you want the SQL/R ODBC Server to be automatically started each time Windows is restarted, you should select the Automatic Startup Type. This is the recommended setting.
If you select the Manual Startup Type, you have to manually start the SQL/R ODBC Server each time Windows is restarted.
By default, the Log On As option is set to System Account. For maximum security, you should create a special account for the SQL/R ODBC Server, e.g. "sqlr". The account should be specified using the This Account option. Next, you should limit access to the SQL/R ODBC Server configuration files odbc.cfg, odbc.user and odbc.dsn to this account only.

When you have setup the Startup Options, you can define additional arguments for the SQL/R ODBC Server as an option. This is usually not required since all settings are normally provided in the odbc.cfg configuration file. However this can be used for tracking down problems (e.g. starting the SQL/R ODBC Server temporarily with different log options).

Possible arguments are:

   -d flags = log flags (debug mode)
   -l name  = log file name
   -s name  = service name
   -h name  = http service name

Option	Description	Equiv.*
`d flags`	Specifies the server debugging log flags.	`LogFlags`
`l name`	Specifies the server log file.	`LogFile`
`s name`	The service name (as defined in the `SERVICES` file) or the port number where the server should listen for requests. The default value is `sqlrodbc`.	`Service`
`h name`	If you want to make use of the HTTP status diplay feature of the server, use this option to specify the service name or port number where the server should listen for HTTP requests.	`HttpService`

(* equivalent configuration file directive)

For example, if you wish to specify a different TCP service name (the default is "sqlrodbc"), you could enter the following into the Startup Parameters field:

   -save -s 8567

This will make the SQL/R ODBC Server listen to the TCP/IP port number 8567. The leading -save argument makes this command line persistent, so that the same arguments are used each time the SQL/R ODBC Server is started. If you do not specify the -save argument this command line will be used only once.

If you want to make sure that the command line is empty and that any persistent command line is deleted, just specify -save without any additional arguments.

At this point, everything is configured and the SQL/R ODBC Server can be started:

Windows XP/2000
Select the SQLRODBC entry in the list, then choose Start either from the menu or using the context menu (right mouse button).
Windows NT
Select the SQLRODBC entry in the list, then click the Start button.

If you did not specify a log file, the SQL/R ODBC Server will write log messages to the Windows Event Log. In case the SQL/R ODBC Server does not start the Event Log will probably contain an error message pointing out the cause of the problem. You should also periodically check the Event Log in order to get aware of possible configuration problems.

Windows XP:

Open the Windows Control Panel
Select Performance and Maintenance
Select Administrative Tools
Open the Event Viewer applet
Select the Application Log

Windows 2000:

Open the Windows Control Panel
Select Administrative Tools
Open the Event Viewer applet
Select the Application Log

Windows NT:

Select Programs - Administrative Tools in the Windows Start Menu
Start the Event Viewer
From the Log menu, select Application

Having started the SQL/R ODBC Server for the first time after installation, the Application Log should already contain a few entries noting that the SQL/R ODBC Server was successfully installed and started.

2e. Controlling the SQL/R ODBC Server from the command line

The net start and net stop system commands can be used to start and stop the SQL/R ODBC Server from the command line. This is equivalent to starting and stopping the server using the Windows Services applet as explained in the previous section.

Command syntax:

   net start SQLRODBC
   net stop SQLRODBC

2f. The SQL/R ODBC Server HTTP status display

When the HttpService is defined in the odbc.cfg configuration file, you can use a WEB browser such as Mozilla, Netscape or Internet Explorer to view the configuration and state of the SQL/R ODBC Server process in your network.

To access the SQL/R ODBC Server, you provide an URL like this:

   http://server:port/

where server is the host name or IP number of the system running the SQL/R ODBC Server and port is the port number used for HttpService in the odbc.cfg file.

3. Additional information

Additional information on SQL/R is available on the Internet.

Please refer to the URL http://www.marxmeier.com/sqlr. The previous SQL/R ODBC documentation is available in PDF format on the SQL/R web page.

4. SQL/R example database

SQL/R ODBC includes a sample database. It must be created manually. The SQL/R sample database is available with English or German table and item names.

First, the sample database should be installed in an Eloquence eloqdb6 database environment which must be already setup and running.

For the English version, open a command shell window and change to the C:\Programs\Marxmeier\SQLR\share\db.e directory.
For the German version, open a command shell window and change to the C:\Programs\Marxmeier\SQLR\share\db.g directory.

The following instructions install the SQL/R sample database in your default eloqdb6 server:

   schema -u dba db.schema
   dbcreate -u dba db
   dbimport -u public -vs db.exp db

Notes:

This assumes that the Eloquence bin directory is included in the PATH. If not, prepend the commands above with C:\Programs\Eloquence\bin\ (depends on the location of your Eloquence installation).
The -u dba and -u public options can be omitted since Eloquence B.07.00.

Next, adjust the odbc.dsn configuration file as below.

For the English version:

  [SQL/R Example]
  Database=localhost:eloqdb/db
  Repository=C:/Programs/Marxmeier/SQLR/share/db.e/db.rep

For the German version:

  [SQL/R Example]
  Database=localhost:eloqdb/db
  Repository=C:/Programs/Marxmeier/SQLR/share/db.g/db.rep

`odbc.cfg`	This is the general server configuration file.
`odbc.user`	In this file all users are defined who are allowed to access the ODBC server.
`odbc.dsn`	This file defines all accessible data sources on the server.