1..When I use my own handle_UploadData connection script, is it required to write all the insert, update and delete prepared statements?
Like they did in the above link:

handleUploadAdminInsert( AdminTable.getInserts() );

handleUploadAdminUpdate( AdminTable.getUpdates() );

handleUploadAdminDelete( AdminTable.getDeletes() );

2.. If I don't write a prepared statement to insert records in the consolidated database, will the uploaded rows by Mobilink not inserted into the consolidated database? Or it does not mater what I do with the uploaded rows, the rows are already saved into the consolidated tables?

3.Is it possible to write an insert prepared statement which inserts the retrieved data into a different table then you retrieved with? Here is some code for more clarification on what I mean:

When I use my own handle_UploadData connection script, is it required to write all the insert, update and delete prepared statements?

You are required to provide a Java class for the event "handle_UploadData". ( See: Required Scripts ). What you do inside this class (handle the insert/updates/deletes) or not, is up to you.

If I don't write a prepared statement to insert records in the consolidated database, will the uploaded rows by Mobilink not inserted into the consolidated database? Or it does not mater what I do with the uploaded rows, the rows are already saved into the consolidated tables?

It depends if you are still using the regular 'SQL Handling' scripts for MobiLink along with the Direct Row handling mechanism (and the scripts perform the insert/update/delete as you would expect normally). You can optionally continue to use them as you would in normal synchronization (and perform 'additional' data operations inside the Direct Row handling) - you simply add new 'handle_UploadData' and 'handle_DownloadData' connection events to start using the direct-row handling gear.

Is it possible to write an insert prepared statement which inserts the retrieved data into a different table then you retrieved with?

Thank you for the answers of the questions. Before I mark your answer as the solution I still have some questions.

I have three tables: action, actionParameter and actionResult.

Below is an example on how the data is inserted into the tables. First a row will be inserted into the action table, then the actionParameter table will be filled with rows. Each row refer to the actionId previously inserted.

Then after inserting the actionParameter table values, the handle_uploadData script will be called.
call ml_add_connection_script('Mobiel-CMP-Synchronization','handle_UploadData','MobielCMPSynchronization.processActionParameter');

I have edited the first post with some additional code like processActionParameter() and getActionName().

Now my questions are:

Is it possible to get the action name from the action table, since it is another consolidated database table?

I want to fill the actionResult table with one row after the action and actionParameter tables are filled. Where in the direct row handling
script should I do it? In the handle_uploadData or handle_downloadData and also in which uploadedTableData?

First a row will be inserted into the action table, then the actionParameter table will be filled with rows.

The rows are inserted on the remote and are coming up in a synchronization session, I assume?

Then after inserting the actionParameter table values, the handle_uploadData script will be called.

handle_UploadData is only executed during a MobiLink synchronization session as part of the upload events and is executed prior to all other events (upload_insert, upload_update, upload_delete).

Be aware that if you are inserting rows against this table on the consolidated database directly (outside of MobiLink entirely), you would still need to process these rows (in some other manner, outside of MobiLink).

1. Is it possible to get the action name from the action table, since it is another consolidated database table?

Yes - but you need to do this more carefully. As I mentioned, you need to remember that the newly uploaded rows from your remote haven't been applied to the consolidated when "handle_UploadData" is run. This means that your query:

SELECT actionName FROM action WHERE actionId='"+actionId+"'

will never return rows from the current upload.

You instead need to do all of this via the API calls and would need to create your own join to discover this information:

Actually, I wouldn't recommend doing this processing inside direct row handling at all. I am assuming that you want to insert rows into the 'actionResult' table, even if the rows are inserted at the consolidated table outside of MobiLink. (e.g. an application directly manipulating rows on the consolidated database). Hence, I would recommend doing all of this logic inside database triggers instead - this would remove the requirement to perform additional processing in a direct row processing script (slowing processing and synchroniation time), and you can cover cases for both 'inserts' and 'updates' from any application (not just MobiLink - note our script above assumes that the remote never updates this information just inserts it - update information is currently just being lost inside the direct row mechanism above).

