DatabaseRow type and current record

Databases
1

Am I missing something here?

Iterating a RowSet provides references to elements of DatabaseRow type.
What I am looking for is a way to assign a DatabaseRow type outside an iterator ?

Examples below simplified for clarity.

//this works
Var rs As RowSet = db.SelectSQL(myQuery)

For Each record As DatabaseRow In rs
  DoSomethingWith(record)
Next 

Sub DoSomethingWith(record as DatabaseRow)
  Var name As String
  name = record.Column("name").StringValue 
End Sub
//but how to do this?
Var record As DatabaseRow, name As String
Var rs As RowSet = db.SelectSQL(myQuery)

rs.MoveToFirstRecord

record = ????  //what to assign to record?

name = record.Column("name").StringValue
2

According to the documentation, DatabaseRow is for creating new Rows (add Row into the Database).

As far as I can understand you want to do (maybe) the reverse: read a row from the Database.

Please make your question clear.

Doc for Database Row:

DatabaseRow

Description

Used to create new Database rows (records). The methods are used to populate the columns in a row (record).

RowSet documentation with examples:
RowSet

I may mismatch your question; however, I shared an example that reads a .sqlite db file here Search the example code with screen shots.

If you want to extract the Columns by name instead of by index, remove the inside For Next block and replace the line:

      LB.CellTextAt(LB.LastAddedRowIndex, Loop_Idx) = row.ColumnAt(Loop_Idx).StringValue

and use the appropriate syntax based on:

  `LB.AddRow(row.Column("Name").StringValue)`

Replace Name with your Column name and use .StringValue if you want to display the data in the ListBox.

3

have you tried?

record = rs

you should check .AfterLastRow if there is no result set.
i remember by default the cursor should stay at the first row if there is data.

4

Thanks for the suggestions.

Yes I have tried

record = rs

// and 
record = DatabaseRow(rs)

Odd that an iterator can assign a DatabaseRow but I can’t find a way to do it directly.

The use case is a query that should only return a single record and wanting to pass the record to a function. I guess I can work around with a For…Each…Next it just seems a little redundant.

5

Did you try:

Var name As String
Var rs As RowSet = db.SelectSQL(myQuery)

rs.MoveToFirstRecord

name = rs.Column("name").StringValue

Using the iterator, even if only 1 record, should be clear to read. I prefer code that works the same for all situations that create code for special cases.

Maybe I don’t understand the ‘problem’. If using ‘for each … as … in …’ works, why change it if you know that you only have 1 record? And I read that .MoveToFirstRecords is not an option for all databases. I prefer coding in a ‘standard’ way that can be easily ported between db.

6

I would say it’s personal preference. A loop implies there may be more than one. I also naturally expect types to behave the same whether they are function parameters or any other declaration.

Not losing sleep over this one. Just a case of did I miss something and the answer apparently being no it can’t be done.

7

Untested… (relies on an edit populating the fields before they are amended… no certainty)

 rs.EditRow
 somevariable = rs.Column("SomeField").StringValue

//dont make changes, dont save?
rs.movetonextrow
8

or use

Sub DoSomethingWith(record as RowSet)

and expect the cursor is on a row