Writer Directives

The suffixes shown are prefixed by the current <WriterKeyword> in a mapping file. By default, the <WriterKeyword> for the MySQL writer is MYSQL_OUT.

DATASET/DATABASE, HOST, PORT, USER_NAME, PASSWORD

These directives operate in the same manner as they do for the MySQL 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 definition statement is:

MYSQL_DEF <tableName> \
     [mysql_type <mysql_type>] \
     [mysql_mode (inherit_from_writer|insert|update|delete)] \
     [mysql_table_type <mysql_table_type>] \
     [mysql_geometry_column <column>] \
   		[mysql_create_spatial_index (yes|no)] \								
		[mysql_drop_table (yes|no)] \								
		[mysql_truncate_table (yes|no)]	\
           [mysql_ops_per_sql_stmt]	\								
[<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 geometric entity stored within the feature. The valid values for the type are listed below: mysql_point mysql_line mysql_area mysql_geoemtrycollection 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 where as 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_geometry_ column	This specifies the name of the column to be created that will hold the geometry when creating a new MySQL table. If this value is set to blank no geometry column will be created and an attribute only table will be written. This may invalidate other geometry related parameters. Not supported in MySQL_DB. Default: geom
mysql_create_spatial_index	This specifies that a spatial index be created on the geometry column named in the above parameter. If no geometry column exists, this parameter is ignored. If this keyword is set, the geometry column will be designated as NOT NULL which is required for spatial index creation, and attempts to insert null geometries will result in those features not being added to the table. If you want a table that contains a geometry column that may contain null geometries, simply set this option to NO and the geometry column will not be specified as NOT NULL and null geometries may be inserted. However, this also means that no spatial index will be created. It is recommended that the spatial and nonspatial data be separated into distinct tables and efforts made to ensure the geometries to be inserted are NOT NULL where possible to improve the overall spatial performance of the database. Not supported in MySQL_DB. Default: YES
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: int smallint tinyint mediumint bigint decimal(width, precision) float(width, precision) double(width, precision) char(width) varchar(width) date time datetime timestamp year tinyblob blob mediumblob longblob tinytext text mediumtext longtext
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 writer module whether or not whitespace should be stripped from field values.

If the MYSQL_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_OUT_STRIP_WHITESPACE YES

Workbench Parameter: Strip Whitespace From Field Values

START_TRANSACTION

Required/Optional: Optional

This statement tells the MySQL writer module when to start actually writing features into the database. The MySQL 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_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_OUT_TRANSACTION_INTERVAL statement is not specified, then a value of 1000 is used as the transaction interval.

Example:

MYSQL_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 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.

If the MYSQL_WRITER_MODE statement is not specified, then a value of INSERT is given.

Example:

MYSQL_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 or writer (depending on where the SQL statement is executed) 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 or writer (depending on where the SQL statement is executed) with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.

Required/Optional

Optional

Workbench Parameter

End SQL