InterBase Change Views – Part 3 – Using a change view
In Part 1 I explored the concept of change views, before how to create a Change View ready for use in Part 2. In Part 3 we are going to explore how to use a Change View to collect data that has changed in your subscription.
Using an InterBase Change View to fetch data deltas
Once a Change View Subscription has been created and access to it has been granted (to either users or roles), then its time to start using your Change View!
The following steps are the high level overview of using a Change View.Start a Transaction in SnapShot isolation mode. Set the Subscription active Run your select statement to fetch you delta Once you have collected the delta commit your transaction to bring your Change View up to date.
OK a bit of background as we all love to know how things work..
InterBase is powered by a multi-generational architecture which is exceptionally useful when data integrity is important. imagine you need to run multiple financial reports while the system is live; Imagine a connection drops part way through a big data change how do you know what has changed? Multi-generational architectures and transactions makes this possible.
Using a transaction set to SnapShot mode we have a stable point in time that means even if records are updated at the same time as your transaction, they are not missed. When you start your next transaction you will locate those concurrently made changes! – imagine trying to do that with date time stamp tracking!
This means you can have multiple users updating the data at the same time safely without having to lock the tables
Setting the subscription active
At this point, lets pretend we have a STOCK table and we run the following statementSelect * from STOCK
the data returned would be all the STOCK records.
How do we get from here to just getting changed data deltas? Well, first we need to activate the subscription and provide an ID that we are going to track against.set subscription sub_stock at 'DeviceID' active;
With the subscription “sub_stock” active, running the initial statement will again return all records as its the first time data has been requested in the subscription.
At this point there are two possible next steps: Commit or Rollback.
Rollback of the transaction will ensure the data is provided again, Commit will bring the Subscription up to date for the start of the transaction.
Running the Select statement again in an active transaction with the subscription active will now return only delta’s
Preventing you data changes showing in your Change View delta
So we have covered selecting data, but what about updating data? How do you stop the changes to data you make appearing in your subscription yet still available to others? Well simply, make the changes while your subscription is active.Start a Transaction in SnapShot isolation mode. Set the Subscription active (using your ID) Run your update statement to modify the data Commit your transaction.
Introduction to Change Views Video on Embarcadero website
The post InterBase Change Views – Part 3 – Using a change view appeared first on Stephen Ball's Technical Blog.
Please login first in order for you to submit comments