

API Docs - v4.0.23

Rdbms

cud (Stream Processor)

The function can be used to perform SQL CUD (INSERT, UPDATE, DELETE) queries on a WSO2 datasource.
Note: This will only work within WSO2 SP.

Syntax

rdbms:cud(<STRING> datasource.name, <STRING> query)

QUERY PARAMETERS

Name	Description	Default Value	Possible Data Types	Optional	Dynamic
datasource.name	The name of the WSO2 datasource on which the query should be performed on		STRING	No	No
query	The update, delete, or insert query(formatted according to the appropriate database type) that needs to be performed.		STRING	No	No

System Parameters

Name	Description	Default Value	Possible Parameters
perform.CUD.operations	Enable/Disable performing CUD operations through rdbms cud function	false	true false

Extra Return Attributes

Name	Description	Possible Types
numRecords	The number of records manipulated by the query.	INT

Examples EXAMPLE 1

from TriggerStream#rdbms:cud("SAMPLE_DB", "UPDATE Customers_Table SET customerName='abc' where customerName='xyz'") 
select numRecords 
insert into  recordStream;

Events will be modified with additional attribute of name 'numRecords' with the number of records manipulated.

query (Stream Processor)

The function can be used to perform SQL retrieval queries on a WSO2 datasource.
Note: This will only work within WSO2 SP.

Syntax

rdbms:query(<STRING> datasource.name, <STRING> query, <STRING> attribute.definition.list)

QUERY PARAMETERS

Name	Description	Possible Data Types	Optional	Dynamic
datasource.name	The name of the WSO2 datasource on which the query should be performed on	STRING	No	No
query	The select query(formatted according to the appropriate database type) that needs to be performed	STRING	No	No
attribute.definition.list	Comma separated list of `<AttributeName AttributeType>`. It is expected that the SQL query will return the attributes in order, as in if one attribute is defined here, the SQL query should return one column result set, if more than one column is returned then the first column will be processed. The Siddhi data types supported will be `STRING`, `INT`, `LONG`, `DOUBLE`, `FLOAT`, `BOOL`. Mapping of the Siddhi data type to database data type can be done as follows, Siddhi Datatype->Datasource Datatype `STRING`->`CHAR`,`VARCHAR`,`LONGVARCHAR` `INT` ->`INTEGER` `LONG` ->`BIGINT` `DOUBLE`->`DOUBLE` `FLOAT` ->`REAL` `BOOL` ->`BIT`	STRING	No	No

Extra Return Attributes

Name	Description	Possible Types
attributeName	The return attributes will be the ones defined in the parameter`attribute.definition.list`.	STRING INT LONG DOUBLE FLOAT BOOL

Examples EXAMPLE 1

from TriggerStream#rdbms:query('SAMPLE_DB', 'select * from Transactions_Table', 'creditcardno string, country string, transaction string, amount int') 
select creditcardno, country, transaction, amount 
insert into recordStream;

Events inserted into recordStream includes all records matched for the query i.e an event will be generated for each record retrieved from the datasource. The event will include as additional attributes, the attributes defined in the attribute.definition.list(creditcardno, country, transaction, amount).

Store

rdbms (Store)

This extension assigns data sources and connection instructions to event tables. It also implements read write operations on connected datasources

Syntax

@Store(type="rdbms", jdbc.url="<STRING>", username="<STRING>", password="<STRING>", jdbc.driver.name="<STRING>", pool.properties="<STRING>", jndi.resource="<STRING>", datasource="<STRING>", table.name="<STRING>", field.length="<STRING>")
@PrimaryKey("PRIMARY_KEY")
@Index("INDEX")

QUERY PARAMETERS

Name	Description	Default Value	Possible Data Types	Optional	Dynamic
jdbc.url	The JDBC URL via which the RDBMS data store is accessed.		STRING	No	No
username	The username to be used to access the RDBMS data store.		STRING	No	No
password	The password to be used to access the RDBMS data store.		STRING	No	No
jdbc.driver.name	The driver class name for connecting the RDBMS data store.		STRING	No	No
pool.properties	Any pool parameters for the database connection must be specified as key value pairs.	null	STRING	Yes	No
jndi.resource	The name of the JNDI resource through which the connection is attempted. If this is found, the pool properties described above are not taken into account, and the connection is attempted via JNDI lookup instead.	null	STRING	Yes	No
datasource	The name of the Carbon datasource which should be used for creating the connection with the database. If this is found, neither the pool properties nor the JNDI resource name described above are not taken into account, and the connection is attempted via Carbon datasources instead.	null	STRING	Yes	No
table.name	The name with which the event table should be persisted in the store. If no name is specified via this parameter, the event table is persisted with the same name as the Siddhi table.	The table name defined in the Siddhi App query.	STRING	Yes	No
field.length	The number of characters that the values for fields of the `STRING` type in the table definition must contain. If this is not specified, the default number of characters specific to the database type is considered.	null	STRING	Yes	No

