jdbctemplate.xml reference

  • Article

 

Applies to: FAST Search Server 2010

Use a copy of jdbctemplate.xml to configure the settings for the FAST Search database connector.

The FAST Search database connector uses the configuration settings to control content extraction. You can run multiple instances of the database connector at the same time by using different configuration files for each instance.

Customizing jdbctemplate.xml

Note

To modify a configuration file, verify that you meet the following minimum requirements: You are a member of the FASTSearchAdministrators local group on the computer where FAST Search Server 2010 for SharePoint is installed.

To extract content from a JDBC database and feed it to a FAST Search Server 2010 for SharePoint installation, first create a copy of the jdbctemplate.xml file. Edit the copy in a text or XML editor of your choice (except Notepad) to include the elements and settings for your environment.

To test your settings, run:

%FASTSEARCH%\bin\JDBCConnector.bat testconnections -f <configuration file>

where <configuration file> is the path and file name of your configuration, and %FASTSEARCH% is the FAST Search Server 2010 for SharePoint installation folder.

To run the connector and feed content to FAST Search Server 2010 for SharePoint, run:

%FASTSEARCH%\bin\JDBConnector.bat start -f <configuration file>

Parameter groups quick reference

The following table contains a list of the parameter groups in jdbctemplate.xml. These groups can appear in any order.

Parameter group Description

Input

Defines the properties of the content sources.

Transformation

Describes which content is transformed into the FAST Search Server 2010 for SharePoint item model, and how it occurs.

FASTSearchSubmit

Describes the communication properties when submitting content to FAST Search Server 2010 for SharePoint.

XMLExport

Specifies the values for exporting to XML formatted file(s).

ChangeDetection

Specifies how to detect changes in the database since the last database feed by using row checksums.

ConnectorExecution

Specifies general values that are used when you run the connector.

General

Specifies the configuration name.

JDBC template file format

XML elements in jdbctemplate.xml begin with < and end with />. The XML is defined in FastConnectorConfig-1.0.dtd.

The basic format is as follows:

<group name="group_name">

    <parameter name="parameter_name" type="parameter_type">

        <value>value</value>

    </parameter>

</group>

Parameter names are case-sensitive, types are not. Parameter names and types must be enclosed in quotation marks (" ").

A parameter definition can span multiple lines. Spaces, carriage returns, line feeds, and tab characters are ignored in an element definition.

For example:

<group name="ConnectorExecution">
    <parameter name="QueueSize" type="integer">
         <value>200</value>
    </parameter>
    <parameter name="WorkFolder" type="string">
         <value>../var/jdbcconnector</value>
    </parameter>
</group>

Tip

For long parameter definitions, position values on separate lines and use indentation to make the file easier to read.

The FastConfig element is a special case and is required. All other elements are contained within the FastConfig element.

Here is a sample snippet from a configuration file:

<!DOCTYPE FastConfig SYSTEM "dtd/FastConnectorConfig-1.0.dtd">
<FastConfig>
    <group name="ConnectorExecution">
       <parameter name="QueueSize" type="integer">
             <value>200</value>
        </parameter>
       <parameter name="WorkFolder" type="string">
             <value>../var/jdbcconnector</value>
       </parameter>
    </group>
</FastConfig>

You can add comments anywhere, delimited by <!-- and -->.

For example:

<!DOCTYPE FastConfig SYSTEM "dtd/FastConnectorConfig-1.0.dtd"><FastConfig>
    <!-- The parameter group ConnectorExecution specifies general values
          used when running a connector. -->
    <group name="ConnectorExecution">
       <parameter name="QueueSize" type="integer">
             <value>200</value>
       </parameter>
       <parameter name="WorkFolder" type="string">
             <value>../var/jdbcconnector</value>
       </parameter>
    </group>
</FastConfig>

Parameter group: Input

The parameters in this group define the properties of the content sources.

Parameter Type Value Description

JDBCDriver

string

text

JDBC driver class name that is defined by the JDBC driver provider. Required.

Example value: com.microsoft.sqlserver.jdbc.SQLServerDriver. More examples can be found in the jdbctemplate.xml file.

JDBCURL

string

text

JDBC URL, specified by the JDBC driver provider. The URL may vary for different JDBC drivers. Required.

Example value: jdbc:sqlserver://<yourServerHere>:1433;database=<yourDataBaseNameHere>. More examples can be found in the jdbctemplate.xml file.

JDBCPreSQL

type="list" separator=";"

text

Specifies the SQL statements run before the JDBCSQL statement. Use this parameter for any pre-processing that is required on the database.

JDBCSQL

string

text

Specifies the SQL crawl query.You must specify either JDBCSQL or JDBCSQLFile.

If you use time stamp updates, this attribute requires one of the following:

  • %TIMESTAMP%: Last crawl time is specified as a datetime value.

  • %TIMESTAMPSEC%: Last crawl time is specified as the number of seconds since the epoch. (epoch is 1970-01-01T00:00:00Z – January 1, 1970)

Default: (none)

JDBCSQLFile

string

text

Specifies the name of the file, in an absolute path, that contains the SQL crawl query. You must specify either JDBCSQL or JDBCSQLFile.

JDBCDeleteSQL

string

text

Specifies the SQL delete query.

If you use time stamp updates, this attribute requires one of the following:

  • %TIMESTAMP%: Last crawl time is specified as a datetime value.

  • %TIMESTAMPSEC%: Last crawl time is specified as the number of seconds since the epoch. (epoch is 1970-01-01T00:00:00Z – January 1, 1970)

Default: (none)

JDBCDeleteSQLFile

string

text

Specifies the name of the file, in an absolute path, that contains the SQL delete query.

JDBCPostSQL

type="list" separator=";"

text

Specifies the SQL statements that run after the extract statement.

Default: (none)

JDBCTimeStampField

string

text

Specifies the time stamp field to use as the source for last crawl time (when you are using time stamp based update detection). Specify the name of the database time stamp column as the value. The highest value is set as the last crawl time.

Note

Using this field requires that a time stamp is extracted by the SQL statement.

Default: (none)

JDBCTimeType

string

date|seconds

Choose date or seconds.

Default: date

JDBCDeleteTimeStampField

string

text

Time stamp field to use as the source for the last delete time (when you are using time stamp based deletion). Specify the name of the database time stamp column as the value. The highest value is set as the last crawl time.

Note

Using this field requires that a time stamp is extracted by the SQL statement.

Default: (none)

JDBCDeleteTimeType

string

date|seconds

Time used in the JDBC delete time stamp file. Choose date or seconds.

Default: date

JDBCUpdateTimeStampOnFailure

boolean

true|false

Set to true if you want error callbacks from FAST Search Server 2010 for SharePoint or missing callbacks to update the time stamp file.

Default: false

JDBCUpdateTimeStampOnDeletion

boolean

true|false

If set to true, the timestamp file will be updated when documents are removed from the FAST Search Server 2010 for SharePoint index using the FAST Search database connector.

Default: false

JDBCUsername

string

text

Database user name.

May be omitted when using Windows integrated authentication to access SQL Server.

JDBCPassword

string

text

Database password.

May be omitted when using Windows integrated authentication to access SQL Server.

JDBCFetchSizeHint

integer

0|50 - 2000

Tells the JDBC driver how many rows to fetch from the database when more rows are needed. The number of rows specified only affects result sets created by using this statement. A value of zero is ignored. Range: 50 to 2000.

Default: 0

JDBCCharacterEncoding

string

text

Specifies the encoding used in the database, when the table contains character data (LONGVARCHAR type).

Default: UTF-8

Important

Set only one of {JDBCSQL, JDBCSQLFile} and optionally only one of {JDBCDeleteSQL, JDBCDeleteSQLFile}. Setting more of these parameters in the same configuration file may result in unexpected results.

Example

The following example is for Microsoft SQL Server:

<group name="Input">
    
    <parameter name="JDBCDriver" type="string">
        <value>com.microsoft.sqlserver.jdbc.SQLServerDriver</value>
    </parameter>
    
    <parameter name="JDBCURL" type="string">
        <value>jdbc:sqlserver://192.168.1.100\SP:1433;DatabaseName=test</value>
    </parameter>
    
    <parameter name="JDBCSQL" type="string">
        <value>select * from employees</value>
    </parameter>
    
    <parameter name="JDBCUsername" type="string">
        <value>john</value>
    </parameter>
    
    <parameter name="JDBCPassword" type="string">
        <value>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAmJRnYSadmEqSBy8q5KCI5QQAAAACAAAAAAADZgAAqAAAABAAAAClpb+U+s2eE9l7Nx34wd</value>
    </parameter>
    
</group>

Parameter group: Transformation

The parameters in this group specify content to transform into the FAST Search Server 2010 for SharePoint item model, and transformation details.

Parameter Type Value Description

JDBCPrimaryKeyField

string

text

The database view/table primary key. Required. Used to create the item ID. Case-sensitive; must match your SELECT statement.

