SQL/R ODBC A.03.09 Release Notes for Windows

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.

Contents

• 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

• 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

• 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

• 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:

1. Log on as Administrator.

2. Open a Command Prompt window.

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

   #
   # 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 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

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

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

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

Command syntax:

   net start SQLRODBC
   net stop SQLRODBC

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

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

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

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.

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