System Parameters

Name	Description	Default Value	Possible Parameters
{{RDBMS-Name}}.maxVersion	The latest version supported for {{RDBMS-Name}}.	0	N/A
{{RDBMS-Name}}.minVersion	The earliest version supported for {{RDBMS-Name}}.	0	N/A
{{RDBMS-Name}}.tableCheckQuery	The template query for the `check table` operation in {{RDBMS-Name}}.	H2: CREATE TABLE {{TABLE_NAME}} ({{COLUMNS, PRIMARY_KEYS}}) MySQL: CREATE TABLE {{TABLE_NAME}} ({{COLUMNS, PRIMARY_KEYS}}) Oracle: CREATE TABLE {{TABLE_NAME}} ({{COLUMNS, PRIMARY_KEYS}}) Microsoft SQL Server: CREATE TABLE {{TABLE_NAME}} ({{COLUMNS, PRIMARY_KEYS}}) PostgreSQL: CREATE TABLE {{TABLE_NAME}} ({{COLUMNS, PRIMARY_KEYS}}) DB2.: CREATE TABLE {{TABLE_NAME}} ({{COLUMNS, PRIMARY_KEYS}})	N/A
{{RDBMS-Name}}.tableCreateQuery	The template query for the `create table` operation in {{RDBMS-Name}}.	H2: SELECT 1 FROM {{TABLE_NAME}} LIMIT 1 MySQL: SELECT 1 FROM {{TABLE_NAME}} LIMIT 1 Oracle: SELECT 1 FROM {{TABLE_NAME}} WHERE rownum=1 Microsoft SQL Server: SELECT TOP 1 1 from {{TABLE_NAME}} PostgreSQL: SELECT 1 FROM {{TABLE_NAME}} LIMIT 1 DB2.: SELECT 1 FROM {{TABLE_NAME}} FETCH FIRST 1 ROWS ONLY	N/A
{{RDBMS-Name}}.indexCreateQuery	The template query for the `create index` operation in {{RDBMS-Name}}.	H2: CREATE INDEX {{TABLE_NAME}}_INDEX ON {{TABLE_NAME}} ({{INDEX_COLUMNS}}) MySQL: CREATE INDEX {{TABLE_NAME}}_INDEX ON {{TABLE_NAME}} ({{INDEX_COLUMNS}}) Oracle: CREATE INDEX {{TABLE_NAME}}_INDEX ON {{TABLE_NAME}} ({{INDEX_COLUMNS}}) Microsoft SQL Server: CREATE INDEX {{TABLE_NAME}}_INDEX ON {{TABLE_NAME}} ({{INDEX_COLUMNS}}) {{TABLE_NAME}} ({{INDEX_COLUMNS}}) PostgreSQL: CREATE INDEX {{TABLE_NAME}}_INDEX ON {{TABLE_NAME}} ({{INDEX_COLUMNS}}) DB2.: CREATE INDEX {{TABLE_NAME}}_INDEX ON {{TABLE_NAME}} ({{INDEX_COLUMNS}})	N/A
{{RDBMS-Name}}.recordInsertQuery	The template query for the `insert record` operation in {{RDBMS-Name}}.	H2: INSERT INTO {{TABLE_NAME}} ({{COLUMNS}}) VALUES ({{Q}}) MySQL: INSERT INTO {{TABLE_NAME}} ({{COLUMNS}}) VALUES ({{Q}}) Oracle: INSERT INTO {{TABLE_NAME}} ({{COLUMNS}}) VALUES ({{Q}}) Microsoft SQL Server: INSERT INTO {{TABLE_NAME}} ({{COLUMNS}}) VALUES ({{Q}}) PostgreSQL: INSERT INTO {{TABLE_NAME}} ({{COLUMNS}}) VALUES ({{Q}}) DB2.: INSERT INTO {{TABLE_NAME}} ({{COLUMNS}}) VALUES ({{Q}})	N/A
{{RDBMS-Name}}.recordUpdateQuery	The template query for the `update record` operation in {{RDBMS-Name}}.	H2: UPDATE {{TABLE_NAME}} SET {{COLUMNS_AND_VALUES}} {{CONDITION}} MySQL: UPDATE {{TABLE_NAME}} SET {{COLUMNS_AND_VALUES}} {{CONDITION}} Oracle: UPDATE {{TABLE_NAME}} SET {{COLUMNS_AND_VALUES}} {{CONDITION}} Microsoft SQL Server: UPDATE {{TABLE_NAME}} SET {{COLUMNS_AND_VALUES}} {{CONDITION}} PostgreSQL: UPDATE {{TABLE_NAME}} SET {{COLUMNS_AND_VALUES}} {{CONDITION}} DB2.: UPDATE {{TABLE_NAME}} SET {{COLUMNS_AND_VALUES}} {{CONDITION}}	N/A
{{RDBMS-Name}}.recordSelectQuery	The template query for the `select record` operation in {{RDBMS-Name}}.	H2: SELECT * FROM {{TABLE_NAME}} {{CONDITION}} MySQL: SELECT * FROM {{TABLE_NAME}} {{CONDITION}} Oracle: SELECT * FROM {{TABLE_NAME}} {{CONDITION}} Microsoft SQL Server: SELECT * FROM {{TABLE_NAME}} {{CONDITION}} PostgreSQL: SELECT * FROM {{TABLE_NAME}} {{CONDITION}} DB2.: SELECT * FROM {{TABLE_NAME}} {{CONDITION}}	N/A
{{RDBMS-Name}}.recordExistsQuery	The template query for the `check record existence` operation in {{RDBMS-Name}}.	H2: SELECT TOP 1 1 FROM {{TABLE_NAME}} {{CONDITION}} MySQL: SELECT 1 FROM {{TABLE_NAME}} {{CONDITION}} Oracle: SELECT COUNT(1) INTO existence FROM {{TABLE_NAME}} {{CONDITION}} Microsoft SQL Server: SELECT TOP 1 FROM {{TABLE_NAME}} {{CONDITION}} PostgreSQL: SELECT 1 FROM {{TABLE_NAME}} {{CONDITION}} LIMIT 1 DB2.: SELECT 1 FROM {{TABLE_NAME}} {{CONDITION}} FETCH FIRST 1 ROWS ONLY	N/A
{{RDBMS-Name}}.recordDeleteQuery	The query for the `delete record` operation in {{RDBMS-Name}}.	H2: DELETE FROM {{TABLE_NAME}} {{CONDITION}} MySQL: DELETE FROM {{TABLE_NAME}} {{CONDITION}} Oracle: DELETE FROM {{TABLE_NAME}} {{CONDITION}} Microsoft SQL Server: DELETE FROM {{TABLE_NAME}} {{CONDITION}} PostgreSQL: DELETE FROM {{TABLE_NAME}} {{CONDITION}} DB2.: DELETE FROM {{TABLE_NAME}} {{CONDITION}}	N/A
{{RDBMS-Name}}.stringSize	This defines the length for the string fields in {{RDBMS-Name}}.	H2: 254 MySQL: 254 Oracle: 254 Microsoft SQL Server: 254 PostgreSQL: 254 DB2.: 254	N/A
{{RDBMS-Name}}.batchSize	This defines the batch size when operations are performed for batches of events.	H2: 1000 MySQL: 1000 Oracle: 1000 Microsoft SQL Server: 1000 PostgreSQL: 1000 DB2.: 1000	N/A
{{RDBMS-Name}}.batchEnable	This specifies whether `Update` and `Insert` operations can be performed for batches of events or not.	H2: true MySQL: true Oracle (versions 12.0 and less): false Oracle (versions 12.1 and above): true Microsoft SQL Server: true PostgreSQL: true DB2.: true	N/A
{{RDBMS-Name}}.transactionSupported	This is used to specify whether or not the JDBC connection that is used supports JDBC transactions.	H2: true MySQL: true Oracle: true Microsoft SQL Server: true PostgreSQL: true DB2.: true	N/A

Examples EXAMPLE 1

@Store(type="rdbms", jdbc.url="jdbc:mysql://localhost:3306/das", username="root", password="root" , jdbc.driver.name="org.h2.Driver",field.length="symbol:100")
@PrimaryKey("symbol")@Index("volume")define table StockTable (symbol string, price float, volume long);

The above example creates an event table named StockTable on the DB if it does not already exist (with 3 attributes named symbol, price, and volume of the types types string, float and long respectively). The connection is made as specified by the parameters configured for the '@Store' annotation. The symbol attribute is considered a unique field, and a DB index is created for it.