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

[Andreas Frieß]

Am 15.12.2022 um 11:15 schrieb Michael Van Canneyt via fpc-pascal:
>
>
> 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.

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.
If you test the SP in the MSSQL-Studio it works, with Lazarus without
the ApplyUpdates not.


>
> Michael.
>
> _______________________________________________
> fpc-pascal maillist  -  fpc-pascal-PD4FTy7X32k2wBtHl531yWD2FQJk+8+b at public.gmane.org
> https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal

Code for Stored Procedure on MS-SQL Server including the tabledefinition
----
USE [CounterTestDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[B_AktStueck](
         [TagNr] [int] NOT NULL,
         [Produkt] [int] NOT NULL,
         [Stueckzaehler] [int] NOT NULL,
  CONSTRAINT [PK_B_AktStueck] PRIMARY KEY CLUSTERED
(
         [TagNr] ASC,
         [Produkt] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY =
OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[B_AktStueck] ADD  CONSTRAINT [DF_B_AktStueck_TagNr]
DEFAULT ((0)) FOR [TagNr]
GO

ALTER TABLE [dbo].[B_AktStueck] ADD  CONSTRAINT [DF_B_AktStueck_Produkt]
  DEFAULT ((0)) FOR [Produkt]
GO

ALTER TABLE [dbo].[B_AktStueck] ADD  CONSTRAINT
[DF_B_AktStueck_Stueckzaehler]  DEFAULT ((0)) FOR [Stueckzaehler]
GO

CREATE PROCEDURE [dbo].[GetNextZaehler]
         -- Add the parameters for the stored procedure here
         @TagNr integer,
         @ProduktNr integer
AS
BEGIN
         -- SET NOCOUNT ON added to prevent extra result sets from
         -- interfering with SELECT statements.
         SET NOCOUNT ON;

         DECLARE @NewCnt integer

     Set @NewCnt = 0;

         BEGIN TRAN Tran1

     SELECT TOP 1 @NewCnt = [StueckZaehler]
           FROM [B_AktStueck]
           WHERE ([TagNr] = @TagNr) AND ([Produkt] = @ProduktNr)
       ORDER BY [TagNr] DESC

    print '---- old CounterValue ----'
    print @NewCnt

    if @NewCnt = 0 begin
      print '-- No entry -> created '
      INSERT INTO [B_AktStueck] ([TagNr], [Produkt], [StueckZaehler])
            VALUES (@TagNr, @ProduktNr, @NewCnt)
    end

    UPDATE [B_AktStueck] SET
                 [StueckZaehler] = [StueckZaehler] + 1
                 WHERE ([TagNr] = @TagNr) AND ([Produkt] = @ProduktNr)

    COMMIT TRAN Tran1

    SELECT TOP 1 [StueckZaehler]
           FROM [B_AktStueck]
           WHERE ([TagNr] = @TagNr) AND ([Produkt] = @ProduktNr)
       ORDER BY [TagNr] DESC


END
GO