Recent

Author Topic: [Solved!] Get the last rowid of a particular table  (Read 1331 times)

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
[Solved!] Get the last rowid of a particular table
« on: March 04, 2023, 08:50:27 am »
Hi all,

What I'm actually trying to achieve here is to go to the last entry on a KDBGrid after inserting a new entry into it.  This does not work:

Code: Pascal  [Select][+][-]
  1. KDBGrid1.Datasource.DataSet.Last;    

It doesn't work at all in a regular DBGrid and is unusably bugged in a KDBGrid; half the time it does nothing and when it does "work" it blanks out half the data in the table until you scroll up to refresh the cells.  Open to any alternative suggestions, but the method I'm attempting at the moment is to grab the last entered rowid and then select that row in the KDBGrid.  This is the select statement I'm using:

Code: Pascal  [Select][+][-]
  1.   UserFileLoad.SQL.Text:=('SELECT rowid, * FROM `Mytable` WHERE _rowid_ = (SELECT MAX(rowid) FROM `Mytable`);');  

But then how do I actually get the rowid from there?  I did the obvious dumb thing of trying to read it like a regular field, but it does not work.

Or am I going about this completely the wrong way?
Thank you!
« Last Edit: March 06, 2023, 10:24:13 am by heebiejeebies »
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

balazsszekely

  • Guest
Re: Get the last rowid of a particular table
« Reply #1 on: March 04, 2023, 09:26:36 am »
There is a returning clause when inserting values into a table, something like this:
Code: MySQL  [Select][+][-]
  1. insert into tablename(name, age)
  2. values ('John', 23)
  3. returning values row_id
  4.  
