Should an empty RowSet be Nil?

Databases
1

I just identified a logic error in my project due to the fact that I was expecting an empty RowSet to be Nil.

// db is an SQLiteDatabase
Var sql As String
Var rs As RowSet
sql = "SELECT * FROM TableName WHERE ID = ?"
rs = db.SelectSQL(sql, 2) // there is no record where ID = 2

If rs Is Nil Then
  // code to insert new row with ID = 2...
End If

I thought this should work as according to the LR for Database.SelectSQL:

If the SQL does not return data then Nil is returned. Nil is also usually returned if there is an error in the SQL statement, but you should instead catch the DatabaseException to check if an error occurred.

However, I have found that rs is not nil, it does return a RowSet, where
BeforeFirstRow = True
AfterLastRow = True
RowCount = 0

So I have changed my code to test for RowCount instead, which I hope will behave as I expect! Have I misinterpreted the LR or should I report some Feedback to have it changed?

Cheers,
Frank

2

Although the names of the properties are changed, that is the expected behavior in API 1. But in API 1, the only time one gets a NIL RecordSet (RowSet in API 2) is if there is an SQL error, whereas in API 2 one gets an exception…

Sounds like the Docs are in error for API 2 if they say when records =0 then the RowSet is NIL … Either that or API 2 was not implement as planned.

That said I would rather a RowSet with 0 records be returned rather than having to test for NIL to find that out… So I hope it is a documentation error!

-Karen

3

without error i would expect a RowSet object without rows.
so AfterLastRow is true or RowCount = 0 if supported.

4

OK thanks. I think I will submit some feedback, at the very least it is ambiguous what “If the SQL does not return data then Nil is returned” means…

Addit: This issue has already been reported in Feedback case #65192 in relation to ODBCDatabase and is marked as reproducible, with the comment “Could be a documentation issue.” :stuck_out_tongue: