Loads data from an ODBC database, PC Windows only (D.B. Baird).
|What information to print (
||Whether to form a Genstat command file or spreadsheet file as output (
||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 (
||Whether to read the column names from the first row of data, or to use default column names (
||Ending statement to use in a GEN output file (
||Whether dialogs giving ODBC error and warning messages are presented (
||Driver version (either 32 or 64) to use for the 64-bit version of Genstat; default 64|
||Path for the folder containing the executable program (
||Number of rows to fetch per driver transaction; default 40|
||Database connection string|
||SQL Query string to run against the ODBC database|
||Name of GDB file to be used in reading from ODBC database|
||Output file to be created; if this is not provided a temporary file will be created, and then deleted if the data is loaded|
||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
||Name of a pointer to save the column identifiers|
||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
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
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
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).
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.
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
Restrictions are not applicable to any of the parameters.
Commands for: Input and output.