Getting started with SQL/R

Introduction

SQL/R adds SQL and ODBC functionality to the Eloquence database. The SQL/R software consists of a client and server component.

On the SQL/R client side, an ODBC driver is provided for the HP-UX, Linux and Windows platforms. It communicates with the SQL/R server via TCP networking.

In addition, the SQL/R client side provides the programs "sqlrexec" and "Sqlr.DLL". sqlrexec is a reporting frontend that may be used to execute SQL statements and view the results or export them to text files. The Sqlr.DLL is an extension for the Eloquence programming language, it allows programs written in the Eloquence programming language to execute SQL statements through the SQL/R server.

The SQL/R server consists of the sqlrodbcd background process and a set of configuration files. For performance reasons it is typically installed and run on the same system with the Eloquence database server.

Of the SQL/R server configuration files, the odbc.dsn file is the most important one because it configures the ODBC data sources.

When the SQL/R client establishes an ODBC connection it specifies an ODBC data source. On the SQL/R server side, this data source may include one or multiple Eloquence databases that will be opened simultaneously.

The odbc.dsn configuration allows to specify whether a data source is read-only or writable.

Installation

The SQL/R software is avaliable for download from the SQL/R download page.

Installation instructions for HP-UX and Linux are included in the SQL/R server release notes.

On Windows, the installation program allows to select which components are installed. This way, either only the ODBC driver or the full software (ODBC driver, server and client components) may be installed.

On HP-UX and Windows, a 30 days temporary license is granted if SQL/R is installed for the first time. On Linux, a temporary license is available through Marxmeier Software AG. Please contact sales@marxmeier.com for SQL/R license requests.

Configuring a data source

The SQL/R server accesses Eloquence databases through ODBC data sources. Each data source must be configured in the odbc.dsn configuration file.

On HP-UX and Linux, this is located at:

  /etc/opt/sqlr2/odbc.dsn

The file may be opened and edited with any plain text editor, such as vi.

On Windows, the odbc.dsn configuration file is accessible through Start Menu -> Programs -> SQLR -> Data Source Configuration.

The odbc.dsn configuration file contains definitions of all data sources that are known to the SQL/R server.

For example:

  [Test Data Source]
  Database    = localhost:eloqdb/OURDB
  Login       =
  Password    =
  Alias       = OURDB
  Repository  =
  UserList    = public
  EnableWrite = readonly

This configures a readonly data source named "Test Data Source".

A data source may include one or multiple Eloquence databases that are simultaneously opened when a connection to the data source is established.
Each included database uses the parameters Database, Login, Password and Alias.
Database - This is the Eloquence database name used to open the database.
Login and Password - These are used to log on to the Eloquence database server before the database is opened. If omitted (as in the example above), the Eloquence default login "public" is used without a password.
Alias - The database name is used as SQL qualifier. However, a fully qualified Eloquence database name such as "localhost:eloqdb/OURDB" does not conform to the SQL syntax. Therefore, Alias is used to specify an alternative SQL qualifier name (the plain database name in the example above).
Repository - A repository is a plain text file that contains initial SQL statements that are executed each time a connection to the data source is established. This is useful to configure SQL views or conversions for date items. In the example above it is not used. A detailed description of the repository syntax is contained in the SQL/R ODBC manual.
UserList - This is a comma-separated list of users that may access the data source. The default "public" user grants access to everyone without a password. SQL/R users other than "public" are configured in the odbc.user configuration file.
EnableWrite - This specifies whether the data source is readonly or read-write. Possible values are readonly, limited and full.
Limited write access requires that each INSERT, UPDATE or DELETE statement is covered by an explicit transaction. Explicit transactions start with the BEGIN WORK statement and end with either the COMMIT WORK or ROLLBACK WORK statement.
Full write access allows the use of mainstream ODBC client programs to write to the data source.

To configure multiple Eloquence databases into a data source, the Database, Login, Password and Alias parameters are simply repeated for each database, as shown below:

  [Test Data Source]
  Database    = localhost:eloqdb/OURDB1
  Login       =
  Password    =
  Alias       = OURDB1
  Database    = localhost:eloqdb/OURDB2
  Login       =
  Password    =
  Alias       = OURDB2
  Repository  =
  UserList    = public
  EnableWrite = readonly

