1. Home
  2. DBIMPORT procedure

DBIMPORT procedure

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


PRINT = string token What information to print (catalogue); default cata
OUTTYPE = string token Whether to form a Genstat command file or spreadsheet file as output (GEN, GSH, GWB); default GWB
METHOD = string token Whether to load data into the Genstat server after creating the file, or merely to create the file, or to run a command with no output (create, load, command); default load
IMETHOD = string token Whether to read the column names from the first row of data, or to use default column names (read, supply, none, default); default read
ENDSTATEMENT = string token Ending statement to use in a GEN output file (RETURN, ENDBREAK); default RETURN
WARNINGDIALOGS = string token Whether dialogs giving ODBC error and warning messages are presented (display, omit); default disp
DRIVER = scalar Driver version (either 32 or 64) to use for the 64-bit version of Genstat; default 64
ODBCPATH = text Path for the folder containing the executable program (Odbcload.exe) used by the 64-bit version of Genstat to load the data when DRIVER=32; default is the folder containing the Genstat executable program
NROWSFETCH = scalar Number of rows to fetch per driver transaction; default 40


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
NROWSALLOCATE = scalars Specifies how many rows to allow space for, in the initial allocation of memory, before the data are read; default 1000


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.

The NROWSFETCH option allows you to specify the number of rows to fetch in each driver transaction (default 40). Fetching several at once saves time, but requires more memory. You can also save time by using the NROWSALLOCATE parameter to pre-allocate space in memory. Currently memory for 1000 rows is allocated initially, and this extended by 1000 rows whenever it is exhausted. Setting ROWSALLOCATED make this more efficient and more likely to succeed when the transfer uses more than half the available RAM (as the extension is then likely to fail).

The ODBCPATH option specifies the path for the folder containing the executable program (Odbcload.exe) used by the 64-bit version of Genstat to export the data when option DRIVER=32. In the 16th Edition, the executable should already be installed the folder containing the Genstat executable program, which is the default setting. So this option should not need to be set. There is more information about using 32-bit ODBC drivers with 64-bit Genstat on the VSN website www.vsni.co.uk.

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




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

Action with RESTRICT

Restrictions are not applicable to any of the parameters.

See also


Commands for: Input and output.

Updated on June 20, 2019

Was this article helpful?