Loads data from an ODBC database, PC Windows only (D.B. Baird).
Options
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 |
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 |
NROWSALLOCATE = scalars |
Specifies how many rows to allow space for, in the initial allocation of memory, before the data are read; default 1000 |
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.
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.)
Options: PRINT, OUTTYPE, METHOD, IMETHOD, ENDSTATEMENT, WARNINGDIALOGS, DRIVER, ODBCPATH, NROWSFETCH.
Parameters: DB, SQL, GDBFILE, OUTFILE, COLUMNS, ISAVE. NROWSALLOCATE.
Method
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
Procedure: DBCOMMAND, DBEXPORT, DBINFORMATION, IMPORT.
Commands for: Input and output.