Loop through rowset twice

have you test this way?


Var rs As RowSet

System.DebugLog "1 Loop"

While Not rs.AfterLastRow
  System.DebugLog rs.Column("id").StringValue
  rs.MoveToNextRow
Wend

System.DebugLog "2 Loop"

rs.MoveToFirstRow

While Not rs.AfterLastRow
  System.DebugLog rs.Column("id").StringValue
  rs.MoveToNextRow
Wend

Sorry for late reply. I am doing home renovations at the same time (new flooring).
I have tested your code but end up with the same result.

So I created a small little Access Database with one table called ‘Table1’. It hold the following values:

ID xField1
1 blah1
2 blah2
3 blah3
4 blah4

I then created a new Xojo application with the following code:

//ODBC Connection to a Microsoft Access database
Dim db As ODBCDatabase
db = New ODBCDatabase
db.DataSource = “DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:\Users\timst\Desktop\xojo test\TestDB.accdb;User Id=admin;Password=;”

If db.Connect() Then
MsgBox “Connected”
Else
MsgBox "Connection failed: " + db.ErrorMessage
db.Close
Return
End If

//Fill rowset with data from SQL query
Var xSQLString As String
xSQLString = “SELECT * FROM Table1”

Var rs As RowSet
rs = db.SelectSQL(xSQLstring)

//Run loops as per MarkusR
System.DebugLog “1 Loop”
While Not rs.AfterLastRow
System.DebugLog rs.Column(“xField1”).StringValue
rs.MoveToNextRow
Wend

System.DebugLog “2 Loop”
rs.MoveToFirstRow
While Not rs.AfterLastRow
System.DebugLog rs.Column(“xField1”).StringValue
rs.MoveToNextRow
Wend

rs.Close
db.Close

Which resulted in the following DebugLog:

11:56:10 AM : test.exe Launched
11:56:15 AM : 1 Loop
blah1
blah2
blah3
blah4
11:56:27 AM : 2 Loop
blah4
11:56:31 AM : test.exe Ended

As you can see in the second loop it only shows the last result (in this case ‘blah4’)

Well
 yes.
But mine might be an isolated case. I am attempting to reduce the amount of communication with the Access Database as much as possible. So my thinking was to load 3 tables into 3 rowsets. Then close the connection and use the rowsets as one would arrays.

However
 because I am self taught I sometimes don’t know what the heck I am doing and I now realize (thanks to Maximilian) that my understanding of rowsets was incorrect.

I think the best way to overcome my original problem is to do one of the following:
a) create an array of objects for each table (as per earlier reply from: Ivan Tellez)
b) copy the data into a SQLite database (as per earlier reply from: MarkusR)

I’m going to play around with both those options tonight to see which one I prefer

Tim

In the last year or so, the API for databases was improved. In particular, how prepared statements are handled was transformed. It used to be that one would have to prepare a statement (binding variables to database columns) and then an SQL statement such as SELECT or INSERT was issued. This was very tedious and as a result I made my own wrapper functions to do the prepare and execute in one method. At that time it was possible to proceed as you describe: get the resuls, close the connection, use the results.

When the improved API came along, very similar changes had been made by Xojo so much so that these days it’s hardly necessary to do statement preparation separately from execution. So you might do such as:

Var dbh as SQLiteDatabase, reg as RowSet, sql as String
sql = "select a, b from mytable where id=? and c=?"
reg = dbh.SelectSQL (sql, 27, "fred")

So I was able to throw away my wrapper methods, which was satisfying. With that change, however, I noticed that one now had to keep the connection open until after processing the results.

OK. Thanks for this information. I appreciate the time you took to write it.
I can see where the logic is coming from in regards to rowsets.

I’m still mystified about certain aspects of it but I’m sure these will become clearer to me as I progress in my knowledge. It’s hard coming up with a way to do things that’s simple enough for beginners but also satisfies the needs of more advanced programmers.

See also:

http://documentation.xojo.com/api/databases/rowset.html#rowset-movetofirstrow

Too bad.

maybe you can set other cursor option in attributes that it is not forward only?
https://documentation.xojo.com/api/databases/odbcconstant.html
https://docs.microsoft.com/en-us/sql/odbc/reference/develop-app/cursor-characteristics-and-cursor-type?view=sql-server-ver15

The purpose of RowSet is to facilitate the transmission of data from the database to your app in a consistent API. Database communication at a low level can be very complex and it differs from database to database. RowSet shields you from all that complexity and variation.

So after weighing the options I decided on this SQLite approach.

