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

[Joost van der Sluis]

On Tue, 2010-11-30 at 07:29 +0000, Mark Morgan Lloyd wrote:
> Joost van der Sluis wrote:
> 
> >> PostgreSQL gets "locked", I mean, the tables being used by sqldb can't
> >> be modified (new columns, new constraints, etc) while the sqldb
> >> connection is active, even if it's a read-only SELECT. If someone is
> >> testing a heavy report the tables affected can't be changed by the DBA
> >> -- it's a major headache. 
> > 
> > Thing is, that TSQLQuery by default fetches only the first 10 records,
> > and then keeps the cursor open, so when you need more records, those are
> > fetched too. Setting PacketRecords to -1 will make it fetch the complete
> > table at once. 
> > 
> > But then still, you have the data fetched in memory, so if you want to
> > change the data, and apply changes, you have to keep the transaction
> > open. (Well, it's up to you. But think through what you're doing)
> > 
> > And I assume that Postgres (don't remember exactly) can set the behavior
> > of transactions. Apparently they are blocking by default. 
> 
> Various parameters to begin transaction, set transaction and lock. It's 
> a few years since I've fiddled with these and I only did it by hand, 
> i.e. fed explicit commands through BDE to the server.
> 
> If I could ask: where can I find a description of how TSQLQuery etc. 
> should be used for non-trivial applications? For example, if I want to 
> set up an explicit transaction, insert a few hundred rows of data, and 
> then make an explicit decision on whether I commit or rollback?

transaction.create
sqlquery.transaction := just created transaction.
sqlquery.sql := 'insert/update table ....'
for i := 0 to few hundred do
  sqlquery.execsql
transaction.commit/rollback

> I can see TSQLQuery.InsertSQL etc., but how should they be used? If I 
> want to force a lock on a table how should I do it?

In most cases you don't need them. The TSQLQuery is a TBufDataset. This
dataset fetches data from a source. (In DBExpress this source is a
dataprovider. In sqldb the source can be defined by a descendant, in
this case the TSQLQuery. But other sources are possible. It would also
be possible to create a descendant which uses a dataprovider-like
component)

The dataset is kept/cached in memory. The changes are kept in an
update-buffer. 

When you call ApplyUpdates, all changes in the update-buffer are pushed
to the underlying dataset. (Again, this is handled in a descendant,
Delphi uses the dataprovider)

What TSqlQuery does when it has to push changes to the database-server,
is creating update/delete/insert sql-statements for each change. Well,
in fact it parses the provided SQL to see if it can create this
statements (using parameters) automatically.

There is a way however to provide these update/insert/delete statements
manually, that's where the .InsertSQL etc properties are for. 

In fpc 2.4.2 and further the TBufDataset has one build-in mechanism of
data retrieval, which is streaming to a binary format. Or, when you add
the xmlstreaming unit, to the same xml format as TClientDataset uses.

Joost.