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.xmlfile as follows, to browse through the database and see the changes. Keep the other properties of theH2DatabaseConfigurationelement 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.
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 Output Mapping Types .
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.keys | sensor-key,sensor-group |
Configuring advanced properties
Advanced properties are configured to select attributes with IDs different to those defined in the event stream to which the publisher is connected.
e.g., If the event stream to which the publisher is connected has an attribute named ID, but you want the publisher to include attributes of which the name is formatted as id, you can define id as an advanced property as shown below.
An RDBMS publisher does not by default identify the persisted attributes of an event stream. You can define the persisted attributes as advanced properties to avoid an exception similar to the following example.
Related samples
For more information on rdbms event publisher type, see the following sample in WSO2 CEP Documentation.