Reader Directives

The suffixes listed are prefixed by the current <ReaderKeyword> in a mapping file. By default, the <ReaderKeyword> for the MySQL reader is MYSQL_IN.

DATASET

Required/Optional: Required

This specifies the name of the MySQL database. The database must exist in the DBMS. This can be verified by executing the query SHOW DATABASES in the MySQL query interpreter.

Example:

MYSQL_DATASET testdb

Workbench Parameter: Source MySQL Spatial Dataset

HOST

Required/Optional: Required

This specifies the machine running the MySQL DBMS as either an IP address or host name. The database must have proper permissions and be set up to accept TCP/IP connections if connecting from a remote machine.

MYSQL_IN_HOST myserver

Workbench Parameter: Host

PORT

Required/Optional: Required

When connecting remotely, this specifies the TCP/IP port on which to connect to the DBMS service. The default port is 3306.

MYSQL_IN_PORT 3306

Workbench Parameter: Port

USER_NAME

Required/Optional: Required

The name of user who will access the database. The named user must exist with appropriate MySQL permissions. The default user name is mysql.

MYSQL_IN_USER_NAME mysql

Workbench Parameter: User Name

PASSWORD

Required/Optional: Optional

The password of the user accessing the database. This parameter is optional when connecting with a username that has a blank password associated with it.

MYSQL_IN_PASSWORD secret

Workbench Parameter: Password

DEF

Required/Optional: Required

The syntax of the definition is:

MYSQL_DEF <tableName> \
	[mysql_where_clause <whereClause>] \
[<fieldName> <fieldType>] +

OR

MYSQL <queryName> \
	[mysql_sql_statement <sqlQuery>]						

The tableName> must match the name of an existing MySQL table in the database. This will be used as the feature type of all the features read from the table. The exception to this rule is when using the sql_statement keyword. In this case, the DEF name may be any valid alphabetic identifier; it does not have to be an existing table name – rather, it is an identifier for the custom SQL query. The feature type of all the features returned from the SQL query are given the query name.

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 exception to this is the geometry field type which is not placed in the DEF.

The definition allows specification of separate search parameters for each table. If any of the per table configuration parameters are given, they will override, for that table, whatever global values have been specified by the reader keywords listed above. If any of these parameters is not specified, the global values will be used.

The following table summarizes the definition line configuration parameters:

Parameter

Contents

where_clause

This specifies the SQL WHERE clause applied to the attributes of the layer’s features to limit the set of features returned. If this is not specified, then all the tuples are returned. This keyword will be ignored if the sql_statement is present.

sql_statement

This specifies an SQL SELECT query to be used as the source for the results. If this is specified, the MySQL reader will execute the query, and use the resulting rows as the features instead of reading from the table <queryName>. All returned features will have a feature type of <queryName>, and attributes for all columns selected by the query.

The where_clause and all parameters which specify a spatial constraint are ignored if sql_statement is supplied.

If selecting a geometry column from a MySQL table, the MySQL function AsBinary(<geometry column name>) should be used to return the geometry in binary format. This is necessary for performance since the MySQL reader does not parse the WKT geometry strings but instead takes WKB strings and converts them to FME geometries.

If no <whereClause> is specified, all rows in the table will be read and returned as individual features. 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.

The MySQL reader allows one to use the sql_statement parameter to specify an arbitrary SQL SELECT query on the DEF line. If this is specified, FME will execute the query, and use each row of data returned from the query to define at least one feature. Each of these features will be given the feature type named in the DEF line, and will contain attributes for every column returned by the SELECT. In this case, all DEF line parameters regarding a WHERE clause or spatial querying are ignored, as it is possible to embed this information directly in the text of the <sqlQuery>.

The following example selects rows from the table ROADS, placing the resulting data into FME features with a feature type of MYROADS. Imagine that ROADS defines the geometry for the roads, and has a numeric field named ID, a text field named NAME and a geometry column named GEOM.

MYSQL_DEF MYROADS \
	sql_statement ‘SELECT id, name, AsBinary(geom) FROM ROADS’

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:

MYSQL_IDs <featureType1> \
	<featureType2> \
	<featureTypeN> 

The feature types must match those used in DEF lines.

The example below selects only the ROADS table for input during a translation:

MYSQL_IDs ROADS

MINX, MINY, MAXX, MAXY

Required/Optional: Optional

These directives when used together specify the spatial extent of the feature retrieval.

Only features that interact with the bounding box defined by these keyword values are returned. If this is not supplied, all features will be returned. If either min value is greater than the corresponding max value, the values will be swapped. If less than the entire set of four values are supplied, the supplied values will be ignored and all features will be returned.

