Is it possible to call web services from the Informix stored procedure. Also the web services returns the value which will be used back in the stored procedure.

Andrew asked:
I just checked page 3-50 of the "Guide to SQL - Syntax" for release 11.5 The only thing a stored procedure can get back from any program it runs with the SYSTEM command is the program's exit code. And it does warn you that:

• Whatever the program does is not part of the transaction that called it. So, in fact, it can start a whole new transaction.
• The procedure (and hence, your thread) will block until that program exits. Thus, if that program blocks, you may find yourself in a long transaction rollback.
• Even worse, you may end up in a deadlock as it tries to roll back.

So I suspect it may not be a good idea to run a web service from your SPL code. And, according to the manual, forget about getting any useful data from that web service directly.

That said: If your web service can be manipulated into dropping data into delimited files then you can SYSTEM call another program to load those files into a table. THEN you can access the data.

Digression: Why don't I suggest loading the table directly from the SP code? as I recall (and the feature may have been added more recently) the LOAD command we use in dbaccess is not actual SQL syntax; I recall getting syntax errors trying to PREPARE a load statement in 4GL.

In Oracle there is a sneaky method called an EXTERNAL TABLE, where you can integrate a delimited file into the database without actually importing it into the database. Updates in place must be horrid on the CPU. If I am reading the manual correctly (Page 2-18) this feature is available only in XPS.

Does the above 2-step sound like a reasonable workaround for your needs?
---------------------------
+ Rasputin Paskudniak ------------------------------+
| In pursuit of undomesticated, semi-aquatic avians |
+---------------------------------------------------+

Thanks for the reply. Really its sounds good. I thought of moving the Stored Procedure into web services accessing and updating the data which Stored Procedure is doing right now.