[fpc-pascal] SqlDB TSQLQuery sqoAutoApplyUpdates does not work with ExecSQL
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
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.
in sqldb.pp the following code is executed
procedure TCustomSQLQuery.ExecSQL;
begin
CheckPrepare;
try
Execute;
// Always retrieve rows affected
FStatement.RowsAffected;
If sqoAutoCommit in Options then
SQLTransaction.Commit;
finally
CheckUnPrepare;
// if not Prepared and (assigned(Database)) and (assigned(Cursor))
then SQLConnection.UnPrepareStatement(Cursor);
end;
end;
I see the autocommit is configured, but sqoAutoApplyUpdates is missing.
It hink it should be
procedure TCustomSQLQuery.ExecSQL;
begin
CheckPrepare;
try
Execute;
// Always retrieve rows affected
FStatement.RowsAffected;
If sqoAutoCommit in Options then
SQLTransaction.Commit;
If (sqoAutoApplyUpdates in Options) then
ApplyUpdates;
finally
CheckUnPrepare;
// if not Prepared and (assigned(Database)) and (assigned(Cursor))
then SQLConnection.UnPrepareStatement(Cursor);
end;
end;
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
___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
[fpc-pascal] debugserver and dbugintf
Hi FPC team,
I'm using FPC 3.2.2 as installed with Lazarus 2.2.4.
I'm using the debugserver/dbugintf for some time and implemented some
small changes to improve this duo.
The files are attached.
Changes to debugserver:
- The name of the executable file is fpcdebugserver as this is the name
dbugint uses as default.
- A few small changes to the user interface. The tray icon can be disabled.
Changes dbugintf:
- All Send... procedures converted to functions Send... : Boolean
returning true on success.
- Added a new variable RaiseExceptionOnSendError : Boolean (false by
default) to control error handling.
Is this the right way to handover or shall I follow some other instructions?
BR
Rolf
{
This file is part of the Free Component library.
Copyright (c) 2005 by Michael Van Canneyt, member of
the Free Pascal development team
Debugserver client interface, based on SimpleIPC
See the file COPYING.FPC, included in this distribution,
for details about the copyright.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.
**}
{$mode objfpc}
{$h+}
unit dbugintf;
interface
uses dbugmsg;
Type
TDebugLevel = (dlInformation,dlWarning,dlError);
TErrorLevel = Array[TDebugLevel] of integer;
//Result is true on success. See RaiseExceptionOnSendError.
function SendBoolean(const Identifier: string; const Value: Boolean) :
Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendDateTime (const Identifier: string; const Value: TDateTime) :
Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendInteger(const Identifier: string; const Value: Integer;
HexNotation: Boolean = False) : Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendPointer(const Identifier: string; const Value: Pointer) :
Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendDebugEx(const Msg: string; MType: TDebugLevel) : Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendDebug (const Msg: string) : Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendMethodEnter(const MethodName: string) : Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendMethodExit (const MethodName: string) : Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendSeparator : Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendDebugFmt (const Msg: string; const Args: array of const) :
Boolean;
//Result is true on success. See RaiseExceptionOnSendError.
function SendDebugFmtEx (const Msg: string; const Args: array of const;
MType: TDebugLevel) : Boolean;
procedure SetDebuggingEnabled(const AValue : boolean);
function GetDebuggingEnabled : Boolean;
{ low-level routines }
//Start the debug server and return its ProcessID.
function StartDebugServer(const ADebugServerExe : String = '';
const ARaiseExceptionOnSendError : Boolean = true;
const aLogFilename : String = '') : integer;
//Initialize the debug client and start the server.
function InitDebugClient : Boolean;
//Initialize the debug client and start the server.
function InitDebugClient(const ShowPID: Boolean; const ADebugServerExe : String
= '';
const ARaiseExceptionOnSendError : Boolean = true;
const ServerLogFilename: String = ''): Boolean;
procedure FreeDebugClient;
ResourceString
SProcessID = '%d Process %s (PID=%d)';
SEntering = '> Entering ';
SExiting = '< Exiting ';
SSeparator = '>-=-=-=-=-=-=-=-=-=-=-=-=-=-=-<';
SServerStartFailed = 'Failed to start debugserver (%s). (%s)';
Var
DebugServerExe: String = ''; { We can override this global var.
in our compiled IPC client, with DefaultDebugServer a.k.a.
dbugmsg.DebugServerID, or something else }
DefaultDebugServer: String = DebugServerID ; { A "last ressort"
simplier compiled IPC server's name, called in command line by your client
a.k.a. the compiler's target file "-o" }
//Last error message of a Send... function. Not cleared on a new call!
SendError : String = '';
//Raise an exception if a Send... function fails.
//Otherwise the Send... functions will return false without an exception in
case of an error.
RaiseExceptionOnSendError : Boolean = false;
implementation
Uses
SysUtils, classes, process, simpleipc;
Const
DmtInformation = lctInformation;
DmtWarning = lctWarning;
DmtError = lctError;
ErrorLevel : TErrorLevel
= (dmtInformation,dmtWarning,dmtError);
IndentChars= 2;
var
DebugCli
Re: [fpc-pascal] SqlDB TSQLQuery sqoAutoApplyUpdates does not work with ExecSQL
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.
Michael.___
fpc-pascal maillist - fpc-pascal@lists.freepascal.org
https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] debugserver and dbugintf
On Wed, 14 Dec 2022, Rolf Wetjen via fpc-pascal wrote: Hi FPC team, I'm using FPC 3.2.2 as installed with Lazarus 2.2.4. I'm using the debugserver/dbugintf for some time and implemented some small changes to improve this duo. The files are attached. Changes to debugserver: - The name of the executable file is fpcdebugserver as this is the name dbugint uses as default. - A few small changes to the user interface. The tray icon can be disabled. Changes dbugintf: - All Send... procedures converted to functions Send... : Boolean returning true on success. - Added a new variable RaiseExceptionOnSendError : Boolean (false by default) to control error handling. Is this the right way to handover or shall I follow some other instructions? It's OK. The normal procedure is to submit the patch on the bugtracker. But I will apply the patch as it is. Thank you ! Michael. ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] SqlDB TSQLQuery sqoAutoApplyUpdates does not work with ExecSQL
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
Re: [fpc-pascal] SqlDB TSQLQuery sqoAutoApplyUpdates does not work with ExecSQL
On Thu, 15 Dec 2022, Andreas Frieß via fpc-pascal wrote: 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. 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.___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] SqlDB TSQLQuery sqoAutoApplyUpdates does not work with ExecSQL
Am 15.12.2022 um 14:59 schrieb Michael Van Canneyt via fpc-pascal: On Thu, 15 Dec 2022, Andreas Frieß via fpc-pascal wrote: 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. 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. 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). 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. 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. Michael. ___ fpc-pascal maillist - fpc-pascal-pd4fty7x32k2wbthl531ywd2fqjk+...@public.gmane.org https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal ___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
Re: [fpc-pascal] SqlDB TSQLQuery sqoAutoApplyUpdates does not work with ExecSQL
On Fri, 16 Dec 2022, Andi Friess via fpc-pascal wrote: Am 15.12.2022 um 14:59 schrieb Michael Van Canneyt via fpc-pascal: On Thu, 15 Dec 2022, Andreas Frieß via fpc-pascal wrote: 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. 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. 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). "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.___ fpc-pascal maillist - fpc-pascal@lists.freepascal.org https://lists.freepascal.org/cgi-bin/mailman/listinfo/fpc-pascal
