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

[Michael Van Canneyt]

On Fri, 16 Dec 2022, Andi Friess via fpc-pascal wrote:

> Am 16.12.2022 um 08:50 schrieb Michael Van Canneyt via fpc-pascal:
>>>> 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).
>>
>> "after changing data" must be understood as 'you change data in the
>> dataset', i.e. a post or delete operation on the dataset.
>>
>> TDataset cannot know what happens behind the scenes in a stored procedure.
> But it opens a transaction without close it.
> If i look before fire the open, the transaction is inactive, after the
> open it is active and after the close it is still active.
>
> This is not expected by me. The query creates a 'dangling' transaction.

Yes, it does, because TSQLQuery can decide whether it needs an active
transaction, but it cannot reliably decide when the transaction needs 
to be closed, and whether it should be committed or rolled back. 
(there may be other active datasets connected, or statements may have been
executed)

Only the programmer can decide correctly when the transaction must be
committed/rolled back.

However:

Starting the transaction is an automatism which you can control. 
See the stoExplicitStart option of TSQLTransaction.

It is not enabled by default for backwards compatibility.

I prefer to start/stop transactions explicitly, but most people seem to
prefer the implicit behaviour.

Similarly, opening a dataset implicitly opens the connection.
Again an automatism which can be controlled.

Conceivably, some options can be added to control closing of a transaction
by a query, but in my opinion, it is better to let the programmer handle it.

>> Since you are not doing a Post(), ApplyUpdates does not apply, nor does
>> sqoAutoCommit. Both are relevant only afer a Post()  or Delete.
>>
>> I will add some remarks to the documentation to make this more clear.
> Thanks,
> now it will be more clear.
>
> But will it be better to add a TStoredProcedure to the SQLdb Components
> as other componentsset do. This can better fullfill the special
> requirements of Stored Procedures and Functions.

Personally, I fail to see what is special about a stored procedure ? It is just SQL which
can or cannot return a result set. Both cases can be covered with TSQLQuery
or TSQLStatement. Which is why we don't have a TStoredProcedure.

>
> I know the work, but if "someone" make it, will it be accepted ? And yes
> i know the freeTDS components have (some) limitation specially with SP
> and returnvalues.

I personally don't see the need for such a component, but I can imagine it
makes life easier for people, so yes, it will be accepted.

Michael.