SQL/R A.03.00 Release Notes - Client Compatibility

SQL/R A.03.00 Client Compatibility

This document covers the changes to the SQL/R client programs sqlrexec and Sqlr.DLL

These programs were rewritten for SQL/R A.02.00 to use the SQL/R ODBC server backend for database access. As a consequence, the SQL/R syntax has changed to be more compliant with the SQL syntax.

A built-in syntax conversion option ensures backward compatibility with most of the existing 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 SQL/R. If an existing script does no longer work with the new SQL/R version, it may need minor adaptions as explained below.

Changed Behavior
Changed Syntax
ODBC Connection String

Changed Behavior

SQL/R has been rewritten for SQL/R A.02.00 and may behave slightly different than previous versions. A lot of effort was spent to make it as compatible as possible.

Most sqlr 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 a <db_name>.qif file. This could be used to initialize conversions.
Since sqlrexec no longer has direct access to the database this is no longer supported. 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).
Since sqlrexec no longer has direct access to the database this is no longer supported. The SQLR_PATH environment variable may be used as an alternative.
The A.01.xx version implicitely added a ".qrf" extension to script files which were executed by RUN. This is no longer supported, any extensions must be specified explicitly.
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 encountering an unknown command, SQL/R A.01.xx versions 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 as 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, preferably, by using a repository file (please refer to the SQL/R ODBC manual, Sections 2.4 and 4.2 for more information).
As of SQL/R 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 temporarily 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 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;

As of 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 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 from 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 as of A.02.xx.
If an item occurs in a previously specified table, the first instance will be used. Previous sqlrexec (depending on WHERE condition) used the field in the related table.
For example:
```
CREATE VIEW p1
 PATH bestellung
 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
 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 versions, column width was determined by the maximum of field width and column title. If a field format has been defined explicitly, SQL/R A.02.xx will honour this request and truncate the column if required. This behaviour can be disabled by using the SQLR_OPTIONS.compat option (see below).

Changed Syntax

sqlrexec supports a more SQL compliant syntax and provides additional options. The supported syntax is controlled using the SET SYNTAX statement (see below). By default sqlrexec translates the previous syntax on the fly and some new features may not be accessible. Please refer to the description of the SET SYNTAX statement below for additional information.

When using the OPEN DATABASE statement to open an Eloquence eloqdb database, the eloqdb database path format must be used:
```
OPEN DATABASE "hostname:port/database";
```
As with Eloquence eloqdb, 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).
If the ODBC server is configured to listen on a service/port other than the default sqlrodbc service, the SQLR_SERVER environment variable may be used to specify the service or port. (Note: Requires SQL/R version A.03.33 or newer.)
For example:
```
OPEN DATABASE "external/PPS";
```
This will use the host name "external" to connect to the ODBC server. If the desired server instance listens on a service/port other than sqlrodbc, for example on port 6789, this may be configured by setting the SQLR_SERVER environment variable like below prior to invoking the client program:
```
SQLR_SERVER=external:6789
```
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 defined 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";
```
The SQLR_SERVER environment variable may be set to specify the default ODBC server address and service/port if different from localhost:sqlrodbc. (Note: Requires SQL/R version A.03.33 or newer.) This is used when neither the Server nor the Service option is specified.
For example, default ODBC server instance on the local system listening on a port different from the default sqlrodbc service (both SQLR_SERVER settings below are equivalent):
```
SQLR_SERVER=localhost:6789
SQLR_SERVER=:6789
```
For example, default ODBC server instance on a remote system in the local network listening on the default sqlrodbc service using an IPv6 link-local address (both SQLR_SERVER settings below are equivalent):
```
SQLR_SERVER=[fe80::9656:d028:8652:66b6]:sqlrodbc
SQLR_SERVER=[fe80::9656:d028:8652:66b6]
```
When using the Eloquence eloqdb 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);
```
Alternative 1:
```
FIELD y = IF(x IS NULL, 0,x);
```
Alternative 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";

Supported 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 an SQL compliant SELECT statement which allows to specify multiple table names. Previous SQL/R versions required to specify table relation with the CREATE 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 {*|<columnList>}
    FROM <tableSpec> [, <tableSpec> ...]
    [ WHERE <conditionList> ]

  Outer join:
    SELECT {*|<columnList>}
    FROM {oj <tableSpec> [LEFT OUTER JOIN <tableSpec> ...]
          ON <condition>}
    [ WHERE <condition> ]
```
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 now supports 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 <viewName> [ (Column [, Column ...]) ]
    AS SELECT {*|<columnList>}
    FROM <tableSpec> [, <tableSpec> ...]
    [ WHERE <condition> ]

  Outer join:
    CREATE VIEW <viewName> [ (Column [, Column ...]) ]
    AS SELECT {*|<columnList>}
    FROM {oj <tableSpec> [LEFT OUTER JOIN <tableSpec> ...]
     ON <condition>}
    [ WHERE <condition> ]
