Limit download_cursor set by UltraLite value

[Former Member]

Hello everyone,

as there are a lot of data in my database tables, I want to limit the data synchronized to my client by user defined parameters. These parameters are set on the client where my application is running on.

For better understanding: I choose an user (from table 'Employee') on that client which has a unique ID. This ID references other data rows in other tables.

So the first synchronization is downloading table 'Employee' without any restrictions. As at startup the employee ID is 0, all other tables will not find any data rows.

When I choose an employee, the employee ID will change to an other value (1889 for example). Now the second synchronization should provide all data rows where ID 1889 is set to.

From documentation I know that the MobiLink events can handle user defined parameters (f. e. {ml u/ui.param1}) but I couldn't find out how to set those parameters from clientside.

My MobiLink event script:

begin_connection
SET NOCOUNT ON

Address (MyDatabase): download_cursor
SELECT "MyDatabase"."dbo"."Address"."ID",
    "MyDatabase"."dbo"."Address"."MandantID",
    "MyDatabase"."dbo"."Address"."AddressID",
    "MyDatabase"."dbo"."Address"."AddressNr",
    "MyDatabase"."dbo"."Address"."AddressType",
    "MyDatabase"."dbo"."Address"."MatchCode",
    "MyDatabase"."dbo"."Address"."Title",
    "MyDatabase"."dbo"."Address"."Name1",
    "MyDatabase"."dbo"."Address"."Street",
    "MyDatabase"."dbo"."Address"."ZipCode",
    "MyDatabase"."dbo"."Address"."City"
FROM "MyDatabase"."dbo"."Address"
WHERE "MyDatabase"."dbo"."Address"."last_modified" >= {ml s.last_table_download} AND
    "MyDatabase"."dbo"."Address"."ID" IN (
        SELECT ID FROM fn_SyncFilterJob('AddressID02', {ml ui.varClientID}, {ml ui.varUserID})
    UNION
        SELECT ID FROM fn_SyncFilterJob('AddressID03', {ml ui.varClientID}, {ml ui.varUserID})
    UNION
        SELECT ID FROM fn_SyncFilterJob('AddressID04', {ml ui.varClientID}, {ml ui.varUserID})
)

Employee (MyDatabase): download_cursor
SELECT "MyDatabase"."dbo"."Employee"."ID",
    "MyDatabase"."dbo"."Employee"."MandantID",
    "MyDatabase"."dbo"."Employee"."AddressID",
    "MyDatabase"."dbo"."Employee"."EmployeeNr",
    "MyDatabase"."dbo"."Employee"."Login",
    "MyDatabase"."dbo"."Employee"."Password",
    "MyDatabase"."dbo"."Employee"."Pin",
    "MyDatabase"."dbo"."Employee"."LastName",
    "MyDatabase"."dbo"."Employee"."FirstName",
    "MyDatabase"."dbo"."Employee"."Street",
    "MyDatabase"."dbo"."Employee"."ZipCode",
    "MyDatabase"."dbo"."Employee"."City",
    "MyDatabase"."dbo"."Employee"."ConcernID",
    "MyDatabase"."dbo"."Employee"."Resource"
FROM "MyDatabase"."dbo"."Employee"
WHERE "MyDatabase"."dbo"."Employee"."last_modified" >= {ml s.last_table_download}


Job (MyDatabase): download_cursor
SELECT "MyDatabase"."dbo"."Job"."ID",
    "MyDatabase"."dbo"."Job"."MandantID",
    "MyDatabase"."dbo"."Job"."AddressID01",
    "MyDatabase"."dbo"."Job"."AddressID02",
    "MyDatabase"."dbo"."Job"."AddressID03",
    "MyDatabase"."dbo"."Job"."AddressID04",
    "MyDatabase"."dbo"."Job"."EmployeeID",
    "MyDatabase"."dbo"."Job"."JobName",
    "MyDatabase"."dbo"."Job"."JobDate",
    "MyDatabase"."dbo"."Job"."Status",
    "MyDatabase"."dbo"."Job"."Additional01"
