Ad-hoc Subscriptions and SQL Language Support

From InterBase
Jump to: navigation, search

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.

Advance To: