Subject Re: [firebird-php] transactions .... I thought I got it, little did I know.
Author Helen Borrie
At 11:25 PM 26/10/2007, you wrote:

>still I would like to know if it should be possible (is some way) to perform
>SELECT queries AND INSERT queries within a single transaction and have the SELECT
>queries be able to see the changes made by prior INSERT queries that have not yet
>been committed, but were performed within the context of the same [active] transaction.

Yes. As long as the select and the dml statements are executed within the same uncommitted transaction, refreshing your select set should show the entire view that your transaction has of the table.

>personally I feel like it should be possible (maybe given certain transaction arguments when the transaction is started) to see uncommitted changes within the confines of the transaction that made them.

Exactly so. No special transaction arguments are needed. But don't waste resources on creating a new select statement unless you need to. If the reselect is the same then your initial query is still prepared. Just do whatever it takes to empty the buffers and call the query again using the same statement handle.

Understand, though, that rolling back the transaction will roll back EVERYTHING you have done in the transaction, including any DML that you determined to be OK. This looks to me like a case where an executable procedure call with exception handling and a return value would suit your requirements better than a messy mix of executes and selects that you might not have a lot of control over...