====================================================================== SQL/R A.02.99 - Server Release Notes / UNIX - 21.02.2013 ====================================================================== This document is specific to the UNIX version of SQL/R. A separate document is available to cover the Windows platforms. This SQL/R A.02.99 is available and supported on all platforms Eloquence versions B.08.10, B.08.00, B.07.10, B.07.00 and B.06.32 are available on: * HP-UX 11.11 or newer (PA-RISC and Itanium) * Linux glibc2.1 or newer (Intel 386/686, AMD64/Intel64 and Itanium) * Windows NT, 2000, XP, 2003, Vista, 2008 and Windows 7 SQL/R A.02.99 supports the Eloquence eloqdb6 database from version A.06.10 to B.08.10 on all platforms as well as the Eloquence A.05.xx database on the HP-UX 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 supports the Eloquence eloqdb6 database (A.06.10 and newer) * SQL/R is available on the same platforms as Eloquence B.08.10, B.08.00, B.07.10, B.07.00 and B.06.32 * 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. 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.02.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. Fixed Problems / Changes ------------------------ The following problems have been fixed since the A.02.98 release: * 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. The following problems have been fixed since the A.02.97 release: * To improve performance in certain cases, the SQL/R optimizer now favors an EQUAL or IN relation over a LIKE relation (#4014). The following problems have been fixed since the A.02.96 release: * 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 dialog now always suggests default values for the server, service and user name entries. The following problems have been fixed since the A.02.95 release: * Fixed a problem where NULL date values were not correctly sorted in a statement using ORDER BY (#3842). This was caused by a side effect of a change in SQLR A.02.90. * To improve ODBC application compatibility, the LONGVARCHAR data type is now used for character data size with more than 254 characters. * The SQLColAttributes ODBC function was extended to accept the attributes defined by the ODBC 3.0 standard. * A.02.96 rev. 2: Fixed a problem where Microsoft Query by default used the ROWID column to join two tables (#3690). This was caused as a side effect of the SQLSpecialColumns() implementation introduced with SQLR A.02.87. * A.02.96 rev. 3: Passwords are no longer visible in the log output. * A.02.96 rev. 4: 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. The following problems have been fixed since the A.02.94 release: * Fixed a problem with COUNT/SUM/AVG/MIN/MAX expressions where NULL values could cause unexpected results (#3823). * In sqlrexec or Sqlr.DLL, NULL values were incorrectly counted in a CALCULATE COUNT/SUM/AVG/MIN/MAX expression (#3823). The following problems have been fixed since the A.02.93 release: * Fixed a problem when comparing NULL date/time values with numeric zero values in a BETWEEN or IN expression (#3816). This may also affect multiple date/time comparisons of the same column with different values combined in an OR expression, as this is internally optimized to an IN expression. This problem is a side effect of the 1970-01-01 date value fix introduced in SQL/R A.02.86 (#3671). The following problems have been fixed since the A.02.92 release: * Fixed problem in a join condition when comparing date/time fields with a numeric value (#3816). For example, a DATE <> 0 comparison in a join condition might cause unexpected results. This was caused by a side effect of a change in SQLR A.02.90. * Comparing a date field with a numeric value did not work in a HAVING clause (#3735). * Changed the default configuration for UseDouble config item in the odbc.dsn file. Previous versions defaulted to UseDouble = no. This config item was used to work around a bug in ODBC client drivers before A.02.50 but could result in precision loss status in some cases. * Fixed SqlrColumnInfo.name == NULL problem on constant column (#3791). The following problems have been fixed since the A.02.91 release: * Fixed a problem that could cause the SQL/R server to abort with a panic message like below when filtering on a nonexisting ROWID (#3093): Assertion failed: rem_sz >= acc->scan_ctx->rec_size For example, a statement like below could trigger this problem if the specified ROWID did not exist: SELECT * FROM CUSTOMERS WHERE ROWID=42 * To improve ODBC application compatibility, an optional table correlation name in the FROM clause may be prefixed with the AS keyword (#3789). For example, both of the statements below are valid: SELECT * FROM CUSTOMERS C, ORDERS O WHERE C.CUSTNO=O.CUSTNO SELECT * FROM CUSTOMERS AS C, ORDERS AS O WHERE C.CUSTNO=O.CUSTNO * On HP-UX and Linux, the SQL/R ODBC driver was enhanced to use the driver manager functionality, if present, to read a data source configuration from the odbc.ini file. If the SQL/R ODBC driver is invoked through an ODBC driver manager such as unixODBC, and this driver manager exports the ODBC function SQLGetPrivateProfileString(), this function is now used to read a data source configuration from the odbc.ini file. Previously, the SQL/R ODBC driver used its own functionality to read a data source configuration from the odbc.ini file. However, this requires that the ODBCINI environment variable is set to appropriately refer to the odbc.ini file, unless it is located in the /etc directory (/etc/odbc.ini) or the invoking user's home directory ($HOME/.odbc.ini). If the driver manager does not export SQLGetPrivateProfileString(), the SQL/R ODBC driver falls back to using its own functionality to read a data source configuration from the odbc.ini file. * On HP-UX and Linux, the SQL/R distributions that include the 64-bit ODBC driver now in addition provide a variant of the 64-bit ODBC driver that supports the Microsoft 64-bit ODBC API definition. This driver 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 driver. If the application uses the LEGACY_64_BIT_MODE API, the driver manager and the ODBC driver 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 driver must both support this API. For SQL/R, this means that the libsqlrodbc64 ODBC driver library must be used. The following problems have been fixed since the A.02.90 release: * The SQL TIMESTAMPADD() function returned wrong results if used with a zero date argument (#3784). This problem is a side effect of the 1970-01-01 date value fix introduced in SQL/R A.02.86 (#3671). * A workaround was implemented so that the SQL/R ODBC driver no longer causes a distributed transaction error message when used within a Microsoft SQL Server database trigger procedure. Please note that this workaround simply fakes success on a SQLSetConnectOption(SQL_ATTR_ENLIST_IN_DTC) invocation to work around an integration problem with Microsoft SQL Server. The SQL/R ODBC driver does not support the OLE transaction protocol necessary to integrate into a distributed transaction. If used in a trigger procedure, the SQL/R ODBC driver performs an autonomous transaction. The following problems have been fixed since the A.02.89 release: * Fixed a backward compatibility problem with NULL date or time values (#3735). In SQL/R versions before A.02.86, a NULL date or time value would match a numeric zero or an empty string in a comparison. For example, SELECT ... WHERE orderdate = 0 would match all NULL orderdate values if orderdate is a date type. As a side effect of the 1970-01-01 date value fix introduced in SQL/R A.02.86 (#3671), this was no longer possible. A NULL date or time value would then only match an IS NULL comparison. The SQL/R A.02.90 version restores the previous behavior. Any NULL date or time value now matches either a numeric zero, an empty string or an IS NULL expression. * A problem was fixed which could cause wrong results if different databases were joined which contain tables or views with identical names and columns (#3736). For example, if database DB1 and database DB2 both contain a table named CUSTOMERS and both of these tables have a column named CUSTNO the statement below returned wrong results: SELECT * FROM DB1.CUSTOMERS, DB2.CUSTOMERS WHERE DB1.CUSTOMERS.CUSTNO=DB2.CUSTOMERS.CUSTNO The problem did not occur when table alias names were used, as in: SELECT * FROM DB1.CUSTOMERS CUST1, DB2.CUSTOMERS CUST2 WHERE CUST1.CUSTNO=CUST2.CUSTNO The following problems have been fixed since the A.02.88 release: * Single-precision floating point database values were not always correctly converted to the internal double-precision format (#3699). In some cases additional post-decimal digits could be added because the conversion result was not rounded. The following problems have been fixed since the A.02.87 release: * Added support for 48 bit integer item types (TurboIMAGE item types I3/J3/K3) (#3692). Previously, use of a 48 bit item type resulted in a SQL/R server panic message like below: panic: ImageDb6::cvdb2dbl, file src/sql/image6/iconvrt.cxx, line 396 On the client side, this caused a connection failure error message. * In some cases an empty string comparison could fail (#3695). This could result in wrong query results. The following problems have been fixed since the A.02.86 release: * A wrong primary key info could be returned to the application if the search item in a manual master is not the first item (#3690). * To improve ODBC application compatibility the SQLSpecialColumns() functionality has been implemented. The following problems have been fixed since the A.02.85 release: * A date value of 1970-01-01 was internally interpreted as a NULL value. As a consequence, 1970-01-01 could not be output or used in a WHERE condition (#3671). * Setting the tmpDir configuration item in the [Config] section of the odbc.cfg configuration file had no effect unless the TMPDIR environment variable was not set (TMP environment variable on Windows) (#3685). * In 64 bit builds, log messages containing Eloquence status codes were in some cases output in a wrong numerical format (#3681). The following problems have been fixed since the A.02.84 release: * The SQL/R A.02.85 release provides a 64 bit ODBC driver for the Windows platform. On 64 bit Windows, choosing to install the SQL/R ODBC driver causes both the 32 bit and the 64 bit ODBC drivers to be installed. Please note that on 64 bit Windows there are two separate ODBC control panels present. The default ODBC control panel, located in the Administrative Tools control panel folder, is the 64 bit ODBC control panel. This is used to manage ODBC data sources that use a 64 bit ODBC driver, to be used by 64 bit programs. The 32 bit ODBC control panel is named odbcad32.exe and is located in the WINDOWS\SysWOW64 directory (C:\WINDOWS\SysWOW64\odbcad32.exe). This is used to manage ODBC data sources that use a 32 bit ODBC driver, to be used by 32 bit programs. The following problems have been fixed since the A.02.83 release: * Although the WorkAroundSignAmbiguity configuration is active, a chained access through the master index could cause an incomplete result (#3575). The WorkAroundSignAmbiguity configuration was not applied in case of a chained access. * The CATALOG_NAME option was added to the SQLGetInfo() ODBC function to improve ODBC 3.0 compatibility. The following problems have been fixed since the A.02.82 release: * Implemented a workaround for a problem when using packed decimal (P) or zoned decimal (Z) item types in a comparison or when joining two tables through a P or Z item (#3575). With P or Z items, identical values may have a different binary representation. For example, the values 42 (unsigned) and +42 (positive) have the same numeric value but differ in their binary representation. Due to a problem in the Eloquence database server, identical values with a different binary representation did not match in all cases. If SQL/R uses an index access method with a P or Z item, this could cause an incomplete result. This problem was fixed in Eloquence B.07.10 with patch PE71-0805070 (eloqdb6 database server patch as of 2008-05-07). In cases where it is not possible to update to this or a superseding patch, a workaround was implemented in SQL/R. When this workaround is enabled, SQL/R internally uses a "greater-or-equal" comparison instead of an "equal" comparison with P and Z item values and then transparently adjusts the result. To enable this workaround, the WorkAroundSignAmbiguity configuration item may be set in the [Config] section of the SQL/R odbc.cfg file: WorkAroundSignAmbiguity = 1 If this is set to 1, the workaround is enabled. The default is 0 which disables the workaround. Please note that if the Eloquence B.07.10 database server patch PE71-0805070 (or superseding) is installed, this workaround should be disabled for optimum performance. Backgrund: If the workaround is enabled, the number of transferred results is reduced to ensure an early EOF detection. If the number of matching results is high, this could have an impact on performance. The following problems have been fixed since the A.02.81 release: * A multi-line statement in a repository file could lead to a syntax error because the statement lines were joined without leaving a space character in-between (#3565). For example, the statement below: CREATE VIEW CUST_VIEW AS SELECT CUSTNO,NAME FROM CUSTOMERS; resulted in: CREATE VIEW CUST_VIEWAS SELECT CUSTNO,NAMEFROM CUSTOMERS; * The length of a single statement in a repository file was limited to 1023 characters (#3566). This could be insufficient for complex CREATE VIEW statements. Exceeding this limit caused a "statement buffer overflow" error. This has been relaxed, the current limit is 8191 characters. * The User and DSN pages in the HTTP status display did not output the current configuration, i.e., the listings were empty (#3564). When invoked, an error message like below was output to the SQL/R server log file: Ini::FindSection() failed: Invalid argument, errno=22, iniapi.c(180) This was a side effect of a modification in the SQL/R A.02.78 version (#3490). * A potential problem was fixed that in rare cases could cause a 64 bit SQL/R server build to abort on a join. In practice, this was never observed or reported. The only platforms where a 64 bit SQL/R server is currently available are Linux x86_64 and Linux ia64. The following problems have been fixed since the A.02.80 release: * Parsing the OUTER JOIN syntax was enhanced to either allow a join condition on each pair of joined tables or a common join condition after specifying all tables to be joined. This improves compatibility to common ODBC applications and utilities. Example for previous SQL/R OUTER JOIN syntax with a common join condition (still valid and supported): SELECT ... FROM {OJ LEFT OUTER JOIN
LEFT OUTER JOIN
ON AND } WHERE ... Example for alternative OUTER JOIN syntax now recognized in addition: SELECT ... FROM {OJ
LEFT OUTER JOIN
ON LEFT OUTER JOIN
ON } WHERE ... * The Oracle(TM) OUTER JOIN syntax is now supported. It is equivalent to the ODBC syntax without using the {OJ ... } specifiers. For example: SELECT ... FROM
LEFT OUTER JOIN
ON LEFT OUTER JOIN
ON WHERE ... * Using a DATE/TIME column in a GROUP BY could cause a connection abort with a message like below being written to the SQL/R server log (#3542): panic: size == sizeof(xtime_t), file ... exprval.cxx ... The problem was introduced with SQL/R A.02.68 when the internal DATE/TIME data type was extended to 64 bit. In a GROUP BY context, however, a 32 bit data type was still assumed. * A 64 bit SQL/R server build could abort a connection with a message like below being written to the SQL/R server log if an INTEGER column was used in a GROUP BY (#3541): panic: size == sizeof(long), file ... exprval.cxx ... The only platforms where a 64 bit SQL/R server is currently available are Linux x86_64 and Linux ia64. * Accessing a 64 bit SQL/R server with a 32 bit SQL/R client could result in INTEGER values always being output as zero if the server and client byte orders are different (for example, 64 bit SQL/R server on Linux accessed by 32 bit SQL/R client on HP-UX) (#3541). The only platforms where a 64 bit SQL/R server is currently available are Linux x86_64 and Linux ia64. * A 64 bit build of the SQL/R client programs (sqlrexec and Sqlr.DLL) could output wrong values instead of NULL values (#3541, #3547). The only platforms where the 64 bit SQL/R client programs are currently available are Linux x86_64 and Linux ia64. The following enhancements have been introduced with the A.02.80 release: * The Eloquence B.07.10 database adds support for case insensitive indexes with the database server patch PE71-0802120. The SQL/R optimizer was enhanced to use case insensitive indexes when appropriate (#3501). To define and use the case insensitive index functionality the Eloquence B.07.10 patches as listed below are required: Patch PE71-0802120 or newer: eloqdb6 database server Patch PE71-0801252 or newer: schema utility Patch PE71-0801253 or newer: prschema utility Patch PE71-0801254 or newer: dbutil program A case insensitive index is defined by setting the CI index option. For example in the schema file: INAME (CI) = NAME; Alternatively you may use dbutil to create or modify an index item to become case insensitive: CHANGE IITEM INAME FLAGS CI; * The optimizer was enhanced to recognize the UCASE() and LCASE() SQL functions in a WHERE condition with case insensitive indexes (#3501). If a column is used in a WHERE condition with the UCASE() or LCASE() SQL functions and a case insensitive index is available for this column, SQL/R uses this index to perform an optimized access. For example: SELECT * FROM CUSTOMERS WHERE UCASE(NAME) LIKE "MAR%" SELECT * FROM CUSTOMERS WHERE LCASE(NAME) LIKE "mar%" Both statements would match on CUSTOMERS.NAME beginning with "MAR" in a case insensitive manner, for example on "MARTIN" and "Marcus". If the database provides a case insensitive index on CUSTOMERS.NAME SQL/R will use this index in this context. * The optimizer was enhanced to use an index if a partial column value is used in a comparison (#3501). In the WHERE condition, if a column is used with the LEFT() or SUBSTRING() SQL functions and an index is available for this column, SQL/R uses this index to perform an optimized search on a partial value. For example: SELECT * FROM CUSTOMERS WHERE LEFT(NAME,3) = "MAR" SELECT * FROM CUSTOMERS WHERE SUBSTRING(NAME,1,3) = "MAR" Both statements would match on CUSTOMERS.NAME beginning with "MAR", for example on "MARTIN" and "MARCUS". Please note that a SUBSTRING comparison only uses an index if it starts with 1, as in the example above. This may also be combined with a case insensitive comparison, such as: SELECT * FROM CUSTOMERS WHERE UCASE(LEFT(NAME,3)) = "MAR" SELECT * FROM CUSTOMERS WHERE SUBSTRING(LCASE(NAME),1,3) = "mar" Both statements would match on CUSTOMERS.NAME beginning with "MAR" in a case insensitive manner, for example on "MARTIN" and "Marcus". * If multiple indexes are present for a column, SQL/R now favors a longer index over a shorter index and a lower number of index segments over a higher number of index segments. This may in some cases result in a more efficient index access. The following problems have been fixed since the A.02.77 release: * Under rare conditions, connecting to a data source could fail with an "Invalid DSN or access denied" error message (#3490). The following problems have been fixed since the A.02.76 release: * The new EscapeChar data source configuration item was added to the odbc.dsn configuration file. It may be used to specify the character used to escape a quotation mark in a string value. By default the backslash escape character is in effect. If an empty escape character is configured, escaping is disabled altogether. * On the Windows platform, ORDER BY and/or GROUP BY could fail if a previous SQL/R process with the same process id was terminated unexpectedly and therefore did not remove its temporary files. The new implementation now ensures that any temporary files are always properly removed. Should there be any left-over files from a previously failed process of an older SQL/R version, these will be reused. * On the HP-UX and Linux platforms the /var/tmp directory is now used by default to hold ORDER BY and/or GROUP BY temporary files. * In rare cases with only very little temporary disk space left, ORDER BY and/or GROUP BY could fail in a way that the same process could no longer execute any subsequent ORDER BY and/or GROUP BY. The following problems have been fixed since the A.02.75 release: * The new SortOrder config item was added to the odbc.cfg configuration file to specify a user defined collating sequence used to sort string values. The collating sequence is defined as a language specifier and either a "fold" or "nofold" modifier separated by a @ character, such as "french@nofold". A "fold" sort order interleaves upper- and lowercase characters such as AaBbCc...Zz, where a "nofold" sort order separates upper- and lowercase characters, such as ABC...Z abc...z. For example: sortorder = german@nofold The supported collating sequences are installed in the directory /opt/sqlr2/lib/nls/collate on HP-UX and Linux or the lib/nls/collate subdirectory below the SQL/R installation directory on Windows. If not defined, string values are ordered by their binary value (in ASCII order). * If an UPDATE statement was used to set an array element other than the first to NULL, subsequent items in the record could become overwritten (#3346). * The INSERT statement did not correctly initialize arrays of item type PACKED. If not specified in the INSERT statement, all but the last element was set to binary zeros (#3346). * A partial index that is used in a WHERE condition could cause a connection abort (#3356). * In the HP-UX pa11 build a date conversion could result in wrong results (#3351). This was caused by a bug in the HP aCC compiler. Please note that this problem only affects the HP-UX pa11 releases of SQL/R A.02.70 and newer. SQL/R versions A.02.69 and before as well as the HP-UX pa11_1020 releases of SQL/R A.02.70 and newer are not affected. * A syntax error was returned if two parameters were concatenated, such as ?&? or CONCAT(?,?) (#3342). * The optimizer now favors an index that covers an entire item over an index that covers only part of an item (#3280). * If space characters preceded a user name in the UserList, ReadList and/or WriteList data source configuration, the user name was not recognized (#1178). The following problems have been fixed since the A.02.74 release: * Fixed a parser problem that could result in random syntax errors. This was introduced in the A.02.73 release (#2932). The following problems have been fixed since the A.02.73 release: * A SELECT statement using ORDER BY and/or GROUP BY could fail in rare cases with an error message like below: CtFile::AllocIndexFile(...) failed on OPNFIL: ... errno=2 CtFile::ReadData(...) failed on fread(): ... errno=2 This was caused by a race condition when accessing temporary results from concurrent sessions (#3317). * If a problem is encountered while reading a configuration file, a detailed diagnostic message is now written to the log file. The following problems have been fixed since the A.02.72 release: * Fixed a performance problem where the optimizer could choose an unfavorable execution plan (#3280). The optimizer now favors the table that has the highest occurrence count in the WHERE condition. * The backslash character in quoted strings was used as an universal escape character (#2932). This caused undesired results. In the current implementation, the backslash character only works as an escape character in front of another backslash character or the quotation mark used to quote the string. This means that \\ would become \ and \' or \" would become ' or " depending on whether the string was quoted with ' or ". In all other cases the backslash character is taken literally. * To specify a quotation mark within a quoted string, the quotation mark may be doubled, as specified in the ANSI SQL standard (#2932). This means that '' or "" would become ' or " depending on whether the string was quoted with ' or ". The following problems have been fixed since the A.02.71 release: * If a connection was established with the OPEN DATABASE statement, the SQL_FLOAT data type was used for floating point values. This could cause undesired rounding differences. To solve this, the SQL_DOUBLE data type is now used. * On Windows, the maximum possible number of SQL/R server connections was limited by the size of the "desktop heap for the noninteractive window station". A workaround has been implemented which overcomes this limitation of the Windows operating system. The following problems have been fixed since the A.02.70 release: * Performance improvements in the SQL/R server and the ODBC driver. The following problems have been fixed since the A.02.69 release: * The UCASE() and LCASE() SQL functions did not correctly handle national characters ("Umlaute"). * A thread locking problem in the ODBC driver was corrected. The following problems have been fixed since the A.02.68 release: * Date values beyond the year 2038 were not recognized due to an internal limitation of the 32 bit date/time encoding (#2780). Valid date values now range from 1900-01-01 to 3000-12-31. * The CURDATE(), CURTIME() and NOW() functions were modified to return the current local time (#3202). Previously the current time was returned in UTC (coordinated universal time). * For SQL compatibility reasons the COUNT(1) expression syntax is now recognized as an equivalent for COUNT(*). * On HP-UX and Linux a non-existing user/group specified in the config file could result in the server process aborting during startup with a segmentation fault. The following problems have been fixed since the A.02.67 release: * Added option to acquire database locks on write operations. * Corrected unexpected behavior on a failed write statement. In this case the entire transaction could be rolled back instead of the last sub-transactions. * Added -version commandline option to return version * Output stack dump to log file on crash or internal failure The following problems have been fixed since the A.02.66 release: * Fixed a defect when converting zoned item values in an index lookup (#2962). Depending on the value an error message like below was issued: [Marxmeier][SQL/R ODBC Server]S1000 - Something went wrong (#99) The following problems have been fixed since the A.02.65 release: * Fixed a defect when converting packed item values into the SQL_DOUBLE type (#2975). A 32bit overflow could occur on values greater than 2,147,483,647. The following problems have been fixed since the A.02.64 release: * Specifying an empty replacement string in the REPLACE() SQL function had no effect (#2872). * The connection string returned by the SQLDriverConnect ODBC function could not always be used to reestablish a connection because the PWD (password) was not included (#2920). The following problems have been fixed since the A.02.63 release: * ORDER BY ... DESC on a large number of results could cause a server crash (#2581). * The DROP VIEW statement did not work on Linux and Windows (#2852). A syntax error message was issued. * VENDOR is no longer a keyword. Previously, VENDOR was misleadingly recognized as a keyword which caused problems if a data set or item is named VENDOR. * Fixed a memory leak in the SQL/R server (#2849). * On Windows the SQL/R ODBC driver displayed a message box in case of a network problem (#2832). The driver blocked until the message box was confirmed by clicking the OK button. The following problems have been fixed since the A.02.62 release: * INSERT INTO and UPDATE statements issued a conversion error if parameters are used to set numeric column values (#2808). * On Windows the SQL/R server aborted with an error message as below if more than 62 concurrent connections are opened (#2818): WaitForMultipleObjects() failed, 87: The parameter is incorrect. The following problems have been fixed since the A.02.61 release: * Some SQL functions that use multiple arguments were not always evaluated correctly (#2798). Using an IF or IFNULL argument could cause a "37000 - Invalid expression (#48)" error. This affected the SQL functions ATAN2, BAND, BIT, DIV, MOD, POWER, ROUND, TRUNCATE, DATE, TIME, TIMESTAMPADD and TIMESTAMPDIFF. The following problems have been fixed since the A.02.60 release: * Fixed an optimizer problem where standalone master sets were handled incorrectly (#2g3). The search item of a standalone master was not recognized as an index. * The WEEKBEG function has been changed to return Monday as the first day of the week instead of Sunday. The following problems have been fixed since the A.02.59 release: * Support bulk fetch method to transfer results from the server. To be active a driver version A.02.60 or newer must be used. * Added a driver option to allow disabling bulk fetch transfer of data. * Bulkfetch={0|1} - Enables or disables ODBC bulk fetch 0 = bulk fetch disabled 1 = bulk fetch enabled (default) The following problems have been fixed since the A.02.58 release: * Support for the SQL_ATTR_MAX_ROWS statement/connection option was added. This may be used to limit the number of result rows for a statement. * Fixed a bug that could cause releasing allocated memory twice when the OPEN DATABASE statement was used. This could result in an error message when the connection was closed. * Added a driver option to allow disabling of identifier quoting. This works around a limitation in OpenOffice that is parsing the SQL statement it generated internally and then fails to handle quoted identifiers. The following parameters are currently supported in the SQLDriverConnect and SQLBrowseConnect ODBC functions: * DSN={name of the data source} * Server={name or IP address of the SQL/R ODBC server} * Service={service name or port number} * UID={user name} * PWD={user password} * Password={yes|no} If set to yes, on Windows a password dialog is opened. * Quoting={0|1|2} Enables or disables ODBC identifier quoting. 0 = default (enabled on Windows, disabled on HP-UX and Linux) 1 = disabled 2 = enabled The following problems have been fixed since the A.02.57 release: * Fixed a bug that caused an internal failure when using the index of a related master set to optimize access to a detail set and the master set used a different search item than the detail set. This bug was exposed by the changes in Eloquence patch PE70-0502070. The following problems have been fixed since the A.02.56 release: * Fixed a bug in optimizing queries on a master set with a search item other than the first item in the entry. This problem resulted in an invalid request to the eloqdb6 server. With some eloqdb6 server versions (before eloqdb6 patch level PE70-0502070) this invalid request could cause an eloqdb6 abort with a message like below: Assertion failed: keydef->k_len == k_ctx->idx_keysz server panic: Aborting on internal failure, file fseq.c, line 830 With Eloquence patch PE70-0502070 installed this results in a SQLR error message like below: [Marxmeier][SQL/R ODBC Server]S1000 - Something went wrong (#99) The following problems have been fixed since the A.02.55 release: * Fixed implicit type conversion of string parameters (#2562) The following problems have been fixed since the A.02.53 release: * The RowidName configuration item has been added to the odbc.dsn configuration (#2229). It specifies the name of the virtual ROWID column. The default value is "ROWID". * If a CHAR value is used to specify a DATE/TIME value, an implicit data conversion is now performed (#2562): SELECT * FROM ORDERS WHERE ORDERDATE >= '2004-12-01' Previously, this resulted in a data type clash error because CHAR and DATE/TIME are different data types. The following CHAR formats are recognized: YYYYMMDD - date without separators, example: '20041201' YYYY-MM-DD - ISO date format, example: '2004-12-01' DD-MON-YYYY - Oracle(TM) default date format, example: '01-DEC-2004' MM/DD/YYYY - American date format, example: '12/01/2004' DD.MM.YYYY - European date format, example: '01.12.2004' HHMMSS - time without separators, example: '140925' HH:MM:SS - standard time format, example: '14:09:25' HH:MM:SS.FF - time format with fraction (ignored), example: '14:09:25.00' Notes: - If a year value is specified with two digits only, the base year 1900 is assumed. - If date and time is combined, separated with a space, a TIMESTAMP value (combined date/time value) is assumed. Example: '01-DEC-2004 14:09:25.00' * The ODBC SQLExtendedFetch() call now allows null pointers to be passed as RowCountPtr and RowStatusArray arguments. The configured ROWSET_SIZE is now used to initialize the RowStatusArray, which is 1 by default but can be altered with the ODBC SQLSetStmtOption() call. The following problems have been fixed since the A.02.52 release: * Fixed a defect when converting packed item values in an index lookup. This defect had the effect that packed item values were not found when using an index. The following problems have been fixed since the A.02.51 release: * Introduced the security=db configuration which passes the SQLR logon credentials to the Eloquence database. This is configured in the [Config] section of the odbc.cfg file. When security is set to db, the user configuration in odbc.user and access lists in odbc.dsn are ignored. The DSN is considered writable, as specified by the EnableWrite configuration item and the permissions associated with the database login. * The ODBC SQLForeignKeys() call has been implemented. * The ODBC SQLPrimaryKeys() call now returns the search item for master data sets and ROWID for detail data sets. The following problems have been fixed since the A.02.50 release: * Increased the parser stack to avoid an overflow. Previous versions could fail with an "yacc stack overflow" error message. * Fixed compatibility problems with MS DTS - SQLGetTypeInfo returned NULL value for precision for some types This results in an DTS error message like below: "Error calling GetRowset to get DBSCHEMA_PROVIDER_TYPES schema info..." - SQLDescribeCol could return quote characters. If identifiers where "quoted" (enclosed in | charcters) the ODBC server also returned the column name quoted. This results in an DTS error message like below: "Unable to find column CUSTNO ..." The following problems have been fixed since the A.02.48 release: * Fixed a problem with OpenOffice. OpenOffice uses the ODBC3 SQLFetchScroll() call instead of SQLFetch(). OpenOffice fails with the error message IM001 - driver does not support this function. A partial implementation of SQLFetchScroll() was added. * Fixed a driver problem with the SQL_DOUBLE type. The driver could cause an appplication abort if the SQL_DOUBLE type is returned by the ODBC server. However OpenOffice requires the use of the SQL_DOUBLE type to work correctly with foating point results. The new UseDouble configuration item enables the use of the SQL_DOUBLE type with the server (default is currently set to off). * Fixed a driver problem with conversion of float variables. OpenOffice requests conversion of SQL_FLOAT results to the C float data type. This coonversion could cause memory corruption. * Documented the ArraySep configuration item in the odbc.cfg configration. ArraySep specifies the separator that is used to separate the name and index of array elements. The default is "__" (two underscore characters) * Added PrimaryKeys configuration item to the odbc.dsn configration. This specifies if the ODBC SQLPrimaryKeys() call should return a result. One of the following values is supported: yes, no Setting this to "no" may be useful with some ODBC enabled applications that use the MS Jet Engine. The default is yes. * Added UseDouble configuration item to the odbc.dsn configration. This specifies if the ODBC server should return the SQL_DOUBLE type. One of the following values is supported: yes, no The default is no. Future versions will likely default to "yes" By default the ODBC server does not return the SQL_DOUBLE type (and uses SQL_FLOAT instead) to work around a driver bug in ODBC drivers before version A.02.51 that could cause an application abort. However OpenOffice (and possibly other applications) requires this to be set (and an updated driver) to work correctly with floating point results. * Added EnableWrite configuration item to the odbc.dsn configuration. Specifies the database is available for write access. One of the following values: readonly, limited, full readonly - specifies read-only access to the database limited - specifies that explicit transactions must be used. This limits any write access to programs that are specifically adapted. This mode is backward compatible to previous SQLR versions. full - specifies that the datasource should support ODBC compliant transaction handling. As only limited testing was performed using different ODBC capable applictions, this mode should be considered experimental. The default is "readonly" or "limited" if a WriteList configuration item is present. We do not recommend to enable "full" write mode. * Enabled ODBC compliant transaction handling. The following problems have been fixed since the A.02.47 release: * Fix problem with NOW() function in WHERE clause (#2127) The following problems have been fixed since the A.02.46 release: * Make the AS keyword to specify a column alias optional * Fix use of table aliases with views. The following problems have been fixed since the A.02.45 release: * Fixed bug in the support for 64 bit integer items (#2135). The following problems have been fixed since the A.02.40 release: * Add support for TurboIMAGE item types. The following problems have been fixed since the A.02.39 release: * Due to a problem in the query optimizer, inner joins were not always resolved correctly (#676). Error message: Unable to resolve table dependencies The following problems have been fixed since the A.02.38 release: * Fixed a problem in query the optimizer which could cause wrong results in some cases with the A.05.xx database (#266) SELECT ... FROM table WHERE 350000 <= item AND item < 360000 In this case the first relational operator (<=) was not evaluated correctly due to operand order. * An outer join with an IS [NOT] NULL where condition did not work correctly (#582). The following problems have been fixed since the A.02.37 release: * Fixed a performance problem related to a bug in the query optimizer (#233). The following problems have been fixed since the A.02.36 release: * A SELECT statement on an outer JOIN could either cause wrong results (missing NULL results) or suffer from a very long execution time. This happened as a side effect of the A.02.36 changes to the query optimizer which sometimes failed to correctly analyze the SELECT statement (#233). * A SELECT statement without a WHERE condition could cause the odbc server to crash (#237). The following problems have been fixed since the A.02.35 release: * Views are now returned with the SQLTables() function. This makes them accessible with MS Query/Access. If the CREATE VIEW statement is added to the repository file it is created automatically each time the data source is opened. For example: CREATE VIEW SQLR.CU(CUSNO,CUSNAME) AS SELECT CUSNO,CUSNAME FROM CUSTOMER WHERE CUSNO LIKE "12%"; Please note: - Joining views is experimental and may result in server error messages or bad performance. - Views without a qualifier are not accesible from MS Query. The view as specified in the example above uses SQLR as a qualifier. * Additional outer join conditions could cause wrong results. When specifying outer join conditions beyond the table relation these conditions were ignored in previous versions possibly causing wrong results (#108). For example: select * from {oj customer left outer join address on address.custno=customer.custno and address.type="KD"}; In this example the condition address.type="KD" was ignored. * Outer join and inner join can now be combined in a single SELECT statement. For example: select * from order,{oj customer left outer join address on customer.adrid = address.adrid} where order.custno=customer.custno; Only a single outer join relation can be specified per SELECT statement. The following problems have been fixed since the A.02.34 release: * Date field could return wrong value due to timezone conversion. The following problems have been fixed since the A.02.33 release: * If a client is terminated while processing a lengthy SQL statement the associated server process is now terminated as well (UNIX and Linux platform). * When converting date and time values the timezone is no longer taken into account. * IF(x, a, b) resulted in a NULL value if the condition was NULL. This has been changed for backward compatibility so that the IF expression returns the result of b. * MIN and MAX now correctly operate on non-numerical values. The following problems have been fixed since the A.02.32 release: * The client/server protocol has been fixed to handle SQL statements larger than 64k. The following problems have been fixed since the A.02.31 release: * When a date field is used to access an index (in a join or optimizing the WHERE clause) and the key value was supplied using the TIMESTAMP SQL data type (e.g. by MS Access 2000) an internal conversion error could occur. * Using parameters in SELECT statements could cause a server crash if a parameter evaluates to NULL. The following problems have been fixed since the A.02.28 release: * When the SUBSTRING function was used to extract the rightmost character this was not included into the internal length calculation. Depending on the client program used, this could cause the rightmost character not to be displayed. The following problems have been fixed since the A.02.24 release: * Ported to Linux glibc2.1. This has not yet been tested to the same depth as previous ports and should be considered experimental for now. * Fixes for ODBC driver on the Linux/UNIX platform: - Connect option parsing has been improved to work around StarOffice issues - odbc.ini settings were ignored in SQLDriverConnect() on the Linux/UNIX platform for previous releases. This is different than the Windows platform which uses default settings either placed in the registry or an ini file ODBC.INI. If present, a file odbc.ini can now ne used to define default connection options unless more specific ones are specified in the CONNECT TO statement. The odbc.ini is an ini-style file which provides a section (enclosed in square brackets) for each data source and which can hold default default connect arguments for a specific data source. For example: [SAMPLE] Server=server.marxmeier.com Service=8003 This specifies that the data source SAMPLE is processed by an sqlrodbc server on host server.marxmeier.com. This server is connected on port 8003. The odbc.ini file is located in the following order: 1. If the environment variable ODBCINI is set, it should point to the file used as odbc.ini. 2. If a .odbc.ini file is present in the $HOME directory 3. Otherwise a global /etc/odbc.ini file is used. For additional information on the odbc.ini file format and usage, please refer to the documentation of the iODBC driver manager (in directory unsupported/iodbc). The following problems have been fixed since the A.02.23 release: * The number of values in an IN condition was limited. This could result in a "yacc stack overflow" error message. The following problems have been fixed since the A.02.22 release: * The HTTP status display did not show multiple databases associated with a single DSN. * The ODBC SQLStatistics function now correctly reports all indices associated with any table. This makes MS Access 2000 behave better with SQL/R ODBC. The following problems have been fixed since the A.02.21 release: * An IF condition which evaluates to NULL caused a runtime error. The following problems have been fixed since the A.02.20 release: * When a date field is used to access an index (in a join or optimizing the WHERE clause) SQL/R did not correctly encode serial date values after 1999-12-31 (e.g. FROM 1972). The following problems have been fixed since the A.02.19 release: * GROUP BY ... ORDER BY did not work if referred to a column containing an expression. The following problems have been fixed since the A.02.18 release: * SQLColumns could return additional columns belonging to another table. The following problems have been fixed since the A.02.15 pre-release: * When using date values in an expression, they are converted from number of seconds into number of days before they are combined with integer or floating point data types (and vice-versa). The following problems have been fixed since the A.02.14 pre-release: * Using date values in an expression caused unexpected results. * In rare cases, ORDER BY and GROUP BY accessed intermediate results which were no longer valid. The following problems have been fixed since the A.02.13 pre-release: * An IF expression could result in an formula evaluation error. The following problems have been fixed since the A.02.12 pre-release: * LIKE "%" did not match on empty strings. The following problems have been fixed since the A.02.10 pre-release: * Due to a bug in the date format scan code, the week number ("%W" date format) resulted in -1. The following problems have been fixed since the A.02.09 pre-release: * The FIELD statement (used by the repository file and sqlrexec) has been enhanced to support additional date and time encodings: FIELD qual.field CONVERT TO DATE [("encoding"[,baseyr])]; FIELD qual.field CONVERT TO TIME [("encoding")]; Where encoding describes the date field encoding in the database and baseyr provides an additional option for this encoding. This is important when a date field is used to access an index (in a join or optimizing the WHERE clause) because SQL/R must encode a value according to conventions used in the database. The following date encodings are supported: "GREG" - number of days since base year (default 1972) "YMD" - YYMMDD encoding, relative to base year (default 1900) "Y2MD" - YYMMDD encoding, split at given year (default 1970) "YYMD" - YYYYMMDD encoding "UNIX" - seconds since 1970 For example: FIELD qual.xx CONVERT TO DATE("YMD"); A value of 990424 is understood as 1999-04-24 FIELD qual.xx CONVERT TO DATE("YMD",1950); A value of 490424 is understood as 1999-04-24 FIELD qual.xx CONVERT TO DATE("Y2MD",1960) A value of 200424 is understood as 2020-04-24 A value of 990424 is understood as 1999-04-24 FIELD qual.xx CONVERT TO DATE("YYMD"); A value of 19990424 is understood as 1999-04-24 The following time encodings are supported: "HM" - 100 * hour + minute "HMS" - 10000 * hour + 100 * minure + second "HM60" - 60 * hour + minute "HMS60" - 3600 * hour + 60 * minute + second For example: FIELD qual.xx CONVERT TO TIME("HMS"); A value of 131512 is understood as 13:15:12 Please note that currently due to internal limitations a year value before 1901 or after 2036 will cause a conversion error (resulting in a NULL value). The following problems have been fixed since the A.02.08 pre-release: * STRINGFMT could cause "unexpected data type" failure if the 2nd argument contained an IF function. The following problems have been fixed since the A.02.07 pre-release: * IF or IFNULL functions could cause a server crash if the condition contained a nested IF or IFNULL function. The following problems have been fixed since the A.02.05 pre-release: * IF or IFNULL functions caused a server crash if the resulting data type could not be determined. The following problems have been fixed since the A.02.04 pre-release: * GROUP BY or ORDER BY could fail to evaluate column expressions. This could happen if a field was used as a column and an expression. * Fixed debug message which could cause server crash. The following problems have been fixed since the A.02.03 pre-release: * Aggregate functions (COUNT, MIN, MAX, SUM, AVG) used with ORDER BY and/or GROUP BY caused the server to panic. The following problems have been fixed since the A.02.02 pre-release: * ORDER BY and GROUP BY on dynamic string columns (created by a function) used a random row to specify the column width. Subsquent results could become truncated. * A syntax error was caused by an expression which contained more than one aggregate function (COUNT, MIN, MAX, SUM, AVG). * The scalar functions DAYNAME, MONTHNAME, DATEFMT and STRINGFMT always returned a fixed field width of 255 characters. These functions now return the resulting field width if it can be determined. If not, they return 255 as before. ODBC client ("Driver") on UNIX ------------------------------ The ODBC client software ("Driver") is included in the HP-UX and Linux distribution. It is currently mainly in use internally by the sqlrexec program and the Sqlr.DLL. Documentation for installation and configuration of the iODBC driver manager and usage of the SQL/R ODBC drivers on the UNIX platform is included with the (unsupported) iODBC driver manager in the directory unsupported/iodbc. We have tested the SQL/R ODBC driver on the Linux/HP-UX platforms with the PHP, StarOffice and Applixware applications. While some minor issues reamain to be solved it works fine for us. However please consider ODBC client support on the Linux/HP-UX platform experimental for now. ---------------------------------------------------------------------- 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 so it does not conflict with a previously installed SQL/R 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-A0299-1.depot where /tmp is the directory where the SQLR-A0299-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-A0299.glibc2.3-1.i686.rpm glibc2.3 (or newer) based systems (eg. SLES 9, RHEL 4) SQLR-A0299.glibc2.3-1.ia64.rpm Itanium based systems running SLES9 (or equivalent Red Hat) SQLR-A0299.glibc2.3-1.x86_64.rpm AMD64/Intel64 based systems running SLES9 (or equivalent Red Hat) Older systems: SQLR-A0299.rh8-1.i386.rpm Red Hat Linux 8 or 9 based systems SQLR-A0299.glibc2.2-1.i386.rpm glibc2.2 based systems (eg. SuSE 8 or newer, Red Hat 7.x) SQLR-A0299.glibc2.1-1.i386.rpm glibc2.1 based systems (eg. SuSE 6.2 or newer, Red Hat 6.x) Installation: ------------- To install SQL/R with the rpm package manager, execute the command below as root: rpm -i SQLR-A0299.glibc2.3-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-A0299.glibc2.3-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.02.xx license key ---------------------------- SQL/R A.02.xx requires a license key in the SQL/R license file (which is located in the SQL/R configuration directory). For this beta test version please add the evaluation license keys (included in the README file) to your license file. 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 Eloquence A.06.10 and newer =========================== Create the SQL/R sample database in your default eloqdb6 server using the following commands /opt/eloquence6/bin/schema -u dba db.schema /opt/eloquence6/bin/dbcreate -u dba db /opt/eloquence6/bin/dbimport -u public -vs db.exp db Adjust the odbc.dsn configuration file as below: [SQL/R Example] Database=localhost:eloqdb/db