[Solved!] Get the last rowid of a particular table

[heebiejeebies]

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!

[balazsszekely]

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]

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.

[Sieben]

What database are you using? And what components to access it?

[balazsszekely]

@heebiejeebies
Please attach a small demo project where the issue is clearly visible.

[heebiejeebies]

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.

[Zvoni]

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

[heebiejeebies]

Thanks for the answers everyone, I figured it out and it was actually very simple.

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

1. KDBGrid1.SelectRow(KDBGrid1.MaxRow);  

[alpine]

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?

[heebiejeebies]

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?

[cdbc]

Hi
"TSQLite3Connection.GetInsertId" does exactly what you want...
Hth -Regards Benny

[heebiejeebies]

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.

[dseligo]

and nothing else does.

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

[Zvoni]

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