FROM "MyDatabase"."dbo"."Job"
WHERE "MyDatabase"."dbo"."Job"."last_modified" >= {ml s.last_table_download} AND
    "MyDatabase"."dbo"."Job"."ID" IN (
        SELECT ID FROM fn_SyncFilterJob('JobID', {ml ui.varClientID}, {ml ui.varUserID})
    )


fn_SyncFilterJob is a function which returns ID's defined by first parameter which are selected through the clientID or the userID I described at the top.
So now I don't know if the definition of my user defined parameters {ml ui.varClientID} and {ml ui.varUserID} is correct.
On clientside, I tried the following code to set the userID for the synchronization:
SyncParms syncParms = mConn.createSyncParms(SyncParms.HTTP_STREAM, "u1", "Android1");
syncParms.getStreamParms().setHost("192.168.1.1");
syncParms.getStreamParms().setPort(2439);
syncParms.setAdditionalParms("varClientID=" + mClientID + ";varUserID=" + mSelectedUserID);
mConn.synchronize(syncParms);
SyncResult result = syncParms.getSyncResult();
mConn.commit();


Can someone please help me with setting those parameters right on Android?
Thank you!
Edit: The Sybase SQL Anywhere version is 16.

[Breck_Carter]

The User-defined named parameters feature is not intended for passing parameter values from the MobiLink client up to the MobiLink server, it is intended for passing parameter values from one MobiLink script on the server side (e.g., begin_synchronization) to other scripts on the server side (e.g., download_cursor). This is helpful if you are NOT using SQL Anywhere for the consolidated database; SQL Anywhere has the CREATE VARIABLE statement so you do not need the user-defined named parameter feature.

AFAIK the setAdditionalParms function is limited to the fixed set of "additional synchronization parameters" defined in this Help topic. Synchronization parameters are used by the client-side synchronization process, NOT the MobiLink SQL scripts running on the server side... again, that is AFAIK since my UltraLite Android skills are [cough] rusty.

Forget parameters, use a table instead. Since a MobiLink synchronization runs the upload first, then the download, you can put all your parameter data in a client-side table befores the synchronization runs, then have that table uploaded as part of the sync, and then you can include the consolidated database copy of that table in the FROM clause of any download_cursor SELECT that needs the values. Folks have been doing this since MobiLink was first introduced... almost everyone has a "sync table" that contains one row per remote database, with the primary key identifying the remote. On the consolidated database, the table has N rows where N = number of remotes, and on each remote database, the table has 1 row.

