Lazarus

Programming => Databases => Topic started by: senglit on September 08, 2022, 06:00:26 pm

Title: [SOLVED] query.ChangeCount is always 0
Post by: senglit on September 08, 2022, 06:00:26 pm

hi all,
I got something like this:

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

1. var
2.   dbConn: TSQLite3Connection;
3.   trans: TSQLTransaction;
4.   query: TSQLQuery;      
5.  
6. procedure NewBox(sn:integer;name:string);
7. var
8.  cc:integer=0;
9. begin
10.     query.Close;
11.     query.SQL.Text :='insert into box(sn,name) values(' +sn.ToString + ',"' + Name + '")';
12.     query.ExecSQL;
13.     cc := query.ChangeCount;
14.     trans.Commit;
15.     query.Close;
16. end;
17.  

After NewBox is called, with database browser I can see the new record is added into the table "box". But I always got cc as 0. why?

ps: I tried with mysql5.7 and sqlite3, with both of these 2 database type, I always got cc as 0.

Title: Re: query.ChangeCount is always 0
Post by: JanRoza on September 08, 2022, 07:44:30 pm

Maybe you have to commit first and after that assign the count to cc.

Title: Re: query.ChangeCount is always 0
Post by: Arioch on September 08, 2022, 09:41:40 pm

I wonder, what would you even expect there. It was always differently named in Delphi

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

1.     query.ExecSQL;
2.     cc := query.RowsAffected;
3.  

See docs - https://docs.getlazarus.org/#fcl+sqldb+tcustomsqlquery.rowsaffected#

And then when the trend became streamlining another pattern emerged:

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

1.     cc := query.ExecSQL;
2.  

ChangeCount ? What that even is, from what a library?

Is it perhaps a number of changes that were cached locally and NOT sent to server, something along MIDAS library patterns, like

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

1. TDataset.Insert;
2. TDataset.FieldByName('xxx').AsInteger := 10;
3. TDataset.Post;
4. cc := TDataset.ChangeCount;
5. TDataset.ApplyCachedUpdates;
6.  

Title: Re: query.ChangeCount is always 0
Post by: senglit on September 09, 2022, 04:37:48 am

Yes, you are right. I should use RowsAffected instead of ChangeCount. Problem solved. Thank you!

Quote from: Arioch on September 08, 2022, 09:41:40 pm

I wonder, what would you even expect there. It was always differently named in Delphi

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

1.     query.ExecSQL;
2.     cc := query.RowsAffected;
3.  

See docs - https://docs.getlazarus.org/#fcl+sqldb+tcustomsqlquery.rowsaffected#

And then when the trend became streamlining another pattern emerged:

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

1.     cc := query.ExecSQL;
2.  

ChangeCount ? What that even is, from what a library?

Is it perhaps a number of changes that were cached locally and NOT sent to server, something along MIDAS library patterns, like

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

1. TDataset.Insert;
2. TDataset.FieldByName('xxx').AsInteger := 10;
3. TDataset.Post;
4. cc := TDataset.ChangeCount;
5. TDataset.ApplyCachedUpdates;
6.  

Title: Re: [SOLVED] query.ChangeCount is always 0
Post by: Zvoni on September 09, 2022, 12:31:00 pm

And use Parameters....

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

1. var
2.   dbConn: TSQLite3Connection;
3.   trans: TSQLTransaction;
4.   query: TSQLQuery;      
5.  
6. procedure NewBox(sn:integer;name:string);
7. var
8.  cc:integer=0;
9. begin
10.     query.Close;
11.     query.SQL.Text :='insert into box(sn,name) values(:psn,:pname)';
12.     query.ParamByName('psn').AsInteger:=sn;
13.     query.ParamByName('pname').AsString:=name;
14.     query.ExecSQL;
15.     cc := query.RowsAffected;
16.     trans.Commit;
17.     query.Close;
18. end;
19.  

Title: Re: [SOLVED] query.ChangeCount is always 0
Post by: Arioch on September 09, 2022, 05:55:39 pm

yeah, this too.

One day he can find a hard way that dates and times have different human format with different programs and/or locales, or that NULL and VOID sometimes are not surnames but something different, like one american programmer learnt hard way :-)

https://bobby-tables.com/