The suffixes shown are prefixed by the current <WriterKeyword> in a mapping file. By default, the <WriterKeyword> for the MySQL (Attributes Only) writer is MYSQL_DB_OUT.
DATASET/DATABASE, HOST, PORT, USER_NAME, PASSWORD
These directives operate in the same manner as they do for the MySQL (Attributes Only) reader. The remaining writer-specific directives are discussed in the following sections.
DEF
Required/Optional: Optional
Each MySQL table must be defined before it can be written. The general form of a MySQL (Attributes Only) definition statement is:
MYSQL_DB_DEF <tableName> \ [mysql_type <mysql_type>] \ [mysql_mode (inherit_from_writer|insert|update|delete)] \ [mysql_table_type <mysql_table_type>] \ [mysql_drop_table (yes|no)] \ [mysql_truncate_table (yes|no)] \ [<fieldName> <fieldType>][,<indexType>]*
The table definition allows control of the table that will be created. If the table already exists, the majority of the mysql_ parameters will be ignored and need not be given. If the fields and types are listed, they must match those in the database.
If the table does not exist, then the field names and types are used to first create the table. In any case, if a <fieldType> is given, it may be any field type supported by the target database.
The configuration parameters present on the definition line are described in the following table:
|
Parameter |
Contents |
|
tableName |
The name of the table to be written. If a table with the specified name exists, it will be overwritten if either the mysql_overwrite_table DEF line parameter set to YES or if the global writer keyword type mysql_out_overwrite is set to YES. Otherwise the table will be appended. Valid values for table names include any character string devoid of SQL-offensive characters and less than 32 characters in length. |
|
mysql_type |
The type of entity stored within the feature. This should always be set to the following: mysql_none |
|
mysql_mode |
The the default operation mode of the feature type in terms of the types of SQL statements sent to the database. Valid values are INSERT, UPDATE, DELETE and INHERIT_FROM_WRITER. Note that INSERT mode allows for only INSERT operations whereas UPDATE and DELETE can be overwritten at the feature levels. INHERIT_FROM_WRITER simply indicates to take this value from the writer level and not to override it at the feature type level. Default:INHERIT_FROM_WRITER |
|
mysql_table_type |
The type of table to be created. The valid values for the type are listed below: MEMORY InnoDB MYISAM Please refer to the MySQL manual for further information on these table types. Default: MyISAM |
|
mysql_drop_table |
This specifies that if the table exists by this name, it should be dropped and recreated before any features are written to it. This parameter, along with mysql_truncate_table, deprecates the older mysql_overwrite_table parameter. Default: NO |
|
mysql_truncate_table |
This specifies that if the table exists by this name, it should be truncated before any features are written to it. This parameter, along with mysql_drop_table, deprecates the older mysql_overwrite_table parameter. Default: NO |
| mysql_ops_per_sql_stmt |
If set to 1, insert data will be bound (packed efficiently, with statement reuse), and sent every feature. Any value N > 1 will cause inserts to be batched without statement reuse and executed every N statements. In general, N=1 will result in fewer bytes transmitted over the network, but more round-trips than a higher N. Experimentation may be required to determine which value will offer the best performance for a given scenario. This parameter is ignored for UPDATE and DELETE mode, where statements are always executed singly. |
|
fieldName |
The name of the field to be written. Valid values for field name include any character string devoid of SQL-offensive characters and less than 63 characters in length. |
|
fieldType |
The type of a column in a table. The valid values for the field type are listed below:
|
|
indexType |
The type of index to create on the given field. The valid values for the index type are listed below: BTREE (default attribute index) PRIKEY (primary key) |
STRIP_WHITESPACE
Required/Optional: Optional
This statement tells the MySQL (Attributes Only) writer module whether or not whitespace should be stripped from field values.
If the MYSQL_DB_OUT_STRIP_WHITESPACE statement is not specified or is set to YES, both leading and trailing whitespace will be stripped from field values.
Example:
MYSQL_DB_OUT_STRIP_WHITESPACE YES
Workbench Parameter: Strip Whitespace From Field Values
START_TRANSACTION
Required/Optional: Optional
This statement tells the MySQL (Attributes Only) writer module when to start actually writing features into the database. The MySQL (Attributes Only) writer does not write any features until the feature number of features are skipped, and then it begins writing the following features. Normally, the value specified is zero – a non-zero value is usually only specified when a data load operation is being resumed after failing partway through.
Example:
MYSQL_DB_OUT_START_TRANSACTION 0
Workbench Parameter: Starting Feature
TRANSACTION_INTERVAL
Required/Optional: Optional
This statement informs FME about the number of features to be placed in each transaction before a transaction is committed to the database. Setting the transaction interval 0 results in the enabling of auto commit transaction mode.
If the MYSQL_DB_OUT_TRANSACTION_INTERVAL statement is not specified, then a value of 1000 is used as the transaction interval.
Example:
MYSQL_DB_OUT_TRANSACTION_INTERVAL 2000
Workbench Parameter: Features Per Transaction
WRITER_MODE
Required/Optional: Optional
Note: For more information on this directive, see the chapter Database Writer Mode.
This directive informs the MySQL (Attributes Only) writer which SQL operations will be performed by default by this writer. This operation can be set to INSERT, UPDATE, or DELETE. The default writer level value for this operation can be overwritten at the feature type or table level. The corresponding feature type def parameter name is called MYSQL_MODE. It has the same valid options as the writer level mode and additionally the value INHERIT_FROM_WRITER which causes the writer level mode to be inherited by the feature type as the default for features contained in that table.
The operation can be set specifically for individual features as well, using the fme_db_operation attribute. Note that when the writer mode is set to INSERT this prevents the mode from being interpreted from individual features and all features are inserted unless otherwise marked as UPDATE or DELETE features. These are skipped.
If the MYSQL_DB_WRITER_MODE statement is not specified, then a value of INSERT is given.
Example:
MYSQL_DB_OUT_WRITER_MODE INSERT
Workbench Parameter: Writer Mode
BEGIN_SQL{n}
Sometimes, you must execute some SQL statements prior to opening a table. For example, it may be necessary to ensure that a view exists before attempting to read from it.
Upon opening a connection to read from a database, the reader looks for the directive <ReaderKeyword>_BEGIN_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.
Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.
For example:
FME_SQL_DELIMITER ; DELETE FROM instructors; DELETE FROM people WHERE LastName='Doe' AND FirstName='John'
Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.
Any errors occurring during the execution of these SQL statements will normally terminate the reader with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.
Required/Optional
Optional
Workbench Parameter
Begin SQL
END_SQL{n}
Sometimes you must execute some SQL statements after a set of tables has been read. For example, it may be necessary to clean up a temporary view after creating it in BEGIN_SQL.
Just before closing a connection on a database, the reader looks for the directive <ReaderKeyword>_END_SQL{n} (for n=0,1,2,...), and executes each such directive’s value as an SQL statement on the database connection.
Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL block into SQL statements, which will then be sent to the database for execution. Note: Include a space before the character.
For example:
FME_SQL_DELIMITER ; DELETE FROM instructors; DELETE FROM people WHERE LastName='Doe' AND FirstName='John'
Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.
Any errors occurring during the execution of these SQL statements will normally terminate the reader with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.
Required/Optional
Optional
Workbench Parameter
End SQL
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