This is my first ever question. I am still learning Xojo.
Brief overview of my intention:
I am creating a little program that will have about 4 users on a shared network. I am using Microsoft Access as the backend. I am attempting to reduce the amount of SQL query read requests as much as possible as I am aware of the limitations of using Access. To this end I am reading an entire table (not very big. Perhaps 300 rows) into a Rowset property and figured I would hold this in memory at the window level public property.
Whenever the user changes the selection in a combobox it changes the displayed items in a listbox (for example if user select âvegetablesâ the listbox would display different kinds of vegetables, and if the user changes selection to 'meat then ⊠well you get the idea).
My issue:
I can loop through the rowset once but after that I cannot loop through a second time. The second time round it just gives me the last row value.
I have tried looping backwards and forwards using .MoveToNextRow or .MoveToPreviousRow.
I have also tried using a For Next loop but I cannot seem to read the row count as I am using ODBC (from what I can gather this is available to other database types).
*** START OF CODE ***
(excluding try/catch statements)
(the variable xRS_itemtypes is my window level Rowset public property)
xSQLString = âSELECT * FROM Tbl_ItemTypesâ
xRS_itemtypes = db.SelectSQL(xSQLstring)
For Each row As DatabaseRow In xRS_itemtypes
messagebox row.ColumnAt(1).StringValue
Next
xRS_itemtypes.MoveToFirstRow
For Each row As DatabaseRow In xRS_itemtypes
messagebox row.ColumnAt(1).StringValue
Next
If the data doesnt change, Read them once to a Data Structure (dictionaries, arrays, etc), close the connection and use the stored data instead of a connection to Access
Probably thatâs due to the ODBC driver limitation⊠as @Ivan_Tellez suggested, you can use some kind of data structure to store the values once and retrieve them as many times you may need.
Thank you Ivan,
I think from the way you worded your answer that my understanding of what a Rowset is may be incorrect. In my mind I see it as a data structure (a fancy array if you will) that you load your sql query result into, and then iterate over to get what you want. But reading between the lines of your answer it sounds like a Rowset still needs a connection to the database?
If this is so then loading the data into a dictionary or array is what I need to do because I am trying to prevent unnecessary communication with the Access database.
Markus,
I like this idea. Seems cleaner than loading tables into an array.
Is it possible to copy a whole table from Access to SQLite in one go? Or is it accepted practice to iterate over each row and add it to SQLite that way. (I think this still closely relates to my initial question, not sure if this should be a separate question? I am unfamiliar with forum protocol but I am aware that questions within questions are frowned upon. I want to do the right thing but have never asked questions on any forum)
Thanks Javier,
Your answer along with Ivanâs are making me suspect my understanding of what a Rowset actually is may be incorrect. I am starting to suspect that besides the initial connection it requires an ongoing connection? The documentation doesnât seem to specify it. These are simply my suspicions as I am trying to work this all out.
My initial understanding was that you do a SQL query, then load the result to a Rowset and then that Rowset can be used much like an Array. You just loop through the Rowset as many times as youâd like without any further database connection.
But it seems this isnât the case. The Rowset appears to be more of an initial place to store the SQL query result and from there itâs best to place it in a data structure as you suggested.
I donât know what the purpose of a rowset is in that case but I am sure there must be one.
But reading between the lines of your answer it sounds like a Rowset still needs a connection to the database?
This is correct. Disconnecting from the database renders your rowset invalid. A rowset basically only loads the current row into memory. It took me a long time to understand this as well.
Thank you SO MUCH for confirming my suspicions⊠That the Rowset is still connected to the database is not obvious at all! (to me)
I thought a rowset was basically an array type of thing. You read the info with a SQL query and stick the result in an recordset. Now that I know otherwise the documentation makes more sense. Although it does make me wonder about the purpose of a rowset. Besides as a temporary place to store my SQL query results I donât see a big benefit to it. I must be missing something.
Anyway⊠Thanks for your reply and setting me straight.
Well, the concept of a rowset is in itself highly useful. If Xojo handed over the entire query result into memory you might run into memory issues. But given the way rowsets work in Xojo you can load millions of rows without worry. And there is simply no alternative in obtaining results from a db, so Iâm not sure what you mean by ânot usefulâ. Its whole purpose is for you to grab the data from it once and do whatever you want with it. And looping through the result once is usually all that is required. But if for some reason you need to do that repeatedly you need to cache the results. Be aware though that your data might get stale.
But how would you expect the results to appear, if not in something like a rowset? Or are you saying that ideally, one could do the SELECT and get a rowset, and then be able to close the database connection prior to processing the rowset results?
