Criteria not met, but no DB error

Hi All… can someone tell me what I am doing wrong with this simplified database example code below? I know I should be checking for a DB error to see if the Recordset is Nil. But it never throws an error. It always returns recordset of 0 records with each field is Nil. But since the Index was not found, I would think it would return a Nil recordset and MainDB.Error = True. But that is never the case:

[code]Dim myRecordSet As Recordset
Dim intRecordCount As Integer

myRecordSet = MainDB.SQLSelect("SELECT * FROM MyTable WHERE MyPrimaryIndexField = " + Str(MyIndex))

If MainDB.Error Then
Return 0
Else
intRecordCount = myRecordSet.RecordCount
Return myRecordSet.Field(“MyField”).IntegerValue
End If[/code]

I use other code to check if the field is Nil, and react accordingly, but from what I have read here and other places, I am under the impression that I should be checking MainDB for an error and that would tell me if the recordset was Nil. Is that not correct?

I guess what I am asking is should I expect a Nil Recordset when the criteria is met, or a Recordset of 0 records (but not Nil), with each field in that record equal to Nil?

The table exists so there’s no error on the query
The query find NO matches - so you get an EMPTY valid non nil record set

Looks fine to me

OK, thanks, Norm. Time to review what I have been reading. Apparently I have been checking it correctly with the secondary check.

Indeed, as Norm says.
If it helps, consider the difference between getting a count and a list

Select count(1) from x where y = z;

versus

Select field1 from x where y = z;

In both cases, there may be no records which match the criteria.
You are saying that you expected the second one to return an error, but you definitely would have wanted the first case to return a single row with the value of 0 in the field.

In GUI terms:
a non-nil recordset with no records is similar to a listbox with no rows.
A db error would be the same as listbox is nil error

Mybe diit like this:

[code]Dim myRecordSet As Recordset
Dim intRecordCount As Integer

myRecordSet = MainDB.SQLSelect("SELECT * FROM MyTable WHERE MyPrimaryIndexField = " + Str(MyIndex))

If MainDB.Error Then
Return 0
Else
If myRecordSet<>Nil And Not myRecordSet.EOF Then
intRecordCount = myRecordSet.RecordCount
Return myRecordSet.Field(“MyField”).IntegerValue
Else
Return 0
End If
End If[/code]

In all cases (broken SQL Querry, no DB connection, no Records returned, …) it would Return 0

Thanks Jeff and Sascha. But I think that is where my confusion comes in. In Sascha’s code, after the Else statement, myRecordSet would always be <> Nil. If it were NIL, then a database error would have been issued, and would have been caught by the top line, “If MainDB.Error Then” At least that is my understanding, but I could certainly be incorrect on that.

You are correct. You’ll only get a nil record set if there is a db error. But you do need to check for an empty recordset using EOF.

Also you should deal with the db error, rather than returning 0 which will NEVER be correct. I usually put a break statement there to fix the problem during debugging, but that only works if the database schema remains static.

[quote=195030:@Wayne Golding]

Also you should deal with the db error, rather than returning 0 which will NEVER be correct. [/quote]

Maybe something like

If MainDB.Error Then Return MainDB.ErrorCode * -1 ...

Then deal with the error in the calling code.

This should cover all cases:

[code]Dim rst As RecordSet = TheDatabase.SQLSelect( — a query which always returns exactly one record — )

If rst Is Nil Then
If TheDatabase.Error Then
// Database error
Else
// Application error
End
Else
If Not rst.EOF Then
// valid result, but no record was returned, for example because the search criteria was not met
Else
// valid result
End
End[/code]

Isn’t it it as simple as?:

If you have a DB Error, deal with it.
ElseIf you have an empty result, deal with it.
Else, you have results to deal with

:slight_smile:

Not quite that simple. A db error implies a program fault rather than an empty recordset which is not. Better to raise an exception than return a “valid” response.

Doesn’t a lost connection also raise a DB Error? And this doesn’t have to be the programs fault then? But in any case, i would have to deal with it?

I still think it’s that easy and in exactly this order:

Handle Errors (whiich could also mean to raise an exception)
Handle invalid or empty results
Handle valid results

:slight_smile:

But here is the part that I am not understanding. Not a huge deal, but if I did, I would probably code it differently. Take ELi Ott’s code:

[quote=195033:@Eli Ott]
If rst Is Nil Then
If TheDatabase.Error Then
// Database error
Else
// Application error
End[/quote]

From what I understand, if rs is Nil, then Database.Error will always be true. Isn’t that correct? If so, // Application error is never reached. Again, I am not sot interested in hashing out working code here, just trying to understand exactly how it works so that my future code may be more efficient / correct.

Sascha has answered the question but I will reiterate.

The first check should be if there was a database error. This could be for any number of reasons, the error code and error message will tell you what.

If that passes (no database error) you now need to look at the recordset returned.

If it is nil or on eof then no records were returned by your query.

If that passes then you have at least one record that you can iterate.

So, the code:

[code]if db.error then
// A database error occurred. Deal with it here.
exit sub
end if

if rs = nil or rs.eof then
// There are no records returned.
exit sub
end if

// If I have got here then I have no database error and I have records in my recordset.
while not rs.eof
// Process each record here
wend
[/code]

Yes, I saw his code. But in your code, the “rs = nil” is never a possibility to be true, the database error would have been issued and caught in the first section. Again, I’m not looking for working code, I am just trying to understand how it works exactly.

But bugs in the runtime can happen and it is always a good idea to be prepared.

I just feel that it doesn’t hurt to check all possibilities, that way your code is safe.

As to the why…

It just does, alright?

:slight_smile:

I’m just comparing it to what I have read in docs, on the forums, xDev magazine and other sites. I am just seeing this issue approached in different ways and I am just trying to understand it fully. I think my confusion came from my own misunderstanding of this, from a well known Xojo blog:

“A better way to do this is to check for the error because we know, from experience, that the ONLY time a nil recordset is returned is when there is a database error.”

I mistakenly equated a Nil recordset with an empty recordset. My secondary check was catching the empty recordset, but now I understand exactly what is going on.