Updating the database

Because EasyCatalog understands when the content of individual fields needs to be updated, the ODBC Data Provider supports updating of the database at a field level.

Each field has an associated SQL statement which is used to update new content to the database. This functionality is optional, and is configured using EasyCatalog’s field Options dialog.

Field options dialog

SQL statements must be configured for each field that will be updated on the database. Keywords can be included in the statement which are replaced with field content, key field value, etc.

Only fields with the Update Using statement check-box set will be updated to the database, regardless of whether the update statement has been entered.

Update using statement

The ODBC Data Provider substitutes keywords contained in the statement immediately prior to execution to construct an SQL statement. These keywords are:

Keyword

Value

{{VALUE}}

Replaced with the current value of the field

{{KEY}}

Replaced with the unique key for the record

{{FIELDNAME}}

The content of other fields for this record can be referenced by including the field name in upper case. e.g. {{PART_NO}}

The Use of Quotes within the statement

When building your ‘update’ statements, it is important to ensure that quotes are used to enclose alphanumeric data.

Typically, around table and column names double quotes (") should be used. Field content should be enclosed in single quotes ('):

update "Stock" set "myfield" = '{{VALUE}}' where "key" = '{{KEY}}'

In the above SQL statement, Stock, myfield and key are table and column names; VALUE and KEY are field content. The usage shown here is typical, and depends on the type of database being connected to.