Table of Contents

Search

  1. Preface
  2. RulePoint
  3. RulePoint Concepts
  4. Using RulePoint
  5. RulePoint Objects
  6. Working with Topics
  7. Working with Connections
  8. Working with Sources
  9. Working with Responders
  10. Working with Responses
  11. Working with Watchlists
  12. Working with Analytics
  13. DRQL
  14. Working with Rules
  15. Working with Alerts
  16. Setting Access Controls
  17. Troubleshooting RulePoint Issues
  18. Connecting to an Ultra Messaging Application
  19. Creating an Ultra Messaging JMS Source

User Guide

User Guide

SQL Source Example

SQL Source Example

A stock broker wants to process stock market data feed. The stock broker wants to retrieve information about all the stocks or some preferred stocks based on the requirement.
You have a Stock table with the Symbol, Price, Volume, and RecordTime information. Consider the Stock table has the following stock information:
Symbol
Price
Volume
RecordTime
ABCD
10
5000
07-NOV-10 10.35.25.000000000 AM
EFG
22
7300
07-NOV-11 10.35.36.000000000 AM
XYZ
19
1200
27-NOV-12 10.38.25.000000000 AM

Simple SQL Query

You can use the following SQL query to retrieve all information from the Stock table and publish events for each row returned from the SQL query:
select * from Stock
The SQL query in the SQL source service results in three RulePoint events, one event for each row in the Stock table. Each event has four properties each, one for each column name in the Stock table. The SQL query extracts the same data every time it runs unless you constrain the query based on time, date, or some other criteria.
You can constrain the SQL query by defining a volume limit.
SELECT * from Stock WHERE Volume > 3000
This SQL query results in two RulePoint events, one event for symbol ABCD and the other for symbol EFG. Each event has four properties each, one for each column name in the Stock table.
You can also constrain the SQL query by defining a limit in the table property.
select symbol, price from stock where rownum <= 2
The SQL query results in two RulePoint events for the first two rows retrieved from the Stock table. Each event has two properties, one each for Symbol and Price.

Parameterized SQL Query

You can parameterize the SQL query and use the columns returned from the SQL query as inputs in the statement.
select symbol, price, recordtime from stock where recordtime > TO_DATE (<<recordtime>>, 'DD-MON-YY')
The parameter in the query is
recordtime
. You can define the parameter as a name-value pair. For example,
recordtime=06-NOV-12
.
The SQL query results in RulePoint events for all records from the Stock table that are saved after the
recordtime
.

Update SQL Query

The SQL Source service can be configured to pull only new or updated records from the database based on the last scheduled run of the SQL service. The filter to select only the updated records is built into the SQL query based on information retained and provided by the SQL source itself.
You can use the SQL query to select only unprocessed records.
In the following example, a stock table has a
processed_ind
column indicating which records have been processed,
F
for not processed and
T
for processed:
SELECT id, processed_ind, to_char(timestamp,'MM/dd/yyyy HH24:MI:SS') as timestamp, symbol, price from processed_stock WHERE processed_ind='F'
You can use the following SQL query to update the
id
if the
processed_ind
column is processed:
UPDATE processed_stock set processed_ind='T' WHERE id=<<id>>
When you update the SQL query, you do not need to initialize the
id
.

0 COMMENTS

We’d like to hear from you!