```
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;
```
Outer Join 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 internally translated into equivalent standard SQL functions:

SQL/R @function               Mapping to 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)

The SET ROWLIMIT statement may 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 qualified 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="ALIEN");
```
This causes values for the field contry to become translated to either "HOME" (if value equals "DE") or "ALIEN" 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 loses its special meaning to the scanner. This makes it possible to use quotes to $ character in a string constant.
A new "SET PAGESEP = <string>" 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	  Carriage return
\0<oct>	  The page separator is specified by an octal number
\0x<hex>  The page separator is specified by a hex number
\<dec>	  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. Optionally, an INTO clause can be specified to define the output destination.

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 [[<owner>.]<table>] [report_options]
VIEW COLUMNS [<column>] [FOR [[<owner>.]<table>]] [report_options]
```
VIEW TABLES returns information on tables provided by the data source. It is based on the SQLTables ODBC function.
Syntax:
```
VIEW TABLES [[<owner>.]<table>] [report_options]
```
Arguments are owner (which is understood as the database name or database 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 first 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):
```
List of Tables in data base source

Data Base	 Table Name
---------------- -----------
%%
@2		 @3
```
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 [<column>] [FOR [[<owner>.]<table>]] [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 than %), 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 first 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 variable specifies 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')
  - When the 'f' compatility option is set (using the SQLR_OPTIONS env variable), using DISPLAY AS (w) with a floating point variable causes the value to be rounded to zero decimal places. 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.
- syntax - syntax style, equivalent to SET SYNTAX directive.
- charset - character encoding, either ISO8859-1 or HPROMAN8 (introduced with A.02.24)

ODBC Connection String

The CONNECT TO statement may be used to connect to an ODBC data source by the name that is defined by the ODBC server. In addition, parameters may be specified to define the ODBC connection.

CONNECT TO "dsn_name"[,"arg" ...];

The parameters below are recognized:

Server={name or IP address of the SQL/R ODBC server}

Specifies the host name or IP address of the SQL/R ODBC server. The default is the local system ("localhost").

Service={service name or port number}

Specifies the TCP service name or port number used by the SQL/R ODBC server. The default is sqlrodbc.

UID={user name}

Specifies the user name used to authorize with the SQL/R ODBC server. The default is public.

PWD={user password}

Specifies the password that is used to authorize with the SQL/R ODBC server. The default is an empty password.

Password={yes|no}

(Windows specific) If set to yes, a password dialog is opened that allows to enter the password interactively. This has no effect on other platforms.

Quoting={0|1|2, or Character / ASCII code}

Enables or disables ODBC identifier quoting or specifies identifier quoting character. Identifier quoting allows to use identifiers (such as table or column names) that do not follow the SQL syntax. For example, an identifier may conflict with a keyword or have improper characters. In this case it can be enclosed in quotes ("quoted") to mark it as an identifier.

0 = default (enabled on Windows, disabled on HP-UX and Linux)
1 = disabled
2 = enabled

The default identifier quote is the pipe character (|). To define a different quoting character, the respective character or its ASCII code may be specified. For example, Quoting=" or Quoting=34 both enable identifier quoting using the double quotation mark.

Bulkfetch={0|1}

Enables or disables the bulk fetch client server protocol option which transfers multiple result rows at once to reduce transport latency. The bulk fetch protocol is enabled by default.

0 = disabled
1 = enabled

Protocol={A03|A02}

As of SQL/R A.03.00 as new network protocol is used by default which supports the encryption and auditing functionality. However, this protocol is not backwards compatible with previous SQL/R versions.

A03 = use the A03 network protocol (incompatible with previous SQL/R A.02.xx server versions)
A02 = use the backward-compatible A02 network protocol

Notice: The SQLR_PROTOCOL=A02 environment variable may be used to specify the A02 protocol to be used by default.

(Windows specific) On Windows the default protocol may be specified in the ODBC Driver configuration file. Specify Protocol=A02 in the [Config] section of the ODBC driver configuration file (Start Menu - Programs - SQLR - ODBC Driver Configuration).

Audit={text}

May be used to specify a text string that is included in the audit information submitted to the database server. If omitted, the value of the SQLR_AUDIT_INFO environment variable is used (if defined).