Possible? Previous Rowset Row Value

General
1

I am taking data directly from a sql query via rowset and writing it to a txt file. I do this for 4 different processes currently but on my 5th process I need to look at the value from the previous row as well so I can do some loops to make up for missing numbers. (if current row is a 10 but previous is a 4 in a data field, I need to write output to the text for the missing numbers)

What would be the best way without writing to any inputs or viewers like a listbox (even hidden ones)?

Below is an over simplified version of what I am trying to do for those who work better with visuals :slight_smile:

Dim sql As New ODBCDatabase
var a1rs as RowSet
dim SQLOutput as String = App.Process1Query
dim filename as String = filename.Text
Var output As TextOutputStream
Var documents As FolderItem = SpecialFolder.Documents
Var file As FolderItem = Documents.Child(filename+".txt")

If file <> Nil Then
    output = TextOutputStream.Create(file)
    try
        a1rs = sql.SelectSQL(SQLOutput)

        while Not a1rs.AfterLastRow
            for each row as databaserow in a1rs
                dim URN as String = row.column("URN").StringValue
                dim PREVURN as String = row
                If URN <> **(PREVIOUS ROW URN+1)** Then
                    FOR LOOP BLAH
                End
            next row
        wend
    end try
End
2

Save it at the end of the for loop and use it on the next iteration. BTW, Ithe while loop is redundant.

1 Like
3

This is where an object oriented ORM like ActiveRecord can come in really handy. You’d be able to access the previous record object while iterating an array of records.

If I was trying to stick within the realm of existing code, I might just add a class for this one use case, but I would still do the class based approach. This forum pseudo-code might help illustrate what I mean:

var rs as RowSet = db.SelectSQL(kSQL)

// Where we'll be storing the previous row
var oPreviousRow as MyRowClass

while not rs.AfterLastRow
  var oThisRow as MyRowClass = MyRowClass.FromRowSet(rs)
  
  // Do stuff
  
  // Store the previous row
  oPreviousRow = oThisRow
  
  // Next record
  rs.MoveToNextRow

wend
1 Like
4

Two great options here. Thank you.

One more followup, and I dont think this is possible. I wrote this so long ago that I forgot about the 1 instance where I am checking to see if a different field is the same as the next field. (I was previously using a listbox but I am trying to speed up my processes for these and write directly to file instead of to listbox then to file).

Is there a way to look at a field on the next row and compare to the current row?

5

You could loop around and save the RowSet into a Dictionary as String values, making the previous row accessible after a rs.MoveToNextRow.