Dear all,

I have viewed the following link containing a demonstration about the direct row handling API: http://sqlanywhere-forum.sap.com/questions/1284/how-do-i-use-the-mobilink-java-direct-row-api

Now I have a few questions about direct row handling.

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:

public void handleUpload( UploadData ud ) throws SQLException, IOException {
    int i;
    UploadedTableData AdminTable = ud.getUploadedTableByName("Admin");
    handleUploadAdminInsert( AdminTable.getInserts() );
 }
public void handleUploadAdminInsert( java.sql.ResultSet ins ) throws SQLException, IOException {
    PreparedStatement ps1 = _sync_connection.prepareStatement( "insert into DIFFERENT-TABLE-THAN-ADMIN values ( ?, ?, DEFAULT )" );
    while( ins.next() ) {
        ps1.setLong( 1, ins.getLong(1) );
        ps1.setString( 2, ins.getString(2) );
        ps1.executeUpdate();
    }
}

Thank you so much for reading! I'm awaiting for the answers.

EDIT: added some code below: handle_UploadData method:

public void processActionParameter( UploadData ut ) throws SQLException {
    UploadedTableData tabelActionParameter = ut.getUploadedTableByName("actionParameter");
    UploadedTableData tabelAction = ut.getUploadedTableByName("action");
    // Get inserts, updates uploaded by the MobiLink client
    ResultSet actionParameterResultSet = tabelActionParameter.getInserts();
    UpdateResultSet actionParameterUpdateResultSet = tabelActionParameter.getUpdates();
    try {
        //pass to this method to insert into the consolidated table
        insertActionIntoConsolidatedTable(actionParameterResultSet, tabelAction);
    } catch (SQLException exception) {
        System.out.println(exception.getMessage());
    }
    //close result set
    actionParameterResultSet.close();
}

Insert action into consolidated table:

private void insertActionIntoConsolidatedTable(ResultSet actionParameterResultSet, UploadedTableData tableAction) throws SQLException {
    String actionName = "";

    while (actionParameterResultSet.next()) {
        //for each actionParameter value get the actionName based on the FK actionId
        ResultSet actionResultSet = tableAction.getInserts();
        while (actionResultSet.next()) {
            String actionId = actionResultSet.getString("actionId");
            if (actionId.equals(actionParameterResultSet.getString("actionId"))) {
                actionName = actionResultSet.getString("actionName");
                break;
            }
        }
        actionResultSet.close();
        //insert the action into the correct consolidated table
        if (actionName.equals("nieuwemelding")) {
            insertNieuweMelding(actionParameterResultSet);
        }
        if (actionName.equals("bewerkenmelding")) {

        }
    }
    actionParameterResultSet.close();
}

Insert new alert into consolidated:

private void insertNieuweMelding(ResultSet actionParameterResultSet) throws SQLException {
    String query = "INSERT INTO melding VALUES(?,?,?,?,?,?,?,?,?,?,?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(query);
    while (actionParameterResultSet.next()) {
        //http://dcx.sybase.com/index.html#1201/en/dbreference/uniqueidentifier-binary-datatypes.html
        //get all the field
        String meldingId = actionParameterResultSet.getString("meldingId");
        String categorieId = actionParameterResultSet.getString("categorieId");
        //...
        //fill all the fields into the prepared statement
        preparedStatement.setString(1, meldingId);
        preparedStatement.setString(2, categorieId);
        //...
        //execute the prepared statement
        preparedStatement.executeUpdate();
    }
}

handle_DownloadData method:

public void processActionResult() throws SQLException {
    DownloadData dd = dbConnectionContext.getDownloadData();
    DownloadTableData tableAction = dd.getDownloadTableByName("action");
    PreparedStatement upsertPreparedStatement = tableAction.getUpsertPreparedStatement();
    PreparedStatement actionPreparedStatement = connection.prepareStatement("SELECT actionId FROM action WHERE tijd >= ?" );
    actionPreparedStatement.setTimestamp(1, lastDownloadTime);
    ResultSet actionResultSet = actionPreparedStatement.executeQuery();
    while (actionResultSet.next()) {
        String actionId = actionResultSet.getString("actionId");
        //insert the action result in the consolidated table with success value
        insertActionResultIntoConsolidatedTable(1, actionId);
    }
    upsertPreparedStatement.close();
}

