Hello Simon, > You could set a very big maximum (e.g. 5000 rows) on > the assumption that users will never actually read or scroll > through that many rows.
:-) In that case I would simply use a simple listview (a listview can handle upto 32000 records), and see if I can use its lazy data retrieval method (never used that method, but there is a first time for everything). Apart from the problems I would need to solve by *not* imposing some abitrary limit (and learn from that ofcourse), it was/is my intention to be able to fully browse a table no matter how long (upto the limits set by SQLite3, although those seem to be quite vague). > It depends on what interface or shim you're using to access your database. I'm programming directly against the SQLite3 DLL, mostly using "sqlite3_prepare". > ... there's no language in SQL to say things like 'column 3' > and most APIs don't supply it. Shucks! That means that I need to use the column names in their full glory, no matter how long they are. And as far as I can tell they can be *long* (upto 2 or 4 gigs?), and could, with a few columns easily exhaust the specified buffer size for a query (IIRC, 110 KByte). > On the other hand, if you were referring to the results > of a SELECT, then results are always returned in the > order you asked for them Yeah, thats another funny thing. To be *sure* about the order of the columns, how the full record is sorted and from which record the "rolling cursor" should continue you're sending the same order of columns 3 times in one query ... > (apart from *) Guess what: for a simple table query thats probably the most-used selection. :-( Currently I'm assuming that querying a table will keep the results in order of the columns in the table. Up until now that seems to be true. Man, trying to understand reasons the designers of the SQL language did certain things in a certain way gives me a headache. :-\ Regards, Rudy Wieser ----- Original Message ----- From: Simon Slavin <slav...@bigfraud.org> To: General Discussion of SQLite Database <sqlite-users@sqlite.org> Sent: Thursday, July 10, 2014 2:17 PM Subject: Re: [sqlite] Questions from a novice - basic browsing of records ina listview. > > On 10 Jul 2014, at 12:54pm, - <mz2n6u7c.temp...@xs4all.nl> wrote: > > > But it might cause another problem: the database could get/be so large that > > the ammount of memory needed to store all the rowIDs in could well exeede > > the ammount of memory available to the program. > > I could ofcourse use (or at that moment switch over to) a local file > > (database!) to store them in, but somehow that feels a bit odd. > > You could set a very big maximum (e.g. 5000 rows) on the assumption that users will never actually read or scroll through that many rows. Use LIMIT 5000 and if you actually get 5000 rows returned put up a messages telling them if the row they want doesn't appear they should be more specific in their query. Or some other cop-out. > > >> At this point you care only about column values and you > >> never need to use SQL to scroll around in a table, > > > > It also restores the use of the listviews own slider to move about in the > > list (do away with the "page up", "page down" buttons). I must say I like > > that. > > We're all still adapting to the changes that the excellent GUIs and ridiculous speed of today's computers require. I now have trivial little systems which reflect in realtime changes made by other users and in other windows, just because I needed to write those library routines for my 'big' systems. > > > Lastly, the whole of the above ignores systems where the > >> user (or another user !) may insert or delete a row that was > >> in your foundset in another window, while it's being shown > > > > I also thought of that, but wasn't prepared to think about the consequences > > (good or bad) before the preceeding problems where solved. My intended > > approach to it was to add a "reload" button/key (F5) for it. > > You can deal with cases where the rowid no longer exists (as long as you do correctly test for it). But yes, spotting new rows is harder. > > > Thanks for the full (explanation, hints) reply. > > The clarity of your question suggested that a long answer would be read and understood. > > > Remark: I've not seen any reference in your reply to my first question where > > I wondered if it would be possible to refer (in queries) to columns other > > than by their full names. Must I assume its not possible ? > > Just that I didn't understand the question well enough to answer it and was hoping someone else did. > > It depends on what interface or shim you're using to access your database. Although several things about SQL syntax betray the fact that columns have an order (for example, you can do INSERT without specifying columns and the third value gets put in the third column) there's no language in SQL to say things like 'column 3' and most APIs don't supply it. > > On the other hand, if you were referring to the results of a SELECT, then results are always returned in the order you asked for them (apart from *), and you have to go to extra effort to find the names of the columns of the values that were returned. So all you have to do is remember what you asked for. > > Simon. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
