Scrollable recordset using MBS SQL to Postgres

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.

Well, I’d have to check it.
Does Autocache Option fix it?

It may be good to see if it also fails for other scrollable databases or wether this is PostgreSQL specific.

MoveFirst is not available for all Database engines

@Dave S: But it does work with this exact setup, same machine, same Postgres server, with the only difference being the MSB SQL plugin version. And the EOF indicator does reset, and the second loop does iterate over the records then hit EOF again. It is just the rs.Field() returns nil instead of field data.

I’m reading up on that now to try in my app. It looks like at that point the second loop should use FetchFirst, FetchNext, and Value instead of Field to get data?

yes agree with that
make another call to sqlselect, and the recordset will be moved first.
only moveNext works.

<https://xojo.com/issue/22656>

That feedback case refers to using the Xojo PostgreSQLDatabase plugin. I am using the MBS SQL plugin, where it is (or was) supported and working fine in version 4.1.10 of the plugin. Using the exact same db server, same database, same SQL statement, and same code, the .MoveFirst still appears to set the navigation point back as the code still loops over the expected number of records. But the .Field() methods are returning nil instead of the actual field contents when using MBS SQL 4.2.25

Or so it seems. The only difference here is the plugin version.

@Christian Schmitz : Are they any expected side effects to using SQL plugin 4.1.10 from MBS 18.4 with all other MBS plugins at 19.x?

I normally keep them all at the same level. It appears that replacing the 19.x SQL plugin with the 18.4 plugin works. At least it does in this case I was debugging. But are there co-requisites that make this a bad idea?

Interesting discovery, which suggests the MBS SQL plugin 4.2.5 may not be at fault here.

I tried MBS 19.1, which also has SQL plugin 4.2.5, and found it DID work with 2018R1.1 but NOT with 2019R1.1, making the Xojo version the main difference here.

So it seems like the matrix of environments are:

  • Xojo 2018 R1.1 with any MBS, including MBS SQL 4.2.5 works
  • Xojo 2019 R1.1 with MBS SQL 4.2.5 fails (any MBS 19.x)
  • Xojo 2019 R1.1 with MBS SQL 4.1.0 works, even with other MBS at 19.x

I still don’t think that feedback case is necessarily relevant – it is from 2012 and now classified as a feature request. But I was able to do it through Xojo 2018 R1.1 and not in 2019 R1.1 unless using MBS SQL 4.1.10 from MBS18.4

Guess I’ll refactor my code to use two SQL selects and two recordsets. I was avoiding that originally becauseI had to debug where my method was going wrong. And currently it takes a recordset as an input argument, so have to refactor the places that call the method now too. Still will take less time then I spent debugging, but I had to isolate what was failing anyway.

Well, first MoveFirst should work with our MBS Xojo SQL Plugin and PostgreSQL as it’s implemented.
AutoCache is on top by the plugin and can enable MoveFirst for all database types.

I’m traveling, but I put it on the todo list to check it.

To be clear, it appears that MoveFirst is working. The EOF property becomes false, and the loop iterates over the records again until it reaches EOF again. What is failing is actually getting data from the records on the second pass. All rs.Field() requests come back nil.

But once I spent the time to isolate where my code started failing, I just refactored it to use two Selects so my code works again.

It was the only spot I was using MoveFirst, so refactor was not a huge issue. Just had to also modify all the places that called this particular method since the method took the recordset as an argument and was not also doing the SQLSelect.

Still could be valuable to put on your todo list, as the MoveFirst itself seems to work, and did in 4.1.10 – so you could compare handling in 4.1.10 and 4.2.5 source code.