Empty MySQL table returns recordset of 1 record?

I’ve got an empty table in MySQL. I am asking for a selection of all records that match a criteria. It should get a recordset of Nil but it’s returning 1 record where the fields are Nil. This happens with both a prepared statement and regular SQLSelect. Not sure what to make of this.

you only get a nil record set if there’s an issue with the query

In your case you’re probably getting a record set that is empty - EOF & BOF are both true when you initially get the record set

Hmm, I don’t understand. In the examples in the docs after executing a SQLSelect it shows testing the returned recordset for being Nil. That’s what I’m doing and when it should be Nil it returns an empty record. I sure it’s always worked for me.

But you are right, I am getting an empty set where the EOF & BOF are both true. Not sure what to do about that. Right now I’m dealing with it in code but think there must be a better way and I still have the feeling something’s messed up somewhere.

nil basically is “you’re query was malformed some how so there’s nothing to return” - an error of some kind
IF the query ran but there are not results you get a recordset - but it has no rows in it

The code is quite simple:

[code]dim rs As Recordset = myDatabase.SQLSelect(“select * from myTable”)

if rs <> nil and not rs.EOF then
// Do my table processing here
end if
[/code]

ah yes, combine the two. duh. Thanks!

Right after a select, if there was any record, EOF should be false and the cursor should be on the first record. So, it’s enough:

dim rs As Recordset = myDatabase.SQLSelect(“select * from myTable”)

if rs.EOF then // you are beyond the last available record
MsgBox “Table is empty”
else
// Do my table processing here
end if

[quote=38602:@Rick Araujo]Right after a select, if there was any record, EOF should be false and the cursor should be on the first record. So, it’s enough:

dim rs As Recordset = myDatabase.SQLSelect(“select * from myTable”)

if rs.EOF then // you are beyond the last available record
MsgBox “Table is empty”
else
// Do my table processing here
end if[/quote]

Still test for nil as well

Ok then. Here is it in context. :slight_smile:

Dim rs As Recordset = myDatabase.SQLSelect("select * from myTable")
If rs=nil then
   MsgBox "Oops. Database unexpected error!"
Else
   If rs.EOF then // you are beyond the last available record
      MsgBox "Table is empty"
   Else
      // Do my table processing here
   End If
End If

Thanks!

I just ran into a similar situation with a newly created database. I am getting a recordset with all the fields nil, but both BOF and EOF are false. The statement is [quote]SELECT *, Max(DateTime) FROM Readings WHERE UnitNo = 2[/quote] It works fine when there is data. The solution was[quote]if rs<>nil And Not rs.EOF and rs.Field(“DateTime”).StringValue <> “” Then[/quote]