The rows are inserted on the remote and are coming up in a synchronization session, I assume?

Yes!

Hence, I would recommend doing all of this logic inside database triggers instead

Does this mean I can create a system trigger at the SQLAnywhere 12 consolidated database?
I'm not very experienced with writing triggers, will look for more information on the internet. But is it possible to create a trigger that checks if the rows were successfully inserted into the tables action and actionparameter?

Well, the tables action, actionParameter and actionResult are just for inserts and will never be updated. So that is okay I suppose.
The method insertNieuweMelding(insertResultSet) inserts a new alert (nieuwe melding) into the consolidated database table. Since you said the direct row mechanism don't work on updates.. is it better to use a trigger instead? Because the alert table will be updated by the remote application.

I want to let users create new alerts. When a user create one, this alert will first be saved in the remote Ultralite database. Then the alert will be synchronized with the consolidated SQLAnwyhere database. The Mobilink will be used as the synchronization system between the remote and the consolidated database.

For "security reasons" we do not allow users to create an alert directly on the consolidated database.
The users just create a new alert, which will be saved directly on the remote database only.
But then in the Java code on the remote the following objects are created: Action and ActionParameter. The actionParameter contains the rows of the alert. These two objects will then be used to store into the remote database tables action and actionParameter respectively. Also the action and actionParameter will be used to synchronize with the consolidated database tables action and actionParameter.

In the synchronization process we want to let the server insert a new alert into the consolidated database. My idea is to use direct row handling to get the action and actionParameter rows, then using these to insert a new alert into the consolidated database. This happens in the handle_UploadData method that creates a new preparedStatement to insert the alert. After that in the handle_DownloadData method the actionId will be retrieved and the consolidated table actionResult will be filled.
Then the actionResult row should be downloaded back to the remote database. In the Java code on the remote there is a query to retrieve the actionResult row from the remote database.

I have added some new code fragments into the first posting, as I cannot post too long comment.

I'm not sure if the above works properly... especially the actionResult insertion in the handle_DownloadData causes me question marks.

Now that you know what I want to achieve, maybe you could give me some advice on how to do it? Like still using triggers instead of direct row handling etc...?

Does this mean I can create a system trigger at the SQLAnywhere 12 consolidated database?

Yes. The benefit of writing a database trigger (as opposed to only worrying about writing the direct row synchronization logic) is that the logic is always fired on the consolidated database - not just when the MobiLink server is talking to the consolidated database. (e.g. if you insert into the 'action' / 'actionParameter' table directly at the consolidated database outside of synchronization).

Since you said the direct row mechanism don't work on updates..

No, I said the code sample above doesn't handle updates. You would have to code for this possibility in the event yourself - I haven't done this.

e.g. UTDActionParam.getUpdates(); ...

This is the general strength and weakness of direct-row handling: you need to be very explicit with what you are doing with the incoming data; it is recommended as an "advanced topic" for MobiLink synchronization. If you are not familiar with both the MobiLink synchronization and the SQL Anywhere database technologies, I would not recommend using the direct row feature - it is more likely you can accomplish what you are trying to do without adding this extra layer of complexity.

is it better to use a trigger instead?

Yes, it is always better to enforce as many business rules as possible inside your database - either via your referential declarations, or via triggers / computed columns.

Below is an example using triggers. This is an example that updates 'melding'/'bewerkenmelding' tables, based on the action.actionName column of the corresponding row. These triggers fire whenever the base actionParameter table is inserted/updated against - this will happen whether MobiLink is involved or not, and consolidates the data integrity rules in the database engine itself.

If you truly do not care about this aspect (you will never allow direct manipulation of these tables on the consolidated) - you should use Graham's advice and set up a "prepare_for_download" script to perform this processing during the MobiLink synchronization phase. (You will need to perform similar logic to the below script in that area instead).

