RDBMS Event Publisher

RDBMS event publisher is used to publish events in map format to a RDBMS in two execution modes, which are insert and update-insert.

Prerequisites

Follow the steps below to set up the prerequisites before starting the configurations.

Create a datasource to connect to the selected database. For instructions on creating a datasource, see Adding Datasources.

If selected database is H2, uncomment the following H2 database configurations in the <PRODUCT_HOME>/repository/config/carbon.xml file as follows, to browse through the database and see the changes. Keep the other properties of the H2DatabaseConfiguration element uncommented.
```
<H2DatabaseConfiguration>
	<property name="web"/>
	<property name="webPort">8082</property>
	<property name="webAllowOthers"/>
</H2DatabaseConfiguration>
```

Creating a RDBMS event publisher

For instructions on creating a RDBMS event publisher, see Creating Alerts.

Configuring global properties

You can change the queries used to perform the standard database operations by adding the customised queries in the <DAS_HOME>/repository/conf/output-event-adapters.xml file. This enables you to use RDBMS database types that use different queries. Customised values can be defined for the following used in standard queries.

Custom properties cannot be added as global properties.

Attribute/activity	Current query
string	`VARCHAR(255)`
`double`	`DOUBLE`
`integer`	`INT`
`long`	`BIGINT`
`float`	`FLOAT`
`createTable`	`CREATE TABLE $TABLE_NAME ($COLUMN_TYPES)`
`insertDataToTable`	`INSERT INTO $TABLE_NAME ($COLUMNS) VALUES ($VALUES)`
`isTableExist`	`SELECT * FROM $TABLE_NAME limit 1`
`updateTableRow`	`UPDATE $TABLE_NAME SET $COLUMN_VALUES WHERE $CONDITION`
`comma`	`,`
`questionMark`	`?`
`equal`	`=`
`and`	`AND`
`selectAllColumnsDataTypeInTable`	`SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$TABLE_NAME'`
selectFromTable	`SELECT $COLUMNS FROM $TABLE_NAME`
`oracle.string`	`varchar2(255)`
`oracle.long`	`CLOB`
`oracle.double`	`BINARY_DOUBLE`
`oracle.isTableExist`	`SELECT * FROM $TABLE_NAME WHERE ROWNUM = 1`
`oracle.selectAllColumnsDataTypeInTable`	`SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLS WHERE TABLE_NAME = '$TABLE_NAME'`
`mssql.string`	`varchar2(255)`
`mssql.isTableExist`	`SELECT TOP 1 * FROM $TABLE_NAME`
`mssql.selectAllColumnsDataTypeInTable`	`SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '$TABLE_NAME'`
`h2.integer`	`varchar2(255)`
`h2.long`	`REAL`
`h2.selectAllColumnsDataTypeInTable`	`SHOW COLUMNS FROM $TABLE_NAME`

Configuring adapter properties

Specify the Static Adapter Properties, when creating a RDBMS event publisher using the management console as shown below.

RDBMS event publisher

After entering the above adapter properties, select the Message Format that you want to apply on the published events. Also, click Advanced to define custom output mappings based on the Message Format you selected. For more information on custom output mapping types, see Output Mapping Types.

An RDBMS publisher does not identify the persisted attributes of an event stream. If you want only the persisted attributes in the connected event stream to be published, the persisted attributes can be defined as advanced properties. For more information about persisting attributes, see Configuring Data Persistence.

You can also define the respective adapter properties of the event publisher based on the transport type within the <to> element of the event publisher configuration in the <PRODUCT_HOME>/repository/deployment/server/eventpublishers/ directory as follows.

<eventPublisher name="RDBMSOutputEventAdapter" statistics="disable" trace="disable" xmlns="http://wso2.org/carbon/eventpublisher">
  ....................
  <to eventAdapterType="rdbms">
    <property name="datasource.name">WSO2_CARBON_DB</property>
    <property name="table.name">sensordata</property>
    <property name="execution.mode">insert</property>
    <property name="update.keys">sensor-key,sensor-group</property>
  </to>
</eventPublisher>

The above adapter properties are described below.

Adapter Property	Description	Configuration file Property	Example
Data Source Name	Name of the datasource	datasource.name	WSO2_CARBON_DB
Table Name	Name of the table	table.name	sensordata
Execution Mode	Type of the execution mode.	execution.mode	insert/update or insert
Composite key columns	Attributes used for uniqueness checks for updates. Use commas to separate if you enter more than one attribute. It is required to enter one or more attributes as composite key columns if you select `update-or-insert` for the Execution Mode property.	update.keys	sensor-key,sensor-group

Related samples

For more information on rdbms event publisher type, see the following sample.

Sample 0072 - Publishing Map Events via RDBMS Transport