====================================================================== SQL/R A.03.11 - Server Release Notes / UNIX - 23.05.2014 ====================================================================== This document is specific to the UNIX version of SQL/R. A separate document is available to cover the Windows platforms. 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 The Eloquence database version A.06.10 and newer are supported. In addition, the Eloquence 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: --------- * New functionality and known issues * Installation on the HP-UX and Linux Platform * Configuring your system * SQL/R example database ---------------------------------------------------------------------- New functionality and known issues ---------------------------------------------------------------------- New functionality ----------------- * 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 eloqdb. 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 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 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 may 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). * SQL/R A.03.xx is installed in separate directories (/opt/sqlr2 and /etc/opt/sqlr2) and can be used in parallel with an old A.01.xx release. ODBC client ("driver") on UNIX ------------------------------ The ODBC client software ("ODBC driver") is included in the HP-UX and Linux distributions and installed below the /opt/sqlr2/lib (32-bit client) and/or /opt/sqlr2/lib64 (64-bit client) directories. On platforms that include the 64-bit client, an additional variant of the client is installed that supports the Microsoft 64-bit ODBC API definition. This client library is named libsqlrodbc64. Technical background: When the ODBC functionality was first used on 64-bit HP-UX and Linux platforms, for example with Apache/PHP through the unixODBC driver manager, the Microsoft 64-bit ODBC API definition was not yet published. However, as a 64-bit API was required but no standard was available yet, implementations such as the unixODBC driver manager derived the 64-bit ODBC API from the 32-bit ODBC API. This 64-bit API definition, nowadays known as LEGACY_64_BIT_MODE is incompatible with the meanwhile published Microsoft 64-bit ODBC API definition, as it uses 32-bit data types for SQLLEN, SQLULEN and SQLSETPOSIROW, while Microsoft defines these data types as 64-bit. As a consequence, when using 64-bit ODBC on HP-UX or Linux, the application must match the API of both the driver manager and the ODBC client library. If the application uses the LEGACY_64_BIT_MODE API, the driver manager and the ODBC client library must both support this API. For SQL/R, this means that the libsqlrodbc ODBC driver library must be used. If the application uses the Microsoft 64-bit ODBC API (also known as REAL_64_BIT_MODE), the driver manager and the ODBC client library must both support this API. For SQL/R, this means that the libsqlrodbc64 ODBC driver library must be used. 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. ---------------------------------------------------------------------- Installation on the HP-UX platform ---------------------------------------------------------------------- SQL/R requires HP-UX revision 11.11 or newer. SQL/R is installed in the /opt/sqlr2 directory and replaces a previously installed SQLR version. Installing on HP-UX ------------------- As "superuser," follow the steps below to install the SQL/R software. 1. Run "swinstall" by typing: /usr/sbin/swinstall -s /tmp/SQLR-A0311-1.depot where /tmp is the directory where the SQLR-A0311-1.depot file is located. 2. In the Software Selection Window, select/highlight the product options you would like to install then choose the "Mark for Install" item from the Actions Menu. The "Marked?" column will automatically be set to "Yes". 3. Select the "Install (analysis...)" item from the Actions Menu. When the analysis is finished with no error, i.e. Status:Ready, click OK. 4. Choose Yes in the Confirmation window to begin the actual installation process. 5. When the installation is completed, a dialog is displayed to notify you that the install task is completed. You may exit then. ---------------------------------------------------------------------- Installation on the Linux platform ---------------------------------------------------------------------- On the Linux platform, SQL/R is available as separate rpm archives for different architectures and glibc versions. Contemporary systems: SQLR-A0311-1.i686.rpm glibc2.3 (or newer) based systems (eg. SLES 9, RHEL 4) SQLR-A0311-1.ia64.rpm Itanium based systems running SLES9 (or equivalent Red Hat) SQLR-A0311-1.x86_64.rpm AMD64/Intel64 based systems running SLES9 (or equivalent Red Hat) Installation: ------------- To install SQL/R with the rpm package manager, execute the command below as root: rpm -i SQLR-A0311-1.i686.rpm Note: SQL/R requires a network card installed as eth0. If you don't have a network card installed, you will be unable to install a permanent license key. Update: ------- To update SQL/R on the Linux platform, execute the command below as root: rpm -U SQLR-A0311-1.i686.rpm Uninstall: ---------- To uninstall SQL/R on the Linux platform, execute the command below as root: rpm -e SQLR Uninstall does not remove the configuration files. They must be removed manually. The following command removes the SQL/R configuration files. It must be executed as root: rm -r /etc/opt/sqlr2 ---------------------------------------------------------------------- Configuring your system ---------------------------------------------------------------------- 1. Server startup/shutdown on Linux ----------------------------------- The SQL/R server process (sqlrodbcd) can be started automatically by init when your system enters runlevel 2. Since this mechanism is slightly different, depending on your Linux distribution, it is currently only installed automatically if a SuSE Linux distribution is recognized. Please refer to /opt/sqlr2/newconfig/startup/README for more information. 2. Configure service name ------------------------- The default SQL/R configuration requires an entry like below in your /etc/services file: sqlrodbc 8003/tcp # SQL/R ODBC The port number 8003 is choosen arbitrarily. Please make sure it is not already in use. 3. Create SQL/R user and group ------------------------------ The default SQL/R configuration requires a user "sqlr" and a group "sqlr". This is defined in the odbc.cfg config file and is used when the server is started with root privileges. Of course, you can use a different user account or group instead. The user/group should not provide any special privileges. 4. Location of SQL/R configuration files ---------------------------------------- The SQL/R configuration files are located in the /etc/opt/sqlr2 directory. 5. SQL/R A.03.xx license key ---------------------------- SQL/R A.03.xx requires a license key in the SQL/R license file (which is located in the SQL/R configuration directory). If an Eloquence Personal Edition is installed and no valid SQL/R license key is detected, a two user license is granted automatically. 6. SQL/R Configuration files ---------------------------- The SQL/R ODBC server is configured through the files odbc.cfg - server configuration odbc.dsn - defines the data sources (data bases) odbc.user - defines the users known to the ODBC server During the initial installation, default configuration files are installed. Existing SQL/R configuration files are not overwritten during the installation or update process. You should check the default configuration files after the initial installation. By default, the SQL/R server writes its log messages to the syslog. You can define a separate log file either in the odbc.cfg configuration file or on the command line. Template configuration files are located in the /opt/sqlr2/newconfig/config directory. 7. Creating the SQL/R example database -------------------------------------- As described in the next section, the SQL/R example database must be created manually. Your odbc.dsn configuration file must be modified accordingly. 8. 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. ---------------------------------------------------------------------- SQL/R example database ---------------------------------------------------------------------- SQL/R ODBC includes a sample database. It must be created manually. Creating the SQL/R example database =================================== The SQL/R sample database is available with German or english table and item names. The instructions below assume you want to install the english version. To install the German version, please replace all instances /opt/sqlr2/share/db.e with /opt/sqlr2/share/db.g and adjust the odbc.dsn file as below: [SQL/R Example] Repository = /opt/sqlr2/share/db.e/db.rep Creating the sample database ============================ Create the SQL/R sample database in your default eloqdb server using the following commands /opt/eloquence/8.0/bin/schema -u dba db.schema /opt/eloquence/8.0/bin/dbcreate -u dba db /opt/eloquence/8.0/bin/dbimport -u public -vs db.exp db Adjust the odbc.dsn configuration file as below: [SQL/R Example] Database=localhost:eloqdb/db