Close menu

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

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:

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.

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