Reader Directives

The suffixes listed are prefixed by the current <ReaderKeyword> in a mapping file. By default, the <ReaderKeyword> for the Microsoft SQL Server Spatial reader is MSSQL_SPATIAL.

SERVER

Required/Optional

Required

The host name of the Microsoft SQL Server.

MSSQL_SPATIAL_SERVER mi6

Workbench Parameter: Server

For SQL Azure, when in Bulk Insert mode (the default for the WRITER ONLY), special syntax is required. Please see the Bulk Insert section for further details.

USE_SSPI

Required/Optional: Optional

This specifies whether Windows Authentication should be used to authenticate to the database server. This directive should be set to either yes or no. The default is no.

If USE_SSPI is set to yes, then Windows Authentication is used, and the USER_NAME and PASSWORD directives are ignored.

MSSQL_ADO_USE_SSPI yes

Workbench Parameter: Use Windows Authentication

ENCRYPT_CONNECTION

Required/Optional

Optional

Mapping File Syntax

MSSQL_ADO_ENCRYPT_CONNECTION yes

Workbench Parameter

Encrypt Connection

PASSWORD

Required/Optional: Optional

The password of the user accessing the database. If Windows Authentication is being used, this is ignored.

MSSQL_SPATIAL_PASSWORD moneypenny

Workbench Parameter: Password

COMMAND_TIMEOUT

The timeout in seconds for a query to the database. If set to zero, there is no timeout. The default is 30.

Required/Optional

Optional

Values

0 = no timeout

Default: 30

Mapping File Syntax

MSSQL_SPATIAL_COMMAND_TIMEOUT 15

Workbench Parameter

Command Timeout

DATASET

Required/Optional: Required

This is the database name.

Example:

MSSQL_SPATIAL_DATASET citySource

Workbench Parameter: Source Microsoft SQL Server Spatial 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 mssql_where_clause or mssql_sql_statement specified in its DEF line, the global <ReaderKeyword>_WHERE_CLAUSE 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 mssql_where_clause or mssql_sql_statement, it will override the global WHERE clause.

The syntax for this clause is:

MSSQL_SPATIAL_WHERE_CLAUSE <whereClause>

Note that the <whereClause> does not include the word “WHERE.”

The example below selects only the features whose lengths are more than 2000:

MSSQL_SPATIAL_WHERE_CLAUSE LENGTH > 2000

Workbench Parameter

Where Clause

IDs

Required/Optional: Optional

This optional specification is used to limit the available and defined database tables that will be read. If no IDs are specified, then no tables are read. The syntax of the IDs keyword is:

MSSQL_SPATIAL_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:

MSSQL_SPATIAL_IDs HISTORY

DEF

Required/Optional: Optional

The syntax of the definition is:

MSSQL_SPATIAL_DEF <tableName> \
	[(mssql_geom_column <geometry column name>) \
	 |(mssql_geog_column <geography column name>)] \
	[mssql_where_clause <whereClause>] \
	[<fieldName> <fieldType>] +

or

MSSQL_SPATIAL_DEF <queryName> \
	[(mssql_geom_column <geometry column name>) \
	 |(mssql_geog_column <geography column name>)]	\
	[mssql_sql_statement <sqlQuery>] \
	[<fieldName> <fieldType>] +

The <tableName> must match the name of an existing Microsoft SQL Server 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 mssql_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 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 such as the WHERE_CLAUSE. 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

mssql_geom_column

This specifies the column FME will read geometry from. If the selected table has exactly one spatial column, and neither the mssql_geom_column or mssql_geog_column keywords are used, FME will determine the spatial column automatically. Only one of mssql_geom_column and mssql_geog_column may be specified on each DEF line.

The MSSQL Spatial Reader supports multiple spatial columns per table when used with the HANDLE_MULTIPLE_SPATIAL_COLUMNS directive.

mssql_geog_column

This specifies the column FME will read geography from. If the selected table has exactly one spatial column, and neither the mssql_geom_column or mssql_geog_column keywords are used, FME will determine the spatial column automatically. Only one of mssql_geom_column and mssql_geog_column may be specified on each DEF line.

