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

[Andreas Frieß]

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
>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.

in sqldb.pp the following code is executed

> procedure TCustomSQLQuery.ExecSQL;
>
> begin
>   CheckPrepare;
>   try
>     Execute;
>     // Always retrieve rows affected
>     FStatement.RowsAffected;
>     If sqoAutoCommit in Options then
>       SQLTransaction.Commit;
>   finally
>     CheckUnPrepare;
>     // if not Prepared and (assigned(Database)) and (assigned(Cursor))
> then SQLConnection.UnPrepareStatement(Cursor);
>   end;
> end;
I see the autocommit is configured, but  sqoAutoApplyUpdates is missing.
It hink it should be

> procedure TCustomSQLQuery.ExecSQL;
>
> begin
>   CheckPrepare;
>   try
>     Execute;
>     // Always retrieve rows affected
>     FStatement.RowsAffected;
>     If sqoAutoCommit in Options then
>       SQLTransaction.Commit;
>     If (sqoAutoApplyUpdates in Options) then
>       ApplyUpdates;
>   finally
>     CheckUnPrepare;
>     // if not Prepared and (assigned(Database)) and (assigned(Cursor))
> then SQLConnection.UnPrepareStatement(Cursor);
>   end;
> end;
to get the correct behavior. Actual only Post and Delete fire the
ApplyUpdates correct if sqoAutoApplyUpdates is activated. Should i file
a Bug or is the actual behavior by design ?! (and cannot be changed)

Andreas

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20221215/6785ba73/attachment.htm>