Problem with nil recordset

Databases
1

Using SQLite

I am confused. I am building a table that contains a TEXT field with the UNIQUE constraint. Before I write each new record, I check to see if the value of that field already exist in the table.

I thought when no records were retrieved than the RecordSet would be nil. But when I run the code and check for rsCheckDup <> nil the test fails - it is never nil. However, using the debugger, I check the RecordSet and it is not nil, I check the contents, and all the fields are nil. So I changed the code to test rsCheckDup.RecordCount and it works fine.

Why isn’t the RecordSet nil? The Xojo docs on RecordSet show testing the rs for nil?

				sSQL = "SELECT BaseName FROM ImageMaster WHERE BaseName = '" + sMasterName + "'"
				rsCheckDup = dbSQL.SQLSelect(sSQL)
				if dbSQL.Error then
						MsgBox(dbSQL.ErrorMessage)
						sErrMsg = "DATABASE ERROR - " + dbLR.ErrorMessage
						UpdateLog("wReadLRCatalog.BuildImageTable1", sErrMsg)
						quit
						Return
				end if
				if rsCheckDup.RecordCount <> 0 then 
						sErrMsg = "The Image Master table already contains a record for Base Name " + sMasterName
						MsgBox(sErrMsg)
						bWriteImage = False
				end if
2

No… when no records are retrieved, rs.RecordCount = 0
but the STRUCTURE of the query is still represented by the RecordSet
If it is NIL, than an ERROR most likely occured ( Zero records is NOT an error)

as long as the SQL being executed is valid, your recordset will NOT be nil

3

In my experience using RecordSets with MySQL, the RecordSet will only be nil if there is an error with the database query.
A query that returns no records will still return a valid RecordSet.

I assume that the same is true for SQLite.

4

Bob has some insight: http://bkeeneybriefs.com/2012/06/database-programmers-do-yourself-a-favor/

5

You have certainly explained what I am experiencing. But the code below is right out of the Xojo RecordSet docs. It checks for an error and then checks for nil. It doesn’t check for record count.

But thanks, guys. You just confirmed that my working code is correct.

[code]Dim data As RecordSet
data = mDB.SQLSelect(sql)

If mDB.Error Then
MsgBox("DB Error: " + mDB.ErrorMessage)
Return
End If

If data <> Nil Then
While Not data.EOF
DataList.AddRow(data.IdxField(1).StringValue, data.IdxField(2).StringValue, _
data.IdxField(3).StringValue, data.IdxField(4).StringValue)

data.MoveNext

Wend
data.Close
End If
[/code]

6

When the query return no data the recordSet is not nil but its property EOF is true.

If there is some trouble in the query (error) then the RecordSet will be nil

Use the EOF property

7

RecordCount is probably not used in the example because it is not supported by all of the database classes.

@Paul Lefebvre may want to confirm, but checking for Error and Nil is redundant. One or the other should be sufficient.

As Antonio stated, use EOF to check for returned records (to be universal). In the example, the loop would never run if no records were returned.

8

thanks everyone.

I always know I will get answers here…

9

Not to side track the thread, but there are several great Filemaker/FoxPro developers here that can help you with the migration off of Filemaker/FoxPro to Xojo. Giving you the “pitfalls” and “challenges” that you might run into. Plus everyone is extremely friendly and helpful.