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

Mark Morgan Lloyd markMLl.lazarus at telemetry.co.uk
Tue Nov 30 13:15:55 CET 2010

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]

More information about the Lazarus mailing list