Writes data to an external relational database.
|
exec_clause |
The destination for the published rows. See EXECUTE STATEMENT DATABASE Clause for more information. The statements in this clause are executed on the external database whenever this statement generates output. |
|
select_clause |
The select list specifying what to publish. See SELECT Clause for more information. All non-asterisk (*) items in this list must include an AS subclause. |
|
from_clause |
The data sources. See FROM Clause for more information. |
|
matching_clause |
A pattern-matching specification. See MATCHING Clause for more information. |
|
on_clause |
A join condition. See ON Clause for more information. |
|
where_clause |
A selection condition. See WHERE Clause for more information. |
|
group_by_clause |
A partitioning specification. See GROUP BY Clause for more information. |
|
having_clause |
A filter definition. See HAVING Clause for more information. |
|
order_by_clause |
A sequencing definition. See ORDER BY Clause for more information. |
|
limit_clause |
A limit on the number of rows to publish. See LIMIT Clause for more information. |
|
output_clause |
A synchronization specification. See OUTPUT Clause for more information. |
The Database statement is a CCL statement that directly modifies data in an external relational database. You must have previously configured the database, as described in the Sybase CEP Installation Guide . Enter the Database statement directly into the CCL query module, and it executes SQL statements against the external database without going through an output adapter. In the case of kdb+ databases, the Database statement can also contain statements in Kx System's language q.
This statement is used only to pass SQL or q statements to databases, not to retrieve data from databases. Data retrieval from databases is handled by database subqueries (see Database Subquery for more information).
The Database statement is syntactically similar to the Query statement. It contains a number of clauses that create a query that subscribes to rows from a data stream, processes the incoming rows, and perform actions against the external database based on the results.
In addition to using the Database statement, you can also write to databases using the Write to DB output adapter, which passes SQL statements to an external database. For instructions on using the adapter, see the Sybase CEP Integration Guide .
The main clauses within a Database statement are processed in the following order, which affects the query's output:
FROM clause (with MATCHING conditions, if any).
WHERE clause (with MATCHING conditions, if any).
SELECT clause (in conjunction with GROUP BY, if present).
HAVING clause.
OUTPUT clause.
ORDER BY clause.
LIMIT clause.
INSERT clause.
Connection with the database listed in the EXECUTE STATEMENT DATABASE clause must first be configured, as described in the Sybase CEP Installation Guide , before you can use the Database statement.
If the SELECT clause of the Database statement uses the "select all" expression (*) and the FROM clause of the statement lists multiple data sources, the data sources cannot share any column names.
If the SELECT clause contains a list of column names, it must assign an output alias to each one, using the AS subclause.
EXECUTE STATEMENT DATABASE
FROM
GROUP BY
HAVING
LIMIT
MATCHING
ON
ORDER BY
OUTPUT
SELECT
WHERE
The following simple example of a Database statement deletes rows from database table Table1, based on their match to values in the Column1 column of StreamIn.
EXECUTE STATEMENT DATABASE "ExternalDatabase" [[DELETE FROM Table1 WHERE Table1.Column1=?Column1]] SELECT StreamIn.Column1 AS Column1 FROM StreamIn;
The following example inserts column values from the ActiveStrategies window and MyTrades streams, as well as a calculated timestamp into TradingDatabase.
CREATE WINDOW ActiveStrategies
SCHEMA (Symbol STRING, Strategy STRING)
KEEP LAST PER Symbol;
CREATE INPUT STREAM MyTrades
SCHEMA (Symbol STRING, Quantity INTEGER, Price FLOAT);
EXECUTE STATEMENT DATABASE "TradingDatabase"
[[INSERT INTO Positions
VALUES (?Symbol, ?Ts, ?Quantity, ?Price, ?Strategy) ]]
SELECT *, GETTIMESTAMP(MyTrades) as Ts
FROM MyTrades, ActiveStrategies
WHERE MyTrades.Symbol = ActiveStrategies.Symbol;