Note: Not supported in MySQL_DB.

The syntax of the directives is:

MYSQL_IN_MINX <minX>
MYSQL_IN_MINY <minY>
MYSQL_IN_MAXX <maxX>
MYSQL_IN_MAXY <maxY>

The example below selects a small area for extraction:

MYSQL_IN_MINX 25.6
MYSQL_IN_MINY 59.0
MYSQL_IN_MAXX 79.2
MYSQL_IN_MAXY 124.5

Workbench Parameter: Minimum X, Minimum Y, Maximum X, Maximum Y

FETCH_ALL_FEATURES

Required/Optional: Optional

This keyword is used to specify that the entire result set of the main feature query should be retrieved into a large buffer in client memory. Otherwise, the default is to be less memory intensive and to retrieve one row at a time from the server, caching them in a smaller buffer. This allows for large datasets to be processed by default without the possibility of running out of client memory. This keyword can be set to YES to improve the performance of smaller queries.

Workbench Parameter: Fetch All Features

RETRIEVE_ALL_SCHEMAS

This parameter is applicable only when generating a mapping file, generating a workspace, or when retrieving schemas in an FME Objects application.

When set to yes, schemas for all of the tables and feature classes in the database are returned.

When set to no (or missing), and if RETRIEVE_ALL_TABLE_NAMES is similarly set to no (or missing), only schemas requested by the IDs parameter are returned.

Required/Optional

Optional

Values

YES | NO (default)

NO: The reader will return the schemas for the feature types specified in the IDs. If no features are specified in IDs, then:

  • for Enterprise Geodatabases, FME will not return any schema features;
  • for Personal and File Geodatabases, FME returns the schema features for all the tables.

If this value is not specified, then it is assumed to be No.

YES: Indicates to the reader to return all the schemas of the tables in the database.

Mapping File Syntax

Not applicable.

FME Objects applications would include RETRIEVE_ALL_SCHEMAS followed by “YES” in the parameters array passed to IFMEUniversalReader::open().

Workbench Parameter

Not applicable

RETRIEVE_ALL_TABLE_NAMES

This parameter is only applicable when generating a mapping file, generating a workspace or when retrieving schemas in an FME Objects application.

When set to yes, and if RETRIEVE_ALL_SCHEMAS is set to no (or missing), names for all of the tables and feature classes in the database are returned. When set to no (or missing), and if RETRIEVE_ALL_SCHEMAS is similarly set to no (or missing), the schemas requested by the IDs directive are returned.

Note: If RETRIEVE_ALL_SCHEMAS is also set to Yes, then RETRIEVE_ALL_SCHEMAS takes precedence.

Required/Optional

Optional

Values

YES | NO (default)

Mapping File Syntax

Not applicable.

FME Objects applications would include RETRIEVE_ALL_TABLE_NAMES followed by “YES” in the parameters array passed to IFMEUniversalReader::open().

Workbench Parameter

Not applicable (used when you browse a Table List)

SEARCH_ENVELOPE

Using the minimum and maximum x and y parameters, define a bounding box that will be used to filter the input features. Only features that interact with the bounding box are returned.

If all four coordinates of the search envelope are specified as 0, the search envelope will be disabled.

Mapping File Syntax

<ReaderKeyword>_SEARCH_ENVELOPE <minX> <minY> <maxX> <maxY>

Note: If all four coordinates of the search envelope are specified as zero, the search envelope will be disabled.

Required/Optional

Optional

Workbench Parameter

Minimum X, Minimum Y, Maximum X, Maximum Y

SEARCH_ENVELOPE_COORDINATE_SYSTEM

This directive specifies the coordinate system of the search envelope if it is different than the coordinate system of the data.

The COORDINATE_SYSTEM directive, which specifies the coordinate system associated with the data to be read, must always be set if the SEARCH_ENVELOPE_COORDINATE_SYSTEM directive is set.

If this directive is set, the minimum and maximum points of the search envelope are reprojected from the SEARCH_ENVELOPE_COORDINATE_SYSTEM to the reader COORDINATE_SYSTEM prior to applying the envelope.

Required/Optional

Optional

Mapping File Syntax

<ReaderKeyword>_SEARCH_ENVELOPE_COORDINATE_SYSTEM <coordinate system>

Workbench Parameter

Search Envelope Coordinate System

CLIP_TO_ENVELOPE

This directive specifies whether or not FME should clip features to the envelope specified in the SEARCH_ENVELOPE directive.

Values

YES | NO (default)

Mapping File Syntax

<ReaderKeyword>_CLIP_TO_ENVELOPE [yes | no]

Workbench Parameter

Clip To Envelope

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