I assume that row_id  is a primary(autoincrement) field, so is omitted from insert. The point is that after insert you already now the ID of the newly inserted row, no need for second select. Now all you have to do is to locate the field:
Code: Pascal  [Select][+][-]
  1. KDBGrid1.Datasource.DataSet.Locate('row_id', row_id, []

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: Get the last rowid of a particular table
« Reply #2 on: March 05, 2023, 01:57:27 am »
Thank you.  Unfortunately on my system this suffers from the same bug as using Datasource.Dataset.Last. :(

If I use KDBGrid1.SelectRow(i); then it works fine if I manually specify a rowid number.  Just can't figure out how to pass the rowid of the last row onto that function.
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

Sieben

  • Sr. Member
  • ****
  • Posts: 310
Re: Get the last rowid of a particular table
« Reply #3 on: March 05, 2023, 03:43:18 am »
What database are you using? And what components to access it?
Lazarus 2.2.0, FPC 3.2.2, .deb install on Ubuntu Xenial 32 / Gtk2 / Unity7

balazsszekely

  • Guest
Re: Get the last rowid of a particular table
« Reply #4 on: March 05, 2023, 02:41:59 pm »
@heebiejeebies
Please attach a small demo project where the issue is clearly visible.

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: Get the last rowid of a particular table
« Reply #5 on: March 06, 2023, 08:09:28 am »
Not sure if I was clear with the question, and sorry that I didn't specify I was using Sqlite.  I have a primary key field just called 'ID', but what I'm trying to get is the rowid, so the default identifier that's always there in Sqlite even if you don't create a primary key field.  This is what the KDBgrid wants when using the SelectRow function.  The KDBGrid is connected via a TSQLQuery and TDataSource.
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: Get the last rowid of a particular table
« Reply #6 on: March 06, 2023, 09:18:40 am »
First off: trying to get the "last" PrimaryKey the way you showed is nonsense.
Why "1"? Sort your Grid by something else than ID, and you'll see (either "pre-sorted" by the underlying Query, or by User-Action).
Why "2"? Through time you'll probably have "gaps" between ROWID's. Now insert a new record using a "free" value for your ID, that's somewhere "in between" two already existing rows

Next: Why querying "rowid" at all? If you have a PrimaryKey of Datatype Integer, this field (usually "ID") becomes an Alias for ROWID,
and you shouldn't have any Problems accessing that Field

Next: Is this Primary Key you have there setup as AUTOINCREMENT? (I hope not, because in SQLite you don't need it)
If yes, there might be a way through a "backdoor"

EDIT: And GetMem showed the usually accepted way, except returning your PK instead of rowid

btw: If you're using the SQLite-specific Connection-Objects, i wouldn't be surprised to find a "LastInsertRow"-Function in either the Connection or the Query-Object
« Last Edit: March 06, 2023, 09:38:32 am by Zvoni »
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: Get the last rowid of a particular table
« Reply #7 on: March 06, 2023, 10:23:52 am »
Thanks for the answers everyone, I figured it out and it was actually very simple.

Code: Pascal  [Select][+][-]
  1. KDBGrid1.SelectRow(KDBGrid1.MaxRow);  
« Last Edit: March 06, 2023, 10:25:42 am by heebiejeebies »
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

alpine

  • Hero Member
  • *****
  • Posts: 1038
Re: Get the last rowid of a particular table
« Reply #8 on: March 06, 2023, 10:42:53 am »
Thanks for the answers everyone, I figured it out and it was actually very simple.

Code: Pascal  [Select][+][-]
  1. KDBGrid1.SelectRow(KDBGrid1.MaxRow);  
Are you sure the newly inserted row will appear always at the KDBGrid1.MaxRow?
"I'm sorry Dave, I'm afraid I can't do that."
—HAL 9000

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: [Solved!] Get the last rowid of a particular table
« Reply #9 on: March 06, 2023, 10:50:37 am »
Well, it has worked in all my test scenarios and I can't imagine any scenario in which it wouldn't?  Maybe if I allowed the user to sort by anything other than the primary key, but I don't - so no matter what filter is applied, the Maxrow of the grid should always represent the highest primary key number?
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

cdbc

  • Hero Member
  • *****
  • Posts: 1028
    • http://www.cdbc.dk
Re: [Solved!] Get the last rowid of a particular table
« Reply #10 on: March 06, 2023, 11:00:57 am »
Hi
"TSQLite3Connection.GetInsertId" does exactly what you want...
Hth -Regards Benny
If it ain't broke, don't fix it ;)
PCLinuxOS(rolling release) 64bit -> KDE5 -> FPC 3.2.2 -> Lazarus 2.2.6 up until Jan 2024 from then on it's: KDE5/QT5 -> FPC 3.3.1 -> Lazarus 3.0

heebiejeebies

  • Full Member
  • ***
  • Posts: 127
Re: [Solved!] Get the last rowid of a particular table
« Reply #11 on: March 07, 2023, 10:03:56 am »
Thanks Benny!

So it turns out there was the same issue with my solution - sometimes it works, sometimes it doesn't.  >:(

I don't know what on earth is going on, but I jerry-rigged a solution that seems to always work, and because I am a kindly, gentle man with golden locks flowing free in the breeze of the green meadow while I strum sonnets on my lute, I thought I'd better share it for any future googlers.  Mainly because knowing my memory, those future googlers will be me.  So, essentially, note-to-self - here is the solution:

Code: Pascal  [Select][+][-]
  1. KDBGrid1.SelectRow(KDBGrid1.MaxRow);
  2. KDBGrid1.Datasource.DataSet.Last;
  3. KDBGrid1.Datasource.DataSet.Last;
  4. KDBGrid1.Datasource.DataSet.First;
  5. KDBGrid1.Datasource.DataSet.Last;
  6. KDBGrid1.Datasource.DataSet.Last;
  7. KDBGrid1.Datasource.DataSet.Last;
  8. KDBGrid1.Datasource.DataSet.First;
  9. KDBGrid1.Datasource.DataSet.Last;
  10. KDBGrid1.SelectRow(KDBGrid1.MaxRow);
  11. KDBGrid1.Datasource.DataSet.Last;
  12. KDBGrid1.Datasource.DataSet.Last;
  13. KDBGrid1.Datasource.DataSet.First;
  14. KDBGrid1.Datasource.DataSet.Last;
  15. KDBGrid1.Datasource.DataSet.Last;
  16. KDBGrid1.Datasource.DataSet.Last;
  17. KDBGrid1.Datasource.DataSet.First;
  18. KDBGrid1.Datasource.DataSet.Last;  

Someone will tell me I'm wrong, but, basically, shhhh. It works.  It takes care of the blank rows bug and seems to always work - and nothing else does.
« Last Edit: March 07, 2023, 10:06:00 am by heebiejeebies »
Fedora 38/Lazarus 2.2.4- FPC 3.3.1

dseligo

  • Hero Member
  • *****
  • Posts: 1196
Re: [Solved!] Get the last rowid of a particular table
« Reply #12 on: March 07, 2023, 11:42:51 am »
and nothing else does.

I doubt that. Probably there is a bug somewhere in code. Most likely in your code.

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
Re: [Solved!] Get the last rowid of a particular table
« Reply #13 on: March 07, 2023, 11:53:46 am »
and nothing else does.

I doubt that. Probably there is a bug somewhere in code. Most likely in your code.

Agreed.
I'd try to run everything without DB-Bound controls, then with the standard DB-Bound-Controls.
If there is still no Bug, then it's either the KControl, or his own code
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018