Modifying Data in External Databases

Use the Database statement to send instructions to modify external relational databases directly from Sybase CEP. The Database statement can execute any valid SQL Update, Insert, or Delete statement in the associated external database.

Assume that a Sybase CEP query is creating ongoing average price data from incoming stock trades. This query sends its information to a stream called StockAvgPrice. Here is an example of how the Database statement can read the StockAvgPrice stream and update the external database:

EXECUTE STATEMENT DATABASE 'StockDatabase'
[[UPDATE StockHistory
 SET StockHistory.avgprice = ?avgpr
 WHERE StockHistory.symbol = ?symb]] 
SELECT symbol AS symb, avgprice AS avgpr 
FROM StockAvgPrice

The syntax of the Database statement is very similar to the database subquery, except that it is a complete statement of its own which is not embedded in an INSERT/SELECT statement. Note the following differences and important points:

No schema is needed with the Database statement, because Sybase CEP simply sends the data to the external database and does not get any results back.
The SQL inside the square brackets can be any valid SQL for the external database but it can not return a result. Typically, this includes UPDATE, INSERT and DELETE statements, but it can also be used to execute stored procedures as long as the stored procedure doesn't return a result set or a cursor.
The SELECT clause using after the SQL statement follows almost all Sybase CEP SELECT clause rules although there are some exceptions. The principal exception is that every column used in the SELECT list must be given an AS column name alias so that it can be uniquely identified in the body of the SQL statement. This is true even if the names are already unambiguous.
Also, because names must be given to each column, SELECT * is not permitted. For other minor differences, see Sybase CEP CCL Reference.

For every row that is published by the SELECT clause, a call is made to the external database, passing the entire SQL statement contained in the square brackets after all ? references are converted to their actual Sybase CEP data values. The external database is responsible for executing the SQL statement. Errors are reflected back to the Sybase CEP server and can be found in the Sybase CEP log files.