Skip to content

API Docs - v4.0.46

Rdbms

cud (Stream Processor)

This function performs SQL CUD (INSERT, UPDATE, DELETE) queries on WSO2 datasources.
Note: This function is only available when running Siddhi with WSO2 SP.

Syntax

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

QUERY PARAMETERS

Name Description Default Value Possible Data Types Optional Dynamic
datasource.name The name of the WSO2 datasource for which the query should be performed. STRING No No
query The update, delete, or insert query(formatted according to the relevant database type) that needs to be performed. STRING No No
parameter.n If the second parameter is a parametrised SQL query, then siddhi attributes can be passed to set the values of the parameters STRING No No

System Parameters

Name Description Default Value Possible Parameters
perform.CUD.operations If this parameter is set to 'true', the RDBMS CUD function is enabled to perform CUD operations. 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;

This query updates the events from the input stream named 'TriggerStream' with an additional attribute named 'numRecords', of which the value indicates the number of records manipulated. The updated events are inserted into an output stream named 'RecordStream'.

EXAMPLE 2

from TriggerStream#rdbms:cud("SAMPLE_DB", "UPDATE Customers_Table SET customerName=? where customerName=?", changedName, previousName) 
select numRecords 
insert into  RecordStream;

This query updates the events from the input stream named 'TriggerStream' with an additional attribute named 'numRecords', of which the value indicates the number of records manipulated. The updated events are inserted into an output stream named 'RecordStream'. Here the values of attributes changedName and previousName in the event will be set to the query.

query (Stream Processor)

This function performs SQL retrieval queries on WSO2 datasources.
Note: This function is only available when running Siddhi with WSO2 SP.

Syntax

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

QUERY PARAMETERS

Name Description Default Value Possible Data Types Optional Dynamic
datasource.name The name of the WSO2 datasource for which the query should be performed. STRING No No
query The select query(formatted according to the relevant database type) that needs to be performed STRING No No
parameter.n If the second parameter is a parametrised SQL query, then siddhi attributes can be passed to set the values of the parameters STRING No No
attribute.definition.list This is provided as a comma-separated list in the '<AttributeName AttributeType>' format. The SQL query is expected to return the attributes in the given order. e.g., 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 is processed. The Siddhi data types supported are 'STRING', 'INT', 'LONG', 'DOUBLE', 'FLOAT', and 'BOOL'.
 Mapping of the Siddhi data type to the 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 parameterattribute.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).

EXAMPLE 2

from TriggerStream#rdbms:query('SAMPLE_DB', 'select * from where country=? ', countrySearchWord, '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). countrySearchWord value from the event will be set in the query when querying the datasource.

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 that 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 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. Each required field must be provided as a comma-separated list of key-value pairs in the '<field.name>:<length>' format. 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}}.fieldSizeLimit This defines the field size limit for select/switch to big string type from the default string type if the 'bigStringType' is available in field type list. H2: N/A
MySQL: N/A
Oracle: 2000
Microsoft SQL Server: N/A
PostgreSQL: N/A
DB2.: N/A		 0 =< n =< INT_MAX
{{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 the JDBC connection that is used supports JDBC transactions or not. H2: true
MySQL: true
Oracle: true
Microsoft SQL Server: true
PostgreSQL: true
DB2.: true		 N/A
{{RDBMS-Name}}.typeMapping.binaryType This is used to specify the binary data type. An attribute defines as 'object' type in Siddhi stream will be stored into RDBMS with this type. H2: BLOB
MySQL: BLOB
Oracle: BLOB
Microsoft SQL Server: VARBINARY(max)
PostgreSQL: BYTEA
DB2.: BLOB(64000)		 N/A
{{RDBMS-Name}}.typeMapping.booleanType This is used to specify the boolean data type. An attribute defines as 'bool' type in Siddhi stream will be stored into RDBMS with this type. H2: TINYINT(1)
MySQL: TINYINT(1)
Oracle: NUMBER(1)
Microsoft SQL Server: BIT
PostgreSQL: BOOLEAN
DB2.: SMALLINT		 N/A
{{RDBMS-Name}}.typeMapping.doubleType This is used to specify the double data type. An attribute defines as 'double' type in Siddhi stream will be stored into RDBMS with this type. H2: DOUBLE
MySQL: DOUBLE
Oracle: NUMBER(19,4)
Microsoft SQL Server: FLOAT(32)
PostgreSQL: DOUBLE PRECISION
DB2.: DOUBLE		 N/A
{{RDBMS-Name}}.typeMapping.floatType This is used to specify the float data type. An attribute defines as 'float' type in Siddhi stream will be stored into RDBMS with this type. H2: FLOAT
MySQL: FLOAT
Oracle: NUMBER(19,4)
Microsoft SQL Server: REAL
PostgreSQL: REAL
DB2.: REAL		 N/A
{{RDBMS-Name}}.typeMapping.integerType This is used to specify the integer data type. An attribute defines as 'int' type in Siddhi stream will be stored into RDBMS with this type. H2: INTEGER
MySQL: INTEGER
Oracle: NUMBER(10)
Microsoft SQL Server: INTEGER
PostgreSQL: INTEGER
DB2.: INTEGER		 N/A
{{RDBMS-Name}}.typeMapping.longType This is used to specify the long data type. An attribute defines as 'long' type in Siddhi stream will be stored into RDBMS with this type. H2: BIGINT
MySQL: BIGINT
Oracle: NUMBER(19)
Microsoft SQL Server: BIGINT
PostgreSQL: BIGINT
DB2.: BIGINT		 N/A
{{RDBMS-Name}}.typeMapping.stringType This is used to specify the string data type. An attribute defines as 'string' type in Siddhi stream will be stored into RDBMS with this type. H2: VARCHAR(stringSize)
MySQL: VARCHAR(stringSize)
Oracle: VARCHAR(stringSize)
Microsoft SQL Server: VARCHAR(stringSize)
PostgreSQL: VARCHAR(stringSize)
DB2.: VARCHAR(stringSize)		 N/A
{{RDBMS-Name}}.typeMapping.bigStringType This is used to specify the big string data type. An attribute defines as 'string' type in Siddhi stream and field.length define in the annotation is greater than the fieldSizeLimit, will be stored into RDBMS with this type. H2: N/A
MySQL: N/AOracle: CLOBMicrosoft SQL Server: N/A
PostgreSQL: N/A
DB2.*: N/A		 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' in the database if it does not already exist (with three attributes named 'symbol', 'price', and 'volume' of the 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.

EXAMPLE 2

@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("symbol")
define table StockTable (symbol string, price float, volume long);
define stream InputStream (symbol string, volume long);
from InputStream as a join StockTable as b on str:contains(b.symbol, a.symbol)
select a.symbol as symbol, b.volume as volume
insert into FooStream;

The above example creates an event table named 'StockTable' in the database if it does not already exist (with three attributes named 'symbol', 'price', and 'volume' of the types 'string', 'float' and 'long' respectively). Then the table is joined with a stream named 'InputStream' based on a condition. The following operations are included in the condition:
[ AND, OR, Comparisons( < <= > >= == !=), IS NULL, NOT, str:contains(Table<Column>, Stream<Attribute> or Search.String)]