Update data in an ODBC database table using Genstat data, PC Windows only (D.B. Baird).
||Type of update on table (
||What to do with unmatched columns (
||Which rows to omit from the data for
||What to do when any non-fatal errors occur, (
||If any errors occur, pop up warning dialogs (
||Name of existing Genstat ODBC Update link file (
||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 (
||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|
||Database connection string specifying the ODBC database to connect to|
||Name of the table in the ODBC database (if
||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|
||Column numbers or names of the subset of data columns (only if a pointer is used for the
||Numbers of the columns in the table to be matched with the column in the table (the names are provided by
||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|
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 (
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 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
WITH (for matching with
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
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.
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.
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.
DBEXPORT replaces the procedure
%ODBCUPDATE from earlier editions of Genstat.)
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.
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.
Commands for: Input and output.