RecordSet reset?

  1. 3 months ago

    Alexandre C

    Feb 28 Pre-Release Testers Brazil

    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…

    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

    this is the getDictMeios 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
  2. Greg O

    Feb 28 Xojo Inc

    Use a second database connection for the subquery.

  3. Alexandre C

    Feb 28 Pre-Release Testers Brazil
    Edited 3 months ago

    @Greg OLone 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

  4. Jean-Yves P

    Feb 28 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    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.

  5. Alexandre C

    Feb 28 Pre-Release Testers Brazil

    @Jean-YvesPochez 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.

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

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

  6. Jean-Yves P

    Feb 28 Pre-Release Testers, Xojo Pro Europe (France, Besançon)

    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.

  7. Alexandre C

    Feb 28 Pre-Release Testers Brazil

    @Jean-YvesPochez 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.

    Could you please post your example?

  8. Jean-Yves P

    Mar 1 Pre-Release Testers, Xojo Pro Europe (France, Besançon)
    Edited 3 months ago

    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.

  9. Christian S

    Mar 1 Pre-Release Testers, Xojo Pro, XDC Speakers Germany

    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.

  10. 2 months ago

    Greg O

    Mar 13 Xojo Inc

    @Alexandre C 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

    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.

  11. Tobias B

    Mar 13 Pre-Release Testers, Xojo Pro Bern, Switzerland

    @Greg OLone The Xojo database object keeps track of whether or not you are in a transaction

    any chance this information could be exposed via a getter? see Feedback Case #12883

  12. 7 weeks ago

    Alexandre C

    Apr 2 Pre-Release Testers Brazil

    @Greg OLone 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.

    This occurs only with ODBCDatabse. With MySQLCommunityServer works perfectly

or Sign Up to reply!