EXECUTE STATEMENT DATABASE clause

Syntax

EXECUTE STATEMENT DATABASE "service" [[statements]]   
   SELECT…

Usage

The EXECUTE STATEMENT DATABASE clause is the first clause of the Database statement. It indicates the database that is impacted when the CCL query in the Database statement generates output, and specifies the SQL or q statements that should be executed against the database in that event.

Sybase CEP Engine sends the text you specify (as statements) to the external database for execution after replacing references to CCL columns and parameters with the appropriate values. Since Sybase CEP Engine does not interpret the statements beyond replacing the column and parameter references, you may specify any statement supported by your database. However, Sybase CEP Engine ignores any return values. To retrieve data from an external database, use a database subquery.

To include the value of a CCL column in your database statement, preface the column name with a question mark (?stream_or_alias.column). To include the value of a CCL parameter, preface the parameter name by a question mark and a dollar sign (?$parameter).

ON ERROR extension

Use the ON ERROR extension to handle database errors that may cause Sybase CEP Engine to stop project execution.

EXECUTE STATEMENT DATABASE "service" [[statements]]   
   ON ERROR [error_insert_clause] CONTINUE
   SELECT…

error_insert_clause: INSERT INTO error_stream_name error_select_list
error_stream_name: Name of an output stream or local stream.
error_select_list: SELECT {error_expression} [,...]
error_expression: A CCL expression.

The ON ERROR extension is optional. The error_insert_clause for the ON ERROR extension is also optional. If the ON ERROR extension is not provided and errors occur, the behavior of this clause is determined by the setting of the IgnoreErrors property in the c8-services.xml service configuration file. By default, this property is set to 'false', which means that if an error occurs, the query is aborted.

Only the INSERT INTO statement or the EXECUTE STATEMENT DATABASE clauses are legal within the ON ERROR clause.

When the ON ERROR extension is provided without the error_insert_clause, errors found in the execution of this clause are ignored and CEP Engine continues to execute the project. Subsequent tuples arriving in any of the input streams of the from_clause continue to trigger execution of this clause. If these executions generate errors, they are also ignored by the CEP Engine.

When the ON ERROR extension is provided with the error_insert_clause, errors found in the execution of this clause are ignored and CEP Engine continues to execute the project. However a new tuple is also inserted into the errorstream (specified by error_stream_name). This tuple contains the fields selected in the error_select_list clause. The timestamp of this tuple is the same as the timestamp of the tuple in the from_clause that triggered the execution of this clause. These tuples are inserted in the errorstream in the same order as that of the original tuples in the from_clause that triggered the execution of this clause.

The ON ERROR extension cannot be used with both an EXECUTE STATEMENT DATABASE/EXECUTE REMOTE PROCEDURE clause and a REMOTE SUBQUERY/PROCEDURE clause in the same CCL statement. This causes errors. To avoid errors, the CCL statements must be broken into separate statements using an intermediate local stream.

<Param Name="MaxRetries">some_number</Param>

If the MaxRetries parameter is set to a value greater than 0, say N, then every time there is an error in an Execute Statement or Subquery statement that subscribes to this service definition, the Sybase CEP Engine automatically retries the execution of that statement up to a maximum of N number of times. If the execution of the statement fails for all the N number of times, an error tuple is inserted into the errorstream. The default value for MaxRetries is 0, therefore there is no retry.

Kdb+ and q

EXECUTE STATEMENT DATABASE clauses that modify kdb+ databases can contain statements in q language.

5+(1;2;3)

5+ ?MyStream.IntColumn1 ?MyStream.IntColumn2 ?MyStream.IntColumn3

{[params] select Price, Volume from trades where StockSym = `$params[0],
   Volume > `$params[1]} ?ParamStream.StockSym ?ParamStream.Volume

EXECUTE STATEMENT DATABASE "MyKDBService"
[[`trades insert (`datetime ; ` ; `real ; `short) $ ?Ts ?StockSym ?Price ?Volume]]
SELECT GETTIMESTAMP (QueryIn) AS Ts,
       StockSym AS StockSym,
       Price AS Price,
       Volume AS Volume
FROM QueryIn;

Examples

EXECUTE STATEMENT DATABASE "MyDatabase"
  [[UPDATE MyTable 
    SET MyTable.Price = ?CurPrice 
    WHERE MyTable.Symbol=?CurSymbol]]
SELECT
  StreamIn.Symbol AS CurSymbol, StreamIn.Price AS CurPrice
FROM StreamIn;

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;

Example:
EXECUTE STATEMENT DATABASE "StockTradeDB"
[[
    INSERT INTO TradeTable VALUE(?Symbol, ?Price, ?Volume, ?Ts)
]]
ON ERROR
INSERT INTO DBWriteErrorStream
SELECT Symbol, Ts, ERROR_MESSAGE()
CONTINUE
SELECT 
Symbol  as Symbol,
Price as Price,
Volume as Volume,
GETTIMESTAMP(InTrades) as Ts
FROM 
InTrades KEEP EVERY 1 minute;