Ad-hoc Subscriptions and SQL Language Support
Go Up to Change Views
Change Views can be subscribed to on a temporary basis using ad-hoc subscriptions. An ad-hoc subscription runs for the duration of a connection. The user subscribes directly to a schema object instead of a named subscription. This subscription is valid until the database connection is terminated or the user deactivates the temporary subscription during the connection. The following example shows a retooling of the ISQL command-line utility that supports changed data views.
A user must be granted temporary subscribe privileges on the schema object (i.e., a base table).
GRANT TEMPORARY SUBSCRIBE
Syntax for GRANT TEMPORARY SUBSCRIBE
GRANT TEMPORARY SUBSCRIBE[(<column_comma-list>)] ON <table_name> TO <user_name>;
REVOKE TEMPORARY SUBSCRIBE[(<column_comma-list>)] ON <table_name> FROM <user_name>;
The user issues a SET SUBSCRIPTION command as usual giving the name of the base table instead of a subscription name.
RETRIEVING CHANGED DATA VIEWS FROM ISQL
SET SUBSCRIPTION "Employees" ACTIVE;
SELECT NAME, DEPARTMENT, SALARY FROM "Employees";
COMMIT;
<Another user reassigns an existing employee to another department and gives another employee a raise>
SELECT NAME, DEPARTMENT, SALARY FROM "Employees";
<CHANGE> NAME DEPARTMENT SALARY
update joe sales 50000
update mary finance 75000
SET SAME;
SELECT NAME, DEPARTMENT, SALARY FROM "Employees";
<CHANGE> NAME DEPARTMENT SALARY
update <same> sales <same>
update <same> <same> 75000
COMMIT;
SET SUBSCRIPTION "Employees" INACTIVE;
ISQL has a collection of SET statements that toggle a display set. The SET SAME display toggle alternates between showing the column data value or its changes state of <same> or the changed data value. The CHANGE column is a pseudo column that is showing the type of DML statement that modified the column value(s). All of this change state is returned by the XSQLVAR.SQLIND member of the new XSQLDA structure.
Minimal support for changed data views is provided by InterBase SQL with the addition of a IS SAME or IS NOT SAME clause as the following example illustrates:
- Using IS NOT SAME in SELECT queries
<SELECT NAME, DEPARTMENT, SALARY FROM "Employees" WHERE SALARY IS NOT SAME;
<CHANGE> NAME DEPARTMENT SALARY
update mary finance 75000
We see that Joe's department reassignment is not returned since he received no compensation adjustment for a lateral move.