Exchanging Data between SIR and other systems

By
Dave Doulton
University of Southampton

Introduction
Setting up ODBC sources
ODBC import
ODBC pql
ODBC members
Using sirweb.cgi
ODBC and the sirweb.cgi
ODBC and the PQLServer
Conclusion


Introduction:
For years the only easy way to exchange data between SIR and other systems was through files.
SIR procedures could be used to write files for specific systems i.e. SPSS,SAS dbase etc.

• SIR PQL can be written to create scripts and files to build databases in other systems such as Oracle and MySql
• SIR batch data input could be used to import data into SIR from other systems provided they could write fixed column data.
• SIR PQL can be written to read other data formats.
• With the advent of ODBC life becomes a lot easier for all ODBC enabled systems.
• Another system that has become very popular is the web. Getting data to and from that is now very easy.
• Cross platform access is now possible for these technologies as well.
• ODBC Open DataBase Connectivity
• This session will show you how to set up ODBC sources and use them from SIR.
• It will show the simplest way to go about this with PQL members available from SUG website.
• It will show how to access SIR data via ODBC from other packages.
• It will show how to exchange data using the web.

Setting up ODBC sources

• ODBC sources are set up using Data Sources from the Administrative Tools Menu
• Sources can be User, available to that user only or System, available to all users both are configured in exactly the same way but from different tabs
• There is another type of DSN labelled File DSN this is an alternative way of storing the ODBC parameters. User and System store the details in the registry, File stores them as the name implies in a file, it can point to the registry for parameters if desired.

• I shall show the steps in setting up  some System DSNs
• I have previously set them all up as User DSNs for use in the demonstrations

• Choose the source and click Finish.
• You will then get a configuration screen which will vary according to the source.
• The next screens will show various different types

If this conversion of the power point presentation is going well enough, I'll continue with the rest after someone else approves this.  - Greg Neils

Problems importing scaled vars

Scaled vars are a really useful facility in SIR. But as the documentation says: "The major limitation on scaled integers is that the maximum integer value is 2,147,483,643. If scaled to a negative power, this may not be large enough. For example, scaling to -2 for money, gives a maximum value of 21,474,836.43. (For larger money values, use whole cents in a double precision floating point variable:R*8)".

On a recent import from SPSS it gave the following error.

4.2623 grow0000 (-2)/
Invalid variable type. (Error 510 - GROW0000)

This was caused by a value label with a value assigned as 999999999 which SIR checked and finding that it was bigger than the limit, and converted to a R*8 and dropped the scaling. There were no values in the data bigger than the limit, so removing the value label prior to import got us back on track.

variable formats displayed in record schema screens

The first record schema screen for a variable (single click on variable name) displays the external external (base 10) variable format (kind of a combination of the variable type and the input columns) whilst the second screen (double click on variable name) displays the internal the internal format(number of bytes required).

The VFORMAT function writes the variable's external (base 10) format. The VARLENG function will give you the number of internal bytes defined.

(extracted from email from David Baxter)

Removing a database password during an unload

In order to remove a database password during an UNLOAD in SIRxs use NEWPW={} on the UNLOAD statement.

using functions directly in WRITE command

In order to use functions directly in the WRITE command enclose the function statement in square brackets [].

E.g. WRITE [AINT(3.14)]