2nd DatabaseRow in RowSet empty

Databases
1

In this loop, the first row (DatabaseRow) displays all fields and field values upon inspection. The second time through, the row object has nothing. No field names or values and is not nil. However, if I inspect the rows (RecordSet) object the second time through, I see all fields and new values.

Xojo 2019r31

[code]Try
Dim rows As RowSet = ExecSQLRS(SQL)

If rows <> Nil Then
For Each row As DatabaseRow In rows
Call AddResultRow(row)

Next

rows.Close

End If

Catch error As DatabaseException
MessageBox("Error: " + error.Message)

End Try[/code]

2

Are you sure this query returns more than one row? Try placing a breakpoint on the “Call AddResultRow(row)” line and see what the “row” object contains on the second loop. Also check the AfterLastRow property of the RowSet (rows).

3

The row object does contain the second row data.

4

Could you rewrite that code to use “Recordset” instead of RowSet to see if the behavior is the same? Also make sure you are moving your rowsMoveToNextRow right after your Call.

Also what is your SQL query variable string?

Thanks

5

[quote] If rows <> Nil Then For Each row As DatabaseRow In rows Call AddResultRow(row) Next rows.Close end if [/quote]

Hmm. I had this feeling that you can’t do this as rows isn’t enumerateable (or something) and that you have to do:

while (rows.AfterLastRow=false) Call AddResultRow(row) rows.MoveToNextRow () wend

6

In your first message you stated:

So I assume this is what you observed in the AddResultRow method? Then you need to look at that method to see what’s happening to the data.

7

is it viable to mix api 1 (dim rows as …)
and api 2 (rowset)
?

8

[quote=487276:@Jean-Yves Pochez]is it viable to mix api 1 (dim rows as …)
and api 2 (rowset)
?[/quote]
Dim vs Var makes no difference to the compiler and does not affect the API.

9

Simply seems a bug needing report. But… Is your AddResultRow cloning the rows content? Because, after the Rows.Close the rowset is in a invalid state.

10

Jay, I have a breakpoint at the For Each. The first time through, I look at the contents of row and see it populated with the values from the 1st record in the DB. The second time through, I look at the contents of row and see absolutely nothing. No field name, no data.

Tim, I modeled this after the example in the Record Navigation: Iterator section of the RowSet documentation page. https://documentation.xojo.com/api/databases/rowset.html

11

So is this just a bug in the IDE, where the debugger doesn’t show the values even though they are there? Or are there actually no values in the row when you try to access them in code?

12

A little more troubleshooting reveals that the row object (DatabaseRow) is never populated. If I inspect the rows object each time, the contents change with each iteration.

https://i.postimg.cc/GhXTWpSY/Empty-Row-Object.png

13

Scott, something is amiss here. At first you say that the first “record” is populated, but not the second. Now you’re saying “row” never contains anything. Then how did you initially determine that it worked for the first one? It sounds like you’re saying one thing but meaning another.

I use this same code structure in lots of places and it works fine. You either have other code you’re not showing us that is interfering, or your debugging/confirmation methods are flawed.

Try putting the breakpoint on the method call itself (as I requested), not the loop structure.

14

More questions:
How many defined Rows in your data base ?
Have-you read with another software your data base and can say for sure there are data in n Rows ?(where n is more than 2).

15

Seems to be an IDE issue. I step through the AddResultRow sub and inspect row As DatabaseRow passed in. It is empty but when I inspect the dictionary populated from the row object, all the data is there. I’ll open a Feedback case.

[code]if row <> nil then
Dim dict as Dictionary

Select Case gpmBook.SelectedItem.Tag
Case “B”
’ Fields: action, birth, baptism, child, father, mother, godfather, godmother

dict = new Dictionary( "birth" : DefineEncoding(row.Column("birth").StringValue,Encodings.UTF8), _
"baptism" : DefineEncoding(row.Column("baptism").StringValue,Encodings.UTF8), _
"child" : DefineEncoding(row.Column("child").StringValue,Encodings.UTF8), _
"father" : DefineEncoding(row.Column("father").StringValue,Encodings.UTF8), _
"mother" : DefineEncoding(row.Column("mother").StringValue,Encodings.UTF8), _
"godfather" : DefineEncoding(row.Column("godfather").StringValue,Encodings.UTF8), _
"godmother" : DefineEncoding(row.Column("godmother").StringValue,Encodings.UTF8))

gsgResults.AddRow(new GraffitiWebGridRow( dict ))

dim newRow as new GraffitiWebGridRow( dict )
newRow.Tag = DefineEncoding(row.Column("id").StringValue,Encodings.UTF8)

Return True

…

End Select[/code]

16

Apparently I have same issue when using this For Each row As DatabaseRow In rows just went crazy today on debugging and still no logical result, If I put 2 records and I use that it takes the second and it skips the first, if I let 1 record then the record is fetched properly and no issues there.

I’ll try to see if using old api will do the job, if that works fine then I guess I’ll have to go back to most of the project to API V1 as this is randomly not working .

Hope somebody will find a fix or a debug point to this issue .