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

[Andi Friess]

Am 15.12.2022 um 14:59 schrieb Michael Van Canneyt via fpc-pascal:
>
>
> 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.
>
Data is changed by the SP. But this is done in a simpy open in the
query. No insert or delete of the Query is activated. It must be done
with open, because a resultset is returned. This can not be handled by
ExecSQL (maybe ia wrong).


>> 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.
Only when i close the connection, but not when i close the Query.
>
> This happens implicitly by ApplyUpdates,
> which is what led you to believe there is an error.

This is correct, i have to explicit close the transaction, because
sqoAutoCommit does seems not to work as expected.

I can see the tranaction is active, but the query doesnt recognize it.
After the transaction.commit the the transaction is inactive and the
data is stored.
>
> Michael.
>
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal-PD4FTy7X32k2wBtHl531yWD2FQJk+8+b at public.gmane.org
> https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal