The following code works:
Var vLst(-1) as DatabaseRow
Var vRowSet As RowSet
Var vStmt As String
vStmt = "SELECT * FROM mytable ORDER BY mycode"
vRowSet = Session.db.SelectSQL(vStmt)
For each vRow As DatabaseRow in vRowSet
vLst.Add vRow
Next
Return vLst
However, I wonder if there is a better way to fetch rows into list without using the loop. Can anyone please help?
What do you intend to do with vLst once itâs populated? You already have everything in vRowSet.
I am confused. As Tim said, your vRowSet already has all the rows. Why are you creating an array?
The code would be stored in custom class as a public method that could be called from other places in the web app, itâs purpose would be to return list of rows from the table (note Return vLst).
Of course the above example is trivial but hopefully illustrates what I need. I need to issue query (any select query) and get all returned rows into the list aka array without doing it in the loop by adding each row.
Hope this helps to clarify my question.
Have you tested/reviewed the âListbox with a Datasourceâ example?
It looks like you want to do that.
Alberto, I am not ready to use DBkit if that is what you are suggesting as this would be a major rewrite for the app. Am I misinterpreting your suggestion?
No it doesnât. You already have all the rows in the RowSet. No need to do anything else.
BlockquoteWhat do you intend to do with vLst once itâs populated?
Well, that is a different story, the list could be needed in many places and I would not want to cache the list but rather pull it from the database on demand.
Ok, so I should return vRowSet instead and the caller method would then have to get the DatabaseRow out of the RowSet.
just get only the primarykey of each row you want to store in the list
get the other data each time you really need it, like when you refresh the list or edit a record.
it will be much faster.
Personally I do nothing with the db until I need the data from it. Itâs plenty fast enough unless perhaps yoy are managing millions of rows.
Note that if you close the database, any RowSets you have from that database cease to be valid.
I am not. DBkit <> âListbox with a Datasourceâ example.
If you need is for small tables (not a lot of records) just do the query and fill the WebListbox that you need. âListbox with a Datasourceâ is for people that want lazy loading the WebListbox (only having less than 100 records read until others are needed).
Alberto, Tim, Jean-Yeves;
I work with Postgres database that has plenty of records, this is a relational design to some extent, the web pages or web containers have many list boxes aka popup menus. When record is loaded from the list there are many records that need to be fetched from related tables to make the detail. The volume of data is huge as several thousand of files (each having thousands of rows) are fed into the database. There are many moving parts in the system, Xojo is just one little piece of it. Yet, people rely on this web app on a daily basis. While I am still learning Xojo I need to make sure that the newest version of Xojo is used efficiently, it is quite possible that my questions sound silly but I prefer to ask rather than pretend. I always value second opinion or different point of view. Nothing is ever easy. Thank you for your help.
so you NEED âlistbox with datasourceâ to read these data efficientlyâŠ
if you use xojo ui controls you can store an object in the .tag so you can memory the DatabaseRow direct there.
in the ListBox there is .rowtag or .celltag
if you need a query on the rowset may store the data into a memory sqlite database.