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 Publishing Events .

Configuring global properties

You can change the queries used to perform the standard database operations by adding the customised queries in the <CEP_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 which 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 Publishing Events in Various Event Formats.

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.	update.keys	sensor-key,sensor-group