Insert into actionResult consolidated table:

private void insertActionResultIntoConsolidatedTable(int success, String actionId) throws SQLException {
    //get current date and use it to set into the prepared statement
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
    Calendar gc = new GregorianCalendar();
    String huidigeDatum = sdf.format(gc.getTime());
    String query = "INSERT INTO actionResult (actionId, success, message, handled, data, tijd)" +
            " VALUES(?,?,?,?,?,?)";
    PreparedStatement preparedStatement = connection.prepareStatement(query);
    //fill all the fields into the prepared statement
    preparedStatement.setString(1, actionId);
    preparedStatement.setInt(2, success);
    preparedStatement.setString(3, "");
    preparedStatement.setString(4, "Handled via MobielCMP Synchronization script");
    preparedStatement.setString(5, "");
    preparedStatement.setString(6, huidigeDatum);
    //execute the prepared statement
    preparedStatement.executeUpdate();
    preparedStatement.close();
}

asked 26 Jan '12, 14:24

Yanny's gravatar image

Yanny
1767917
accept rate: 75%

edited 29 Jan '12, 15:25


  1. 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.
  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?

    • 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.
  3. Is it possible to write an insert prepared statement which inserts the retrieved data into a different table then you retrieved with?

permanent link

answered 26 Jan '12, 14:39

Jeff%20Albion's gravatar image

Jeff Albion
10.8k171175
accept rate: 25%

edited 26 Jan '12, 14:43

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.

Action table: insert actionId: 1, actionName: newAlert

ActionParameter table: insert actionParameterId: 1, paramNumber: 1, paramValue: wake up alert, actionId: 1 (foreign key)

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:

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

  2. 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?

(26 Jan '12, 17:10) Yanny
Replies hidden
1

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:


public void processActionParameter( UploadData ud ) throws SQLException {

UploadedTableData UTDAction = ud.getUploadedTableByName("Action"); UploadedTableData UTDActionParam = ud.getUploadedTableByName("ActionParameter");

ResultSet RSActionParamIns = UTDActionParam.getInserts(); String actionName = "";

while(RSActionParamIns.next()){ ResultSet RSActionIns = UTDAction.getInserts(); while(RSActionIns.next()){ if ( RSActionIns.getInt("actionId") == RSActionParamIns.getInt("actionId") ){ actionName = RSActionIns.getString("actionName"); break; } } RSActionIns.close(); if (actionName.equals("newAlert")) { insertNieuweMelding(insertResultSet); } actionName = ""; } RSActionParamIns.close();
}


2. Where in the direct row handling script should I do it?

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).

