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+...@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
_______________________________________________
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
