Introduction
Burr IST Ltd have been working with a wide range of customers since 2001 on all sorts of reporting, integration and analysis projects using SQL-Hub, JetViewer and other products.
Over the years we have come across a lot of the same challenges and looked at many ways of solving them
These articles explore some of the things that are often discussed and the pros and cons of a number of different solutions. These discussion are intended to give an understanding of some different types of scenarios and how they can be addressed.
Detecting Changes in Database Data
Often we need to find out which records in a database have changed since a certain point (when we last looked).
There are a number of ways of doing this and the choice of the best solution depends on the circumstances.
1) The records are timestamped
If the records in a database are timestamped (either with an automatic database timestamp or reliably by an application working with the database) then it's easy to see which records have been changed after a particular point.
2) Compare new version with the old version
If there is a copy of the old version of a table it is quite straight forward to compare the values field by field to see if a record has changed. However to do this in the case of a remote database all of the data needs to be transfered before the comparison can be made.
3) Compare new CRC with the old CRC
Rather than comparing the actual values of all the fields a CRC value can be calculated over the record - if the record changes the CRC value changes. If the old CRC value for a record has been stored a comparison with the current CRC will show which records have been modified. The CRC can be calculated at source (e.g. on a remote server) so there is no need to transfer the whole record to check for changes, only the CRC value.
MS-SQL:
SELECT OrderID,checksum( OrderID,ProductID,[Value],OrderDate)
FROM Orders
MySQL:
SELECT OrderID ,crc32(CONCAT_WS('#',OrderID,ProductID,Value,OrderDate))
FROM Orders
SQL-Hub has implementations of methods 1 and 3 - but only when used in a custom deployment
Note that if records in the source database can be deleted you will also need a way to detect deletions - typically by comparing a list of Unique IDs either with the target database or with an "old" list of IDs from the source system.