On MSSQL i use a stroed procedure to count a value in a table and use the following statement in Lazarus

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

I see the autocommit is configured, but  sqoAutoApplyUpdates is missing. It hink it should be

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

But you are not modifying anything or posting any data, why do you need an applyupdates ?

What do you want ApplyUpdates to do ?

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.

Michael.

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.

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

Yes, but the reason is a side effect of using ApplyUpdates. See below.

ApplyUpdates as you use it, is not the correct solution. Once more: ApplyUpdates ONLY makes sense after changing data and doing a Post. Any other use does not make any sense.

If you test the SP in the MSSQL-Studio it works, with Lazarus without the ApplyUpdates not.

That is because you're not doing a commit of the transaction. It has nothing to do with "needing to call ApplyUpdates".

By default, the transaction is rolled back when you close. You must explicitly commit the transaction.

This happens implicitly by ApplyUpdates,
which is what led you to believe there is an error.

You may need to use sqoKeepOpenOnCommit in the options if you want to keep the dataset open after the commit was done.

Michael.

Data is changed by the SP. But this is done in a simpy open in the query. No insert or delete of the Query is activated. It must be done with open, because a resultset is returned. This can not be handled by ExecSQL (maybe ia wrong).

Only when i close the connection, but not when i close the Query.

This happens implicitly by ApplyUpdates, which is what led you to believe there is an error.

This is correct, i have to explicit close the transaction, because sqoAutoCommit does seems not to work as expected.

I can see the tranaction is active, but the query doesnt recognize it. After the transaction.commit the the transaction is inactive and the data is stored.

"after changing data" must be understood as 'you change data in the dataset', i.e. a post or delete operation on the dataset.

TDataset cannot know what happens behind the scenes in a stored procedure.

Since you are not doing a Post(), ApplyUpdates does not apply, nor does sqoAutoCommit. Both are relevant only afer a Post() or Delete.

I will add some remarks to the documentation to make this more clear.

Michael.

But it opens a transaction without close it.
If i look before fire the open, the transaction is inactive, after the open it is active and after the close it is still active.

This is not expected by me. The query creates a 'dangling' transaction.

Thanks,
now it will be more clear.

But will it be better to add a TStoredProcedure to the SQLdb Components as other componentsset do. This can better fullfill the special requirements of Stored Procedures and Functions.

I know the work, but if "someone" make it, will it be accepted ? And yes i know the freeTDS components have (some) limitation specially with SP and returnvalues.

Hello,

I've got a similar problem about 2 days ago. I have a stored MSSQL procedure that changes a table. When it is run in SQL Managements Studio, the data get changed.

When I run it with FPC, it runs fine but nothing changes. I do commit the transaction explicitly but the data isn't changed anyway. Do I need to run ApplyUpdates as well? Isn't a commit enough?

I'll check it again.

Ondrej

Yes, it does, because TSQLQuery can decide whether it needs an active transaction, but it cannot reliably decide when the transaction needs to be closed, and whether it should be committed or rolled back. (there may be other active datasets connected, or statements may have been executed)

Only the programmer can decide correctly when the transaction must be committed/rolled back.

However:

Starting the transaction is an automatism which you can control. See the stoExplicitStart option of TSQLTransaction.

It is not enabled by default for backwards compatibility.

I prefer to start/stop transactions explicitly, but most people seem to prefer the implicit behaviour.

Similarly, opening a dataset implicitly opens the connection. Again an automatism which can be controlled.

Conceivably, some options can be added to control closing of a transaction by a query, but in my opinion, it is better to let the programmer handle it.

Personally, I fail to see what is special about a stored procedure ? It is just SQL which can or cannot return a result set. Both cases can be covered with TSQLQuery or TSQLStatement. Which is why we don't have a TStoredProcedure.

I know the work, but if "someone" make it, will it be accepted ? And yes i know the freeTDS components have (some) limitation specially with SP and returnvalues.

I personally don't see the need for such a component, but I can imagine it makes life easier for people, so yes, it will be accepted.

Michael.

The commit should be enough.

Once more:
ApplyUpdates is ONLY for when you change data in the dataset.

Michael.