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

[Mark Morgan Lloyd]

Joost van der Sluis wrote:

>> 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 think I've got that. So TSQLQuery.ExecSql is good for statements that 
don't return a result set- I don't have to go down to the connection 
object to get this functionality.

>> 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.

So if I'm reading you correctly, ApplyUpdates in this case uses the .SQL 
property with parameters...

> 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.

..while this case uses .InsertSQL etc. What determines which case 
applies- is it purely whether .InsertSQL etc. are defined?

Is there somewhere I can hook into the TSQLQuery to (a) see exactly what 
query was passed to the server (b) collect any error text and (c) get 
execution timing?

-- 
Mark Morgan Lloyd
markMLl .AT. telemetry.co .DOT. uk

[Opinions above are the author's, not those of his employers or colleagues]