CREATEVARIABLE@last_action_idINTEGER;INSERTINTOaction(actionID,actionName)VALUES(DEFAULT,'nieuwemelding');SELECT@@IDENTITYINTO@last_action_id;INSERTINTOactionParameter(actionParameterID,paramNumber,paramValue,actionID)VALUES(DEFAULT,1,'wake up alert',@last_action_id);SELECT*FROMmelding;INSERTINTOaction(actionID,actionName)VALUES(DEFAULT,'bewerkenmelding');SELECT@@IDENTITYINTO@last_action_id;INSERTINTOactionParameter(actionParameterID,paramNumber,paramValue,actionID)VALUES(DEFAULT,1,'wake up alert',@last_action_id);SELECT*FROMbewerkenmelding;UPDATEactionParameterSETparamValue='new value'WHEREactionID=@last_action_id;SELECT*FROMbewerkenmelding;

If you need to, sure. My personal recommendation is to put everything you possibly can into database triggers/logic/declared references and remove as much processing from the MobiLink synchronization step as possible.

If you can make the data calculation by just looking at the incoming row values in the insert/update statement (coming from MobiLink or another application...) and the existing values in the consolidated to populate your 'calculated' rows, this is an ideal place to use a database trigger.

Ideally, your database should be able to maintain data integrity/business rules outside of a synchronization environment.

The trigger you wrote is just an example, I see I cannot use it.
Because each paramValue row from the actionParameter table should be used as the column values in 1 insert query.
I have made a screenshot below:

I still think it would be possible to do what you want to do, but you would need to be more careful on the trigger logic. I can't claim I understand all of your underlying logic details, so I cannot comment on the exact implementation details.

What I don't understand from your description is "when" you consider the parameter list for the resulting insert statement "complete" - is the parameter list always an arbitrary number of items? Is it always "10" items? Are all the items always inserted by one INSERT statement based on a SELECT? (If so, you can use statement-level processing instead of row-level processing for the trigger)

If this grouping is always based on the synchronization logic (all rows come up as an upload, and only that way), you should switch to the prepare_for_download strategy mentioned by Graham below, as this will only fire once a synchronization upload request has been completed.

If it's a set size of parameters (e.g. 10), you just need to trigger the generation of the resulting row once you see a 'paramNumber' of "10" - you can select whichever rows from the consolidated at that point. Updates to the table after that (once you have seen the '10th' parameter') should trigger updates to the triggered row in the 'melding' table.

As an observation from your questions, it seems that you're struggling to put together a solution to meet your specific usage rather than requiring answers to general SQL Anywhere / MobiLink questions.

Since this forum does not guarantee a reply (and your questions are very specific to what you're trying to accomplish), I would encourage you to open a technical support case so that we can work with you and your development staff directly for your future questions surrounding your project : http://www.sybase.com/contactus/support/

If I understand correctly, you want remotes to upload rows from action and actionParameter tables to corresponding consolidated tables, do some processing on those in the consolidated to generate actionResult rows, then download the actionResult rows. Is that correct?

If so, you don't need direct row handling, since the MobiLink server applies the upload before creating the download. You could use a prepare_for_download script to do the processing that generates actionResult rows. That would be run after the upload was committed, and would be committed before the download scripts are run.

Yes, on the consolidated the action and actionParameters rows will be used to do two operations:
1. Insert a row into the table alert.
2. Insert a row into the table actionResult.
The actionResult row will be downloaded to the remote indeed.

So does it mean I can call the insertActionIntoConsolidatedTable() and the processActionResult() method inside the prepare_for_download method (see the code in the first posting)?

Not as written, at least for processActionResult(), since MobiLink only provides an UploadData instance to the handle_UploadData script. Note that your upload_insert and upload_update scripts might be able to do all you want (eg. by calling stored procedures); you might not need a prepare_for_download script unless you needed to wait until all uploaded rows were committed to the consolidated.