RecordSet reset?

Hi!

I need to export some JSON from Database
First i need to do a primary query and then, a “subquery” for each primary query
The result will be something like this:
Id:1,name:alex,address:[street name:abc,number:123]
Id:2,name:ingrid,address:[street name:xpto,number:444]
and so on…

But, when i finish the subquery, after the FIRST primary recordset, the recordset resets…

Why??

…there are some code before, but it’s not rellevant…

[code]
dim jsonConsolidado as new JSONItem
while not rs.eof
dim dict as new Dictionary
for i=1 to rs.FieldCount
if instr(“nu_sequencia,st_tipovenda,dt_contabil”,rs.IdxField(i).name)>0 then // there is a reason for this // i’ll use to make a new query
continue
end if
dict.value(rs.IdxField(i).name) = rs.IdxField(i).StringValue // put all values inside dictionary, but nu_sequencia, st_tipovenda, dt_contabil
next

dim nu_sequencia, st_tipovenda, dt_contabil as string // i’ll use to make a new query now

nu_sequencia = rs.Field(“nu_sequencia”).StringValue
st_tipovenda = rs.Field(“st_tipovenda”).StringValue
dt_contabil = rs.Field(“dt_contabil”).StringValue

  // IF THIS LINE IS UNCOMMENT, THE RS.MOVENEXT EXIT WHILE/WEND
 // IF THIS LINE IS COMMENT, THE RS.MOVENEXT DO THE JOB!!!
  dict.value("meios") = getDictMeios(nu_sequencia,  st_tipovenda, dt_contabil) // to get the subquery contents

jsonConsolidado.Append dict
rs.MoveNext
wend[/code]

this is the getDictMeios code:

[code]dim strSQL as string
dim rs2 as RecordSet
dim i as integer

strSQL = " Select… … …"

rs2 = app.db.SQLSelect(strSQL)
dim jsonConsolidado as new JSONItem
while not rs2.eof
dim dictMeios as new Dictionary
for i=1 to rs2.FieldCount
dictMeios.value(rs2.IdxField(i).name) = rs2.IdxField(i).StringValue
next
jsonConsolidado.Append dictMeios
rs2.MoveNext
wend

return jsonConsolidado[/code]

Use a second database connection for the subquery.

Worked!

But why?
The data on the record set resets?
I do multiple “recordsets” all the time, and never needed to open new db connections

I would do if possible one query, even with multiple redundant columns, and then sort them out in another loop
but out of the database query.

[quote=426590:@Jean-Yves Pochez]I would do if possible one query, even with multiple redundant columns, and then sort them out in another loop
but out of the database query.[/quote]

Yes, sure it’s smart. But this is a specific case.

Still want to know why the recordset does not work in this example.

I had some of theses strange recordset behavior once with some complex queries.
so I made a class that mimic the recordset, initialize it with a fresh recordset,
then I can do whatever I want with it, moveprevious, moveto, have an iterator, and they always keep their records.
sometimes the recordset looses its records, I already had this, never figured out why it happened.

[quote=426592:@Jean-Yves Pochez]I had some of theses strange recordset behavior once with some complex queries.
so I made a class that mimic the recordset, initialize it with a fresh recordset,
then I can do whatever I want with it, moveprevious, moveto, have an iterator, and they always keep their records.
sometimes the recordset looses its records, I already had this, never figured out why it happened.[/quote]

Could you please post your example?

sorry, can’t really. (belongs to one customer)
you make a property with an array of 2 dimensions of variants.
in the constructor you pass the xojo recordset and store all the recordset in the variant array
then you make a field method tha returns a variant just like the field method of the xojo recordset
make a movenext method just like the xojo recordset
it’s not very difficult, but then it makes using recordset very easier.
may be it’s not appropriate for huge recordset as it will double the memory footprint, but it’s so easier to use.

Some database connections have no problem with two RecordSets.

You may see that when you get an error with the second query.

With MBS Xojo SQL Plugin, you can usually either set option for AutoCache or for Scrollable record sets to allow multiple record sets.

[quote=426585:@Alexandre Cunha]Worked!

But why?
The data on the record set resets?
I do multiple “recordsets” all the time, and never needed to open new db connections[/quote]
The Xojo database object keeps track of whether or not you are in a transaction and other connection “state” info that is necessary for the record sets to be iterated.

any chance this information could be exposed via a getter? see <https://xojo.com/issue/12883>

This occurs only with ODBCDatabse. With MySQLCommunityServer works perfectly