SQLite: MoveNext and MovePrevious… 2021r2.1

Databases
1

They do not works… Used code:


Var row As RowSet

Try
  row = App.db.SelectSQL("SELECT * FROM Employee")
  // MessageBox "There are " + Str(row.RowCount) + " Records." // 5 Records, now…
  If row = Nil Then
    Return
  End If
  
  Select Case Command
    
  Case 0 // First
    row.MoveToFirstRow
    L_Info.Text = "Move to the First Row (0)"
    If row.BeforeFirstRow Then
      L_Info.Text = "You already reached the first Record !"
      Return
    End If
    
  Case 1 // Previous
    row.MoveToPreviousRow
    L_Info.Text = "Move to the Previous Row (1)"
    If row.BeforeFirstRow Then
      L_Info.Text = "You already reached the first Record !"
      Return
    End If
    
  Case 2 // Next
    row.MoveToNextRow
    L_Info.Text = "Move to the Next Row (2) " + Row.Column("ID").StringValue
    If row.AfterLastRow Then
      L_Info.Text = "You already reached the last Record !"
      Return
    End If
  Case 3 // Last
    row.MoveToLastRow
    L_Info.Text = "Move to the Last Row (3)"
    If row.AfterLastRow Then
      L_Info.Text = "You already reached the last Record !"
      Return
    End If
    
  End Select
  
  // Populate the window contents with the actual row
  TF_Name_First.Text          = row.Column("Name_First").StringValue
  TF_Name_Last.Text           = row.Column("Name_Last").StringValue
  TF_Job_Title.Text           = row.Column("Job_Title").StringValue
  TF_Job_Salary.Text          = row.Column("Job_Salary").StringValue
  TF_Employee_ID.Text         = row.Column("Employee_ID").StringValue
  // cEmployee_Photo.Backdrop = row.Column("Employee_Photo").Picture // Unused
  TF_Employee_Entry_Date.Text = row.Column("Employee_Entry_Date").StringValue
  
  row.Close
Catch error As DatabaseException
  MessageBox("Error: " + error.Message)
End Try

Command is a value (0…3) to Move First (0), Previous (1), Next (2), Last (3).

The code above is called from one of the four buttons with the Command value…
As you can read, I set report for each case and I can tell you that:
Move To Next: works once only…
Move To Previous (when this is not the first Record) do not works…

SQLite / Xojo 2021r2.1 and Xojo 2022r3
Monterey 12.6

2

A lot of SQL engine only support forward only cursors. MoveToPreviousRow would require a bidirectional one. I can’t remember which SQLite provides.

To put that another way you can only walk trough the results in the forward direction when using a forward only cursor.

1 Like
3

It’s in the LR and it provides all four commands…
What you wrote as Forward is MoveNext and it works only once; a second click does nothing.

I replaced MoveNext with MovetoLast (in the shared code): it goes to the last Record (execute normally).

4

Many DBs provides means for a full set, but Xojo can simply ignore it and implement a partial simple set like a forward only. Xojo says they implement the full set for SQLite, Oracle and ODBC. PostgreSQL is fully capable but they don’t, I don’t know why.

If testing with SQLite and it fails for some Xojo version, that version has a bug.

5

you always do the query so i expect row is always on the first recordset.
usually u open the data once and then navigate.

6

This test shows it working ok with 2021r3.1

Var db As New SQLiteDatabase

Var sql As String

call db.Connect // in memory

sql = "CREATE TABLE contents (id INTEGER PRIMARY KEY AUTOINCREMENT, content TEXT); " +_
"INSERT INTO contents (content) VALUES ('line 1');" +_
"INSERT INTO contents (content) VALUES ('line 2');" +_
"INSERT INTO contents (content) VALUES ('line 3');"

db.ExecuteSQL sql

Var rs As RowSet

sql = "SELECT * FROM contents;"

rs = db.SelectSQL(sql)

rs.MoveToNextRow // should be at line 2

MessageBox rs.Column("content").StringValue // show

rs.MoveToFirstRow // should be at line 1

MessageBox rs.Column("content").StringValue // show

rs.MoveToLastRow // should be at line 3

MessageBox rs.Column("content").StringValue // show

rs.Close
db.Close
1 Like
7

I will try, Thank you. (time to eat some meal now)…

8

Looks like you are creating new rowset each time instead of navigating in one previously cached.

1 Like
9

As @Rick_Araujo wrote.

Make “row” an Object (a Property) of the Window or a Module or similar, so that “row” is not recreated everytime you call your Method (or destroyed everytime you leave your method). Then remove the Var row As RowSet and do f.e. a ModuleName.theRowSet = App.db.SelectSQL("SELECT instead. :slight_smile:

10

Everytime ou do the above, you recreate the row RowSet. And tho you can’t navigate within it.

I’d recommend to make the row RowSet a Property of the Window for example. Then do a Window1.row = App.db.SelectSQL("SELECT * FROM Employee").
Do it for example in a Thread which automatically runs everytime the Window opens.

Now in your Button(s), you can do the `
If row = Nil Then Return

Select Case Command
…`

11

It’s always the first record. With the above code, when you enter the Select Case Command, you will always be at the first record.

1 Like
12

Yes.

The code was correct, but that part must be located elsewhere :wink:

I corrected it in the night and now it works.