SQLite Basics documentation

Databases
1

This document SQLite basics have inteesting examples. But I have hard time with one:

Var rs as RowSet = App.db.SelectSQL("select * from person where id = (select max(id) from person)")

I replaced the TABLE name and removed App to conform to where I pasted it, but it does not compiles.

This is meant to ask the last Row of the Data Base.

Before going there, I went to SQLiteDataBase, the example SQLite, and the Forum and failed to reach what I was searching.

There are 2512 Records in that SQLite file.

image

I will explore the DB navigation later today.

In the mean time, is this code correct ?

Desktop
Xojo 2024r4.2
Sequoia 15.4
MacBook Pro 13" m1

2

Note that there is no “last row” in an SQL database. If you don’t specify a WHERE or ORDER BY then the order in whichb the rows are returned is undefined.

Otherwise, here your statement compiled as soon as I added db as SQLiteDatabase to app.

What is the error message you get?

3

I do not know.

I add that line after code to open and connect.

But the main problem is not there, read the page and try to follow the tutorial.

Be back after that. Thanks.

4

Emile, is it that your app doesn’t compile or that you are getting an exception when you run?

Either way, what’s the message?

5

I just copy/paste the same line at the same location, and do not get the error anymore.

But the displayed user is the same: the last Row is not displayed.

I have to go now, be back in two hours or so and will check stepping in the debugger what happened (rs = Nil I suppose).

6

If you get this:
image

you should be able to see what Xojo reports as an error.

7

The problem is I do not get this anymore.

8

It’s OK for me, but what about the documentation ?

and here’s what the debugger display:

image
image794×272 31.1 KB

How do you feel today ?

9

Which documentation?

That’s showing you a RowSet that is part-way through being processed.

Waiting to get a date for my operation.

10

If you simply want the record of the database that has largest ID, you can more efficiently use the following SQL:

Var rs as RowSet = db.SelectSQL("select * from person order by id desc limit 1")

It’s one query, rather than two. The subquery that retrieves the max id and the other that selects that row. So long as there’s an index on ID it should be very quick.

11

Read the thread title, the first sentence of the opening text (entry #1).

Nice idea. It will be better to forward that to the documentation writer (since that line comes from the current documentation and it used it widely).

12

Emile, we are making assumptions about your fields - probably correct assumptions, but assumptions none the less.

For example, if “id” is just a field of data then you may not get the last record unless the record with max(id) is sorted to the bottom ahead of time or via the “order by” that Ian gave. Also, although “id” may be thought of as being unique, That doesn’t mean there is only one record with the max(id) - for example, a database that hold records of customer purchases (multiple records for the same customer).

When possible, it’s handy to provide a small sample of what the data looks like. Next, given that sample, what would you’d like the result of the code to look like? That way we can recommend how to get from here (your starting data) to there (the result you seek). Maybe it requires fixing a problem with your provided code statements; maybe it’s recommending a more efficient way of writing the SQL.

When I have a problem with a database, I find it helpful to leave the explicit situation alone and create a small example that will exercise just the action I’m looking for. For example, for your case, it seems like I’d make a database of five different fruits (apple, orange, cherry, pear, lemon). With your data example, I’d see how your “id” is determined/created. Then I’d run an SQL statement to create the result you want. If it works, then one can compare the code that works with what doesn’t and see what needs adjustment or replacement in the original code.