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
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
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.
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)
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.