The results of an SQL query sent to a relational database (RDB) can be joined to an event stream. Specify this using the following syntax in a FROM clause:
Syntax:
sql:databaseName [" sqlQuery "] or
sql:databaseName [' sqlQuery ']
In databaseName, specify the "development asset ID" specified in the RDB reference definition.
Enclose sqlQuery in double quotation marks (") or single quotation marks ('), and enclose this specification in square brackets "[" and "]".
Alternate parameters can be included in sqlQuery. Specify alternate parameters in the ${expression} format. The expression is evaluated when the statement is executed.
Note
Minimize RDB referencing, because it may cause a decline in the performance of Complex Event Processing.
Multibyte characters cannot be used for definition names in a relational database such as table names and table item names to be referenced from the complex event processing language. Multibyte characters can be used for item values.
To specify a nonnumeric literal enclosed in single quotation marks (') in an SQL query that is further enclosed in single quotation marks ('), use the escape notation (\') or the Unicode notation (\u0027).
Example
Example of using relational database referencing
This example uses relational database referencing in a complex event processing rule (SELECT statement).
@Name("PutRecommend")
@SoapListener("soap-001")
select tvevnt.gatewayId as gatewayId,
case when db.DRY_FUNC = '1' then 'USE_DRY_FUNC' else 'HANG_LAUNDRY_INSIDE' end as recommendId
from TVControlRain as tvevnt,
sql:app_db[ 'SELECT DRY_FUNC FROM PRODUCTFUNC_TBL WHERE HGW_ID=${tvevnt.gatewayId}' ] as db;
The development asset ID of the RDB reference definition to be used is set to "app_db".
The relational database table being referenced is "PRODUCTFUNC_TBL".
The alternate parameter "${tvevnt.gatewayId}" is specified in the condition for referencing the relational database table.
The alias "db" is assigned to the reference results and is used in the SELECT statement.
