Close menu

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

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.

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.

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.

(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.

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

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).

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).