SQLite RecordSet = NIL from SELECT statement with error #0

Databases
1

Hello. I am currently using Xojo 2018r2 for this project in Windows.
In a select statement, I sporadically have a recordset return as NIL with an error set to 0 (zero) message ‘not an error’. Normally, the SQLSelect correctly returns an empty recordset when there are no records corresponding to the selection criteria.

In one case where this has happened, I am using the SELECT statement to determine whether a specific record already exists. If it does (a recordset with a row returned), I UPDATE it. If does not (an empty recordset), I use INSERT to add the row.

My problem is that I do not know how to handle a recordset = NIL where there is no error produced. I certainly cannot update it, but neither can I be sure that it needs to have the row INSERTed.

The query is simple. An example is:

SELECT id, date_time FROM order_export WHERE id_profiles = 342356 AND line_data = ‘11,18,22890,12,3,8’ AND id_quotes = ‘Windows’ AND q_type_version = 0 AND creation_source = ‘System’ AND added_on = ‘2022-09-01 18:32:32’

When this exact string that failed in Xojo SQLSelect is pasted into a sqlite database browser, it produces the expected results; a recordset either empty or with a single row.

Can anyone give me a definitive answer when or why the recordset would be NIL and the error # set to zero?

Is there a remedy so I can know how to treat the row as an UPDATE or INSERT?

Thanks for the help.
Rocky Scofield

2

RecordSet is nil if the SQL is bad. A query that returns 0 rows will return a RecordSet object. Bob Keeney, an expert, points out in comment #8 that the reason we get a nil RecordSet is because the error happens inside the database, not the Xojo framework.

I don’t have any ideas for why the error code would be 0. If memory serves, checking for the database error should work. If you’re not already doing error checking the way Bob’s article suggests, I would really recommend it.

3

What happens if you use SelectSQL (API 2) instead of SQLSelect?

4

Sorry for the delay in responding.

@Tim_Parnell, I have similar error reporting for sql that Bob Keeney mentions. I have all the xojo database stuff in a wrapper that handles things like this. I understand that a recordset = NIL is supposed to ALWAYS indicate an error. My database wrapper reflects that.

But I am definitely, but sporadically, getting returned a NIL recordset with the error# set to 0 (zero) and message is “not an error”. I trap the error information on the very first line of code after the SQLSelect. (This is because the error info can reset itself soon after.) The exact query string executes correctly in a database browser. So, I know it is not a syntax issue.

@Christoph_Emrich, I cannot check this behavior in api 2.0.

  1. This project (a service) is built in Xojo 2018r2
  2. I am not upgrading until Xojo releases a must have feature that I need or when a full 1.0 version of Android is released, which ever comes first.

Has anyone done bettere using the MBS SQLite plugin?

Thanks,
Rocky Scofield