DBIMPORT procedure

Loads data from an ODBC database, PC Windows only (D.B. Baird).

Options

PRINT = string

What information to print (catalogue); default cata

OUTTYPE = string

Whether to form a GenStat command file or spreadsheet file as output (GEN, GSH); default GSH

METHOD = string

Whether to load data into the GenStat server after creating the file or whether merely to create the file (create, load); default load

IMETHOD = string

Whether to read the column names from the first row of data, or to use default column names (read, none); default read

ENDSTATEMENT = string

Ending statement to use in a GEN output file (RETURN, ENDBREAK); default RETURN

WARNINGDIALOGS = string

Whether dialogs giving ODBC error and warning messages are presented (display, omit); default disp

Parameters

DB = text

Database connection string

SQL = text

SQL Query string to run against the ODBC database

GDBFILE = text

Name of GDB file to be used in reading from ODBC database

OUTFILE = text

Output file to be created; if this is not provided a temporary file will be created, and then deleted if the data is loaded

COLUMNS = text

Names and/or type codes for the columns read (the type of column can be forced by ending the column name, if supplied, with the code ! for a factor, # for a variate, and $ for a text)

ISAVE = pointer

Name of a pointer to save the column identifiers

Description

This procedure runs an SQL command against an ODBC database and returns the data as a set of GenStat structures. The COLUMNS parameter can be used to set the names and types of the structures or to receive back a pointer to the structures created. You can force the type of column by ending the column name with the code ! for a factor, # for a variate, and $ for a text. For example

COLUMN=!T('Trt!','ID$','Rank#')

will create a factor called Trt, a text called ID and a variate called Rank. If only the type code is provided, the columns will not be renamed, but the new types will set, e.g.

COLUMN=!T('!','$','#')

will force the first three columns to be of type factor, variate and text respectively. A column name ending with an underscore (_) will also be converted to a factor in GenStat.

Either an existing GDB file is used which contains an ODBC query, or the texts supplied by the DB and SQL parameters are used to specify the ODBC query. The GDB file can be created using the Spread > New > ODBC Data Query menu. The DSN line in this text file can be used to connect to the same database as specified by the DB parameter with ad hoc queries specified with the SQL parameter.

Note that any file names in the DB connection string will need to use \\ rather than / for the directory separator, i.e. the file name C:\WORK\MYDATA.MDB would need to be given in GenStat as 'C:\\WORK\\MYDATA.MDB' rather than as C:/WORK/MYDATA.MDB.

(Note: DBIMPORT replaces the procedure ODBCLOAD from earlier editions of GenStat.)

Options: PRINT, OUTTYPE, METHOD, IMETHOD, ENDSTATEMENT, WARNINGDIALOGS.

Parameters: DB, SQL, GDBFILE, OUTFILE, COLUMNS, ISAVE.

Method

The SQL query is sent to the ODBCLOAD.DLL library which runs the query and saves the results in a temporary GSH file. This is then loaded using the SPLOAD directive.

Action with RESTRICT

Restrictions are not applicable to any of the parameters.