To configure a read-write database, a WriteList must be configured and EnableWrite must be set, such as:

  [Test Data Source]
  Database    = localhost:eloqdb/OURDB
  Login       =
  Password    =
  Alias       = OURDB
  Repository  =
  WriteList   = public
  EnableWrite = full

In the example above, the WriteList replaces the UserList shown in the previous examples. However, it is possible to configure both an UserList and a WriteList to grant read-write access only to specific users, for example:

  [Test Data Source]
  Database    = localhost:eloqdb/OURDB
  Login       =
  Password    =
  Alias       = OURDB
  Repository  =
  UserList    = public
  WriteList   = mary,joe
  EnableWrite = full

In the example above, the users "mary" and "joe" (configured in the odbc.user configuration file) are granted read-write access while anybody else is granted readonly access.

Please note that a user should not be listed in both UserList and WriteList. Doing so results in readonly access because the UserList takes precedence.

Starting the SQL/R server

On HP-UX and Linux, before the SQL/R server can be started the necessary operating system user and group must be configured.

The easiest way to do this is to add the line below to the end of the /etc/passwd file:

  sqlr:*:82:82:DO NOT USE OR DELETE - needed by SQLR:/tmp:/bin/nologin

Equivalently, add the line below to the end of the /etc/group file:

  sqlr::82:

Note: This configures the user and group id 82. Please make sure that they are not already in use, for example with grep 82 /etc/passwd and grep 82 /etc/group. If the id 82 is already in use, try a different one.

To start the SQL/R server on HP-UX, please use:

  /sbin/init.d/sqlr2 start

On Linux (systemd), please use:

  systemctl start sqlr2.service

On Linux (System V init), please use:

  /etc/init.d/sqlr2 start

On Windows, please open the Services applet in the Windows Control Panel below Administrative Tools. Locate the "SQLRODBC" service and open its properties dialog. You may want to configure the Automatic Startup option so that the SQL/R server is always started when the system is booted. Then please start the "SQLRODBC" service by clicking the Start button.

Configuring a Windows ODBC data source

To access the configured SQL/R data sources from Windows, the SQL/R ODBC driver must be installed on the particular Windows PC. The SQL/R Windows installation program allows to install the ODBC driver alone.

Each data source must be configured in the Windows ODBC Control Panel below Administrative Tools.

Please note that on 64-bit Windows there are two separate ODBC control panels present. The default ODBC control panel, located in the Administrative Tools control panel folder, is the 64-bit ODBC control panel. This is used to manage ODBC data sources that use a 64-bit ODBC driver, to be used by 64-bit programs.

To invoke the 64-bit ODBC control panel, use:
Control Panel -> Administrative Tools -> Data Sources (ODBC)

To invoke the 32-bit ODBC control panel, use:
Start Menu -> Programs -> SQLR -> 32-bit ODBC Administrator

The Windows ODBC Control Panel allows to configure User, System and File data sources. A User DSN is specific to the logged on user. A System DSN is stored in the global registry and therefore available to all users on the system. A File DSN is saved as .dsn file in the file system.

The required type of data source (User, System or File) depends on the ODBC application. SQL/R supports all three types.

SQL/R ODBC data sources must use the SQL/R ODBC driver. Furthermore, the data source name must match the configured name of the data source in the SQL/R server's odbc.dsn configuration file. This is the name in square brackets, for example "Test Data Source".

In the SQL/R ODBC driver configuration dialog, the User Name should be set to a valid user configured in the UserList or WriteList of the data source. If this is the "public" user, the User Name must be set to "public".

If this user has a password configured in the SQL/R server's odbc.user configuration file, the Ask for Password option should be checked.

In the Network Connection box, Server should be set to the IP address or host name of the system where the SQL/R server runs. Service is typically set to "sqlrodbc" or "8003", i.e., the service name or TCP port number the SQL/R server listens to.

If this is appropriately configured and the SQL/R server is active, the newly configured data source should now immediately work.

Using an ODBC connection string

