The Read From Database adapter reads information from a table on an external database server and sends that data to a stream.
This adapter has some of the characteristics of the Poll From Database adapter and some of the characteristics of the Read From CSV File adapter. The table below compares and contrasts some key characteristics of these three adapters.
|
Poll From Database adapter |
Read From Database adapter |
Read From CSV File adapter |
|---|---|---|
|
This adapter is used primarily for processing live data. |
This adapter is used primarily for replaying historical data or running simulations, rather than for processing live data. |
This adapter is used primarily for replaying historical data or running simulations, rather than processing live data. |
|
This adapter does not support Accelerated Playback. |
This adapter supports Accelerated Playback. |
This adapter supports Accelerated Playback. |
|
You specify the entire database query. |
You specify the database table name (or view name) and the WHERE clause. |
You specify the name of a file that contains the data. |
|
You can specify the order of the records by using an ORDER BY clause. |
The adapter automatically includes an ORDER BY clause that orders by the Timestamp Field. See below for a description of the Timestamp Field property for this adapter. |
The order is determined by the physical order of the rows in the data file and by the row timestamps in the data file. |
See "Sybase CEP Engine Third-Party Software Dependencies" in the Sybase CEP Installation Guide for information about supported databases.
This adapter is an in-process adapter. The server starts it automatically when necessary. Attach this adapter to a stream by using commands inside Sybase CEP Studio. Properties for this adapter are set using Sybase CEP Studio. Alternatively, use the ATTACH ADAPTER statement to attach the adapter to a stream and specify the values of the adapter properties.
The adapter properties are listed in the table below.
| Property Name (screen) | Property Name (Attach Adapter) | Type | Description | Optional/Required |
|---|---|---|---|---|
| DBName | DBName | String | Indicates which database to execute the query on. This is the name of a service that is defined in the c8-services.xml file, not the name of the database. The service information includes information about how to connect to the external database server and the name of the database to connect to. | Required |
| Table or View | Table | String | The table from which to retrieve the data. This can be a table or a view. | Required |
| Where Clause | WhereClause | String | This clause limits the result set. This WHERE clause is applied on the remote database server, and can reference any of the fields in the remote table, even if those fields are not also in the schema of the stream into which the data is inserted. You should omit the keyword WHERE. For example, enter "X = Y" rather than "WHERE X = Y". | Optional |
| Loop Count | LoopCount | Integer | How many times to loop through this data. If the field is left empty, the default value is 1. | Optional |
| Rate | Rate | Float | How fast to read the records. If this field is not set, the timing of the data is based on the Timestamp field. | Optional |
| Timestamp Column | TimestampColumn | String | This is the name of the timestamp/datetime column. This is the name of the field in the external table, not the name of the field in the Sybase CEP schema. This column's data type should be compatible with Sybase CEP's TIMESTAMP data type. See Datatype Mappings for tables that show which database data types correspond to Sybase CEP's TIMESTAMP data type. The actual data type can have a resolution less than or equal to TIMESTAMP. If the resolution is greater than TIMESTAMP (for example, nanosecond vs. microsecond), you may get occasional cases of missing or duplicate records. | Required |
| Timestamp column initial value | TimestampColumnInitValue | Timestamp | If this field is set, the first time that the query is executed, the adapter retrieves only records whose timestamp field contain values greater than the value specified in this Initial Value field. Data values equal to the Initial Value are not be retrieved. The format of the date should be "YYYY-MM-DD HH24:MI:SS.FF", where "FF" indicates microseconds (up to 6 digits). | Optional |
Each input stream has a property (see the stream's Properties tab in Studio) that can specify whether to use the current server timestamp value instead of the row timestamp set by the adapter. If this stream property is set to true, it overrides any row timestamp set by the adapter.
The Database Name property indicates which database to connect to. Despite the fact that we call this property the Database Name, it is actually the name of a service thatmust be configured in the c8-services.xml file. The service name is not necessarily identical to the name of the database. Each service has information about how to connect to the external database server and the name of the database on that server. For more information about configuring a service to provide access to a database on an external database server, see the Sybase CEP Installation Guide .
The database query is constructed from the Table Name, the Where clause, and the Timestamp Field. The query selects the columns of the table (or view) that match the names and data types of the fields in the target stream. This means that the stream schema must either match the table schema, or be a subset of the table schema.
Specify which field in the table should be used as the Timestamp field. The Timestamp field acts like the Row Timestamp in a file read by a Read From CSV File adapter. The Row Timestamp controls the order and relative timing with which rows are processed. The adapter uses the Timestamp field as the ORDER BY clause in the query. Once the rows arrive at the server, the server treats the rows as though they arrived at the same relative times as the Timestamp field (Row Timestamp) specifies. For example, if the row timestamps are 10,000 microseconds apart, then the server treats the rows as though they had arrived 10,000 microseconds apart. This Timestamp field can be one of the columns of the table; the Timestamp field can also be, but is not required to be, one of the columns in the stream schema.
The Loop Count property allows you to tell the adapter to read the same data more than once. This is useful in prototyping if you want to do a test run with a large amount of data but you only have a small amount of data available.
When looping is used, the row timestamps in the data are incremented appropriately for each loop so that time does not appear to jump backwards when you finish one loop and start the next (which of course involves re-reading rows you have already read).
For each loop iteration, the adapter re-runs the query. This allows the adapter to avoid buffering old query results, which would significantly hamper performance for large result sets.
Since the query is re-executed each time, and since the table data can change between queries, the data may change between loop iterations.
When using looping, you can set property values to specify whether you want the entire set of records read on the first cycle, or whether you want to read only records that have a Timestamp field value greater than a value that you specify in the Timestamp Field Initial Value.
To read the entire table on the first cycle, leave Timestamp Field Initial Value empty. To read just the records with Timestamp field values greater than a specified value (for example, greater than 2007-09-01 11:30:00.0), put that specified value into the Timestamp Field Initial Value property.