It is extremely rare for me to care about a scrollable recordset, but I have one method in a project that takes a recordset as an argument, and became desirable to make two passes over the RS. This worked fine when I was using MBS SQL version 4.1.10, but now the MoveFirst is failing to return to the first record data in the second loop when using MBS SQL 4.2.5
The database server is the same: Postgres 9.5.14, and the condition acts the same using both Xojo 2018R1.1 and 2019R1.1 – it also works with 2019R1.1 and MBS 19.x if I replace the MBS SQL plugin with my 4.1.10 version from MBS 18.4
But I don’t know if it is safe / desirable to run the SQL plugin from 18.4 when otherwise using MBS 19.x plugins.
After I create the SQLDatabase() object and before the Connect(), the Scrollable property is false (as expected) then I set to true before doing the Connect(). Prior to and after running the SQLSelect(), the scrollable property reports true.
But after my iteration over the recordset, I do a .MoveFirst (while the scrollable property reports true). And I can do another loop over the records, but every field comes back nil instead of the record contents as it did with MBS SQL 4.1.10
Pseudo code looks like this:
// First pass
while not rs.eof
' Here every rs.Field() returns the proper value
rs.MoveNext
Wend
// Second pass
rs.MoveFirst
while not rs.eof
' Here every rs.Field() returns nil
rs.MoveNext
Wend
But the second pass otherwise seems to work – its loop does iterate just supplies nil values.
I thought I may be able to work around it using CloneMBS like below, but that does not work either as the CloneMBS creates a similar outcome:
system.DebugLog("pre clone rs EOF is " + rs.EOF.StringValue + ", clone EOF is N/A")
dim clone as RecordSet = rs.CloneMBS()
system.DebugLog("post clone rs EOF is " + rs.EOF.StringValue + ", clone EOF is " + clone.EOF.StringValue)
// First pass, uses cloned RS
while not clone.eof
' Here every clone.Field() returns the proper value
clone.MoveNext
Wend
// Second pass uses original rs
system.DebugLog("pre MoveFirst rs EOF is " + rs.EOF.StringValue + ", clone EOF is " + clone.EOF.StringValue)
rs.MoveFirst
system.DebugLog("post MoveFirst rs EOF is " + rs.EOF.StringValue + ", clone EOF is " + clone.EOF.StringValue)
while not rs.eof
' Here every rs.Field() returns nil
rs.MoveNext
Wend
The debug log shows the following states for the EOF property:
pre clone rs EOF is False, clone EOF is N/A
post clone rs EOF is True, clone EOF is False
pre MoveFirst rs EOF is True, clone EOF is True
post MoveFirst rs EOF is False, clone EOF is True
Does this suggest a regression in MBS SQL 4.2.25 vs 4.1.10? Or am I doing something wrong?
The scrollable property is set to true prior to the .Connect(), and still reports true prior to the above loops.
Edit to clarify the first paragraph.