Personally, I always try to convince clients to number remotes 1, 2, 3 and use exactly the same value as the MobiLink user id '1', '2', '3' and the SET OPTION PUBLIC.GLOBAL_DATABASE_ID = '1', '2', '3' to specify the DEFAULT GLOBAL AUTOINCREMENT partition on each remote. When folks do that, administration is easy, including debugging with the mlsrv16 -o log file. When folks use something complicated, like actual user ids, or business-related values, or (heavens forfend!) GUID values, administrative life becomes a nightmare (folks who recommend alternatives to 1, 2, 3 make a lot of money rescuing folks who actually administer MobiLink setups, so if you have lots of money and you want less of it, you know what to do 🙂

[regdomaratzki]

I agree with Breck that using a table to store your parameter data is a better idea. You have a secondary table (MyEmpIDs) that stores the list of employee_ids from which this remote database is interested in downloading data for. The download_cursor on the Employee table downloads all rows, but the download_cursor on the Address and Job table is a join between the Address and MyEmpIDs table to only download rows from those tables that correspond to employee ID values that are stored in the MyEmpIDs table.

In the first synchornization, the remote database is empty, so the MyEmpIDs table is empty. Only rows from the Employee table will be downloaded. After the first synch, the user can add rows to the MyEmpIDs table, which will cause rows from the Jobs and Address table to download, since the join in the download_cursor for those tables will now return more rows.

[Former Member]

FYI in performance benchmarking I found that using authentication parameters had better performance than using a table for parameter data. Likely that was because the consolidated database was the bottleneck, and authentication parameters are handled within the MobiLink server.

[regdomaratzki]

If you are going to use authentication parameters to identify data to be downloaded to the remote database, please make sure that the same authentication parameter is there for every single synchronization, or you risk missing updates to data in the consolidated database when using timestamp based downloads.

[Former Member]

Ditto for parameter tables.

[Former Member]

They are often overlooked (probably because the name we chose doesn't reflect their scope) but authentication parameters are a way to pass parameters from a remote to be used as named parameters in synchronization scripts. The "names" are just numbers though (up to 255), and the values are strings up to 4000 bytes.

[Former Member]

FYI, if authentication parameters are uploaded from remotes the MobiLink server expects an authenticate_parameters connection script to be defined. If you don't need that script, just define it as an ignored script.

[Former Member]

Here's an example for Microsoft SQL Server (where the remote is uploading 3 authentication parameters):

-- authentication parameters
-- a.1 clientId
-- a.2 selectStart
-- a.3 rowsToDownload
exec ml_add_connection_script 'MyVersion', 'authenticate_parameters',
'--{ml_ignore}' 
go
exec ml_add_table_script 'MyVersion', 'Purchase', 'download_cursor',
'SELECT emp_id, purch_id, cust_id, cost, order_date, notes
    FROM Purchase
    WHERE emp_id = {ml a.1
    AND purch_id BETWEEN CAST( a.2 AS INT ) AND CAST( a.2 AS INT ) + CAST( a.3 AS INT ) - 1}'
go

[Breck_Carter]

For completeness, please show how to use authentication parameters on the UltraLite Android client side... as the Help says, The syntax varies depending on the API you use 🙂

[Breck_Carter]

Authentication parameters certainly appeal to developers who don't understand tables or databases or SQL... but one has to ask what such a developer is doing with UltraLite or MobiLink or synchronization in the first place 🙂

Having said that, authentication parameters certainly seem to have come a long way since they were first introduced, as this Help topic mentions: "Authentication parameters can be used in all other events (except begin_connection and end_connection) to pass information from MobiLink clients. This technique is a convenient way to do something that you could otherwise do by uploading rows to a table. With authentication parameters the values are available prior to the table's upload events."

Score 1 for authentication parameters: "available prior to the table's upload event"

[Former Member]

The authentication parameters would be set like this in the UltraLiteJ API:

syncParms.setAuthenticationParms( "value1,value2,value3" );

[Former Member]

Don't forget lower per-synchronization overhead as an advantage for authentication parameters.

[Former Member]

Hello Graham, Andy and Breck,

thank you so much for your quick replies. I will try both of your suggested ways and post my results for the community.

And thanks to all others who tried to help! 😃

[VolkerBarth]

IMHO, the drawback would be their "hidden" meaning - both the feature's somewhat misleading name and the fact that they are justed numbered - code like "CAST( a.2 AS INT ) AND CAST( a.2 AS INT ) + CAST( a.3 AS INT )" doesn't seem too easy to maintain...

[Former Member]

Is this right?

client: syncParms.setAuthenticationParms( "value1" );

mobilink: Job (MyDatabase): download_cursor

SELECT "MyDatabase"."dbo"."Job"."ID",
    "MyDatabase"."dbo"."Job"."MandantID",
    "MyDatabase"."dbo"."Job"."AddressID01",
    "MyDatabase"."dbo"."Job"."AddressID02",
    "MyDatabase"."dbo"."Job"."AddressID03",
    "MyDatabase"."dbo"."Job"."AddressID04",
    "MyDatabase"."dbo"."Job"."EmployeeID",
    "MyDatabase"."dbo"."Job"."JobName",
    "MyDatabase"."dbo"."Job"."JobDate",
    "MyDatabase"."dbo"."Job"."Status",
    "MyDatabase"."dbo"."Job"."Additional01"
FROM "MyDatabase"."dbo"."Job"
WHERE "MyDatabase"."dbo"."Job"."last_modified" >= {ml s.last_table_download} 
AND "MyDatabase"."dbo"."Job"."EmployeeID" = {ml a.1}

[Former Member]

That will restrict the downloaded rows to ones where the EmployeeID column has the value 'value1' (as well as being modified since the last download).