(27 Jan '12, 12:08) Jeff Albion

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.

(27 Jan '12, 16:12) Yanny
Replies hidden

Maybe it's better for me if I completely explain what I want to do:

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...?

(29 Jan '12, 14:34) Yanny
Replies hidden

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.

See: http://dcx.sybase.com/index.html#1201/en/dbusage/intresp.html

http://dcx.sybase.com/index.html#1201/en/dbusage/intprot.html

(30 Jan '12, 12:28) Jeff Albion
Comment Text Removed
1

Like still using triggers instead of direct row handling etc...?

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).

CREATE TABLE IF NOT EXISTS action (
  actionID  INTEGER     DEFAULT AUTOINCREMENT PRIMARY KEY,
  actionName    VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS  actionParameter (
  actionParameterID INTEGER     DEFAULT AUTOINCREMENT PRIMARY KEY,
  paramNumber   INTEGER,
  paramValue    VARCHAR(255),
  actionID    INTEGER
);

ALTER TABLE actionParameter
   ADD CONSTRAINT FK_actionParam_action
   FOREIGN KEY ( actionID ) 
   REFERENCES action ( actionID );

CREATE TABLE IF NOT EXISTS melding (
  meldingID INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY,
  actionID INTEGER,  
  categorieID   VARCHAR(255)
);

CREATE TABLE  IF NOT EXISTS bewerkenmelding (
  bewerkenmeldingID INTEGER DEFAULT AUTOINCREMENT PRIMARY KEY,
  actionID INTEGER,  
  categorieID   VARCHAR(255)
);

----

CREATE OR REPLACE TRIGGER trg_actionParameter_ins AFTER INSERT
ON actionParameter
REFERENCING NEW AS new_row
FOR EACH ROW
BEGIN
  DECLARE @action_type VARCHAR(255);
  SELECT actionName INTO @action_type FROM action WHERE actionID = new_row.actionID;
  IF (@action_type = 'nieuwemelding') THEN
    INSERT INTO melding (meldingID, actionID, categorieID) VALUES (DEFAULT, new_row.actionID, new_row.paramValue);
  ELSEIF (@action_type = 'bewerkenmelding') THEN
    INSERT INTO bewerkenmelding (bewerkenmeldingID, actionID, categorieID) VALUES (DEFAULT, new_row.actionID, new_row.paramValue);
  ELSE
    ROLLBACK TRIGGER;
  END IF;
END;

CREATE OR REPLACE TRIGGER trg_actionParameter_upd AFTER UPDATE
ON actionParameter
REFERENCING NEW AS new_row
            OLD AS old_row
FOR EACH ROW
BEGIN
  DECLARE @action_type VARCHAR(255);
  SELECT actionName INTO @action_type FROM action WHERE actionID = old_row.actionID;
  IF (@action_type = 'nieuwemelding') THEN
    UPDATE melding SET categorieID = new_row.paramValue WHERE actionID = old_row.actionID;
  ELSEIF (@action_type = 'bewerkenmelding') THEN
    UPDATE bewerkenmelding SET categorieID = new_row.paramValue WHERE actionID = old_row.actionID;
  ELSE
    ROLLBACK TRIGGER;
  END IF;    
END;

Here's my example using the tables:

CREATE VARIABLE @last_action_id INTEGER;
INSERT INTO action (actionID, actionName) VALUES (DEFAULT, 'nieuwemelding');
SELECT @@IDENTITY INTO @last_action_id;
INSERT INTO actionParameter (actionParameterID, paramNumber, paramValue, actionID) VALUES (DEFAULT, 1, 'wake up alert', @last_action_id);
SELECT * FROM melding;

INSERT INTO action (actionID, actionName) VALUES (DEFAULT, 'bewerkenmelding');
SELECT @@IDENTITY INTO @last_action_id;
INSERT INTO actionParameter (actionParameterID, paramNumber, paramValue, actionID) VALUES (DEFAULT, 1, 'wake up alert', @last_action_id);
SELECT * FROM bewerkenmelding;

UPDATE actionParameter SET paramValue = 'new value' WHERE actionID = @last_action_id;

SELECT * FROM bewerkenmelding;
(30 Jan '12, 14:17) Jeff Albion

So does it mean I can use triggers to make a new alert (melding) and use the prepare_for_download method script to insert the ActionResult row into consolidated?

(30 Jan '12, 15:00) Yanny
Replies hidden
2

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.

(30 Jan '12, 16:01) Jeff Albion

Thanks. I will change to use triggers instead and will use the example code posted earlier to start with.

(31 Jan '12, 04:55) Yanny

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:

alt text

This means one sql insert query like this:

INSERT INTO melding VALUES('368593c0-4f4f-11e1-8000-aedffb37760d','9dc0d67f-50af-4f04-a4b7-65e0dbafc33e','59059449-da9b-4454-9152-fb89fa923fc6',?,?,?,'Omgevallen boom',?,?,?,?,?)

I have not filled all the question marks with values, but hopefully you understand what I mean.

So my question is it possible to do that, what I want?

(04 Feb '12, 14:22) Yanny
1

Yanny,

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/

(06 Feb '12, 13:30) Jeff Albion
More comments hidden
showing 5 of 11 show all flat view

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.

permanent link

answered 30 Jan '12, 12:04

Graham%20Hurst's gravatar image

Graham Hurst
2.7k11843
accept rate: 29%

edited 30 Jan '12, 12:15

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)?

(30 Jan '12, 13:38) Yanny
Replies hidden

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.

(30 Jan '12, 15:41) Graham Hurst
Your answer
toggle preview

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "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:

×371
×84

question asked: 26 Jan '12, 14:24

question was seen: 2,760 times

last updated: 06 Feb '12, 13:30