Connecting to External Databases

Several transformers allow you to attach data to features from an external database.

Joiner

The DatabaseJoiner transformer allows you to query a database to retrieve attributes associated with a feature. One or more feature attributes are used as keys to join to one or more columns in the database, and the values from the matching database table row (or rows) are added as feature attributes. 1 to 0 or 1, 1 to 1, and 1 to many relationships are supported.

The external database can be an Access MDB file, an ODBC connection, MySQL, PostGRES, PostGIS, Oracle 7, 8, 8i, or 9i, a dBase III (dbf) file, or a CSV file.

A wizard is used to set the database type, and connection parameters, and key field properties. If a 1 to many relationship is defined, a list name must be specified to hold the attribute data retrieved from the database. Each row retrieved is another entry in the list. Subsequent transformers can perform operations on the resulting list.

An optional prefix can be added to all the attributes retrieved from the database to allow repeated joins to the same database table to store their results into unique attributes.

SQLExecutor

The SQLExecutor transformer executes an arbitrary SQL statement against a database. If the SQL statement resulted in a row (or rows) being returned, the attributes from the row are added to the feature. This transformer can also be used in conjunction with a Creator transformer to perform database operations such as DROPping or CREATEing tables at the beginning or end of a translation.

This transformer operates against MySQL, PostGRES, PostGIS, ODBC connections, or directly on Oracle databases.

See Also