--------------------------------------------------------------------------- SQL/R ODBC A.02.53 Release Notes for Windows - 04.11.2004 --------------------------------------------------------------------------- Welcome to the SQL/R ODBC Server for Windows. SQL/R A.02.53 supports the Eloquence eloqdb6 database from version A.06.10 to B.07.00 on all platforms. ODBC drivers are available for the Windows, Linux and HP-UX platforms. Contents ======== 1. New functionality and known issues 2. SQL/R ODBC Server Configuration 3. Additional information 4. SQL/R example database 1. New functionality and known issues ===================================== * SQL/R 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. Limitations ----------- * SQL/R is currently readonly. Writing is available (as an experimental feature) but considered unsupported. We plan to add r/w support for the eloqdb6 database in a subsequent release. * Some features of the SQL kernel are not yet available. This includes: * Sub SELECT, UNION SELECT - not supported yet These features are not required by the ODBC standard. We intend to include them in a subsequent release. * ASYNC mode is currently not implemented on the client side. This will result in your PC stay busy while transferring data. This is an optional part of the ODBC standard. We plan to include it in a subsequent release. * SELECT DISTINCT has no effect. * SQLExtendedFetch is currently not supported. This is an optional part of the ODBC 2.x standard. However some applications seem to depend on it. We intend include SQLExtendedFetch in a subsequent release. There is currently a limited/experimental version implemented in the Linux/HP-UX driver. * SQLDescribeParam for a prepared statement may return SQL_CHAR type instead of correct type. Applications depending on SQLDescribeParam() could fail with an ODBC error indicating a type conflict when passing non-string arguments as a string. This has been observed with PHP. Fixed Problems / Changes ------------------------ 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.50 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 configration. 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 reults (#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: * 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.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: * 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. 2. SQL/R ODBC Server Configuration ================================== In order to run properly, the SQL/R ODBC Server must be configured prior to its first usage. The following steps should be taken and are described below: 2a. Registering the SQL/R ODBC Server with Windows 2b. Configuring your system 2c. Configuring the SQL/R ODBC Server 2d. Configuring the SQL/R ODBC Server startup 2e. Controlling the SQL/R ODBC Server from the command line 2f. The SQL/R ODBC Server HTTP status display 2a. Registering the SQL/R ODBC Server with Windows -------------------------------------------------- The SQL/R ODBC Server is installed as a Windows service. During the installation process the sqlrodbcd.exe executable is installed in the Windows system directory (C:\Windows\System32). In addition, it is automatically registered with the Windows Service Control Manager. NOTE: Manual registration of the SQL/R ODBC Server with the Windows Service Control Manager is usually not required since the installation program performs a default registration. However, before uninstallation the SQL/R ODBC Server should be manually unregistered. In order to manually register the SQL/R ODBC Server with the Windows Service Control Manager you must execute sqlrodbcd.exe with the -install argument: a) Log on as Administrator. b) Open a Command Prompt window . c) Execute the command: sqlrodbcd -install This makes the SQL/R ODBC Server available to the Windows Service Control Manager. The service name is "SQLRODBC". To unregister, use the command: sqlrodbcd -remove Please make sure you unregister the SQL/R ODBC Server before you uninstall the SQL/R software. 2b. Configuring your System --------------------------- For SQL/R, the installation program adds the following default TCP service to your local SERVICES file: # # SQL/R related services # sqlrodbc 8003/tcp # SQL/R ODBC where the first column specifies the service name ("sqlrodbc") and the second column the associated port number and protocol ("8003/tcp"). The installation program takes care that the selected port number is not already used otherwise. In rare cases it might be necessary to modify this preset configuration, for example if the port number does not match the one configured on other systems. The location of your SERVICES file depends on your Windows version: Windows XP: C:\Windows\System32\Drivers\etc\SERVICES Windows 2000/NT: C:\WINNT\System32\Drivers\etc\SERVICES 2c. Configuring the SQL/R ODBC Server ------------------------------------- The SQL/R ODBC Server is configured by editing the odbc.cfg, odbc.user and odbc.dsn configuration files. They are located in the SQL/R configuration directory. All configuration files provide complete inline documentation. The location of the SQL/R configuration directory depends on where SQL/R has been installed. All SQL/R configuration files are installed in the etc subdirectory of the SQL/R installation directory. This is usually C:\Programs\Marxmeier\SQLR\etc. Each configuration file is responsible for a specific part of the SQL/R ODBC Server configuration: odbc.cfg - This is the general server configuration file. odbc.user - In this file all users are defined who are allowed to access the ODBC server. odbc.dsn - This file defines all accessible data sources on the server. 2d. Configuring the SQL/R ODBC Server startup --------------------------------------------- After the SQL/R ODBC Server has been registered with the Windows Service Control Manager it can be accessed using the Services applet. Windows XP: * Open the Windows Control Panel * Select Performance and Maintenance * Select Administrative Tools * Open the Services applet Windows 2000: * Open the Windows Control Panel * Select Administrative Tools * Open the Services applet Windows NT: * Open the Windows Control Panel * Open the Services applet Locate the SQLRODBC entry in the list and select it with the mouse. On Windows XP/2000, open the Properties dialog, either from the menu or using the context menu (right mouse button). On Windows NT, click the Startup... button to open the Startup Options dialog: * If you want the SQL/R ODBC Server to be automatically started each time Windows is restarted, you should select the Automatic Startup Type. This is the recommended setting. * If you select the Manual Startup Type, you have to manually start the SQL/R ODBC Server each time Windows is restarted. * By default, the Log On As option is set to System Account. For maximum security, you should create a special account for the SQL/R ODBC Server, e.g. "sqlr". The account should be specified using the This Account option. Next, you should limit access to the SQL/R ODBC Server configuration files odbc.cfg, odbc.user and odbc.dsn to this account only. When you have setup the Startup Options, you can define additional arguments for the SQL/R ODBC Server as an option. This is usually not required since all settings are normally provided in the odbc.cfg configuration file. However this can be used for tracking down problems (e.g. starting the SQL/R ODBC Server temporarily with different log options). Possible arguments are: -d flags = log flags (debug mode) -l name = log file name -s name = service name -h name = http service name Option Description Equiv.* -d flags Specifies the server debugging log flags. LogFlags -l name Specifies the server log file. LogFile -s name The service name (as defined in the Service SERVICES file) or the port number where the server should listen for requests. The default value is sqlrodbc. -h name If you want to make use of the HTTP HttpService status diplay feature of the server, use this option to specify the service name or port number where the server should listen for HTTP requests. (* equivalent configuration file directive) For example, if you wish to specify a different TCP service name (the default is "sqlrodbc"), you could enter the following into the Startup Parameters field: -save -s 8567 This will make the SQL/R ODBC Server listen to the TCP/IP port number 8567. The leading -save argument makes this command line persistent, so that the same arguments are used each time the SQL/R ODBC Server is started. If you do not specify the -save argument this command line will be used only once. If you want to make sure that the command line is empty and that any persistent command line is deleted, just specify -save without any additional arguments. At this point, everything is configured and the SQL/R ODBC Server can be started: * Windows XP/2000 Select the SQLRODBC entry in the list, then choose Start either from the menu or using the context menu (right mouse button). * Windows NT Select the SQLRODBC entry in the list, then click the Start button. If you did not specify a log file, the SQL/R ODBC Server will write log messages to the Windows Event Log. In case the SQL/R ODBC Server does not start the Event Log will probably contain an error message pointing out the cause of the problem. You should also periodically check the Event Log in order to get aware of possible configuration problems. Windows XP: * Open the Windows Control Panel * Select Performance and Maintenance * Select Administrative Tools * Open the Event Viewer applet * Select the Application Log Windows 2000: * Open the Windows Control Panel * Select Administrative Tools * Open the Event Viewer applet * Select the Application Log Windows NT: * Select Programs - Administrative Tools in the Windows Start Menu * Start the Event Viewer * From the Log menu, select Application Having started the SQL/R ODBC Server for the first time after installation, the Application Log should already contain a few entries noting that the SQL/R ODBC Server was successfully installed and started. 2e. Controlling the SQL/R ODBC Server from the command line ----------------------------------------------------------- The net start and net stop system commands can be used to start and stop the SQL/R ODBC Server from the command line. This is equivalent to starting and stopping the server using the Windows Services applet as explained in the previous section. Command syntax: net start SQLRODBC net stop SQLRODBC 2f. The SQL/R ODBC Server HTTP status display --------------------------------------------- When the HttpService is defined in the odbc.cfg configuration file, you can use a WEB browser such as Mozilla, Netscape or Internet Explorer to view the configuration and state of the SQL/R ODBC Server process in your network. To access the SQL/R ODBC Server, you provide an URL like this: http://server:port/ where server is the host name or IP number of the system running the SQL/R ODBC Server and port is the port number used for HttpService in the odbc.cfg file. 3. Additional information ========================= Additional information on SQL/R is available on the Internet. Please refer to the URL http://www.marxmeier.com/sqlr The previous SQL/R ODBC documentation is available in PDF format on the SQL/R web page. 4. SQL/R example database ========================= SQL/R ODBC includes a sample database. It must be created manually. Creating the SQL/R example database ----------------------------------- The SQL/R sample database is available with English or German table and item names. First, the sample database should be installed in an Eloquence eloqdb6 database environment which must be already setup and running. For the English version, open a command shell window and change to the C:\Programs\Marxmeier\SQLR\share\db.e directory. For the German version, open a command shell window and change to the C:\Programs\Marxmeier\SQLR\share\db.g directory. The following instructions install the SQL/R sample database in your default eloqdb6 server: schema -u dba db.schema dbcreate -u dba db dbimport -u public -vs db.exp db Notes: * This assumes that the Eloquence bin directory is included in the PATH. If not, prepend the commands above with C:\Programs\Eloquence\bin\ (depends on the location of your Eloquence installation). * The -u dba and -u public options can be omitted in Eloquence B.07.00. Next, adjust the odbc.dsn configuration file as below. For the English version: [SQL/R Example] Database=localhost:eloqdb/db Repository=C:/Programs/Marxmeier/SQLR/share/db.e/db.rep For the German version: [SQL/R Example] Database=localhost:eloqdb/db Repository=C:/Programs/Marxmeier/SQLR/share/db.g/db.rep