Change View API Support (API Guide)

From InterBase
Jump to: navigation, search

Go Up to Working with Dynamic SQL


Change Views API support is provided through the extended SQLVAR structure, XSQLVAR, via a new interpretation of the SQLIND member. To review, a developer places a pointer to a variable in XSQLVAR.SQLIND to request NULL state. When the query is executed, InterBase places a zero at that pointer address if the column value for the returned row is non-NULL and sets it to -1 if it is NULL.

Under the new interpretation, the dual concepts of NULL state and CHANGE state are overlayed in the SQLIND member variable. The lower bits of the SQLIND variable are reserved as column change indicators: Bit 0 indicates INSERT; Bit 1 indicates UPDATE; Bit 2 indicates DELETE and Bit 3 designates CHANGE. The logical bitwise OR of the 3 bits indicates a change of data of some kind, except that if all 3 change bits are set, it indicates that the value did not change (i.e., it is the "same" value as previously observed ). To check for a NULL state, a developer should check if SQLIND is less than 0 rather than for an explicit -1 value. A value greater than or equal to 0 stored at the SQLIND address indicates a non-NULL value.

Change Status of SQLIND Values
NOT NULL NULL CHANGE STATUS

SQLIND = 0

SQLIND = -1

Legacy SQLIND values returned when Change Views and Subscriptions not used. Change status is undefined.

SQLIND >= 0

SQLIND < 0

New SQLIND values. Change status may be present and can be tested (as below).

SQLIND > 0

SQLIND < -1

New SQLIND values. Change status may be present and can be tested (as below).

Once it is determined that the SQLIND value contains change status, it is necessary to clear the possible presence of the SQLIND_NULL bit before testing for specific state. The following SQLIND_xxxxxx defintions are included in <ibase.h>. By performing various bitwise OR operations on these definitions, it is possible to test for interesting change status.

SQLIND Definitions included in ibase.h
SQLIND VALUE CHANGE STATUS

SQLIND_CHANGE_VIEW

The column value is <same> value; it did not change.

SQLIND_CHANGE_VIEW | SQLIND_CHANGE

It is <unknown> whether the column value changed.

SQLIND_CHANGE_VIEW | [ SQLIND_CHANGE ] |

{ SQLIND_INSERT | SQLIND_UPDATE| SQLIND_DELETE }

Some combination of SQL operations changed the column value.

SQLIND_CHANGE_VIEW is a tag bit to indicate the presence of change status. The remaining definitions correspond to the FOR ROW clause options of CHANGE, INSERT, UPDATE, and DELETE, respectively. The CHANGE option can cause <unknown> change state because a changed row is returned as soon as the presence of any column value change is detected. Those column values that did change return definite state, while the others return <unknown> state.

Changes in data made by a subscription is not visible by that subscription when observed at a later time. This is a bow to a possible application of change views as a component in bi-directional replication. When one side of a replication pair updates the other side with their local changes, that side does not want those changes to be reflected back when the other side replicates in the reverse direction.

Advance To: