Hi all, I don't think this is the right list for the question but I am hoping someone in the list will be able to point me in the right direction.
I am testing the use of Delphi with MySQL (using Micoolap's DAC) to access and manipulate a number of tables in a databse. Some of the more complex structures I want to do are quite clear on how to implement them with many papers and books published. My problem is the technique on implementing a far more fundamental issue. Let us say I have a couple of tables with a large number of rows (Master and Detail) with a common (indexed for performance) column MasterRef. Getting the rows from table Detail is straight forward by using a Query select * from Detail where Master.MasterRef = Detail.MasterRef The problem is more fundamental with the scrolling through the records/rows of Master. Reading previously posted information, it comes to light that if I open a table to scroll through using an application navigator then the app creates and uses a local dataset. Not a big issue if the database is local, on a high speed connection, or has a relatively small number of rows. But what if Master has 1 million rows with 200 columns. It is not sensible to allow 200 million pieces of data to be transferred to the Delphi PC to build a local Dataset to scroll through. I note there is the concept of LIMIT. This looks good until I try to see how to implement its usage in the real world. The concept of creating an app that only works on one (or a small number of rows) at a time is eluding me at the moment. Getting the first row is easy select * from Master order by MasterRef limit 1; but getting the next record isn't as straight forward. Mainly because I have no idea of what the next value of MasterRef is. All I know is that I want to get the next row in sequence. None of the papers I have or have seen addresses this issue. Either this concept is not required in SQL programming or it is so obvious that it doesn't need explaining. Either way I can't see the wood for the trees. If MasterRef is a unique value column then the next record would be: select * from Master order by MasterRef limit 1 where MasterRef > MyCurrentMasterRefValue; This simply raises questions 1) how to get the previous row (presume you use the DESCENDING keyword of the table, 2) how to test for Begining and End of Table and 3) what to do if the column being ordered on is not unique. Are there any known papers, documents, references, books etc that go through these issues. Or can someone tell me that the posts I have been reading are no longer applicable and that when I program using Table components that it doesn't download a complete large dataset and that I only get one row at a time which takes away the concern, and the need to manage the data scroll directly. JOIN is not an option because in my project one form can have up to 9 DETAIL tables showing with the Master table. I am at the point where I have done a lot of reading and now want to look at the reality of implementation. Which means I have a "little knowledge" which is a dangerous thing. I want to change that status. Many thanks Kerry
