Nil RecordSet returned but no DB.Error

General
1

I have a normal SQL query to return * from an existing table in an SQLiteDatabase. I know the table exists. It’s returned in the DB schema and it’s supposed to contain millions of records.

The query to select * from this table returns a Nil recordset. Everything I’ve read on the forum and elsewhere says that only happens when the query is malformed. But the query isn’t malformed. And, everyone says when the result is Nil, the database will throw an error. But the DB doesn’t throw an error.

The table exists, but I get a Nil recordset, and there is no error in the database.

Anyone have an idea what’s going on or what steps I should take to find out?

2

Do you have permissions to access the table, with your Xojo login?

3

To start with, lets see the schema and the query.

Try with SELECT COUNT(*) … instead of SELECT * … - what does that give you. That MUST return one record.

1 Like
4

Yes.

Good idea! Unfortunately I am remote debugging and this will take a long time to do, but thank you, I really should have thought of that!

5

It is possible to have you method ?

6

Thanks to your help, I’ve just found the cause of the problem.

It appears that the DB most likely was returning an error, and the problem was that the error dialog was not being shown because it was being triggered by a timer which went out of scope before firing. A real “forehead slapper” !

7

Well, that was part of the problem, but I just got the report back from the client, and it’s basically what I said before …

Here’s what we know:

  • the logs show the table was successfully added to the DB without error
  • we know the table exists, it’s returned in the schema
  • checking the record count(*) of the table shows the correct number of records are there
  • when attempting to load the table for display, the recordset is Nil, and DB.Error is true (this is API 1.0), but the error code is 0 (zero) = not an error.

Any more ideas?

8

Have you tried the using the column names instead of the asterisk *?

e.g., SELECT col1, col2, col3 FROM myTable

Maybe with the asterisk, you’re running into some kind of name conflict or column limitation with that particular version of the db? Just guessing…

9

I suppose that could reveal whether some column contains corrupted data, but I wonder what would that be? There are VARCHAR columns which are being fed memoryblock bytes. So far that has not caused any problems, but maybe there is a case where that could cause a problem?

Thanks for helping my (shrinking?) brain to work. I do need to sleep now but will return to this in the morning.

1 Like
10

I presume you’re not using SQL reserved words as column names, if so you can get around it by wrapping each column name in ``s. As in Select col1, col2, col3 FROM myTable.

Is there another app you can run the SQL statement in. I’m thinking of something like MySQL workbench or MS SQL query analyser?

11

Or use the sqlite3 CLI program to run the query. That’s always the best bet.

NB SQlite doesn’t have varchar or any of that stuff. It just has TEXT.

12

You can try add LIMIT (some as 100 or 1000) to your query and see if then it returns something.

13

Make a sample in API2

Currently we don’t use “DB.Error”. On error an exception arises and we just intercept it, the message says what is going on. Interestingly, many times the error code is 0.

If API2 says some message, check it.

14

Get them to mail you a copy of the database and try it locally… should be easier than remote debugging and waiting for responses.

15

Still no sight of the schema or the query.

In addition, presumably for your SELECT, you are using SQLSelect and not SQLExecute?

Also, I’d be inclined to try:

Try
rs = dbSQLSelect (your SQL)
Catch e as DatabaseException
print out the error messhere here
End Try

(with the correct syntax, of course)

16

Yes, SQLSelect …

Okay, so this is one of those cases. I did see the Try … Catch in the docs but as I mentioned, the app is API 1.0. Especially since I read about trouble with databases and API 2.0 I’m not changing any database code to API 2.0, but I could write a little test in a button or something just to see if it behaves differently.

Good idea, I’ll try that.

I would have, but the DB is many GB and the client has no way to send it.

The project is under NDA, so although I can discuss generic issues publicly like this, I can’t share any of the details about what it actually is.

17

Excellent advice. I’ll see if I can work that out (again, remotely).

18

OK - you don’t have the try/catch. But after the SELECT, the errorcode/message may nonetheless be set, if there’s an error.

19

Right. There is an error (DB.Error = true) and the error code is set, to 0 (zero) which means “not an error”. And Rick mentioned that this can happen, but didn’t say why (and my guess is nobody knows).

20

Humph! Well, try with the sqlite CLI when you can.