MSSQL sqoAutoApplyUpdates doesnt work for SP

[af0815]

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][+][-]

1. procedure TForm1.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.     if not(Query.EOF and Query.BOF) then begin
17.       Memo1.Append('Wert='+Query.FieldByName('StueckZaehler').AsInteger.ToString);
18.     end
19.     else begin
20.       Memo1.Append('Kein Wert');
21.     end;
22.     //Query.ApplyUpdates; // <-- If i use this it works
23.     Query.Close;
24.   except
25.     on E : Exception do begin
26.       Memo1.Append('BuExecuteClick Exception =>' + E.Message);
27.     end;
28.   end;
29. end;
30.  

Error in Programmer or FPC ?

Code for the MSSQL DB

Code: Pascal  [Select][+][-]

1. USE [CounterTestDB]
2. GO
3.  
4. SET ANSI_NULLS ON
5. GO
6.  
7. SET QUOTED_IDENTIFIER ON
8. GO
9.  
10. CREATE TABLE [dbo].[B_AktStueck](
11.         [TagNr] [int] NOT NULL,
12.         [Produkt] [int] NOT NULL,
13.         [Stueckzaehler] [int] NOT NULL,
14.  CONSTRAINT [PK_B_AktStueck] PRIMARY KEY CLUSTERED
15. (
16.         [TagNr] ASC,
17.         [Produkt] ASC
18. )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]
19. ) ON [PRIMARY]
20. GO
21.  
22. ALTER TABLE [dbo].[B_AktStueck] ADD  CONSTRAINT [DF_B_AktStueck_TagNr]  DEFAULT ((0)) FOR [TagNr]
23. GO
24.  
25. ALTER TABLE [dbo].[B_AktStueck] ADD  CONSTRAINT [DF_B_AktStueck_Produkt]  DEFAULT ((0)) FOR [Produkt]
26. GO
27.  
28. ALTER TABLE [dbo].[B_AktStueck] ADD  CONSTRAINT [DF_B_AktStueck_Stueckzaehler]  DEFAULT ((0)) FOR [Stueckzaehler]
29. GO
30.  
31. CREATE PROCEDURE [dbo].[GetNextZaehler]
32.         -- Add the parameters for the stored procedure here
33.         @TagNr integer,
34.         @ProduktNr integer
35. AS
36. BEGIN
37.         -- SET NOCOUNT ON added to prevent extra result sets from
38.         -- interfering with SELECT statements.
39.         SET NOCOUNT ON;
40.  
41.         DECLARE @NewCnt integer
42.  
43.     Set @NewCnt = 0;
44.  
45.         BEGIN TRAN Tran1
46.  
47.     SELECT TOP 1 @NewCnt = [StueckZaehler]
48.           FROM [B_AktStueck]
49.           WHERE ([TagNr] = @TagNr) AND ([Produkt] = @ProduktNr)
50.       ORDER BY [TagNr] DESC
51.  
52.    print '---- old CounterValue ----'
53.    print @NewCnt
54.  
55.    if @NewCnt = 0 begin
56.      print '-- No entry -> created '
57.      INSERT INTO [B_AktStueck] ([TagNr], [Produkt], [StueckZaehler])
58.            VALUES (@TagNr, @ProduktNr, @NewCnt)
59.    end
60.  
61.    UPDATE [B_AktStueck] SET
62.                 [StueckZaehler] = [StueckZaehler] + 1
63.                 WHERE ([TagNr] = @TagNr) AND ([Produkt] = @ProduktNr)
64.  
65.    COMMIT TRAN Tran1
66.  
67.    SELECT TOP 1 [StueckZaehler]
68.           FROM [B_AktStueck]
69.           WHERE ([TagNr] = @TagNr) AND ([Produkt] = @ProduktNr)
70.       ORDER BY [TagNr] DESC
71.  
72.  
73. END
74. GO
75.  

[Zvoni]

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]

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

[af0815]

Looking 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]

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]

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]

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.