Environment


Environment		ODBC

ODBC

The SIR2002 ODBC driver implements Core level ODBC API, plus some functionality of Level 1 which is commonly used by ODBC applications to import data. The driver has been tested with MS Access, MS Excel, MS Query and Minitab, SAS and SPSS Statistical Software to import data from SIR data sources into these products.

The SIR2002 ODBC Driver provides client services to ODBC enabled applications. It uses the SirSQL Server to retrieve data. The driver is designed for 32-bit systems only, and runs on Windows (95/98 and NT). It cannot be used under Win32s.

The SIR2002 ODBC Driver does not require a licence. You may copy all the files from the SIRODBC directory onto a floppy disk or onto a directory on a network drive for free distribution among your users. You may install and use the driver on as many computers as you need. (N.B. These can only access data from licenced remote SirSQL Servers.)

The SirSQL server runs under the various operating systems supported by SIR2002 (Windows 95/98, NT, Unix, OpenVMS) and SIR handles all issues of cross machine compatibility. SirSQL Server is part of SIR 2000 package and you need a licence for each computer where you install it.

Installation

The ODBC driver is automatically installed as part of the SIR 2000 installation. There is a separate installation procedure for the ODBC driver in a standalone mode.

Installing the driver in a standalone mode

Run setup.exe from the SIRODBC directory.

This copies sirdbc.dll into the Windows System directory. If you install the complete SIR2002 locally after you have installed the standalone ODBC driver, you should remove the sirdbc.dll from your Windows System Directory since this is installed as a part of SIR2002 into the SIR2002 home directory on a full installation.

Configuring ODBC sources

After the driver is installed, use the ODBC item on the Control Panel to configure the data sources.
Select the User DSN tab, click Add...;
select SIR the list of ODBC drivers and click OK. You can use SIR2002 databases and tabfiles as ODBC data sources.

SIR2002 ODBC driver sends requests to a SirSQL server and the data sources are located by the server. If you use a remote server, the location of the data source must be specified so that the server can locate the data source on its directory structure.

If you setup a SIR2002 database as a data source, type the database name in UPPERCASE in SIR Name field and the directory name (make sure you specify a terminating directory separator) in Location field. The database name must be in UPPERCASE because many OBDC clients use this name literally (in double quotes) in their SELECTS. "COMPANY" is NOT the same as "company".

If you setup a Sir tabfile as a data source, type the internal tabfile name (not the file name) in the SIR Name field and the full name (directory and filename) of the physical file in the Location field.

Don't forget to select the corresponding storage type in the Type choice control.

For local data sources you can use the Browse button and the program configures the data source for you.

The Server field specifies the server's host name and, if the server uses a non-standard port, the port number. SIR SQL Server uses port 3050 as its default, standard port.

If you run the server locally (on the same machine as the ODBC client), leave the Server field empty.

Examples:

1) Local database

    DS Name:   Fast cars
    Comments:  To qualify must do the quarter under 15 sec
    Server:
    File type: SIR Database
    Location:  d:\personal\intimate\cars\
    SIR name:  FASTCARS

2) Local tabfile

    DS Name:   Speedtraps in NSW
    Comments:  Speed kills
    Server:
    File type: SIR Tabfile
    Location:  d:\personal\intimate\cars\police\radars_nsw.tbf
    SIR name:  RADARS

3) Remote database on Unix system

    DS Name:   Diving in Australia
    Comments:  Dive sites, operators, clubs
    Server:    wildrun.com.au
    File type: SIR Database
    Location:  /home/fred/diving/
    SIR name:  DIVEAUS

4) Remote tabfile on Unix system (non-standard port 3800)

    DS Name:   Dive gear
    Comments:  Stuff from the latest catalogues
    Server:    realmad.com.au:3800
    File type: SIR Tabfile
    Location:  /users/bob/underwater/gear.tbf
    SIR name:  DIVEGEAR

Some ODBC applications can only use File DSNs (as opposed to User DSNs). You can tell if this is the case because, even though you have configured a User DSN for your ODBC source, the application does not show it in the list of available ODBC sources. If this is the case you need to create a non-shared File DSN pointing to your User or System DSN.

Create a File DSN for SIR ODBC driver (ignore any verify error messages). Go to directory where File DSNs are stored and edit the .dsn file, so it points to your real DSN (let's say 'My data' is the name of your User DSN) and doesn't include the DRIVER keyword. e.g.

[ODBC]
DSN=My data

Your applications can then access SIR data sources using both User and File DSN lists.

Logging on in an ODBC Application

Normally your ODBC application allows the SIR driver to ask you about the login information and to specify passwords, etc. In this dialog, you are asked for user name and password plus passwords specific to the data source.

The user name and password are not pre-registered on the server and can be anything you wish. Each user can have multiple connections using ODBC and the user name and password allow you to do queries across multiple data sources. You can query any data source with the same user name/password. Using a specific name and password prevents other users from retrieving the data from the data sources opened by the server for you.

Other passwords are normal SIR database or tabfile passwords.

In the unlikely case that your ODBC application does not display the SIR dialog but asks you about user id's and passwords directly, you may be given only two edit fields. In this case, type all id's separated by commas in the ID field, and all passwords separated by commas in the password field.

SirSQL Server

The new SIRSQL server must be running somewhere on the network to support any ODBC clients or any VisualPQL client processing. Start the server by running SirSQLs.exe. This starts the server at the default port of 3050 and, in a text window, reports the current IP address. There are a small number of command line options:

SirSQLS.EXE

options

Options

-help Lists the command line options.
-port:nnnn Specify a non-standard port which must be an even number.
-tout:nn Specify a non-standard server timeout. The default is 10 secs.
-kill:pwd Specify a password which allows remote user to stop (kill) the server.
-logw:filename Specify a filename to write an activity log to.
-loga:filename Specify a filename to append an activity log to.
-logd:conn Specify that connections are logged.

-tron Specifies that commands are written to the consol and logged.

To interact with the server, press CTRL-C/CTRL-Break then enter a command:
exit Stops the server.
limit n Limits the maximum attached clients. Default 128.
list Lists the attached clients
kill n Stops the specified client connection.
tron Switches on the detailed command trace.
troff Switches off the detailed command trace.

The server can be stopped by a remote process if the -kill:pwd is specified. The remote process runs sirkill.exe specifying the numeric IP address of the server and the appropriate password. This stops the server.