Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in August and this forum will be retired.

How can I make the synchronization to download different records based on user id? Now i pass the user id as an authentication parameter ("user_id" = {ml a.1} )but when the logged in user on the mobile device is changed the {ml s.last_table_download} is not changed according to the user and does not download all the records for the current user

asked 11 Feb '13, 02:17

katalun4o's gravatar image

accept rate: 85%

Just to understand: Is the device used by several users during a period of time (i.e. they share the device), or is only one user using (aka "owning") the device, and now and then it will be passed over to a new owner?

(13 Feb '13, 03:42) Volker Barth

The same device is used by several users. It is about drivers that deliver packages, and every day the same device could be given to different driver. Now it works fine if the application is installed for the 1st time and works with one user, but when the user is changed it becomes synchronizing using the last download date from the previous user. I am trying to do it as Graham said using modify_last_download_timestamp event in Mobilink(SybaseCentral). I tried writing a stored procedure as it is said in the documentation but i got errors

CREATE PROCEDURE ModifyDownloadTimestamp @download_timestamp DATETIME OUTPUT, @user_name VARCHAR( 128 ) AS BEGIN SELECT @download_timestamp = '1999-01-01' END

and in mobilink

modify_last_download_timestamp​ exec dbo.ModifyDownloadTimestamp {ml s.last_table_download}, 'sa'

(13 Feb '13, 05:06) katalun4o
Replies hidden

What type of consolidated database are you using?

It looks like you followed the documentation example for SA, ASE or MSS. Since you are using named parameters, you don't need the user parameter, and this was the logic I was suggesting (untested):

CREATE PROCEDURE ModifyDownloadTimestamp
    @download_timestamp DATETIME OUTPUT,
    @changed_user VARCHAR(128)
    IF @changed_user = 'true'
        SELECT @download_timestamp = '1900-01-01 00:00'
        -- Don't need to change timestamp, so ELSE not needed

Assuming your remotes set authentication parameter 2 to 'true' for just the first sync after changing user, your modify_last_download_timestamp script would be:

'exec ModifyDownloadTimestamp {ml s.last_download, a.2}'
(13 Feb '13, 13:38) Graham Hurst

You must be using timestamp-based downloads as well as subset by user. When the user changes, you want all the rows for the new user, but currently get just those changed since the last sync.

One way to solve this is to add another authentication parameter to indicate a user change and add a modify_last_download_timestamp event that returns '1900-01-01 00:00' if the authentication parameter is true, and otherwise returns just the passed-in timestamp.

If you also want to remove the previous user data from the remote but not from the consolidated, you can temporary stop synchronizing deletes.

FYI, we call this type of requirement the "territory realignment" problem.


permanent link

answered 12 Feb '13, 16:56

Graham%20Hurst's gravatar image

Graham Hurst
accept rate: 29%

Thank you for the answer. Can you give me an example how the modify_last_download_timestamp​ script in sybase central should look like? Should it be a select statement, update a value or just set a parameter? i found only samples with a stored procedure about that

(12 Feb '13, 17:47) katalun4o
Replies hidden

The Results section of the documentation says:

SQL scripts for the modify_last_download_timestamp event must be implemented as stored procedures.

That documentation (which I also linked in my answer) gives a SQL example. As Russ pointed out, you haven't mentioned what type of consolidated database, so the exact SQL syntax you need may be different.

If you are using a synchronization model in the MobiLink plug-in for Sybase Central, then you just put the CALL statement in the Events editor, and (assuming a SQL Anywhere consolidated) add the stored procedure using the SQL Anywhere plug-in for Sybase Central.

(12 Feb '13, 18:00) Graham Hurst

FYI, we call this type of requirement the "territory realignment" problem.

According to katalun4o's description, I would not call that "territory realignment", i.e. it's not the shift from one "user-data partition" to another one; it's just that the device may be used by one of several users each day.

So, in case the number of co-users is limited, and the mobile application may itself restrict the data access to the "data of the current user", one might also use an ordinary timestamp-based sync to upload/download data of all "possible" users in one go, i.e. do not try to sync only for the current user.

(13 Feb '13, 07:00) Volker Barth
Replies hidden

" i.e. do not try to sync only for the current user." So you suggest to download the data for all the users?

(13 Feb '13, 07:38) katalun4o
Replies hidden

Not really, simply as I don't know the situation well enough, i.e. is it feasible and allowed to do so (size of sync data, time needed, number of "possible users per device" compared to "all users", privacy of data per user, ...)?

I'm just trying to make the point that (from my understanding) you are basically are in a situation similar to one where a office desktop syncs with a headquarter, and the office desktop may be shared among multiple users. In such a situation, one would usually sync data for all relevant users and would rely on the app security to decide whether a particular user can access other users's data...

It's also the question whether data entered (but not already uploaded) on the device by user1 must be uploaded if user2 takes over the device. IMHO, a log-based sync would always upload "fresh" data from user1, too, so there is no explicit "user-specific" upload usually. And if there is no user-specific upload, why bother with a user-specific download (in contrast to a device-specific download)?

As stated, that's just my limited understanding of the situation - handle with care, as they say:)

(13 Feb '13, 08:02) Volker Barth

I forgot to say that the remote database is on android mobile phones, that are working with 3G network and I want to download only the records for the currently logged in user, because there may be 20 different users and i don't need the data for all of them on the device..

(13 Feb '13, 08:19) katalun4o

it's not the shift from one "user-data partition" to another one; it's just that the device may be used by one of several users each day.

As I read it, when the user changed katalun4o wanted all of the new user's rows to be downloaded.

As you point out, if all remote devices are used by the same set of users and the device has sufficient storage, it might be more efficient to sync the rows for all the users of that remote, and just have the application filter for the current user. That would save network bandwidth and reduce sync times after switching user.

(13 Feb '13, 13:56) Graham Hurst
showing 4 of 7 show all flat view

We'll need more information to get started. What version of MobiLink are you using? What consolidated database type are you synchronizing against?

If your version of MobiLink server supports named parameters, you will need to reference at least two of them: {ml s.username} and {ml s.last_table_download}. With these you create a download_cursor as follows, assuming you have the username directly in the table (you can join to another table if you need to but I'll leave that as an exercise):

select pk, c1, c2 from my_table where username = {ml s.username} and last_modified >= {ml s.last_table_download}
  • Russ
permanent link

answered 11 Feb '13, 09:54

RussC_FromSAP's gravatar image

accept rate: 18%

This is how I finally did it :) Thanks for the help to Graham and Volker. I created a new table - UsersLastSync with 2 columns - UserID and LastSync(Datetime) After that in mobilink i override 2 events - end_publication and modify_last_download_timestamp. After every download i write in that table the user and the last download date and before each download i get the last_download_date from that table


if {ml s.publication_name} = 'myPublicationName'
    if exists (select * from UsersLastSync where userid = {ml a.1})
         UPDATE UsersLastSync SET LastSync = {ml s.last_publication_download} WHERE UserID = {ml a.1}
        INSERT INTO UsersLastSync (UserID, LastSync) VALUES ({ml a.1}, {ml s.last_publication_download})


exec ModifyDownloadTimestamp {ml s.last_download},{ml a.1}

and the procedure ModifyDownloadTimestamp:

ALTER PROCEDURE ModifyDownloadTimestamp
    @download_timestamp DATETIME OUTPUT,
    @UserID numeric(18,0)
    select @download_timestamp = lastsync from userslastsync where userid = @UserID

    if  @download_timestamp is null
        SET @download_timestamp = '1900-01-01'
permanent link

answered 13 Feb '13, 18:10

katalun4o's gravatar image

accept rate: 85%

edited 13 Feb '13, 18:30

Graham%20Hurst's gravatar image

Graham Hurst

I think your end_publication code should be in the begin_publication event instead, since the current sync should be using the s.last_publication_download (or s.last_download) value.

As you have it, I think each sync will download the rows modfied since two syncs ago instead of just those modified since the last sync.

See How download timestamps are generated and used.

(13 Feb '13, 19:17) Graham Hurst

BTW, your solution could end up with all user's rows on the device. It will at least have all the rows for each user that has used the device, up to the time that each last synchronized. So your application will have to filter by user anyway.

(13 Feb '13, 19:34) Graham Hurst

Yes you are correct, It should be in begin_publication event. My application filters by user so it is not a problem.

(14 Feb '13, 08:27) katalun4o
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 11 Feb '13, 02:17

question was seen: 1,920 times

last updated: 14 Feb '13, 08:27