Where do i find sample code for using SQL Remote hook procedures

[Former Member]

Hi,

where do i find samples that illustrates the use of the SQL Remote hook stored procedures? - how to process/intercept new changes (updates, inserts, deletes) - how to prevent/skip certain updates to the target database

I wasn't able to find any sample code. Thanks a lot Frank

[VolkerBarth]

It seems there are no (or few?) public samples available, which is a pity.

Nevertheless, I'd ask first what are you trying to achieve?

Basically, a hook procedure is just a procedure with a pre-defined name which is then called by SQL Remote - think of a callback.

What we are using such hook proedures for is basically one thing:

• The need to do DML statements within the contexts of a replication run that are replicated back to the message originator.

That has to do with the following SQL Remote rule:

SQL Remote ensures that statements are not sent to the remote user that initially executed them.

Here's a small sample:

Say, you have remotes that generate some kind of orders, and you want those orders to have a central order number or a central registration time once the order has been applied within the consolidated database. Until the order is applied at the cons, it would just have a provisory order number marked via a field OrderNrIsProvisory.

So you might have a trigger in the cons that runs when a new order is processed and would do an update on the order to make it a "central one", such as

    ...
    update T_Order
       set OrderNr = <...>, OrderNrIsProvisory = 0
       where OrderNrIsProvisory = 1;
    ...

Straightforward, but it won't work as expected within SQL Remote:

If that trigger is run within the contexts of DBREMOTE, it will not sent that UPDATE back to the remote the order was created, since DBREMOTE does not do that unless an update conflict was detected (see the cited ruel). As a consequence, the remote won't have the freshly updated values.

One solution here is to use the trigger for a different purpose: Do not change the row itself but do insert an entry into a help table (say T_NewOrder) that only exists on the cons (and therefore won't be replicated at all).

Then, use a hook procedure like sp_hook_dbremote_receive_end() to process that help table, such as

create procedure sp_hook_dbremote_receive_end()
begin
   for forNewOrder as crsNewOrder cursor for
      select OrderID as NewOrderID from T_NewOrder order by NewOrderID
      for update
   do
      update T_Order
         set OrderNr = <...>, OrderNrIsProvisory = 0
         where OrderNr = NewOrderID and OrderNrIsProvisory = 1;
      delete where current of crsNewOrder;
   end for;
end;

Note, DML actions run within one of the SQL Remote hooks run as normal code, i.e. they don't fall under the "Do not replicate back to the originator" rule. As such, the according UPDATE will be replicated to all according remotes, just as it would have been done in a normal SQL session within the remote.

That's because procedures specified in a hook are called on a separate connection.

HTH.

[Former Member]

Thanks Volker.

As information on SQL Remote hooks are sparse, I wasnt quite sure if its the right tool. Basically I want to intercept data that is coming from the remotes and forward it to a 3rd party system for processing purposes, i.e a way of capturing any change from the remote databases.

a. Is there more information, I am not clear on the parameters/syntax of the sp_hook_dbremote_receive_end procedure. Where do i get the table name and values that changed from ?

b. Is there a way to skip the processing/storing of the data that comes back from the remotes. I dont need it in the cons db, bc i am already processing it in the 3rd party system. All i want is to capture the delta changes easily from the remotes.

Thanks a lot in advance Frank

[VolkerBarth]

I frankly guess when you do not want SQL Remote to apply changes from a remote, then SQL Remote won't help you much. You would obviously "mistreat" that tool here, and I guess that would lead to more problems than benefits.

The hook procedures aren't called table-by-table, therefore they do not have entries for table names or the like in the #hook_dict table, and they do not "list" the changes values either. So hooks won't help you at all here.

What you "can do* with SQL Remote is using its verbose mode: With option -v -o <yourlog.txt>, it will output (among other information) all applied (or failed) SQL statements in the order they were generated in the according remote. (IIRC: You could also use SQL Remote with the compression option set to 0, that way the messages are not encoded and contain the SQL statements in readable form.)

---

However, that could be done as well when translating the log of a single database, which would not require the addition of a - here basically not needed - replication/sync system.