Database Input Adapter

Adapter type:db_in. The Database Input adapter receives data from a database table.

You can use the adapter to periodically poll the table and receive updates. The required properties depend on the database type you are connecting to. The supported databases for JDBC are SAP Sybase ASE, Microsoft SQL Server, IBM DB2, Oracle, and KDB. The supported databases for ODBC are SAP Sybase ASE, Microsoft SQL Server, IBM DB2, Oracle, SAP Sybase IQ, SQL Anywhere®, TimesTen, MySQL 5.x, and PostgreSQL.

The service.xml file contains service definitions and the properties required for a database connection. For the service definition name, consult the person responsible for configuring and maintaining the service.xml file. See the Configuration and Adminstration Guide for information on configuring database connections using the service.xml file.

Use the query property to override the table selection and get data from an arbitrary query. This adapter supports schema discovery.

If you use the CCL ATTACH ADAPTER statement to attach an adapter, you must supply the adapter type.

Important: For an SAP Sybase ASE database, enable the ddl in tran option on the temporary database (tempdb) to discover all tables when using schema discovery. Then, update the ESP Server by performing a checkpoint on tempdb or restarting the database instance. For more information on the ddl in tran option, consult your SAP Sybase ASE documentation.
Property Label Description
Database Service Name

Property ID: service

Type: string

(Required for adapter operation and schema discovery) Name of the database service as defined in the service.xml file. No default value.

Database Query

Property ID: query

Type: string

(Optional) The SQL query to be executed by the adapter. The adapter definition requires either query or table to be defined. If both parameters are defined, the query parameter is used.

No default value.

Input Table Name

Property ID: table

Type: tables

(Optional) A string value representing the name of the table from which the adapter reads. No default value.

Poll Period (in seconds)

Property ID: pollperiod

Type: uint

(Advanced) Period for which the adapter polls for new content, in seconds. The default value is 0, which means no polling.

Date Format

Property ID: dateFormat

Type: string

(Advanced) The format string for parsing date values. The default value is %Y-%m-%dT%H:%M:%S.

Timestamp Format

Property ID: timestampFormat

Type: string

(Advanced) The format string for parsing timestamp values. The default value is %Y-%m-%dT%H:%M:%S.

Field Mapping

Property ID: permutation

Type: permutation

(Advanced)

Mapping between Event Stream Processor and external fields, for example:

<esp_columnname>=<database_columnname>:<esp_columnname>=<database_columnname>. No default value.

For Oracle 11g, DB2 9.7, and SAP HANA, the metadata services return results in uppercase, so ensure the database column key in the permutation is in uppercase. For example, in CCL, this command does not work:
permutation= 'Subject=subject:c_string=c_string'
but this one does:
permutation= 'Subject=SUBJECT:c_string=C_STRING'

For SAP Sybase ASE 15.5 and Microsoft SQL Server 2008, the metadata results are the same as the case in the defined column name, unless the name is modified in the SELECT statement.

PropertySet

Property ID: propertyset

Type: string

(Advanced) Specifies the name of the property set. Property sets are reusable sets of properties that are stored in the project configuration file. Using these sets allows you to move adapter configuration properties out of the CCL file and into the CCR file. If you specify the same properties in the project configuration file and the ATTACH ADAPTER statement, the values in the property set override the values defined in the ATTACH ADAPTER statement. No default value.

The Database Input adapter has these limitations:
  • Ensure, when polling, that this is the only adapter.
  • Any data updates received from any other source are undone on the next poll.