First this is a Desktop App and I am on Mac OS 10.9.5 with latest Xojo.
I have a view with a lot of fields across 2 tables
I use it as a table in a SELECT statement and iterate through the resulting recordset (RSView) in a While Loop… This works fine on it’s own.
Inside that While Loop I perform select on a List table containing compound names using data one of the fields from RSView to get the shortest name to display. This results in the recordset RSName:
While Not RSView.EOF
CmpKey = RSView.Field("CompoundKey").StringValue
RSName = DB.SQLSelect( "SELECT Name, Min(LENGTH(Name)) FROM CompoundNames Where CompoundKey = "+ CmpKey)
CompoundName = RSName.IdxField(1).StringValue
RSName.Close
... ' Does the work
RSView.MoveNext
WEnd
The Same DB connection is used for both selects.
The first time through the While loop all works as expected…
BUT the 2nd time through RSName is NIL even though when I paste the actual select string into the the FireFox SQLite Manager the select Works as expected an in general it works elsewhere in the app while looping through other RecordSets
Looking at the DB error I get on the RSName Select, it says the DB is closed…
BUT IT"S NOT. If I have it ignore the NIL RSName and don’t do more RSNAme Selects in the loop the rest of the loop iterations executes fine…
While Not RSView.EOF
CmpKey = RSView.Field("CompoundKey").StringValue
RSName = DB.SQLSelect( "SELECT Name, Min(LENGTH(Name)) FROM CompoundNames Where CompoundKey = "+ CmpKey)
If RSName <> NIL then
CompoundName = RSName.IdxField(1).StringValue
RSName.Close
End if
... ' Does the work
RSView.MoveNext
WEnd
That loops runs… But all the compounds have the same name - so the DB can not be closed!
I am at a loss here… Any help would be appreciated!
Well, the only time RS will be nil is if there is an SQL error. Since you’re not checking for an error anything can happen. Checking for nil is worthless, in my opinion, because it works around a symptom and tells you nothing of the original problem.
I can see you’re missing the quotes on the CmpKey component of the query which should make RSName nil for every iteration leaving CompoundName unchanged.
CompKey is an integer so it does not need to be quoted… Also when I copy the Query string (put it in a temp var and put it on the clipboard, the query itself works when I Run it in SQLite Manager on the same DB… so I don’ think that is the issue.
In any case I worked around it by doing a direct select on one table instead of the view and put the names in a dictionary before the View loop runs, and that works.
I left out a detail… I subclass SQLite DB and override the following methods- but it’s all completely hidden from my code so I don’t think about it:. I took that out as part of troubleshooting to make sure it was not involved.
Public Function SQLSelect(SelectString As String) as RecordSet
DIm RS as RecordSet = Super.SQLSelect(SelectString)
If Error Or RS Is NIL then
Dim C as New Clipboard
C.Text = SelectString
C.Close
If Error then
Raise NEw DatabaseException( ErrorCode, ErrorMessage + EndofLine _
+ SelectString)
Else
Raise New DatabaseException( -9999, "NIL RecordSet" + EndofLine _
+ SelectString)
End if
End if
Return RS
End Function
Public Sub InsertRecord(tableName As String, dbRecord As DatabaseRecord)
Super.InsertRecord(tableName, dbRecord)
If me.Error Then
Dim Err as New DatabaseException(ErrorCode, ErrorMessage)
Raise Err
End if
End Sub
Public Function Connect() as Boolean
If Super.Connect Then
SQLExecute("PRAGMA foreign_keys = ON")
Return True
Else
If Error Then Raise New DatabaseException(ErrorCode, ErrorMessage)
End if
End Function
[quote=323047:@Jean-Jacques Chailloux]No No No!
CmpKey MUST be… well not quoted but converted to a string:
… Where CompoundKey = "+ Str(CmpKey))
jjc_Mtl[/quote]
It was a string… Would not have compiled otherwise… When I said Integer, I was taking about the data in the string, which is why it did not need to be quoted.