When using ODBC in a program, the SQLDriverConnect ODBC function is often used. This function allows to connect to an ODBC data source even if it is not configured in the Windows ODBC Control Panel (see the note below). To achieve this, a connection string is passed to the SQLDriverConnect function.

A connection string consists of one or more fields that are separated with ';' semicolon characters. The available fields are:

DSN=name of the data source
The specified data source name must match the name of a data source in the SQL/R server's odbc.dsn configuration file. If the data source is not configured in the Windows ODBC Control Panel the field must be labeled ServerDSN, not DSN, and an additional DRIVER field must be specified (see the note below).
Server=IP address or host name of the SQL/R server
Service=service name or port number the SQL/R server listens to
If the default "sqlrodbc" service name or the TCP port number 8003 is used, Service may be omitted.
UID=user name
This should be set to a valid user configured in the UserList or WriteList of the data source. If this is the "public" user, UID may be omitted.
PWD=user password
The password specified here is matched against the configured password of the user in the SQL/R server's odbc.user configuration file. Please note that the "public" user does not have a password, in which case PWD may be omitted.
Password=yes or no
If set to yes, on Windows a password dialog is opened before the ODBC driver tries to connect to the data source.
Quoting=0, 1 or 2, or character / ASCII code
This may be used to enable or disable the ODBC identifier quoting. For example, OpenOffice fails if ODBC identifier quoting is enabled.
0 = default (enabled on Windows, disabled on HP-UX and Linux)
1 = disabled
2 = enabled
The default identifier quote is the pipe character (|). Starting with the A.03.08 version, a different quotation character may be defined by specifying the respective character or its ASCII code. For example, Quoting=" or Quoting=34 both enable identifier quoting using the double quotation mark.
Bulkfetch=0 or 1
If set to 1, multiple rows of data are fetched within the same network transaction, which may significantly improve performance. This is the default behavior if Bulkfetch is not specified.

As an example, the following connection string would connect to a SQL/R server running on the 192.168.55.66 system and open an ODBC data source named "Test Data Source" using the "public" user (please note that UID=public may be omitted because "public" is the default user):

  DSN=Test Data Source;Server=192.168.55.66;UID=public

The following example uses a configured user named "joe" with the password "secret":

  DSN=Test Data Source;Server=192.168.55.66;UID=joe;PWD=secret

In the last example, the password is not specified. Instead, the ODBC driver will open a password dialog to query the password when the data source should be opened:

  DSN=Test Data Source;Server=192.168.55.66;UID=joe;Password=yes

Please note: If the data source is not configured in the Windows ODBC Control Panel, the DRIVER field must be specified to tell the Windows ODBC Driver Manager that it should use the SQL/R ODBC driver. Furthermore, the DSN keyword must be replaced with ServerDSN because the DRIVER and DSN keywords are mutually exclusive.

The following connection string illustrates how a connection may be established to a data source on a remote system without having configured the data source in the Windows ODBC Control Panel:

  DRIVER=SQL/R Driver (32 Bit);ServerDSN=Test Data Source;Server=192.168.55.66

where "SQL/R Driver (32 Bit)" is the name of the SQL/R ODBC driver as displayed in the ODBC driver list in the Windows ODBC control panel.

SQL/R database locking options

Starting with the A.02.68 version, SQL/R provides optional database locking when executing a write statement such as INSERT, UPDATE or DELETE.

Database locking is configured per data source in the SQL/R server's odbc.dsn configuration file by means of the UseLocks parameter. Possible values are no, set and db.

no - specifies that database locks should not be automatically acquired. This will increase concurrency but may result in failed write statements due to concurrent locks.
set - specifies to acquire a data set level database lock on database write calls (DBLOCK mode 3).
db - specifies to acquire a database level lock on database write calls (DBLOCK mode 1).

The database lock mode should comply to the lock strategy of those IMAGE applications that access the same database. For example, if these applications use data set level locks, UseLocks = set should be configured.

In the example below, a data source is configured to use data set level locks:

  [Test Data Source]
  Database    = localhost:eloqdb/OURDB
  Login       =
  Password    =
  Alias       = OURDB
  Repository  =
  WriteList   = public
  EnableWrite = full
  UseLocks    = set