[Lazarus] SQLDb and TSQLQuery usage for MSSQL database...

[Michael Van Canneyt]

On Thu, 25 Feb 2016, Bo Berglund wrote:

> On Wed, 24 Feb 2016 11:47:18 +0100 (CET), Michael Van Canneyt
> <michael at freepascal.org> wrote:
>
>>> constructor TUserDb.Create;
>>> begin
>>>  FConn := TMSSQLConnection.Create(NIL);
>>>  FTrans := TSQLTransaction.Create(NIL);
>>
>> You must additionally set
>>
>>   FConn.Transaction:=FTrans;
>>
>>>  FQuery := TSQLQuery.Create(NIL);
>>>  FQuery.DataBase := FConn; // <== Database property set here!!!
>>>  FQuery.Transaction := FTrans;
>>> end;
>
> Now reading a bit more on the transaction subject and I get confused
> by the wiki examples:
> http://wiki.freepascal.org/SqlDBHowto#How_to_execute_direct_queries.2Fmake_a_table.3F
> and
> http://wiki.freepascal.org/SqlDBHowto#Example:_reading_data_from_a_table
>
> It seems like in some examples one uses the transaction as follows:
> ATransaction.StartTransaction;
> .. do database stuff ...
> ATransaction.Commit;
>
> while in other cases there is no action taken on the ATransaction
> object, it just sits there on both the query and connection objects
> and it still works in my case.
>
> I would like to understand what is happening here...

In general it is better to explicitly handle your transactions.
However, if you don't do that, then SQLDB will do it for you.

>
> Note that in our stored procedures in MSSQLServer there are
> transactions built-in where they are needed to safeguard consistency
> across data in different tables for insance.

This is a given. You cannot disable transactions on a RDBMS server. 
(MySQL is not a RDBMS server in this sense)

They always are part of the game, but some engines 'hide' them by doing
transaction management implicitly.

> The SQLDb transactions are still a bit unclear to me.
> As yet I have only run stored procedures that return datasets
> (basically select procedures) and these work OK but I don't know if
> the transaction object really is involved here...
>
> It seems like a TSQLTransaction activity is only needed if data are to
> be changed in the database and not if they are only read from the
> database. Is this correct?

Even reading is done in the context of a transaction.
But, quite often, it happens in an 'implicit' transaction.

Michael.