nlv11281 at natlab.research.philips.com
Thu Oct 5 07:11:02 EDT 2006
Even with transactions, you have to keep the connection open and assure that the same connection is being used for all paging queries for a user. Keeping transactions open while waiting for user input is a very, very, very bad idea. A simple solution that will work on all databases is to fetch data into a temporary table. If you can assure that you use the same connection within a session, you can use TEMP tables (add "INTO TEMP..." to your SELECT query), otherwise you'll have to do something like having a table with a primary key of (SessionID, RowNr). By numbering the rows, you can always fetch the range you need (WHERE RowNr BETWEEN ? AND ?) for the pager. A query like "SELECT COUNT(*) FROM tmp WHERE SessionID=?" will cost nothing (the DB need not access the table) and in addition, you'll always get a correct result. Mike Looijmans Philips Natlab / Topic Automation durumdara wrote: > Hi ! > > I wrote some mails about this theme, but I forgot to ask an important > question: > How to I get the number of elements in the query... ? > > These valued needed for the Pager: > > 1. "Split by" - the max. record number you can see in the page. > 2. "RecordCount" - how many records you have (total) - to calc number > of pages. > 3. "ThumbCount" - how many page thumb (link) you see in one page. > 4. "Actual Page Number" - where are we in the page collection. > > Simply I get every of these values except RecordCount. To get it, I must > count the records in the full query... > I have many elements (1000, 10000 or 100000), so I don't want to fetch > them all. > Because this I need to exec the query in twice, because I need to get > the count of elements, and I need some elements from it. > > 1.) select count(*) from ... > 2.) select * from ... > > Some of the databases supports "repeatable read" transaction isolation, > and they are protect the view to I get same thing in different times. > But if database not supports this, I can get different values with these > queries - example: new record inserted after COUNT(*), so number of the > elements is greater than in the first query... > > How do you handle this problem in MySQL, or other database that cannot > isolate the transactions (example) ?