Query using a time stamp

You can include logic to only publish changed data. This is required to sync only the changes to another system or to decrease the number of messages or the message size. It is also required to define an event monitor on data from a database, otherwise the same alert is reported for each time the same data is published.

ION does not prescribe the mechanism to be used for detecting changes, it depends on your specific situation. For example, the data in your database can or does not have a time stamp.

In this example we assume a time stamp is available, and we keep track of the last moment data was read. Therefore, we introduce a new table: LastChangeRetrieval.

This table shows the layout of the LastChangeRetrieval table:

LastChangeRetrieval
tableName	lastRetrieval
Orders	30 Mar, 2011 18:00:00

To use this, multiple statements are required. Consequently you cannot define these statements directly in ION. Instead, you must call a stored procedure from ION.

The stored procedure GetOrders can contain this code:

BEGIN
  -- Prevent extra result sets from interfering with SELECT statements
  SET NOCOUNT ON;

  -- Declare variables
  DECLARE @currentTime as datetime;
  DECLARE @lastTime as datetime;

  -- Determine time range
  SET @currentTime = CURRENT_TIMESTAMP;
  SET @lastTime = (SELECT lastRetrieval FROM LastChangeRetrieval 
                   WHERE  tableName='Orders');
  IF (@lastTime is null) 
  BEGIN
    -- Insert new row for first time retrieval
    SET @lastTime = 0;
    INSERT INTO LastChangeRetrieval (tableName, lastRetrieval) VALUES ('Orders', 0);
  END

  -- Select the data
  SELECT MySalesOrder.orderNumber AS orderID, MySalesOrder.orderStatus, 
         MySalesOrder.orderDateTime, MySalesOrder.customer,
         MySalesOrderLine.lineNumber, MySalesOrderLine.item, 
         MySalesOrderLine.quantity, MySalesOrderLine.price
  FROM   Orders AS MySalesOrder, OrderLines AS MySalesOrderLine
  WHERE  MySalesOrderLine.orderNumber = MySalesOrder.orderNumber and
         MySalesOrder.lastModification >= @lastTime and 
         MySalesOrder.lastModification < @currentTime
  FOR XML AUTO, ELEMENTS, root('DataArea');

  -- Update status
  UPDATE LastChangeRetrieval SET lastRetrieval = @currentTime
  WHERE tableName ='Orders';
END

Then in ION, you define this statement to read the database:

EXEC GetOrders;

The output is the same as in the previous example, except that at the next scheduled read action, only the items having a newer time stamp are included.

To prevent memory exceptions, limit the amount of data that is read at once.

See A simple query.

When you use a database connection point for the first time, a load of data might be retrieved, because none of the existing data was retrieved before. In that case you can temporarily use a small schedule interval in the database connection point, to gradually publish the complete data set.

The inserts or updates you perform in your stored procedure are fully transactional. They are only committed to the database if the resulting data is posted successfully by ION in a persistent store. Consequently, no data is lost.