|
The stored procedure for updates must return a result set that includes two sets of values:
The first set of values specifies the pre-image for the update (the values in the row the last time it was received from, or successfully uploaded to, the MobiLink server).
The second set of values specifies the post-image of the update (the values the row should be updated to in the consolidated database).
This means that the stored procedure for updates must return a result set with twice as many columns as the insert or delete stored procedure.
For a detailed explanation of how to define stored procedures for updates, see Scripted upload example.
The following example creates a table called t1 and a publication called p1. The publication specifies WITH SCRIPTED UPLOAD and registers the stored procedure t1_update as the update procedure. The publication specifies three columns to be synchronized: pk, c1 and c3. The update procedure returns a result set with six columns. The first three columns contain the pre-image of the pk, c1 and c3 columns; the second three columns contain the post-image of the same columns. Note that in both cases the columns are ordered as they were when the table was created, not as they are ordered in the CREATE PUBLICATION statement.
CREATE TABLE t1(
//Column ordering is taken from here
pk integer primary key,
c1 char( 30),
c2 float,
c3 double );
CREATE PROCEDURE t1_update ()
RESULT( preimage_pk integer, preimage_c1 char(30), preimage_c3 double,
postimage_pk integer, postimage_c1 char(30), postimage_c3 double )
BEGIN
...
END
CREATE PUBLICATION WITH SCRIPTED UPLOAD p1 (
// Order of columns here is ignored
TABLE t1( c3, pk, c1 ) USING (
PROCEDURE t1_update FOR UPLOAD UPDATE
)
)