Close menu

SQL/R Installation on Windows
Version A.03.00 to A.03.20

This document contains installation instructions specific to the Windows platform for SQL/R version A.03.20 or older.

For SQL/R version A.03.21 or newer, please refer to the updated Windows installation instructions.


Installation instructions

 SQL/R is available for 32-bit (x86) and 64-bit (x64) systems.

Administrative privileges are required to install SQL/R. Current Windows versions (starting with Windows Vista) will automatically request administrative privileges after the installation program is started.

In the setup dialog, the SQL/R component categories may be selected or unselected for installation. On initial installation, the ODBC Driver component is selected. Subsequently, the selected componts reflect the current installation and can be used to change the installation.

The following categories are available:

SQL/R ODBC Driver
Selecting this category installs the SQL/R ODBC driver.

SQL/R Server
Selecting this category installs the SQL/R ODBC server. As an option a temporary license key may be installed that is valid for four weeks (once per system).

SQL/R Client Components
Selecting this category installs the sqlrexec and SQL.DLL components.

Tutorial and example database (English and/or German)
The installs the sqlrexec tutorial and example database for the selected language.

SQL/R must be installed to a local disk (not a network drive) as some binaries are registered with Windows and may be required even if the network storage is not available.

Updating an existing installation will retain the configuration files.

ODBC driver on 64-bit Windows versions

On a 64-bit (x64) Windows installation, choosing to install the SQL/R ODBC driver causes both the 32-bit and the 64-bit ODBC drivers to be installed.

The 64-bit ODBC driver is only used with 64-bit applications. Most applications are 32-bit and only use the 32-bit ODBC driver.

Please note that 64-bit Windows has separate ODBC control panels for 32 and 64 bit ODBC drivers.

Configuring sqlrodbc TCP service

The installation program adds the following TCP service definition to your local SERVICES file: 

#
# SQL/R related services
#
sqlrodbc        8003/tcp    # SQL/R ODBC

where the first column specifies the service name ("sqlrodbc") and the second column the associated port number and protocol ("8003/tcp").

This entry associates the service name "sqlrodbc" with the TCP port number 8003. The service name "sqlrodbc" is used as a default.

In rare cases it might be necessary to modify this preset configuration, for example if the port number is already in use or does not match the one configured on other systems.

The SERVICES file is located at:
C:\Windows\System32\Drivers\etc\SERVICES


ODBC Server Configuration

In order to run properly, the SQL/R ODBC Server must be configured prior to its first usage. Some tasks are automatically performed by the setup program.

Permissions to change SQL/R configuration files

On current Windows versions (starting with Windows Vista) a local group named "SQLR Administrators" is created which has modify/delete permissions on the SQL/R etc configuration directory and files.

The installing user is automatically made a member of this group.

After the installation has finished, it may be necessary to log off and log on again to activate this new group membership and also to activate the modified PATH environment variable where the SQL/R bin directory was added.

To add other users to the "SQLR Administrators" group:

  1. Open the Computer Management Console

    From the Start Menu, navigate to:
    Control Panel » Administrative Tools » Computer Management

    Alternatively, from the Start Menu, open:
    All Programs » Accessories » Run
    Then enter "compmgmt.msc" and click OK.

  2. In the left pane, choose
    Local Users and Groups » Groups

  3. In the right pane, locate the "SQLR Administrators" group and open its properties with a doubleclick. The properties dialog allows to change the group's list of members.
Users added to the "SQLR Administrators" group may need to log off and log on again to activate this new group membership.

Registering the SQL/R ODBC Server with Windows

The SQL/R ODBC Server is installed as a Windows service. During the installation process the sqlrodbcd.exe executable is registered with the Windows Service Control Manager.

To manually register the SQL/R ODBC Server with the Windows Service Control Manager the command sqlrodbcd.exe -install is used:

  1. Log on as Administrator.

  2. Open a Command Prompt window.

  3. Execute the command: sqlrodbcd -install
    This makes the SQL/R ODBC Server available to the Windows Service Control Manager. The service name is "SQLRODBC".

To manually unregister, the command: sqlrodbcd -remove may be used.

Configuring the SQL/R ODBC Server

The SQL/R ODBC Server is configured by editing the odbc.cfg, odbc.user and odbc.dsn configuration files, located in the SQL/R configuration directory. During the initial installation a template file is installed.

The SQL/R configuration files are installed in the etc subdirectory of the SQL/R installation directory. This is usually C:\Programs\Marxmeier\SQLR\etc.

Each configuration file is responsible for a specific aspect of the SQL/R ODBC Server configuration:

odbc.cfg This is the general server configuration file.
odbc.user In this file all users are defined who are allowed to access the ODBC server.
odbc.dsn This file defines all accessible data sources on the server.

Configuring the SQL/R ODBC Server startup

After the SQL/R ODBC Server has been registered with the Windows Service Control Manager it can be accessed using the Services applet.

  1. Open the Windows Control Panel
  2. Select Performance and Maintenance
  3. Select Administrative Tools
  4. Open the Services applet

Locate the SQLRODBC entry in the list and select it with the mouse. Then open the Properties dialog, either from the menu or using the context menu (right mouse button):

Along with the Startup Options additional arguments for the SQL/R ODBC Server may be specified. This is usually neither required nor recommended since all settings are normally provided in the odbc.cfg configuration file. However this option may be useful in some cases, for example, starting the SQL/R ODBC Server temporarily with different log options.

Supported arguments are:

Option Description Equiv.*
-d flags Specifies the server debugging log flags. LogFlags
-l name Specifies the server log file. LogFile
-s name The service name (as defined in the SERVICES file) or the port number where the server should listen for requests. The default value is sqlrodbc. Service
-h name To enable the HTTP status display feature of the server this option may be used to specify the service name or port number where the server should listen for HTTP requests. HttpService
(* equivalent configuration file directive)

For example, to specify a different TCP service name (the default is "sqlrodbc"), you could enter the following into the Startup Parameters field: 

-save -s 8567

This will make the SQL/R ODBC Server listen to the TCP/IP port number 8567. The leading -save argument makes this command line persistent, so that the same arguments are used each time the SQL/R ODBC Server is started. If you do not specify the -save argument this command line will be used only once.

If you want to make sure that the command line is empty and that any persistent command line is deleted, just specify -save without any additional arguments.

At this point, everything is configured and the SQL/R ODBC Server can be started:

Unless a log file is specified, the SQL/R ODBC Server will write log messages to the Windows Event Log. In case the SQL/R ODBC Server does not start the Event Log should provide an error message pointing out the cause of the problem. You should also periodically check the Event Log in order to get aware of possible configuration problems.

  1. Open the Windows Control Panel
  2. Select Performance and Maintenance
  3. Select Administrative Tools
  4. Open the Event Viewer applet
  5. Select the Application Log

Having started the SQL/R ODBC Server for the first time after installation, the Application Log should already contain a few entries noting that the SQL/R ODBC Server was successfully installed and started.

Starting/Stopping the SQL/R ODBC Server from the command line

The net start and net stop system commands can be used to start and stop the SQL/R ODBC Server from the command line. This is equivalent to starting and stopping the server using the Windows Services applet as explained in the previous section.

Command syntax: 

net start SQLRODBC
net stop SQLRODBC

The SQL/R ODBC Server HTTP status display

When the HttpService is defined in the odbc.cfg configuration file, a WEB browser may be used to view the configuration and current use of the SQL/R ODBC Server process in your network.

To access the SQL/R ODBC Server, you provide an URL like this: 

http://server:port/

where server is the host name or IP number of the system running the SQL/R ODBC Server and port is the port number used for HttpService in the odbc.cfg configuration file.

Find an example of these http status pages for SQL/R here.

The SQL/R ODBC Example Database

SQL/R ODBC includes a sample database. It must be created manually. The SQL/R sample database is available with English or German table and item names.

First, the sample database should be installed in an Eloquence database environment which must be already setup and running.

The following instructions install the SQL/R sample database in the default Eloquence database server: 

schema db.schema
dbcreate db
dbimport -vs db.exp db

Notes:

Next, adjust the odbc.dsn configuration file as below.

For the English version: 

[SQL/R Example]
Database=localhost:eloqdb/db
Repository=C:/Programs/Marxmeier/SQLR/share/db.e/db.rep

For the German version: 

[SQL/R Example]
Database=localhost:eloqdb/db
Repository=C:/Programs/Marxmeier/SQLR/share/db.g/db.rep