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

[Joost van der Sluis]

On Tue, 2010-11-30 at 12:15 +0000, Mark Morgan Lloyd wrote:
> 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.

Indeed.

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

Not exactly, it parses the .SQL property to obtain the tablename which
has to be updated. Then it generates queries (one insert, one updte and
one delete) like 'update tablename set field1=:field1, field2=:field2
where keyfield=:old_keyfield'.

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

Yes. If you define .InsertSQL it won't use it's own generated
insert-sql.

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

a) Easiest is to use the database-client's log mechanism. (If there is
any). Else you can change the TPQConnection.Preparestatement function.
The 'buf' parameter is the actual query. But as you'll see it also adds
some more to the query, so it opens a cursor, before it is send to the
database-server.

b) Ehm.. the error-text is in the exception?

c) Hook into TPQConnection.PrepareStatement and TPQCOnnection.Execute

Joost