MSSQL sqoAutoApplyUpdates doesnt work for SP
lazarus / 16-Databases

----------------------------------------------------------------------
af0815 · 2022-12-14 14:57

I have on a MSSQL Server (Linuxbased under Windows the same) a Stored
Procedure. This SP rises a Field in a Table by one every execution. If
i do it and close the connection all is lost.  I have added to the
used Query to AutoApplyUpdates and AutoComit. But it doesnt store. If
i add a explcit AppyUpdates the Valueas are stored.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.BuExecuteClick(Sender: TObject);var  SQL: string;begin 
Memo1.Clear;  SQL := '';  SQL := 'EXECUTE [dbo].[GetNextZaehler]
:TagNr,:ProduktNr ';  Query.Active:= false;  Query.Clear; 
Query.SQL.Text:= SQL;  Query.ParamByName('TagNr').AsInteger:= 10; 
Query.ParamByName('ProduktNr').AsInteger:= 100;  Query.Options:=
[sqoAutoApplyUpdates,sqoAutoCommit]; // <-- AutoApplyUpdates doesnt
work !?  try    Query.Open;    if not(Query.EOF and Query.BOF) then
begin      Memo1.Append('Wert='+Query.FieldByName('StueckZaehler').AsI
nteger.ToString);    end    else begin      Memo1.Append('Kein
Wert');    end;    //Query.ApplyUpdates; // <-- If i use this it
works    Query.Close;  except    on E : Exception do begin     
Memo1.Append('BuExecuteClick Exception =>' + E.Message);    end; 
end;end; 
Error in Programmer or FPC ?

Code for the MSSQL DB
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i USE
[CounterTestDB]GO SET ANSI_NULLS ONGO SET QUOTED_IDENTIFIER
ONGO 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 integerASBEGIN        -- 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  ENDGO

----------------------------------------------------------------------
Zvoni · 2022-12-14 15:09

Just guessing: you start (and commit) a transaction within the SP,
which would be isolated from the „outer“ transaction your query-object
is a part off

----------------------------------------------------------------------
Sieben · 2022-12-14 15:27

Looking at the sources of TSQLQuery, sqoAutoApplyUpdates needs a Post
(or Delete) to be triggered.

----------------------------------------------------------------------
af0815 · 2022-12-14 16:35

Quote from: Sieben on December 14, 2022, 04:27:15 pmLooking at the
sources of TSQLQuery, sqoAutoApplyUpdates needs a Post (or Delete) to
be triggered.
Ok, now i have a startingpoint. i have searches this in the
mssqlconnection itself. I have tested this with the the exception
handling auto or not, but i have not in focus, that the query need a
special setting to make ApplyUpdates automatic.

But it is good to know where the limitations are.

Edit: I have found the places in sqldb.pp and ask o the mailinglist if
i should file a bug or is this the expected behavior (and will not
fixed).

----------------------------------------------------------------------
af0815 · 2022-12-16 07:40

It is not the sqoApplyUpdates.

The sqoAutoCommit does not work ! You have to close the transaction
(implizit created) explicit. The query handle this this not as
expected. This was hidden by the ApplyUpdates, because this make a
implicit transaction close. MvC brought me in the correct direction.

----------------------------------------------------------------------
Sieben · 2022-12-16 14:47

So what does your code look like now...? And if I understand you
correctly, the implicit closing of the implicit transaction via
ApplyUpdates did work, after all, as well?

----------------------------------------------------------------------
af0815 · 2022-12-16 16:11

In the place of "//Query.ApplyUpdates" in the first post must be a
"Trans1.Commit;" be. The transaction is not visible in the sample, but
placed on the Form.