DATABASE subquery

Syntax

( DATABASE "service"  schema_clause [[statements]]
[cache_clause] ) [AS] alias

Usage

You use a database subquery in a FROM Clause: Database and Remote Subquery Syntax. This specialized subquery contains statements that are passed to an external relational database server or to a CCL public window. The statements select data from the external database tables or public windows contained in the project specified by the service entry, and allow you to access the data in a CCL query. Before using a database subquery, perform the following steps:

• Configure a connection between an external relational database and Sybase CEP Engine, as explained in the Sybase CEP Installation Guide , or, in the case of public windows, create the window, using the Create Window statement.

• Set up a service entry for the external database containing the tables, or the project containing the public windows you want to query in the file c8-services.xml. For more information about configuring Sybase CEP Engine database and public window services, see the Sybase CEP Installation Guide .

The schema you specify must provide a column name and data type for every column you want to retrieve from the database or public window. The column names in the schema do not have to match the column names in the database or public window, but the data type for each column must be a CCL data type that is compatible with the data type of the corresponding column in the database or public window. In cases where data types used by the external database server do not perfectly match the data types used by Sybase CEP Server, Sybase CEP Server automatically converts the values. See Conversion Between CCL and ODBC and Oracle and Conversion Between CCL and Q for more information.

Sybase CEP Engine sends the text you specify (as statements) to the external database or project containing the public window for execution after replacing references to CCL columns and parameters with the appropriate values. Sybase CEP Engine does not interpret the statements beyond replacing the column and parameter references, but you cannot include statements that modify the database or public window. For information about writing to or deleting from an external database, see Database Statement. If you are querying a public window, your statements must be made up of SQL statements. See SQL for more information.

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

Access the retrieved data using the alias and column names previously specified.

Within the FROM clause, always define the database subquery in a join with a single data stream. This join can be either an inner or an outer join. Define inner joins with either a comma-separated syntax, or with the use of the JOIN keyword. When you use the comma-separated syntax, you define the join condition in a WHERE clause. When you use the JOIN keyword syntax, however, you cannot use the CCL ON clause with non-database subquery joins. Instead, define the join condition with an SQL WHERE clause inside the SQL statements.

ON ERROR extension

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

( DATABASE "service"  schema_clause [[statements]]
[cache_clause] ON ERROR [error_insert_clause] CONTINUE ) [AS] alias

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.

When the ON ERROR extension is provided without the error_insert_clause and there is an error executing the subquery, the error is ignored by the Sybase CEP Engine and the subquery execution is considered as having returned no results. Subsequent tuples arriving in the joining stream continue to trigger execution of the subquery. If these executions generate errors then these errors are also ignored by the Sybase CEP Engine.

When the ON ERROR extension is provided with the error_insert_clause and there is an error executing the subquery, a new tuple is inserted into the errorstream (specified by error_stream_name). This tuple contains values of 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 joining stream that triggered the execution of this subquery. These tuples are inserted in the errorstream in the same order as that of the original tuples in the joining stream that triggered the execution of this subquery. When there is no error, no tuples are inserted in the errorstream. The responses containing errors are not cached. If there is a subsequent tuple with the same binding key, it triggers another execution of the database subquery.

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.

Set the MaxRetries parameter in the service definitions for Database to determine how many times Sybase CEP Engine should retry statement execution after experiencing errors.

<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 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, that is, by default there is no retry.

CCL References within SQL Statements

The statement within the database subquery can refer to column names in the data stream to which the database subquery is joined, as well as to CCL module parameters. CCL data stream column references within the SQL query use the following syntax to distinguish them from internal database or public window columns:

?
data-stream-name
.
column-name

OR

?
data-stream-alias
.
column-name

CCL module parameters can be referenced within the SQL query using the following syntax:

?$
parameter-name

When the SQL statements inside your database subquery refer to a public window, the references must use the following syntax:

Restrictions

Queries that include database subqueries are subject to the following restrictions:

• A query that includes a database subquery must have exactly one other data source.

• The non-database, non-public window data source must not be windowed.

• In the statement, all names of stream columns must be prefixed with the data stream name or alias.

• A left outer join that joins a database subquery to a data stream must list the data stream on the left; a right outer join that joins a database subquery to a data stream must list the data stream on the right.

• No full outer joins are allowed with database subqueries.

• For a discussion restrictions specific to kdb+, see kdb+_and_q.

• The use of the ON clause is not allowed with database subqueries.

• If you are querying a public window contained in a submodule, and the public window reference is the last component of your SQL query, you must separate the closing bracket (]) of the window reference from the closing double bracket (]]) of the statements by one or more spaces. A triple bracket (]]]) generates an error.

• If you are querying an external database, the exact syntax allowed between the double brackets is controlled by the target database. In particular, be aware that the target database may not support comments.

• Sybase CEP does not support stored procedures in database subqueries that connect to an external database using the Oracle native driver.

See Also

• Create Schema Statement

• Database Statement

• CACHE

• SCHEMA

• SQL

Examples

This example executes a database subquery on the MyDB database server. The file valuation.ccs contains the schema to be used with the imported data. The data retrieved from the database is then references as the Shares_outstanding column.

INSERT INTO Valuation 
SELECT T.Symbol, T.Price, S.Shares_outstanding,
        T.Price * S.Shares_outstanding 
FROM TradeStream T,
    (DATABASE "MyDB" SCHEMA 'valuation.ccs'
    [[SELECT * FROM Stocks WHERE ?T.Symbol=Stocks.Symbol ]]) AS S

This example queries a public window named GroupsWindow in the Groups submodule of the project's main module.

INSERT INTO WinOut 
SELECT pw.GroupID, pw.GroupName, pw.SeverityAssessmentLevel,
       pw.PrincipleAffiliation 
FROM MyStream,      
     (DATABASE "SecurityThreatGroups"       
      SCHEMA (GroupID STRING, GroupName STRING, 
              SeverityAssessmentLevel STRING, PrincipleAffiliation STRING)  
      [[SELECT GroupID, GroupName, SeverityAssessmentLevel, 
               PrincipleAffiliation         
        FROM [Groups/GroupsWindow] ]]) AS pw ;

INSERT INTO OutTradesWithVwap
SELECT    InTrades.*,    Vwap.*
FROM
    InTrades,
    (DATABASE "ExamplesDB"
     	SCHEMA (Vwap FLOAT)
     	[[
     		SELECT Vwap
        	FROM Vwap
        	WHERE Symbol = ?InTrades.Symbol
        	ORDER BY Ts DESC
        	LIMIT 1
     	]]
    		CACHE
        	MAXIMUM AGE 1 minute,
        ON ERROR
        INSERT INTO errorstream
        SELECT InTrades.*, ERROR_MESSAGE()
        CONTINUE;
) as Vwap