SQLite: Another row available

Databases
1

I’m getting reports of an exception that makes no sense to me. I’m getting a DatabaseException with the message another row available on the line Var Rows As RowSet = SelectSQL("SELECT * FROM maps ORDER BY official DESC, sort;").

This is one of the least helpful errors I’ve ever seen. I also haven’t been able to reproduce it myself, this is coming from my automated reporter. It’s also pretty new, though my IDE version has not changed. This is likely to be something to do with the database and not a Xojo bug, but I don’t understand the error. I don’t know what to look for. And Google has not been helpful.

2

Just grasping here, but maybe not really an error and shouldn’t throw an exception; see the SQLite docs for: Result and Error Codes (is 100 the result code in the error?) and also the note at the bottom here (under “goofy interface alert”): Evaluate An SQL Statement

1 Like
3

Sounds like that could be it. I don’t have the error number logged, but I guess I should add that. If this is a “not-really-an-error” error, I’ll have to find a way to reproduce this reliably to convince Xojo to fix it. Too early to tell though, since I’m not certain that’s what is going on.

4

It sounds like it might be quickest to give the customer a build to try, where you check the error number in a Try catch block and if 100 then ignore it, otherwise re-raise it?

(Or use API 1 and just check if the error code. If it is neither 0 or 100, then raise an exception yourself otherwise just keep going.)

Then see if that fixes their issue.

-Karen

5

Aren’t you missing the database ie db.SelectSQL. Also is the ‘;’ needed at the end?:

Var Rows As RowSet = db.SelectSQL("SELECT * FROM maps ORDER BY official DESC, sort") .

6

The semicolon is not required, though I usually include it. I did doctor the line a little because the original is Self.SQLSelect. It’s a custom subclass that gave me API2-like features before API2. That isn’t important to the issue though, so I chopped that off, but should have replaced it with something.

7

Yeah but unfortunately I don’t know which customer.

8

Some databases don’t accept the * when you ORDER BY columns that don’t exist.

I recommend that you make this query in the query tool within the database.

The use of “;” in POSTGRES it is important!

If what you want is to sort the sort field, the correct way to do it is like this:

SELECT 
	first_name,
	LENGTH(first_name) len
FROM
	customer
ORDER BY 
	len DESC;

Another example

SELECT
	first_name,
	last_name
FROM
	customer
ORDER BY
	first_name ASC,
	last_name DESC;

Note that it does not use the “*”.

9

Right but we’re talking about SQLite. The semicolon is implied at the end of the string. I do normally use it because it’s good practice, but it’s just forgotten here. The code works 99.99% of the time. If it were an issue with the wildcard, ordering, or semicolon, I’d see it every time. Plus, SQLite is more forgiving about ordering by columns that were not selected.

1 Like
10

Did you happen to forget to close an recordset?
Remeber that you sometimes need Finally to close a sticky rowset in scope. Local scoped rowsets close automaticly after scope loss.

11

Nope. This is happening on the select statement. And I never, ever retain a rowset, they are always locally scoped.

For what it’s worth, I’m using For Each Row As DatabaseRow In Rows to iterate, but like I said, the exception happens on the select.

1 Like
12

Yeah sorry. It’s not an error but rather a result code:
https://sqlite.org/rescode.html

As @Matthew_Dinmore suggested
If the error code is 100, it’s a result code of “(100) SQLITE_ROW”
which indicates there is another row to step, so it’s probably a xojo bug that’s not handling the stepping ?
(step = MoveToNextRow or For each… iterate ?)

Maybe they are using the legacy code? not sure how the sqlite 3 is implemented in xojo but it sounds like you sould NOT get that exception… (since it’s a result code, not an error)

Correct me if i’m wrong, this is how i read it from the docs of sqlite

13

For now the best I can do is add the error number to the exception message and wait for it to trigger next time I make a build. But yes I agree that this is feeling like something that Xojo will need to fix.

1 Like
14

SQLite is flexible in everything. Include the type of data to handle. Very different from its competitor like Postgres.

15

I know. I’m very familiar with both.

16

What’s very important here is: Is anyone else seeing such kind of fail in Xojo 2021 releases? Can anyone replicate the issue?

1 Like
17

I doubt it. I’m not even seeing it, and I spend boatloads of time in the app. In this case my had been released for about a week and downloaded hundreds of times in that period before anybody ran into the issue. My exception reporting is 100% automated, so this isn’t even a case of somebody hitting it and failing to report. So whatever is happening is exceptionally rare. It’s not an issue I’d call a showstopper, but it’s definitely a curiosity.

18

Sort SQLite
I leave it out then it works for me
sort outputs the error message Column does not exist

Var rs As RowSet
rs = db.SelectSQL(“SELECT * FROM customer Order BY lastName DESC, firstname ASC”)

1 Like
19

That could mean the bug is when the sqlite “sort” is called… narrowing it down here …

20

That’s a good point. Sort is a reserved word in sqlite and to define your field you may need to enclose it in single quotes.