[Lazarus] What is a TSQLTransaction and why do I need one?

[Joost van der Sluis]

On Tue, 2010-11-23 at 16:01 +0000, Mark Morgan Lloyd wrote:
> Before anything else I'd like to say that I'm entirely familiar with 
> transactions in the SQL context. However I've been having a bit of 
> difficulty sorting out the relationship between a TPQConnection, 
> multiple TSQLQuery, and transaction object(s).

In the case of Postgres it's a little bit hard to understand, because
the transaction-paradigm of Firebird/Interbase is used. Postgres'
transaction can be used in this manner, but you have to know how.

> I'm roughing out a program which I am trying to keep responsive even if 
> getting updated data from the backend using SQL is slow. To do that I've 
> got two TSQLQuery objects, one of which can be queried by code which 
> updates the UI and the other of which will be updated the next time a 
> query is to be run (at which point they'll be swapped).
> 
> Since this is strictly read-only, I can't see why I need transactions, 
> but I obviously respect the judgment of whoever designed the architecture.

Well, you always need a transaction. Without a transaction, Postgres
will do nothing, ever.

I assume you are used to the fact that postgres automatically creates an
transaction for you. With Sqldb you have to do this yourself.

> Am I correct in my understanding that in order to close the transaction 
> I have to set Action (e.g. to caRollback) and then Active to False? It 
> appears that if I don't do this the query never sees data being updated 
> at the backend.

To close the transaction, use .commit or .rollback. (In your
case .rollback)

That the query isn't updated, isn't that because it's cached? Did you
close and reopened the query?

If that does not help, you have to set the proper
transaction-properties, so that it can read data committed in other
transactions. 

> To support the two TSQLQuery objects, do I need one or two 
> TSQLTransaction objects? If I have to have two TSQLTransaction objects, 
> does that imply that I also have to have two TPQConnection objects?

Just one. 

Joost.