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

[Michael Van Canneyt]

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

> On MSSQL i use a stroed procedure to count a value in a table and use
> the following statement in Lazarus
>
>> 1.
>>     procedureTForm1.BuExecuteClick(Sender:TObject);
>> 2.
>>     var
>> 3.
>>       SQL:string;
>> 4.
>>     begin
>> 5.
>>       Memo1.Clear;
>> 6.
>>       SQL:='';
>> 7.
>>       SQL:='EXECUTE [dbo].[GetNextZaehler] :TagNr,:ProduktNr ';
>> 8.
>>       Query.Active:=false;
>> 9.
>>       Query.Clear;
>> 10.
>>       Query.SQL.Text:=SQL;
>> 11.
>>       Query.ParamByName('TagNr').AsInteger:=10;
>> 12.
>>       Query.ParamByName('ProduktNr').AsInteger:=100;
>> 13.
>>       Query.Options:=[sqoAutoApplyUpdates,sqoAutoCommit];// <--
>>     AutoApplyUpdates doesnt work !?
>> 14.
>>     try
>> 15.
>>         Query.Open;
>> 16.
>>     ifnot(Query.EOFandQuery.BOF)thenbegin
>> 17.
>>          
>>     Memo1.Append('Wert='+Query.FieldByName('StueckZaehler').AsInteger.ToString);
>> 18.
>>     end
>> 19.
>>     elsebegin
>> 20.
>>           Memo1.Append('Kein Wert');
>> 21.
>>     end;
>> 22.
>>     //Query.ApplyUpdates; // <-- If i use this it works

But you are not modifying anything or posting any data, why do you need an
applyupdates ?

What do you want ApplyUpdates to do ?

>> 23.
>>         Query.Close;
>> 24.
>>     except
>> 25.
>>         on E:Exceptiondobegin
>> 26.
>>           Memo1.Append('BuExecuteClick Exception =>'+E.Message);
>> 27.
>>     end;
>> 28.
>>     end;
>> 29.
>>     end;
>> 30.
>> 
> I must extra write an ApplyUpdates, because the sqoAutoApplyUpdates  is
> ignored by the ExecSQL of the query.

There is no relation between ExecSQL and applyupdates, so your solution is
definitely faulty. An ApplyUpdates only makes sense in the context of the
Post operation.

Michael.