In my app users will just be viewing information most of the time so I needed to reduce unnecessary communication with the Access database over the shared drive. When my app starts it now creates a rowset for each table in my Access database. I then loop through each rowset once and load it into an in-memory SQLite database. This way the user can view the information and run queries on it without constantly communicating with the Access database.

I have added my code that runs on startup. Might be of use to someone looking for a solution one day.
(Not that I’m an expert by any means so use at your own risk)

//Create connection To Access database
Var xDBaccess As ODBCDatabase
xDBaccess = New ODBCDatabase
xDBaccess.DataSource = “DRIVER={Microsoft Access Driver (*.mdb, *.accdb)}; DBQ=C:\Users\timst\Desktop\Folder\Filename.accdb;User Id=admin;Password=;”
Try
xDBaccess.Connect
Catch error As DatabaseException
MsgBox "Access Connection failed: " + error.Message
xDBaccess.Close
Return
End Try

//Create connection To SQLite database
Var xDBsqlite As New SQLiteDatabase
xDBsqlite.DatabaseFile = FolderItem(SpecialFolder.CurrentWorkingDirectory.child(“database.sqlite”))
Try
xDBsqlite.Connect
Catch error As DatabaseException
MessageDialog.Show error.Message
Return
End Try

//Clear Sqlite database
xDBsqlite.ExecuteSQL(“DELETE FROM Tbl_ItemTypes”)

//Copy tables from Access database to SQLite database
Var xSQLString As String
Var xTempRow1 As New DatabaseRow
Var xRS1 As RowSet

xRS1 = xDBaccess.SelectSQL(“SELECT * FROM Tbl_ItemTypes”)
For Each row As DatabaseRow In xRS1
xTempRow1.column(“ID”) = row.column(“ID”)
xTempRow1.column(“Equipment_Type”) = row.column(“Equipment type”)
xTempRow1.column(“Equipment_Model”) = row.column(“Equipment model”)
xDBsqlite.AddRow (“Tbl_Itemtypes”, xTempRow1)
Next

//Fill combobox with values from SQLite database
Var xRS As rowset
xRS = xDBsqlite.SelectSQL(“SELECT * FROM Tbl_Itemtypes”)
For Each row As DatabaseRow In xRS
ComboBox1.AddRow row.column(“Equipment_Type”)
Next

xDBaccess.Close
xDBsqlite.Close
xDBaccess = Nil
xDBsqlite = Nil

you could not copy this access db file to a better place?
(i guess shared drive means network drive.)
and local just query the same because forward cursor.

A few observations from my experience with Xojo, Access, MS SQL Server and MySQL 


Reading and writing databases get complicated because you have several “levels” of software all trying to work together:

  1. Your Xojo (or other language) program
  2. Xojo (or other) built-in framework
  3. Database driver (sometimes called a database connector)
  4. Database engine (Access, MySQL, etc.)

As you’ve now learned, the RowSet is part of Xojo’s framework. It exposes only 1 record at a time. So you need to copy the data into something else if you want it to persist in your program.

And the forward-only restriction is not unusual, it occurs in other databases depending on the database engine and driver. Documentation doesn’t always make this clear. And the presence of a .MostToFirstRow method implies it will work but it doesn’t always.

Not mentioned in any of the other replies is you should use a Try/Catch block to catch and handle any database errors. At least give your users some kind of error message instead of returning no data.

And a note about Access in particular - if you’re using a database on a network drive, know that very minor network problems (that you won’t otherwise notice) can cause database corruption. Maybe not an issue for you if you’re read-only.

Finally, if you have only about 300 records it would be easy to read them all into an array, structure or dictionary. Then your program can do whatever it needs.

1 Like

yes, but searching will be more painful than with sql language

Great informative answer. Thank you.

Again agreed. However I am limited to using Access at the moment.
This is why I am trying to limit communication with the Access database as much as possible. It will also only have about 4 users (my coworkers) with only a handful of records added per week. So I hope I don’t run into too many issues.

Agreed. I am at this point in time recreating a copy of the whole database on the user machine, rather than using array, structure or dictionaries. But this has more to do with me wanting to learn about databases than choosing perhaps a better way to solve the problem. If all I do is read the data and then stick the result into an array then I won’t learn as much. Keeping it in a database also allows me to do relational SQL queries, which I can then use for future ideas/projects.

I’d be able to create this project in Excel in no time at all. But XOJO is a nice language to progress to from VBA. A standalone app that looks clean and polished rather than looking ‘like a spreadsheet’.
I’m just running into a few hickups along the way and appreciate the feedback on this forum.

Thank you for your reply

Tim

I just saw your answer as I submitted my reply to Eric.
I covered this aspect in my reply to him also.
I agree. I am trying to maintain the ability to do relational SQL queries.