Queue Oracle transactions using PHP oci_pconnect function in a webservice

Question

I have written a webservice using the PHP SOAP classes. It has functions to return XML data from an Oracle database, or to perform insert/update/delete on the database.

However, at the moment it is using Autocommit, so any operation is instantly commited.

I'm looking at how to queue up the transactions, and then commit the whole lot only when a user presses a button to "save". I'm having difficulty in finding out if this is possible. I can't maintain a consistent connection easily, as of course the webservice is called for separate operations.

I've tried using the PHP oci_pconnect function, but even when I connect each time with the same parameters, the session appears to have ended, and my changes aren't commited when I finally call oci_commit.

Any ideas?

Answer 1

Reusing the same uncommitted database session between PHP requests is not possible. You have no way to lock a user into a PHP processes or DB connection as the webserver will send a request to any one of many of them at random. Therefore you cannot hold uncommited data in the Oracle session between requests.

The best way to do this really depends on your requirements. My feeling is that you want some sort of session store (perhaps a database table, keyed on user_id) that can hold all the pending transactions between requests. When the user hits save, extract out all the pending requests and insert them into their final destination table and then commit.

An alternative would be to insert all the transactions with a flag that says they are not yet completed. Upon clicking save, update the flag to say they are completed.

Either way, you need somewhere to stage your pending requests until that save button is pressed.