SQL Anywhere Mobile - filtering synced data in upload

Author

filtering synced data in upload_update?

Jim

2005-04-19, 8:24 pm

I have ML (and ASA) 9.0.2.2451

What if you want to filter the rows that get updated? For example, I only
wanted to update the rows where our client app had set the status to 1--not
all rows that had been modified. So I tried this in the upload_update:

UPDATE master SET "turnout"=?, "gate"=?, "route"=?, . . . WHERE "id"=?
AND "status">0

My sync wouldn't work until I got rid of the:

AND "status">0

Is there a way to accomplish this?

Thanks

Greg Fenton

2005-04-19, 8:24 pm

Jim wrote:
>
> What if you want to filter the rows that get updated? For example, I only
> wanted to update the rows where our client app had set the status to 1--not
> all rows that had been modified. So I tried this in the upload_update:
>

I'm not sure why you were getting an error based on the script snippet
you posted. We'd need to see the exact error message text.

There are at least two ways to handle this situation though. The first
is to use a stored procedure call for you upload_update script instead
of writing an UPDATE statement. In that stored procedure you can write
much more complex SQL than a single UPDATE statement.

A second way is probably the better one. Why bother uploading the row
at all if you don't want it? Try adding a WHERE clause to your
publication on the remote so that the publication will only upload the
row should it meet the particular criteria:

CREATE PUBLICATION my_pub (
TABLE table_a,
TABLE table_b,
TABLE master WHERE "status" > 0,
TABLE table_c,
...
);

Then only modified rows in the "master" table meeting the given criteria
will get uploaded. This is the better solution for a couple of reasons,
the main one being that you are only transfering (uploading) rows that
you *want* uploaded [better performance, less data to transfer, less
business logic to code in your synch scripts, etc....]

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Breck Carter [TeamSybase]

2005-04-20, 7:24 am

Please note that the 'AND status > 0' refers to the *current* value of
master.status on the consolidated database, not the column value being
uploaded.

Greg's suggestion about putting a WHERE clause in the publication is
best.

Breck

On 19 Apr 2005 14:13:16 -0700, "Jim" <jimsjbox@yahoo.com> wrote:

>I have ML (and ASA) 9.0.2.2451
>
>What if you want to filter the rows that get updated? For example, I only
>wanted to update the rows where our client app had set the status to 1--not
>all rows that had been modified. So I tried this in the upload_update:
>
> UPDATE master SET "turnout"=?, "gate"=?, "route"=?, . . . WHERE "id"=?
>AND "status">0
>
>My sync wouldn't work until I got rid of the:
>
> AND "status">0
>
>Is there a way to accomplish this?
>
>Thanks
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com

Breck Carter [TeamSybase]

2005-04-20, 7:24 am

Sponsored Links