The MSSQL Spatial Reader supports multiple spatial columns per table when used with the HANDLE_MULTIPLE_SPATIAL_COLUMNS directive.

mssql_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 rows are returned. This keyword will be ignored if the mssql_sql_statement is present.

mssql_sql_statement

This specifies an SQL SELECT query to be used as the source for the results. If this is specified, the Microsoft SQL Server Spatial 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 mssql_where_clause is ignored if mssql_sql_statement is supplied. This form allows the results of complex joins to be returned to FME.

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 Microsoft SQL Server Spatial reader allows one to use the mssql_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 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>.

In the following example, all records whose ID is less than 5 will be read from the supplier table. If the supplier table contains a geometry column, it will be automatically detected and read.

MSSQL_SPATIAL_DEF supplier \
    mssql_where_clause "id < 5" \
    ID  integer \
    NAME char(100) \
    CITY char(50) 

In this example, the results of joining the employee and city tables are returned. All attributes from the two tables will be presented on each returned feature. The feature type will be set to complex. Geometry will be read from the GEOM column in the CITY table.

MSSQL_SPATIAL_DEF complex \
	mssql_geom_column GEOM \
	mssql_sql_statement \
		"SELECT * FROM EMPLOYEE, CITY WHERE EMPLOYEE.CITY = CITY.NAME"

READ_CACHE_SIZE

Required/Optional: Optional

This keyword controls how the reader retrieves rows from the database. This must be a numeric value which must be greater than 0.

The READ_CACHE_SIZE is used to determine the number of rows that are retrieved at one time into local memory from the data source. For example, if the READ_CACHE_SIZE is set to 10, after the reader is opened, the reader will read 10 rows into local memory. As features are processed by the FME, the reader returns the data from the local memory buffer. As soon as you move past the last row available in local memory, the reader will retrieve the next 10 rows from the data source.

This keyword affects the performance of the reader, and will result in significantly degraded performance if incorrectly set. The optimum value of this keyword depends primarily on the characteristics of individual records and the transport between the database and the client machine. It is less affected by the quantity of rows that are to be retrieved.

By default, the READ_CACHE_SIZE is set to 10. This value has been determined to be the optimal value for average datasets.

Workbench Parameter: Number of Records to Fetch at a Time

ASSUME_ONE_SRID_PER_COL

Required/Optional: Optional

SQL Server does not constrain all geometry objects in a column to have the same Spatial Reference ID (SRID). However, it is common practice to use a single SRID within a given column. If this directive is set to “NO”, FME will not assume that each geometry column uses a single SRID when querying the database.

Parameter

Contents

<assume_one_srid_per_col>

“YES” or “NO”

Default: YES

Example:

MSSQL_SPATIAL_ASSUME_ONE_SRID_PER_COL YES

Workbench Parameter: Geometry Columns Have Exactly One SRID

PERSISTENT_CONNECTION

Required/Optional

Optional

If this directive is set to YES, database connections will be left open and reused when possible until FME is shut down.

The syntax of the PERSISTENT_CONNECTION directive is:

<ReaderKeyword>_PERSISTENT_CONNECTION [yes | no]

Workbench Parameter

Make Connection Persistent

USE_MAKE_VALID

By default, USE_MAKE_VALID is set to NO.

Required/Optional

Optional

Workbench Parameter

Use MakeValid with Search Envelope

HANDLE_MULTIPLE_SPATIAL_COLUMNS

If this directive is set to YES, feature geometry will be read into an aggregate. A directive is set on the aggregate to indicate that each part of the aggregate is independent from the others, and its own geometry. Geometry parts of the aggregate are named and contain geometry according to their respective column in the table being read.

When using this feature, neither the geometry/geography column, nor the feature type SELECT statement can be specified.

Required/Optional

Optional

Mapping File Syntax

<ReaderKeyword>_HANDLE_MULTIPLE_SPATIAL_COLUMNS YES

Workbench Parameter

Handle Multiple Spatial Columns

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