[fpc-pascal] SqlDB TSQLQuery sqoAutoApplyUpdates does not work with ExecSQL

[Michael Van Canneyt]

On Thu, 15 Dec 2022, Andreas Frieß via fpc-pascal wrote:

>> Post operation.
>
> No, if you use a stored procedure on the MSSQL Server there can be
> changes on tables. Without the ApplyUpdates these changes are not
> persitent. If you close and reopen the connection ALL is lost. With
> ApplyUpdates it works.

Yes, but the reason is a side effect of using ApplyUpdates. See below.

ApplyUpdates as you use it, is not the correct solution.
Once more: ApplyUpdates ONLY makes sense after changing data and doing a
Post. Any other use does not make any sense.

> If you test the SP in the MSSQL-Studio it works, with Lazarus without
> the ApplyUpdates not.

That is because you're not doing a commit of the transaction. 
It has nothing to do with "needing to call ApplyUpdates".

By default, the transaction is rolled back when you close. 
You must explicitly commit the transaction.

This happens implicitly by ApplyUpdates,
which is what led you to believe there is an error.

You may need to use sqoKeepOpenOnCommit in the options if you want to keep the 
dataset open after the commit was done.

Michael.