====================================================================== SQL/R A.03.09 - Client Release Notes - 11.10.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.06.10 and newer. On the HP-UX PA-RISC platform, Eloquence A.05.xx is also supported. Installation of Eloquence B.08.10 or B.08.00 is recommended. Please ensure that current Eloquence patches are installed. * SQL/R is installed in the /opt/sqlr2 directory on the HP-UX and Linux platforms. * 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. 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 many user visible changes and the ODBC release notes (RELNOTES-ODBC) describe the differences/changes details. * 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. * To communicate with a previous SQL/R A.02.xx server, the SQL/R client / ODBC driver must use the backward-compatible A02 network protocol. The A02 network protocol is used: - either if the environment variable SQLR_PROTOCOL=A02 is set - or if the Protocol=A02; option in the ODBC connection string is specified - or if the Protocol=A02 option is specified in the data source definition in the odbc.ini configuration file of the ODBC driver manager - or, on the Windows platform, if the option to use the A02 network protocol is checked in the data source configuration dialog - or, on the Windows platform, if Protocol=A02 is configured in the [Config] section of the ODBC driver configuration file (Start Menu - Programs - SQLR - ODBC Driver Configuration). ---------------------------------------------------------------------- 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.03.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.03.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.03.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.03.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.03.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.03.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.03.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 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). * 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 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); 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.03.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. ---------------------------------------------------------------------- ODBC Connection String ---------------------------------------------------------------------- An ODBC connection string is typically used to establish a connection to the SQL/R server. The parameters below are recognized: * DSN={name of the data source} * Server={name or IP address of the SQL/R ODBC server} * Service={service name or port number} * UID={user name} * PWD={user password} * Password={yes|no} If set to yes, on Windows a password dialog is opened. * Quoting={0|1|2, or Character / ASCII code} Enables or disables ODBC identifier quoting or specifies identifier quotation character. 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 quotation 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 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} By default, a new network protocol is used which supports the encryption and auditing functionality. A03 = use the new network protocol (incompatible with previous SQL/R A.02.xx server versions) A02 = use the backward-compatible A02 network protocol Alternatively, to use the A02 protocol, the environment variable SQLR_PROTOCOL=A02 may be set or Protocol=A02 may be configured in the [Config] section of the ODBC driver configuration file (Start Menu - Programs - SQLR - ODBC Driver Configuration). * Audit={text} Optionally specifies 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, if set, is used.