The directives that are processed by the ODBC 3.x reader are listed below. The suffixes shown are prefixed by the current <ReaderKeyword> in a mapping file. By default, the <ReaderKeyword> for the ODBC 3.x reader is ODBC2.
DATASET
Required/Optional: Required
Example:
ODBC2_DATASET citySource
Workbench Parameter: Source ODBC 3.x Dataset
USER_NAME
Required/Optional: Optional
The name of user who will access the database. For some database types, this is ignored.
ODBC2_USER_NAME bond007
Workbench Parameter: User ID
PASSWORD
Required/Optional: Optional
The password of the user accessing the database. For some database types, this is ignored.
ODBC2_PASSWORD moneypenny
Workbench Parameter: Password
DEF
Required/Optional
Required
Each database table must be defined before it can be read. The definition may take two forms:
The syntax of the first form is:
ODBC2_DEF <tableName> \ [odbc2_where_clause <whereClause>] \ [odbc2_sql <sql statement>] \ [<fieldName> <fieldType>] +
In this form, the fields and their types are listed. The <fieldType> of each field must be given, but it is not verified against the database definition for the field. In effect, it is ignored.
The <tableName> must match a table in the database. This will be used as the feature type of all the features read from the table.
If no <whereClause> is specified, all rows in the table will be read and returned as individual features, unless limited by a global directive:
<ReaderKeyword>_WHERE_CLAUSE
If a <whereClause> is specified, only those rows that are selected by the clause will be read. Note that the <whereClause> does not include the word “WHERE.”
In this example, the all records whose ID is less than 5 will be read from the supplier table:
ODBC2_DEF supplier \ odbc2_where_clause "id < 5" \ ID integer \ NAME char(100) \ CITY char(50)
The syntax of the second form is:
ODBC2_DEF <tableName> \ odbc2_sql <sqlStatement>
In this form, an arbitrary complete <sqlStatement> will be executed. The statement is passed untouched to the database (and therefore may include non-portable database constructions). The results of the statement will be returned, one row at a time, as features to FME. This form allows the results of complex joins to be returned to FME.
All features will be given the feature type <tableName>, even though they may not necessarily have come from that particular table. Indeed, with this form, the <tableName> need not exist as a separate table in the database.
In this example, the results of joining the employee and city tables are returned. All attributes from the two tables will be present on each returned feature. The feature type will be set to complex.
ODBC2_DEF complex \ odbc2_sql \ "SELECT * FROM EMPLOYEE, CITY WHERE EMPLOYEE.CITY = CITY.NAME"
WHERE_CLAUSE
Required/Optional
Optional
This optional specification is used to limit the rows read by the reader from each table. If a given table has no odbc2_where_clause or odbc2_sql specified in its DEF line, the global <ReaderKeyword>_WHERECLAUSE value, if present, will be applied as the WHERE specifier of the query used to generate the results. If a table’s DEF line does contain its own odbc2_where_clause or odbc2_sql, it will override the global WHERE clause.
The syntax for this clause is:
ODBC2_WHERECLAUSE <whereClause>
Note that the <whereClause> does not include the word “WHERE.”
The example below selects only the features whose lengths are more than 2000:
ODBC2_WHERECLAUSE LENGTH > 2000
Workbench Parameter
Where Clause
IDs
Required/Optional: Optional
This optional specification is used to limit the available and defined database tables files that will be read. If no IDs are specified, then all defined and available tables are read. The syntax of the IDs keyword is:
ODBC2_IDs <featureType1> \ <featureType2> … \ <featureTypeN>
The feature types must match those used in DEF lines.
The example below selects only the HISTORY table for input during a translation:
ODBC2_IDs HISTORY
PERSISTENT_CONNECTION
A user may want to keep a connection to a database for reuse during a particular FME session. For example, when running a batch of 100 mapping files on the same database connection, it may be desirable to keep a connection open and save the processing time required to make and break a database connection.
A database connection will be determined to be the same when the database name, the username, the password, and the transaction interval are the same.
Values: YES | NO
Default value: NO
Example:
ODBC2_PERSISTENT_CONNECTION YES
Workbench Parameter: Persistent Connection
EXPOSED_ATTRS
This directive allows the selection of format attributes to be explicitly added to the reader feature type.
This is similar to exposing format attributes on a reader feature type once it has been generated; however, it is even more powerful because it enables schema-driven applications other than Workbench to access and leverage these attributes as if they were explicitly on the schema as user attributes.
The result of picking a list of attributes is a comma-separated list of attribute names and types that will be added to the schema features. Currently all reader feature types will receive the same set of additional schema attributes for a given instance of the reader.
Required/Optional
Optional
Mapping File Syntax
Not applicable.
While it is possible for FME Objects applications to invoke this directive, the required format is not documented.
This directive is intended for use in our GUI applications (for example, Workbench) only.
Workbench Parameter
Additional Attributes to Expose