DBEXPORT procedure

Update data in an ODBC database table using GenStat data, PC Windows only (D.B. Baird).

Options

METHOD = string

Type of update on table (create, insert, merge); default crea

ROWMERGEMETHOD = string

For METHOD=merge, what action to take when rows do not match any in the existing table (none, matched, all); default all

COLMERGEMETHOD = string

What to do with unmatched columns (add, omit); default add

OMIT = string

Which rows to omit from the data for METHOD settings other than merge (none, restricted); default rest

ERRORACTION = string

What to do when any non-fatal errors occur, (continue, stop); default stop

WARNINGDIALOGS = string

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

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.

(Note: DBEXPORT replaces the procedure %ODBCUPDATE from earlier editions of GenStat.)

Options: METHOD, ROWMERGEMETHOD, COLMERGEMETHOD, OMIT, ERRORACTION, WARNINGDIALOGS, GLKFILE.

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.