How do I call a web service from a MobiLink synchronization script?

[Breck_Carter]

Are there any code examples of calling a web service from MobiLink Version 12 synchronization script written in Oracle PL/SQL, Java or .NET VB or C#? ...or Direct Row Handling, if necessary, but I would like to avoid that if possible.

In particular, the wish is to handle row INSERT and UPDATE operations via web services.

[regdomaratzki]

Breck, where are the web services being hosted? Do I understand correctly that ML is connected to an ORA consolidated, and you have some Web Server in the ether somewhere to which you want to redirect inserts on a table to, and not place the data in the Oracle consolidated?

[Breck_Carter]

I don't know where the web services are hosted, but "somewhere else" is my best guess. Your assumptions are correct; most of the synchronization happens in the traditional manner with Oracle tables via PL/SQL scripts, but some tables are "somewhere else" that is isolated behind web services.

[Former Member]

FYI, if performance is an issue, we've found that plain SQL scripts perform significantly better than PL/SQL scripts.

[Breck_Carter]

@Graham: I probably mispoke, not truly understanding the mode differences (PL/SQL versus SQL)... what I call the "traditional" scripts are just naked INSERT, UPDATE, DELETE and SELECT statements, similar to the stuff the MobiLink sync model generates. That's what you call "plain SQL", right? For connection-level events I have been known to CALL massive stored procedures... that is a descent into PL/SQL, right? ( I remember reading Tracy Kidder's The Soul of a New Machine, I hate modes 🙂

[Former Member]

@Breck: I suspect SQL is always faster than PL/SQL, but the performance difference would be most noticeable in what we call the "data" scripts (which you call the "traditional" scripts). AFAIK, in Oracle anything between a BEGIN and END is PL/SQL.

[Breck_Carter]

Does your consolidated database run on SQL Anywhere?

Because the question asked about coding a MobiLink script for Oracle, not SQL Anywhere... and AFAIK Oracle doesn't have OPENXML.

[regdomaratzki · Accepted solution]

Not a great answer, but here goes :

If you were using SQL Anywhere as your consolidated database, you could write stored procedures that act as web service clients, and not have to worry about writing any Java or .NET code. I don't know if Oracle PL/SQL has similar capabilities, but 10 minutes of Google searches and searching through the Oracle documentation didn't find anything. That doesn't necessarily mean it doesn't exist, it just means that I didn't find it.

That means that you will need to write Java or .NET code to do this. I always suggest Java over .NET for cross-platform support so you can re-use your code if find yourself with another customer in the same boat, but running on Solaris or Linux.

If you're using the MobiLink Java API and you want access to the data values in the upload stream, that means using the Direct Row API so you can get access to the data values in the upload stream. Note that you can use the Direct Row API to only handle the data you want, and then write standard PL/SQL synch scripts for the rest (use --{ml_ignore} for scripts whose logic you've implemented in the Direct Row API).

As far as accessing the web services from a Java class, it looks like the JAX-WS project at https://jax-ws.dev.java.net/ has done most of the heavy lifting for you, and there's a good sample located at http://www.codeproject.com/KB/java/edujini_webservice_java.aspx.

So the real answer is no, we do not have any samples already written to do this, but hopefully the information here is helpful. I can provide a very simple Java sample that uses the Direct Row API to do exactly what SQL scripts do against a database. It will at least give you a foundation of how the Direct Row API works and you can replace the JDBC calls to access the database with your Web Service calls. Let me know if that would be helpful.

Reg

[VolkerBarth · Accepted solution]

@Reg: That's a lot of useful information, and as such you're really invited to post more of those "not great answers":)

[Breck_Carter · Accepted solution]

@Reg: Yes, please post a Direct Row API sample. Feel free to post another answer to this question, or edit your answer, or... post your own question "How do I write a Direct Row API script?" and answer it yourself. You may run into a lack-of-points issue, email me if that happens breck.carter@gmail.com. THANK YOU!

[Breck_Carter · Accepted solution]

@Reg: I won't be marking your answer with the Big Green Checkmark until after the software lets me assign a bounty in a couple of days :)... that will solve any points problem.

[regdomaratzki · Accepted solution]

@Breck: Sample code for ML Direct Row Java API posted on another question.