JDBCDeletePrimaryKeyField

string

text

The database view/table primary key. Used to delete the item ID.

JDBCUseBytesForString

boolean

true|false

If set to true, strings are treated as byte-arrays instead of strings.

Default: false

UseMultiRowNormalizer

boolean

true|false

If set to true, values for columns of records with identical identifiers are merged.

Default: false

JDBCAttributeColumn

string

text

For normalized tables, specifies the column that contains attribute names.

JDBCValueColumn

string

text

For normalized tables, specifies the column that contains attribute values.

JDBCMultiValueIncludeFields

string

text

Semicolon (;) separated list of column names that should be concatenated in the output. This field overrides any settings to JDBCMultiValueSkipFields.

JDBCMultiValueSkipFields

string

text

Semicolon (;) separated list of column names that have the same values in each row, and that should not be repeated in the output.

Note

Do not use JDBCPrimaryKeyField and JDBCDeletePrimaryKeyField at the same time.

Example

<group name="Transformation">
    <parameter name="JDBCPrimaryKeyField" type="string">
        <value>ID</value>
    </parameter>
</group>

Parameter group: FASTSearchSubmit

The parameters in this group describe communication properties for submitting content to FAST Search Server 2010 for SharePoint.

Parameter Type Value Description

Collection

string

text

Required name of the destination collection.

ActuallyPublish

boolean

true|false

If true, submits the item to FAST Search Server 2010 for SharePoint.

Default: true

Example

<group name="FASTSearchSubmit">
    <parameter name="Collection" type="string">
        <value>collectionname</value>
    </parameter>
</group>

Parameter group: XMLExport

The parameters in this group specify an export to FASTXML formatted file(s).

Parameter Type Value Description

ExportToXML

boolean

true|false

If true, exports all items as XMLExport.

BinaryFields

boolean

data

Semicolon (;) separated list of field names of binary fields. Uses BASE64 encoding in the XMLExport.

FASTXML is stored in the %FASTSEARCH%\var\jdbcconnector\<collection name>\fastxml folder.

%FASTSEARCH% is the FAST Search Server 2010 for SharePoint installation folder.

Example

The following example disables FASTXML export:

<group name="XMLExport">
    <parameter name="ExportToXML" type="boolean">
        <value>false</value>
    </parameter>
</group>

Parameter group: ChangeDetection

The parameters in this group specify how to detect database changes that were made since the last feed (from that database), using row checksums.

Parameter Type Value Description

Enabled

boolean

true|false

Set to true to enable change detection.

Default: false

ChangeDBPurge

boolean

true|false

Set to true to delete all entries in the checksum database at startup.

Default: false

ChangeDBAbortPercent

integer

percentage

When using checksum-based change detection, this value defines the maximum percentage of accepted deleted records in the source. If a higher percentage of deletions is observed during a run, an error occurs and no changes are made to the collection.

Default: 10

ChangeDBIncludeFields

string

text

A semicolon (;) separated list of field names that are included in the checksum computations. By default, all fields are included.

ChangeDBExcludeFields

string

text

A semicolon (;) separated list of field names that are excluded from the checksum computations.

These fields take precedence over the include field list. For example, if only the metadata of an item may change (not the binary content), exclude the data element from the computations.

By default, no fields are excluded.

Example

<group name="ChangeDetection">
    <parameter name="Enabled" type="boolean">
        <value>false</value>
    </parameter>
</group>

Parameter group: ConnectorExecution

The parameters in this group specify general values that are used during a connector run.

Parameter Type Value Description

QueueSize

integer

1 - 10000

Size of the internal queue of items.

Default: 200

EnableStatusTracker

boolean

true|false

The status tracker logs the item crawl status. To disable status tracking, set to false.

WorkFolder

string

text

The work folder for all file output that includes log files and XMLExport files. Default location: ..\var\jdbcconnector

Example

<group name="ConnectorExecution">
    <parameter name="QueueSize" type="integer">
        <value>200</value>
    </parameter>
</group>

Parameter group: General

This parameter specifies the configuration name by defining an optional prefix for the property set. If empty, the configuration uses a predefined propset.

Parameter Type Value Description

Name

string

name

Specifies the configuration name, the prefix for the propset. The name must be unique, with a maximum length of 32 characters, in the range a-f and 0-9

Example

<group name="General">
    <parameter name="Name" type="string">
        <value>abcdef01</value>
    </parameter>
</group>

See Also

Reference

jdbcconnector.bat reference