The directives that are processed by the Pervasive PSQL Writer are listed below. The suffixes shown are prefixed by the current <WriterKeyword> in a mapping file. By default, the <WriterKeyword> for the Pervasive PSQL Writer is PERVASIVE.
DATASET
Required/Optional: Required
This is the ODBC data source name.
Example
PERVASIVE_DATASET cityDestination
Workbench Parameter: Destination Pervasive PSQL DSN
DEF
Required/Optional: Required
Each Pervasive PSQL table must be defined before it can be written. The general form of a Pervasive PSQL definition statement is:
PERVASIVE_DEF <tableName> \ [pervasive_update_key_columns <keyColumns>] \ [pervasive_drop_table (yes|no)] \ [pervasive_truncate_table (yes|no)] \ [pervasive_table_writer_mode (inherit_from_writer| insert|update|delete)] \ [<fieldName> <fieldType>[,<indexType]]]+
When the destination table does not exist, the table definition specifies the table that will be created. When the destination table exists and is not being dropped, the fields and types listed should match those of the existing table.
Fields in the table definition that do not match any existing columns will be ignored by the writer. Where <fieldType> is specified, it should be a 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 the pervasive_drop_table DEF line parameter is set to YES, or it will be truncated if the pervasive_truncate_table DEF line parameter 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 20 characters in length. |
|
pervasive_table_ |
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 |
|
pervasive_update_key_ |
This is a comma-separated list of the columns which are matched against the corresponding FME attributes’ values to specify which rows are to be updated or deleted when the writer mode is either UPDATE or INSERT. For example: pervasive_update_key_columns ID would instruct the writer to ensure that the FME attribute is always matched against the column with the same name. Also, the target table is always the feature type specified in the DEF line. Each column listed with the pervasive_update_key_columns directive must be defined with a type on the DEF line, in addition to the columns whose values will be updated by the operation. |
|
pervasive_drop_table |
This specifies that if the table exists by this name, it should be dropped and replaced with a table specified by this definition. Default: NO |
|
pervasive_truncate_ |
This specifies that if the table exists by this name, it should be cleared prior to writing. Default: NO |
|
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 20 characters in length. |
|
fieldType |
The type of a column in a table. The valid values for the field type are listed below: bit longvarchar longvarbinary char(width) binary(width) smallint tinyint usmallint utinyint smallidentity integer bigint uint ubigint identity real double currency timestamp date time decimal(width,decimal) numeric(width,decimal) varchar(width) |
|
indexType |
The type of index to create for the column. If the table does not previously exist, then upon table creation, a database index of the specified type is created. The database index contains only the one column. The valid values for the column type are listed below: - indexed: An index without constraints. - unique: An index with a unique constraint. |
START_TRANSACTION
Required/Optional: Optional
This statement tells the Pervasive PSQL writer module when to start actually writing features into the database. The Pervasive PSQL writer does not write any features until the feature is reached that belongs to <last successful transaction> + 1. Specifying a value of zero causes every feature to be output. Normally, the value specified is zero – a non-zero value is only specified when a data load operation is being resumed after failing partway through.
|
Parameter |
Contents |
|
<last successful transaction> |
The transaction number of the last successful transaction. When loading data for the first time, set this value to 0. |
Example:
PERVASIVE_START_TRANSACTION 0
Workbench Parameter: Start transaction at
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.
If the PERVASIVE_TRANSACTION_INTERVAL statement is not specified, then a value of 1000 is used as the transaction interval.
|
Parameter |
Contents |
|
<transaction_interval> |
The number of features in a single transaction. |
If the PERVASIVE_TRANSACTION_INTERVAL is set to zero, then feature based transactions are used. As each feature is processed by the writer, they are checked for an attribute called fme_db_transaction. The value of this attribute specifies whether the writer should commit or rollback the current transaction. The value of the attribute can be one of COMMIT_BEFORE, COMMIT_AFTER, ROLLBACK_AFTER or IGNORE. If the fme_db_transaction attribute is not set in any features, then the entire write operation occurs in a single transaction.
Example:
PERVASIVE_TRANSACTION_INTERVAL 5000
Workbench Parameter: Transaction interval
WRITER_MODE
Required/Optional: Optional
Note: For more information on this directive, see the chapter Database Writer Mode.
This directive informs the Pervasive PSQL 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 pervasive_table_writer_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 PERVASIVE_WRITER_MODE statement is not specified, then a value of INSERT is given.
|
Parameter |
Contents |
|
<writer_mode> |
The type of SQL operation that should be performed by the writer. The valid list of values are below: INSERT UPDATE DELETE Default: INSERT |
Example:
PERVASIVE_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