====================================================================== SQL/R A.02.99 - Client Release Notes - 21.02.2013 ====================================================================== This document covers the SQL/R client programs sqlrexec and Sqlr.DLL These programs have been rewritten and now use the SQL/R ODBC server backend to access the database. As a consequence, the SQL/R syntax is now more compliant with the SQL syntax. A built-in syntax conversion module ensures backward compatibility with most of the existent SQL/R scripts. Backward compatibility is controlled by the new SET SYNTAX statement which allows to selectively enable or disable the different conversion methods. Please refer to the notes below for details about the changed syntax. Existing SQL/R scripts should usually be compatible with the new SQLR. If an existent script does no longer work with the new SQL/R version it may need minor adaptions as explained below. ---------------------------------------------------------------------- Prerequisites / Installation Notes: ---------------------------------------------------------------------- * SQL/R requires Eloquence revision A.05.xx or A.06.10 and newer. Installation of Eloquence B.08.10 or B.08.00 is recommended. Please ensure that current Eloquence patches are installed. Eloquence A.06.00 and A.06.01 are NOT supported. * SQL/R is now installed in the /opt/sqlr2 directory on the HP-UX and Linux platforms to retain an existing SQLR A.01.xx installation. * Automatic startup/shutdown scripts are installed automatically on the HP-UX as well as on the SuSE and RedHat Linux platforms. For other Linux distributions you can use the template code included in the directory /opt/sqlr2/newconfig/startup/ to configure automatic startup of the SQL/R server process. Please send us a copy of all necessary modifications to support@marxmeier.com. * If an Eloquence Personal Edition is installed and no valid SQL/R license key is detected a two user license is granted automatically. * SQL/R internally uses the ODBC server backend to accomplish database access. Although it is possible to run the Eloquence database server and the ODBC server on different machines, we recommend to install them on the same system (please refer to the OPEN DATABASE section below for more information). * SQL/R requires a running sqlrodbcd server. While previous sqlrexec versions (A.01.xx) implemented their own SQL language it now relies on a common server. Even if you do not use ODBC access you now need to setup and run the SQL/R ODBC server component. You don't need a separate ODBC license to use the SQL/R server with sqlrexec. Installation, configuration and administration of the SQL/R ODBC server is described in the SQL/R ODBC manual. While the ODBC manual is still in revision A.01.xx there were not much user visible changes and the ODBC release notes (RELNOTES-ODBC) describe the differences/changes detail. * On the Windows platform, please ensure that the latest SQL/R ODBC drivers are installed, since sqlrexec (and the Sqlr.DLL) rely on internal driver functionality which is not included in former SQL/R ODBC drivers. ---------------------------------------------------------------------- Changed Behavior SQL/R has been rewritten completely and may behave slightly different than previous versions although a lot of effort was spent to make it as compatible as possible. ---------------------------------------------------------------------- Most scripts should work unmodified, more complex scripts may require minor changes as discussed below. * The previous QPATH environment variable has been renamed to SQLR_PATH. The SQLR_PATH environment variable is similar to the UNIX PATH variable to specify a colon separated list of directories which is used by SQL/R to locate files. * When opening a database, sqlrexec (and the Sqlr.DLL) previously tried to execute .qif file . This could be used to initialize conversions. Since sqlrexec no longer has direct access to the database this is no longer possible. The repository file feature of the ODBC server should be used instead. Please refer to the SQL/R ODBC documentation for more information. * sqlrexec previously searched in the directory where the database was located for script files (.qrf files). This is no longer done. Since sqlrexec no longer has direct access to the database this is no longer possible. You can use the SQLR_PATH environment variable to have a similar effect. * The A.01.xx version implicitely added a ".qrf" extension to script files which were executed by RUN. This is no longer done. You need to specify any extension expicitely. The previous RUN command has been renamed to CALL to make it more explicit. However the RUN command is still recognized. RUN "WhatEver.qrf" CALL "WhatEver.qrf"("Arg1") * When it encountered an unknown command, SQL/R previously tried to execute a corresponding .qrf script file. This is no longer supported and an explicit RUN or CALL keyword is now required. * With the SQL/R DLL (which is used from Eloquence), the Exec function returned a status reflecting the SQL statement which was executed. The current SQL/R DLL only differentiates between 8 different status values (besides error conditions): 0 SELECT statement -1 EXIT statement -6 CALL/RETURN statement -9 END-OF-DATA or data not available -11 BREAK line retrieved -12 OPEN DATABASE statement -13 CLOSE DATABASE statement -100 Other statement * The previous -e option to sqlrexec has been changed to -v. Specifying the -v option causes sqlrexec to output the commands to stderr while they are processed. This is equivalent to using the SET ECHO = ON|OFF statement. * When setting page length to zero column headers and pagination are suppressed. This makes it easier to use sqlrexec output with a pipe (eg. shell script). SET LENGTH = 0; The example above sets page length to zero. * By default, sqlrexec uses a form feed as a page separator. This can be changed with the SET PAGESEP command (see below). Previous SQL/R versions used line feeds to fill a page to the defined page length. Please note, that a form feed character is only output to separate pages - there is no form feed sent after the last page. * Using the FIELD statement to specify an alias of a database field with a specific output format or field encoding (eg. date) used to define the format for both the database field _and_ the alias. This is no longer done. For example: FIELD xdate = table.datefield DISPLAY AS DATE; In this case the FIELD statement only applies to xdate _not_ table.datefield. This is likely to cause unexpected results unless table.datefield is of a similar data type. This can be achieved by using a separate FIELD statement or - preferable - by using a repository file (please refer to the SQL/R ODBC manual, Sections 2.4 and 4.2 for more information). * As of A.02.30 the default date encoding has been changed from "YMD" to "YYMD". This change should not have any impact on existing scripts unless the date field is used for an index lookup (in this case the exact encoding must be known to SQL/R) and the data base is using the YYMMDD format. In this case an explicit "YMD" format must be specified. For example: FIELD xdate = table.datefield DISPLAY AS DATE FROM "YMD"; * A SELECT without a FROM clause is currently not supported. SQL/R A.01.xx supported a SELECT syntax without specifying a FROM clause and table which caused any SELECT arguments to be output without accessing the database. To achieve the same result you can either use the new ECHO statement or temporily set the ROW LIMIT to 1 on an arbitrary table. For example: ECHO "Hello World"; This writes "Hello World" to current output. SET ROWLIMIT = 1; SELECT "Hello World" FROM ANYTABLE; SET ROWLIMIT = 0; Where ANYTABLE is any nonempty table in your data source. This writes "Hello World" to current output. This variant allows specification of expressions. * VIEWs (created by CREATE VIEW) must be dropped using a DROP VIEW statement before before the view name can be re-defined. Previous sqlrexec allowed to re-define a view. For example (old CREATE VIEW syntax): CREATE VIEW vname PATH Orders TO Customers WHERE Custno=Custno. SELECT Orderno,Custno,Custname FROM vname; DROP VIEW vname; CREATE VIEW vname PATH Orders TO Customers WHERE Custno=Custno. SELECT Orderno,Custno,Custname FROM vname; Example (new CREATE VIEW syntax): SET SYNTAX = "1"; CREATE VIEW vname AS SELECT * FROM Orders,Customers WHERE Customers.Custno=Orders.Custno; SELECT Orderno,Custno,Custname FROM vname; DROP VIEW vname; CREATE VIEW vname AS SELECT * FROM Orders,Customers WHERE Customers.Custno=Orders.Custno; SELECT Orderno,Custno,Custname FROM vname; With SQL/R A.02.xx you could as well omit the view and specify multiple tables in the SELECT statement: SELECT Orderno,Custno,Custname FROM Orders,Customers WHERE Customers.Custno=Orders.Custno; * SQL/R A.02.xx follows the SQL standard more closely and syntax checking is more strict. This may affect your use of the SELECT statement. Previous SQL/R version allowed arbitrary order for some SELECT clauses while SQL/R A.02.xx requires SELECT clauses to be specified in the order as shown below. 1. WHERE condition 2. GROUP BY clause 3. HAVING condition 4. ORDER BY clause Different ordering of these clauses will now result in a syntax error. * When using the old CREATE VIEW syntax, it may be necessary to qualify fields in the WHERE condition. In some cases the A.01.xx sqlrexec used the hierarchy for additional context information which is no longer possible with SQL/R A.02.xx. If an item occurs in a previously specified table, the fist instance will be used. Previous sqlrexec (depending on WHERE condition) used the field in the related table. For example: CREATE VIEW p1 PATH bestellung WHERE identnr = identnr TO lieferant WHERE lfntnr = lfntnr TO adresse WHERE adrid = adrid; In this particular case, the field ADRID was present in both BESTELLUNG and LIEFERANT. sqlrexec A.01.xx used the ADRID in table LIEFERANT while A.02.xx uses the ADRID in BESTELLUNG. In order to resolve the ambiguity field may need to be qualified by specifying the table name. CREATE VIEW p1 PATH bestellung WHERE identnr = bestellpos.identnr TO lieferant WHERE lfntnr = bestellung.lfntnr TO adresse WHERE adrid = lieferant.adrid; * Custom field formats are not propagated. If you define a field format and then subsequently use it in a formula, the field format does not apply to the formula result. This is a limitation of the current A.02.xx architecture. For example: FIELD xyz DISPLAY AS (8,2); ... SELECT ... SUM(xyz) ... In this example, the field format for xyz does not apply to column SUM(xyz) and it is displayed using a field width of 11 instead of the specified 8. As a workaround you can specify an alternative field title: FIELD xyz DISPLAY AS (8,2); ... SELECT ... SUM(xyz) AS "XYZ" ... In this case sqlrexec understands that the format for XYZ should be applied to this column. Another workaround is to define a field instead of using the database field: FIELD xyz = table.field DISPLAY AS (8,2); ... SELECT ... SUM(xyz) ... This works exactly as above because sqlrexec internally specifies the field name as an alternative column header during syntax translation. * With previous SQL/R version, column width was determined by the maximum of field width and column title. If a field format has been defined explicitely SQL/R A.02.xx will honor this request and truncate the column if required. This behavior can be disabled by using the SQLR_OPTIONS.compat option (see below). ---------------------------------------------------------------------- Syntax Changes sqlrexec supports a more SQL compliant syntax and provides additional options. The supported syntax is contolled using the SET SYNTAX statement (see below). By default sqlrexec translates the previous syntax on the fly and some new features may not be acessible. Please refer to the desription of the SET SYTAX statement below for additional information. ---------------------------------------------------------------------- * When using the OPEN DATABASE statement to open an Eloquence eloqdb6 database, the eloqdb6 database path format must be used: OPEN DATABASE "hostname:port/database"; As with Eloquence eloqdb6, the hostname defaults to 'localhost' and the port defaults to 'eloqdb' if omitted: short form equivalent to --------------------------- ------------------------------------ OPEN DATABASE "db"; OPEN DATABASE "localhost:eloqdb/db"; OPEN DATABASE "server/db"; OPEN DATABASE "server:eloqdb/db"; OPEN DATABASE ":8123/db"; OPEN DATABASE "localhost:8123/db"; In the current implementation, the hostname is also used to locate the ODBC server. As a consequence, it must be installed on the same system as the database server. If this is not the case, the CONNECT TO statement should be used (see below). * To use an A.05.xx database with the OPEN DATABASE statement, you need to specify the absolute path. The leading slash is used to distinguish between an A.05 and A.06 database. * As an addition to the OPEN DATABASE statement, there is a new CONNECT TO statement where you can use the ODBC data source name (as definend in odbc.dsn) as an argument: CONNECT TO "dsn_name"[,"arg" ...]; For example: connect to "SQL/R Example","UID=public;PWD=secret"; connect to "SQL/R Example", "UID=public","PWD=secret", "Server=localhost","service=sqlrodbc"; * When using the Eloquence eloqdb6 database, sqlrexec uses the user name "public" by default. The -u commandline option can be used to specify a different user name. When using the CONNECT TO statement to establish the connection to the database server, the UID= argument can be used to specify a user name. Otherwise the default one (public or specified on the commandline) is used. The OPEN DATABASE syntax has been enhanced. In addition to the password you can also specify a user name. For example: OPEN DATABASE "db" AS "user" "pswd"; * The following statements are no longer supported: HELP, SHOW, DEFINE * The '= NULL' condition causes a syntax error and should be replaced by either 'IS NULL', 'IS NOT NULL' or 'IFNULL'. For example: FIELD y = IF(x = NULL, 0,x); Alterntive 1: FIELD y = IF(x IS NULL, 0,x); Alterntive 2: FIELD y = IFNULL(x, 0); * The new CALL statement should be used as a replacement for the old RUN statement (although the RUN statement still works). * A new SET SYNTAX statement has been added to SQL/R to allow specifying the syntax (backward) compatibility level. usage: SET SYNTAX = "1"; SET SYNTAX = "ALFTR"; SET SYNTAX = "f"; Possible settings: "0" - full backward compatibility (equiv. flags = ALCFTVR) "1" - backward compatibility (equiv. flags = ALFTR) "2" - don't translate statements (equiv. flags = none) By specifying flags, the current syntax compatibility setting is changed incrementally. Specifying an upper case flag letter adds the capability, while using the lower case equivalent disables this particular setting. For details about the specific syntax changes, please refer to the descriptions below. A - translate array elements (a[1] -> a__2) L - translate LIKE wildcards ('*' -> '%', '?' -> '_') C - handle multiline comments enclosed with {} F - support SQL/R field syntax T - translate @functions (@now -> NOW()) V - translate SQL/R CREATE VIEW syntax R - translate ORDER BY and GROUP BY column references. This is intended for backward SQL/R compatibility when a field which specifies a formula is used with ORDER BY or GROUP BY. By default, the syntax compatibility level is set to 0. However, to use the new features we recommend to modify your scripts and use level 1. * Since the ODBC SQL syntax defines that identifiers must not contain the '[' ']' characters, array elements are now addressed with two underscore '_' characters followed by the 1-based element index, e.g. old syntax: a[1] -> new syntax: a__2. As long as the 'A' syntax flag is set, the old syntax is automatically converted (see above, the 'A' flag is set by default for backward compatibility). * LIKE wildcards have changed according to the ODBC SQL standard. The '*' wildcard is replaced by '%', while '?' is replaced by '_'. As long as the 'L' syntax is set, old-style wildcard characters are automatically converted (see above, the 'L' flag is set by default for backward compatibility). * SQL/R used the '{' '}' characters as comment delimiters. This is supported as long as the 'C' syntax flag is set (see above, the 'C' flag is set by default for backward compatibility). Note that when using the '{' '}' comment delimiters use of some of the new SQL syntax features, such as outer joins and ODBC date constants is not possible. * Date constants can now additionally be specified in ODBC extension format: {d 'yyyy-mm-dd'} for example: {d '1998-01-01'} * The order of the GROUP BY, HAVING and ORDER BY clauses is strictly determined: GROUP BY must be specified before HAVING and ORDER BY. HAVING must be specified after GROUP BY and before ORDER BY. ORDER BY must be specified after GROUP BY and HAVING. * FIELD statements are accepted in the former SQL/R syntax as long as the 'F' syntax flag is set (see above, the 'F' flag is set by default for backward compatibility). If not, the SQL/R ODBC FIELD syntax must be used, which has the following form: FIELD name [= expression] [CONVERT TO type] [DESCRIBE AS description]; * FIELD statements which specify a conversion to the DATE type must provide a qualified field name (the table name must be specified). Otherwise, it will only be executed locally and probably result in either no effect at all or wrong output. * SQL/R now supports a SQL compliant SELECT statement which allows to specify multiple table names. Previous SQL/R versions required to specify table relation with the CREATRE VIEW statement. Both inner joins (where all table entries must exist in order to be included in the result) as well as outer joins (where table entries may not exist in order to be included) are supported. New SELECT syntax: Inner join: SELECT {*|} FROM [, ...] [ WHERE ] Outer join: SELECT {*|} FROM {oj [LEFT OUTER JOIN ...] ON } [ WHERE ] * The CREATE VIEW statement now creates a temporary view in the ODBC server backend. Alternatively, views can directly be specified in the ODBC server repository file. * SQL/R does now support SQL compliant CREATE VIEW syntax. Both inner joins (where all table entries must exist in order to be included in the result) as well as outer joins (where table entries may not exist in order to be included) are supported. New CREATE VIEW syntax: Inner join: CREATE VIEW [ (Column [, Column ...]) ] AS SELECT {*|} FROM [, ...] [ WHERE ] Outer join: CREATE VIEW [ (Column [, Column ...]) ] AS SELECT {*|} FROM {oj [LEFT OUTER JOIN ...] ON } [ WHERE ] Please note that the 'V' backward compatibility flag must be disabled in order to use the new CREATE VIEW syntax. By default the old syntax is accepted and translated to the new syntax. * The previous CREATE VIEW syntax is translated into outer joins, so the previous behaviour is retained. However since using CREATE VIEW to join multiple tables is optional now and in addition the standard SQL CREATE VIEW is available, we recommend to avoid the old CREATE VIEW syntax. You can simply modify your SELECT statement and include the additional table names in the FROM clause and the join expression to the WHERE clause. Be aware that this results in an 'inner join' where the previous 'CREATE VIEW' was an implicit 'outer join'. An outer join can be accomplished by using the ODBC extension syntax. For example: CREATE VIEW tmp PATH Orders TO Customers WHERE Custno=Custno. SELECT Orderno,Custno,Custname FROM Tmp; Alternative 1: SELECT Orderno,Custno,Custname FROM Orders,Customers WHERE Customers.Custno=Orders.Custno; Alternative 2: CREATE VIEW tmp AS SELECT * FROM Orders,Customers WHERE Customers.Custno=Orders.Custno; SELECT Orderno,Custno,Custname FROM Tmp; For example: CREATE VIEW tmp PATH Customers TO Orders WHERE Custno=Custno. SELECT Custno,Custname,Orderno FROM Tmp; Outer Join equivalent: SELECT Orderno,Custno,Custname FROM {oj Customers LEFT OUTER JOIN Orders ON Customers.Custno=Orders.Custno}; Outer join alternative using CREATE VIEW: CREATE VIEW tmp AS SELECT * FROM {oj Customers LEFT OUTER JOIN Orders ON Customers.Custno=Orders.Custno}; SELECT Orderno,Custno,Custname FROM Tmp; * The SQL/R @functions are now translated internally into standard SQL functions. Old SQL/R function Mapping to new SQL/R ODBC function ------------------------------ ---------------------------------- @CHAR(n) CHAR(n) @LEFT(string, cnt) LEFT(string, cnt) @LENGTH(string) LENGTH(string) @LOWER(string) LCASE(string) @NUM(string) ASCII(string) @POS(string, pattern) LOCATE(pattern, string) @RIGHT(string, cnt) RIGHT(string, cnt) @RPT(string, cnt) REPEAT(string, cnt) @STRING(fmt, value) STRINGFMT(fmt, value) @SUBSTR(string, start, len) SUBSTRING(string, (start) + 1, len) @TRIM(string) LTRIM(RTRIM(string)) @UPPER(string) UCASE(string) @VALUE(string) CONVERT(string, SQL_DOUBLE) @DATE(year,month,day) DATE(year,month,day) @DATETOCHAR(date, picture) DATEFMT(picture, date) @DATEVALUE(string) CONVERT(string, SQL_DATE) @DAY(date) DAYOFMONTH(date) @DIFFTIME(date1, date0) TIMESTAMPDIFF(SQL_TSI_SECOND, date0, date1) @HOUR(time) HOUR(time) @MINUTE(time) MINUTE(time) @MONTH(date) MONTH(date) @MONTHBEG(date) MONTHBEG(date) @NOW NOW() @QUARTER(date) QUARTER(date) @QUARTERBEG(date) QUARTERBEG(date) @SECOND(time) SECOND(time) @TIME(hour, minute, second) TIME(hour, minute, second) @TIMEVALUE(string) CONVERT(string, SQL_TIME) @WEEKBEG(date) WEEKBEG(date) @WEEKDAY(date) (DAYOFWEEK(date) - 1) @YEAR(date) (YEAR(date) - 1900) @YEARBEG(date) YEARBEG(date) @DAYS(n) TIMESTAMPADD(SQL_TSI_DAY, n, 0) @WEEKS(n) TIMESTAMPADD(SQL_TSI_WEEK, n, 0) @HOURS(n) TIMESTAMPADD(SQL_TSI_HOUR, n, 0) @MINUTES(n) TIMESTAMPADD(SQL_TSI_MINUTE, n, 0) @SECONDS(n) TIMESTAMPADD(SQL_TSI_SECOND, n, 0) @ABS(x) ABS(x) @DIV(x, y) DIV(x, y) @FRACT(x) ((x) - FLOOR(x)) @INT(x) FLOOR(x) @MOD(x, y) MOD(x, y) @ROUND(value, digits) ROUND(value, -(digits)) @BIT(x, b) BIT(x, b) @BAND(x, b) BAND(x, b) * There is a new SET ROWLIMIT statement which can be used to limit the number of result rows. A zero argument indicates no limit. SET ROWLIMIT = 0 SET ROWLIMIT = 100 * With the A.01.xx SQL/R, a leading underscore character '_' was used to indicate a quoted identifier. This is still supported but there are additional options available: SELECT TABLE.COLUMN ... SELECT |COLUMN| ... A qulified column does no longer need to be quoted. Enclosing the identifier in vertical bars '|' also prohibits recognition as a keyword. * SQL/R A.02.xx adds a new DEFAULT value to the FIELD ... VALUES ARE clause. For example: FIELD country VALUES ARE ("DE","HOME",DEFAULT="ALIAN"); This causes values for the field contry to become translated to either "HOME" (if value equals "DE") or "ALIAN" for any other value. When a DEFAULT value is not specified, the original field value is returned untranslated. * sqlrexec allows masking of special characters in string constants by using a backslash character (\). Any character after a backslash looses any special meaning to the scanner. This makes it possible to use quotes to $ character in a string constant. * A new "SET PAGESEP = " statement allows to re-define the page separator for formatted output. The default page separator is a formfeed (\f). If the page separator is set to a newline (\n) then the page is filled by issuing line feed characters until the page is completed, otherwise a single character is output to separate pages. The following special characters are supported: \f Form Feed (default) \n Line Feed \r Carridge return \0 The page separator is specified by an octal number \0x The page separator is specified by a hex number \ The page separator is specified by a decimal number Please keep in mind that you need to specify two backslashes to pass a special character: SET PAGESEP = "\\n"; SET PAGESEP = "\\012"; SET PAGESEP = "\\0x0a"; SET PAGESEP = "\\10"; The example above specifies the line feed character as a page separator. Setting the page separator may be necessary if your printer has a different understanding of the page length than sqlrexec. * Similar rules (as the ones for specifying PAGESEP) also apply for defining a custom value for ROWSEP (row separator) and COLSEP (column separator). Specifying a zero value reverts the setting back to the default. The column separator is used to separate columns on output (unless when using a FORM) or fields when outputting to a file. Default is a space (for normal output) and a comma when writing to an EXPORT or ASCII (CSV formatted) file. The row separator is currently used with normal output to separate database results break results (CALCULATE ...). The example below specifies to use a "|" as a separator between columns: SET COLSEP = "|"; * A new ECHO statement has been added which simply writes the specified string argument to the output. Optional a INTO clause can be specified to define the output desctination. Syntax: ECHO "String" [ INTO {TERMINAL|PRINTER|[ASCII|EXPORT] FILE "fileName"} ]; For example: ECHO "Hello World"; Writes "Hello World" to currently defined output. ECHO "Hello World" INTO TERMINAL; Writes "Hello World" to stdout. ECHO "Hello World" INTO PRINTER; Writes "Hello World" to printer. * A new VIEW statement has been added to sqlrexec which returns information on the data source. Syntax: VIEW TABLES [[.]] [report_options] VIEW COLUMNS [] [FOR [[.]
]] [report_options] VIEW TABLES returns information on tables provided by the data source. It is based on the SQLTables ODBC function. Syntax: VIEW TABLES [[.]
] [report_options] Arguments are owner (which is understood as the database or data base alias specified in the odbc.dsn configuration file) and table name. Owner and table name arguments can either be specified as an identifier, a quoted string or a % (percent sign) as a wildcard. If the arguments are omitted, a list of all tables for all data bases is returned. If a % or "%" is specified for the owner name and the table name is given as an empty string, a list of all databases is returned. For example: VIEW TABLES; VIEW TABLES Sample.%; VIEW TABLES "%".""; The fist example returns all tables. The second example returns all tables in the database sample and the third one returns a list of all databases in the data source. The report options are the same as allowed for REPORT SELECT (i.e. INTO ..., USING "form", USING LINEAR LIST, DATE AS, TITLE AS, LENGTH, WIDTH) and work identical as with REPORT SELECT. Returned columns: TABLE_QUALIFIER - always NULL, usually suppressed TABLE_OWNER - database or alias name TABLE_NAME - data set name TABLE_TYPE - TABLE or VIEW (not yet supported) REMARKS - Description of table as specified in the repository file (not yet supported) or NULL Please refer to the documentation on the ODBC standard for additional informaion. These fields are currently displayed using a builtin default format but you are free to either specify a different display format using the FIELD statement and/or to use a form file. For example: FIELD TABLE_TYPE DISPLAY AS (0); FIELD REMARKS DISPLAY AS (0) NULL = ""; VIEW TABLES; Create the following form file (myForm.frm): --- form file --- List of Tables in data base source Data Base Table Name ---------------- ----------- %% @2 @3 --- end form file --- and use the form to specify output format: VIEW TABLES USING "myForm.frm"; VIEW COLUMNS returns information on columns provided by the data source. It is based on the SQLColumns ODBC function. Syntax: VIEW COLUMNS [] [FOR [[.]
]] [report_options] Arguments are column, owner (which is understood as the database or data base alias specified in the odbc.dsn configuration file) and table name. Column, Owner and table name arguments can either be specified as an identifier, a quoted string or a % (percent sign) as a wildcard. If the arguments are omitted, a list of columns in all tables for all data bases is returned. If a column name is specified (other that %) a list of all tables is returned in which the specified column is present. For example: VIEW COLUMNS "ID"; VIEW COLUMNS FOR Parts; VIEW COLUMNS FOR Sample.Parts; The fist example returns column information on column "ID" for all tables which provide the column "ID". The second example returns column information on table "Parts" for all data bases. The third example returns column information on the table "Parts" in the data base "Sample". The report options are the same as allowed for REPORT SELECT and work identical. Returned columns: TABLE_QUALIFIER - always NULL, usually suppressed TABLE_OWNER - database or alias name TABLE_NAME - data set name COLUMN_NAME - column name DATA_TYPE - ODBC SQL data type (numeric) TYPE_NAME - ODBC SQL data type (as a string) Currently the following types are used: INTEGER, FLOAT, VARCHAR PRECISION - Column width LENGTH - number of bytes required to hold the value for strings this includes a terminating NUL byte SCALE - number of digits post decimal point RADIX - (currently NULL) NULLABLE - (currently 1) REMARKS - Description of field as specified in the repository file (not yet supported) or NULL Please refer to the documentation on the ODBC standard for additional informaion. * The SQLR_OPTIONS environment variable can be used to define SQL/R compatibility options without requiring script changes. Syntax: The SQLR_OPTIONS environment varible is specified as a list of options separated by a semicolon (;). Each option consists of a keyword and a value, separated by an equal sign (=). For example: SQLR_OPTIONS="compat=fw;syntax=0" export SQLR_OPTIONS Please note that the quotes are important since the semicolon is handled as a statement separator by the shell. Keywords: compat - compatibility flags ('f' and 'w') syntax - syntax style, equivalent to SET SYNTAX directive. charset - character encoding, either ISO8859-1 or HPROMAN8 (introduced with A.02.24) - When the 'f' compatility option is set (using the SQLR_OPTIONS env variable), using DISPLAY AS (w) with a floating point variable causes the post decimals to be rounded to zero digits. This follows the behaviour of previous SQL/R versions. - When the 'w' compatibility option is set, the column width will depend on the column title as previous SQL/R versions. ---------------------------------------------------------------------- Fixed Problems This section covers bug fixes and changes SQL/R A.02.xx releases. ---------------------------------------------------------------------- The following problems have been fixed since the first A.02.00 pre-release: * FIELD auftrag.auftrdatum DISPLAY AS DATE("%m/%d/%y"); SELECT auftrag.auftrdatum FROM auftrag; Does not result in an error, but the output was in the form dd.mm.yyyy * FIELD _match DISPLAY AS LEFT(18); The leading underscore was not recognized. * SET SYNTAX=... caused a hang. * FIELD Selected = "$1"; In A.01.xx arguments were ignored inside string literals when not present. A.02.xx resulted in an error message. * FIELD LastCloseDate = @12/01/98; This worked with A.01.xx, but did not work with A.02.00. * Environment variables were not recognized in string literals. The following problems have been fixed since the A.02.02 pre-release: * A FIELD statement containing an expression caused a syntax error when used in the CALCULATE rule: FIELD total = price * amount; REPORT SELECT ... CALCULATE SUM(total); * The first SKIP in the CALCULATE rule caused a skipped line before _and_ after the break section. * The last SKIP in the CALCULATE rule caused an additional empty line after the last result was output. This could cause an additional empty last page to be output. * FIELD formats were not correctly recognized if a qualifier was present: FIELD item.unit DISPLAY AS DOUBLE(10,3); SELECT item.unit ...; resulted in: 115.94 * SET LENGTH = 0 did not suppress the column headers. * CALCULATE ... recognizes aliases and fields (defined with the FIELD statement) in addition to a column index. * ORDER BY and GROUP BY column references can be translated to a column index. The following problems have been fixed since the A.02.04 pre-release: * Fixed handling for old style SQL/R comments. * Fixed field resolution for subsequent SELECT statements. The following problems have been fixed since the A.02.06 pre-release: * Decoding of numeric constants could fail depending on current locale setting. The following problems have been fixed since the A.02.09 pre-release: * Comment before end of file caused syntax error. * When printing, pages are separated by a form feed. * On a tty device the last page is not filled up with line feeds. * Date field width is derived from the specified format. * The field syntax has been enhanced to allow specification of date 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 encoding can either be specified in the repository file or the SQL/R script file (and is then transmitted to the SQL/R server). The previous field syntax for date fields: FIELD xx DISPLAY AS DATE [("fmt"[,wdth])] [FROM baseyr|SYSDATE] ... In additon the following syntax is supported: FIELD xx DISPLAY AS DATE [("fmt"[,wdth])] FROM "encoding"[,baseyr] Where encoding describes the date field encoding in the database and baseyr provides an additional option for this encoding. 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 xx DISPLAY AS DATE FROM "YMD" A value of 990424 is understood as 1999-04-24 A value of 1000424 is understood as 2000-04-24 FIELD xx DISPLAY AS DATE FROM "YMD",1950 A value of 490424 is understood as 1999-04-24 FIELD xx DISPLAY AS DATE FROM "Y2MD",1960 A value of 200424 is understood as 2020-04-24 A value of 990424 is understood as 1999-04-24 FIELD xx DISPLAY AS DATE FROM "YYMD" A value of 19990424 is understood as 1999-04-24 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.10 pre-release: * A problem introduced with A.02.10 has been fixed. - A form feed could be missing after the first page - An superfluous form feed could be output after the last page * Due to a bug in the date format scan code, the week number ("%W" date format) was output as -1. The following problems have been fixed since the A.02.11 pre-release: * A CLOSE DATABASE statement could result in subsequent failures The following problems have been fixed since the A.02.12 pre-release: * The backward compatibility @function translation which is applied if the 'T' syntax flag is set caused a panic on any of the @WEEKDAY, @YEAR and @FRACT functions. The following problems have been fixed since the A.02.13 pre-release: * Form feed characters were not correctly output on REPORT SELECT statements using form files. The following problems have been fixed since the A.02.15 pre-release: * With REPORT SELECT statements using form files, if a column was referenced more than one and their field widths differed, the resulting fields were not correctly formatted. * NULL values in date/time fields are displayed as blank, not NULL. The following problems have been fixed since the A.02.16 pre-release: * Fixed a build problem with libsqlrodbc.so on Linux glibc. The driver could not be loaded by unless the symbol __pure_virtual was exported. * Type conversion from floating point value to string could loose precision. This could happen for example when using the SQL/R DLL and transferring a floating point value into a string variable. * Improved backward compatibility: - Added SQLR_OPTIONS environment variable. This can be used to easily set SQL/R compatibility options without requiring script changes. Syntax: The SQLR_OPTIONS environment varible is specified as a list of options separated by a semicolon (;). Each option consists of a keyword and a value, separated by an equal sign (=). For example: SQLR_OPTIONS="compat=fw;syntax=0" export SQLR_OPTIONS Please note that the quotes are important since the semicolon is handled as a statement separator by the shell. Keywords: compat - compatibility flags ('f' and 'w') syntax - syntax style, equivalent to SET SYNTAX directive. charset - character encoding, either ISO8859-1 or HPROMAN8 (introduced with A.02.24) - When the 'f' compatility option is set (using the SQLR_OPTIONS env variable), using DISPLAY AS (w) with a floating point variable causes the post decimals to be rounded to zero digits. This follows the behaviour of previous SQL/R versions. - When the 'w' compatibility option is set, the column width will depend on the column title as previous SQL/R versions. * CREATE VIEW (old syntax) could result in a syntax error when (quoted) identifiers could be understood as keywords. When sqlrexec and the SQL/R translate the old CREATE VIEW syntax on the fly the quoting of the identifier could be lost resulting in a server syntax error. The following problems have been fixed since the A.02.16 pre-release: * Date constants specified with the old syntax (eg. @01.01.00) are considered relative to 2000 when less than 70, otherwise they are considered relative to 1900. * Fixed column width when SQLR_OPTION="compat=w" is specified. The following problems have been fixed since the A.02.22 pre-release: * Empty detail and/or break sections in form files are now supported. * The previous CREATE VIEW syntax did not allow to qualify any tables, thus it could not be used with tables from multiple databases. This has been fixed, however, the former qualification syntax which used an underscore character (TABLE_DATABASE) must be changed to the new syntax which uses a dot character (DATABASE.TABLE). The following problems have been fixed since the A.02.23 release: * On HP-UX, for backward compatibility reasons sqlrexec now uses the HP Roman 8 character encoding by default. On all other platforms it still defaults to the ISO 8859-1 character encoding. This does not affect the Sqlr.DLL which is intended as a Eloquence client and therefore defaults to the HP Roman 8 character encoding on all platforms. Currently, you can choose between ISO8859-1 and HPROMAN8 character encodings. This can be achieved by (in the order of precedence) a) specifying "Charset=" as optional argument to the CONNECT TO statement (applies to both sqlrexec and Sqlr.DLL), b) specifying the -z option on the command line (applies to sqlrexec only) or c) specifying "charset=" with the SQLR_OPTIONS environment variable (applies to both sqlrexec and Sqlr.DLL) where is either ISO8859-1 or HPROMAN8. The following problems have been fixed since the A.02.24 release: * Using a qualified field (eg. table.field) with a form file could result in an internal failure. Assertion failed: !name panic: Aborting on internal failure, file format_core.cxx, line 134 The following problems have been fixed since the A.02.26 release: * Undefined values in a VALUES ARE clause of a FIELD statement returned the last defined value. This behaviour is different than implemented in recent SQL/R A.01.xx versions. In case no replacement value is found and no default value has been specified, the original value is returned. * Pagination did not work reliable when printing multiple reports in the same output stream. * Executing multiple SET OUTPUT = PRINTER could result in an error (Unable to close device). This error is triggered by lp or lpr which return a failure if no data are output. This error is now ignored by sqlrexec unless some output has happened. Please note that lp and lpr will write a warning to stderr unless the -s option to lp is spcified or stderr is redirected when opening the printer. HP-UX: SET PRINTER "lp -s -dprinter" Linux: SET PRINTER "lpr -Pprinter 2>/dev/null" * The backward compatible syntax translation for the @FRACT function caused an endless loop in the scanner. * The ECHO statement has been added. * The VIEW statement has been added. * Default page size (lines and width) is initially determined from terminal settings on the Linux and HP-UX platform by using the LINES and COLUMNS environment variables. * Column title no longer has influence on field width when using a form. Unless a custom field format has been defined the column width is defined by the maximum from the field format (as returned by the server) and the column title. The following problems have been fixed since the A.02.27 release: * Support for 14 digit precision values as used by Eloquence A.06.30 * Fixed bug resetting the values of the last CALCULATE ... BREAK section when output using a form. * When output was set to file, CALCULATE ... BREAK results were omitted. * TAB character was replaced by space. The following problems have been fixed since the A.02.28 release: * When output was set to file or printer, the output data was not flushed when the output channel was closed. * Output lines were truncated when using form files. The following problems have been fixed since the A.02.29 release: * Using a format more than once resulted in an internal failure. panic: Fatal problem detected in void Format::SetColIdx(int) Assertion failed: col_idx == -1 panic: Aborting on internal failure, file format_core.cxx, line 159 * A problem in the format lookup function could apply a format for the wrong field if field name matched - even if the format was qualified and the qualifier did not match. * The default date encoding has been changed from "YMD" to "YYMD". This change should not have any impact on existing scripts unless the date field is used for an index lookup (in this case the exact encoding must be known to SQL/R) and the data base is using the YYMMDD format. In this case an explicit "YMD" format must be specified. The following problems have been fixed since the A.02.30 release: * Referencing a column without a FIELD format could either result in an empty column or cause a core dump. The following problems have been fixed since the A.02.31 release: * Tab characters in form files were not correctly expanded. 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. * A debug output could cause a stack overflow when debugging is enabled. The following problems have been fixed since the A.02.33 release: * For backward compatibility, the conversion of date and time values into either numerical or string types has been changed. This especially applies to the Sqlr.DLL functions 'Nextval' and 'XNextval'. - The representation of date and time values converted into numerical types was always seconds since 1970. This has been changed to YYYYMMDD for date values (10000 * year + 100 * month + day) and HHMMSS for time values (10000 * hour + 100 * minute + second). - The format of date values converted into string types was 'YYYY-MM-DD'. This has been changed to 'YYYYMMDD'. - The format of time values converted into string types was 'HH:MM:SS'. This has been changed to 'HHMMSS'. * When converting date and time values the timezone is no longer taken into account. * When a field qualifier did not exactly match the format qualifier the field format was not found. * The compatibility flags equivalent to SET SYNTAX = "1" are now "ALFTR", not "ALFT" anymore. The 'R' flag turned out to be essential whenever a field which specifies a formula is used with ORDER BY or GROUP BY. 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.35 release: * Using date/time values with CALCULATE SUM caused a runtime error. * The INTO ... and USING "form" report options could not be specified in any order (#97). The following problems have been fixed since the A.02.38 release: * When using a form, each output line was filled with trailing space characters up to the page width (#353). * Column alias names can be specified without quotes (#466). For example: SELECT CUSTNO AS "NO" FROM CUSTOMERS is now equivalent to SELECT CUSTNO AS NO FROM CUSTOMERS The following problems have been fixed since the A.02.39 release: * When using a form, the field width calculation has been changed for backward compatibility reasons (#675). 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.48 release: * Add SET AUTOCOMMIT = {on|off} syntax to allow specifying the autocommit mode. Unless specified, the default of the driver is used (SQL/R defaults to autocommit on). The following problems have been fixed since the A.02.50 release: * Fixed improper handling of quotes in character fields with the export file format. * Increased size of stdin buffer to 4096 bytes. This currently limits the max. statement length that could be passed to stdin. * Increased the parser stack to avoid an overflow. Previous versions could fail with an "yacc stack overflow" error message. * The SQLR.DLL function nextval could cause a crash when used improperly. It now returns a status -10 when no results are available. The following problems have been fixed since the A.02.58 release: * A bug on the server side could cause releasing memory twice when the OPEN DATABASE statement was used. This could result in an error message when the connection was closed. The following problems have been fixed since the A.02.60 release: * If a negative value was multiplied with 0, -0 (negative zero) was displayed (#2121). * 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.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.63 release: * 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 sqlrexec and Sqlr.DLL (#2853). 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 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(*). The following problems have been fixed since the A.02.69 release: * The UCASE()/@UPPER() and LCASE()/@LOWER() SQL functions did not correctly handle national characters ("Umlaute"). 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.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. The following problems have been fixed since the A.02.72 release: * 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.73 release: * If a DIF file is created with string values containing quotation mark characters, these characters were not correctly escaped (#3327). The following problems have been fixed since the A.02.75 release: * 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. * Fixed a potential crash in sqlrexec and Sqlr.DLL if OPEN DATABASE was used more than once without a CLOSE DATABASE in between (#3341). The following problems have been fixed since the A.02.77 release: * The Sqlr.DLL could crash on SQL statements where a @-date-literal is the very last element (#3482). 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 new functionality was added since the A.02.81 release: * The Rowcount function was added to the Sqlr.DLL to query the number of rows affected by the last SQL statement (#3560). Syntax: CALL DLL Sqlr("Rowcount",Num_rows,Status) Num_rows - the number of rows affected by the last SQL statement or -1 if the number of affected rows cannot be determined Status - zero on success, nonzero in case of an error (call the Explain function to obtain error details) If executed after an INSERT, UPDATE or DELETE statement, the Rowcount function will return the number of affected rows. If executed after a SELECT statement, the number of affected rows can only be predicted if the SELECT statement uses a GROUP BY and/or an ORDER BY clause, otherwise the Rowcount function will return -1. * The SET CHARSET statement was added to specify the output character set (#3561). Syntax: SET CHARSET {=|TO} "character set name"; Valid character set names are "HPROMAN8" (alternative form: "ROMAN8") or "ISO-8859-1" (alternative forms: "ISO8859-1" or "ISO88591"). For example: SET CHARSET TO "ROMAN8"; SET CHARSET = "ISO8859-1"; The specified output character set comes into effect after the next CONNECT TO or OPEN DATABASE statement is executed, i.e., the output character set is a connection attribute and therefore cannot be changed during an existing connection. 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.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.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.90 release: * The SQL TIMESTAMPADD() function returned wrong results if used with a zero date argument (#3784). This affected the SQL/R functions @DAYS(), @WEEKS(), @HOURS(), @MINUTES() and @SECONDS(). 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.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 The following problems have been fixed since the A.02.92 release: * Improve rounding of IEEE fields (#3818). The field format functions in sqlrexec will now take the column type into consideration to define that max. number of significant digits. This will avoid potential trailing garbarge values when using an improper format. * 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 unexptected results. This was caused by a side effect of a change in SQLR A.02.90. The following problems have been fixed since the A.02.93 release: * Comparing a date field with a numeric value did not work in a HAVING clause (#3735). * 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.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.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. 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.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.