====================================================================== SQL/R A.02.82 - Server Release Notes / UNIX - 04.04.2008 ====================================================================== 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.82 is available and supported on all platforms Eloquence B.07.10, B.07.00 and B.06.32 is available on: * HP-UX 10.20, HP-UX 11.x (PA-RISC and Itanium) * Linux glibc2.1 or newer (Intel 386/686, AMD64/Intel64 and Itanium) * Windows XP, 2000 and NT SQL/R A.02.82 supports the Eloquence eloqdb6 database from version A.06.10 to B.07.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 above) * SQL/R is available on the same platforms as Eloquence B.07.00 * 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.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 above 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 backwards 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 backwards 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 above 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 above 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 10.20 or above. 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-A0282-1.depot where /tmp is the directory where the SQLR-A0282-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-A0282.glibc2.3-1.i686.rpm glibc2.3 (or newer) based systems (eg. SLES 9, RHEL 4) SQLR-A0282.glibc2.3-1.ia64.rpm Itanium based systems running SLES9 (or equivalent Red Hat) SQLR-A0282.glibc2.3-1.x86_64.rpm AMD64/Intel64 based systems running SLES9 (or equivalent Red Hat) Older systems: SQLR-A0282.rh8-1.i386.rpm Red Hat Linux 8 or 9 based systems SQLR-A0282.glibc2.2-1.i386.rpm glibc2.2 based systems (eg. SuSE 8 or newer, Red Hat 7.x) SQLR-A0282.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-A0282.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-A0282.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 above =========================== 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