Update data in an ODBC database table using Genstat data, PC Windows only (D.B. Baird).
Options
METHOD = string token |
Type of update on table (create, insert, merge); default crea |
|---|---|
ROWMERGEMETHOD = string token |
For METHOD=merge, what action to take when rows do not match any in the existing table (none, matched, all); default all |
COLMERGEMETHOD = string token |
What to do with unmatched columns (add, omit); default add |
OMIT = string token |
Which rows to omit from the data for METHOD settings other than merge (none, restricted); default rest |
ERRORACTION = string token |
What to do when any non-fatal errors occur, (continue, stop); default stop |
WARNINGDIALOGS = string token |
If any errors occur, pop up warning dialogs (display, omit); default disp |
GLKFILE = text |
Name of existing Genstat ODBC Update link file (*.GLK) to use |
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 export the data when DRIVER=32; default is the folder containing the Genstat executable program |
Parameters
DATA = pointer or text |
Pointer to a compatible set of data structures to add to the table or text with a name of an existing Genstat spreadsheet file containing data to be added |
|---|---|
DB = text |
Database connection string specifying the ODBC database to connect to |
TABLENAME = text |
Name of the table in the ODBC database (if METHOD is set to insert or merge, then this must already exist in the database) |
COLUMNNAMES = text |
Names of the columns in the table to be updated; if this is not provided, it will be assumed that the columns in the table have the same names as the Genstat data structures |
SUBSET = variate or text |
Column numbers or names of the subset of data columns (only if a pointer is used for the DATA parameter) to be added to the table; if SUBSET is not set, all columns are added to the table |
MATCH = variate |
Numbers of the columns in the table to be matched with the column in the table (the names are provided by WITH) |
WITH = text |
Names of the columns in the table to be matched with the Column; if this not provided, it is assumed that these columns have the same names as those of the Genstat data structures |
Description
DBEXPORT can be used to add either a new table to an ODBC data source (METHOD=create), add rows to an existing table (METHOD=insert), or update rows in an existing table (METHOD=merge).
The form of the DB connection string can be found by saving a ODBC Query in the Genstat client in a GDB file (using the Spread > New > ODBC Query menu in Genstat for Windows) and then examining this file with a text editor. The second line contains the database connection string.
The data to be sent can either be specified as a pointer to a set of structures in Genstat or a text giving a Genstat spreadsheet (GSH) file. The DATA parameter need not be set if a GLKFILE is specified, as this may point to an existing GSH file. If a GLKFILE is provided, all options and parameters will be taken from this, with the exception that a different DATA set and/or TABLENAME can be provided and this will be used with the existing parameters from the GLKFILE. A GLKFILE can be created using the Spread > Export menu items and using the Save Export Link option in these menus.
The column names within the ODBC table are assumed to be the same as the Genstat identifiers, unless you specify COLUMNNAMES and WITH (for matching with MATCH).
If COLMERGEMETHOD=omit, any columns in the data not found in the database table will be omitted; otherwise new columns will be added to the existing table. The SUBSET parameter can be set to pick a subset of columns from an existing GSH file. However if DATA is set to a pointer, it would be normal to only form this to contain only the elements that you wanted updated in the table, instead of using the SUBSET parameter.
If METHOD=merge, the MATCH parameter must be set. At most only five columns can be matched. The WITH parameter may be set if the columns in the table do not have the same names as the structures used in the DATA parameter. The ROWMERGEMETHOD option controls how unmatched rows are handled in a merge: the setting none does not add unmatched rows, the setting matched only adds a row if another with the same matching criteria already existing in the table, and all adds in all unmatched rows into the table.
If the WARNINGDIALOGS option is set to display, message boxes will pop up on the windows desktop detailing any errors; the setting omit suppresses the warning messages. The Genstat server will wait until the user clicks OK on these, so this will halt any processing, and is better not used in batch jobs. If option ERRORACTION=stop, any warnings (such as not being able to add missing values into a column or not being able to add rows with duplicate ID’s) will cause the update to stop; otherwise all valid data will be added to the table, unless a fatal error occurs.
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: DBEXPORT replaces the procedure %ODBCUPDATE from earlier editions of Genstat.)
Options: METHOD, ROWMERGEMETHOD, COLMERGEMETHOD, OMIT, ERRORACTION, WARNINGDIALOGS, GLKFILE, DRIVER, ODBCPATH.
Parameters: DATA, DB, TABLENAME, COLUMNNAMES, SUBSET, MATCH, WITH.
Method
The structures in DATA are saved to a GSH file using FSPREADSHEET. A GLK file is built using the supplied parameters or an existing GLK file, and then this is passed to the ODBCLOAD.DLL library to be processed.
Action with RESTRICT
Restrictions on the structures are obeyed if OMIT=restricted, otherwise they are ignored. If the restrictions on the structures are not consistent, a fault will occur.
See also
Procedure: DBCOMMAND, DBIMPORT, DBINFORMATION, EXPORT.
Commands for: Input and output.