Getting started with SQL/RIntroduction
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.
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 firstname.lastname@example.org 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:
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.
[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
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:
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:
On Linux, please use:
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 machine 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.
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 machine 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 machine 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 machine 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
- 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