Select and show a part of a result from a query

Hi,

I look for a solution for my wd12 application that shows a lot of records as a result of a query. Cause we use it over the internet I try to implement a solution like in browsers:

Show 10 hits per page and then the next 10 .... and so on.

How can I this do with a query ?? Any ideas ?

Hi Michael
I have done something similar with MSSQL giving me a page loaded browse. Essentially you need to make your query something like "SELECT TOP 10 fields FROM file" to load the first page. You then store the last (and first if you want to scroll backwards) record id (all our files include a unique system id field) so your subsequent query will be "SELECT TOP 10 fields FROM file WHERE SysId > stored field". Scrolling backwards is a little more tricky but the general principle is the same.
We provide buttons to scroll up/down/top/bottom and if you want the thing to scroll automatically as the user moves down the table just add the "Next Page" code in the row selection section as something like
IF CurrentSubscript() = TableCount(MySelf) THEN
oBrowseageDown()
END
We generally load a block of 100 records at a time, even though the table may only physically have 10 lines displayed as we found the load time difference between 10 or 100 to be so minimal that we got better results using the larger block.
We have built a class around this general logic which allows us to scroll through thousands of records, filter the records etc,almost instantly, no matter what the size of the data file.

HTH

David

Hi David,

nice idea, I will try this .... thx

David,

how do you get the number of the max. results ? In browsers you can see


1234....35 36 37

To do something like this, I must know the max. results of the query.
I can do a count() query but this means, I must call the query 2 times ...

Hi Michael
Yes, I do a count(*) at the start. Within my class I have an Init method which does the count and calls my LoadFirstPage method. This Init method gets called at the beginning and subsequently only if the selection criteria change. The overhead of a count query is small and by minimising the calls to this its impact is minimal.

Regards

David

Hi David,

just one question:

How do you deal with different sort criterias ?
If the query is sorted by name the id is not sorted ascending and the query statement fails ... or I am wrong ?

Hi Michael
Sorry for the delay, my hard-drive crashed on the weekend and whilst it's all backed up it's still a pain getting everything up and running properly again.
Now I've given you a bit of a bum steer I'm afraid. Your last question made me check exactly what we are doing. We faced exactly the same issue you raised so we changed to use a LIMIT clause (which MSSQL doesn't support so MSSQL4WD from SQLManagerX converts the query. WD11 doesn't support a LIMIT clause either). Our query for the first page is as follows

SELECT Code FROM file ORDER BY Code LIMIT 0,100

This returns up to 100 records, starting at position 0. This gets translated to

SELECT * FROM (SELECT TOP 100 * FROM (SELECT TOP 100 Code FROM file ORDER BY Code)
AS TBL1 ORDER BY Code DESC) AS TBL2 ORDER BY CODE

For the next page the limit clause changes to LIMIT 100,100 and then LIMIT 200,100 and so on. The actual query code then changes to

SELECT * FROM (SELECT TOP 100 * FROM (SELECT TOP 200 Code FROM file etc

and

SELECT * FROM (SELECT TOP 100 * FROM (SELECT TOP 300 Code FROM file 

and so on.
We do virtually all our work with MSSQL and it works great for that. I have done a (very) quick test with HF which gave me a syntax error but hopefully the above will give you a place to start.

Regards

David

Michael
This may or may not be useful but according to the help HSavePosition/HRestorePosition works with queries. I haven't tried it but I would think you could use that to store the first and last records displayed in your table and base your next pageload from there.

David

Hi David,

I will try this.

thx

Hi Michael
I was just playing with this and came across a really simple way to get it to work.
I used 2 variables gi_top and gi_bottom and a data source. The code below does not have any range checking etc and you'd put the table loading into a procedure but it will give you the idea.

Initialisation of Table

HExecuteSQLQuery(ds_test,hQueryDefault,"SELECT * from testdat order by code")
gi_top = 1
gi_bottom = 10
FOR i = gi_top TO gi_bottom
	HRead(Testdat,i)
	TableAddLine(Table1,Testdat.Code,Testdat.Name)
END

Button to load next page

TableDeleteAll(Table1)
gi_top = gi_bottom + 1
gi_bottom = gi_top + 9
FOR i = gi_top TO gi_bottom
	HRead(Testdat,i)
	TableAddLine(Table1,Testdat.Code,Testdat.Name)
END

Button to load previous page

TableDeleteAll(Table1)
gi_top = gi_top - 10
gi_bottom = gi_top + 9
FOR i = gi_top TO gi_bottom
	HRead(Testdat,i)
	TableAddLine(Table1,Testdat.Code,Testdat.Name)
END

Regards

David

Hi David,

may be I'm missing something but why do you HRead() and TableAddLine() from file Testdat while you do a query with datasource ds_test? Why not using datasource ds_test instead of Testdat for reading, etc.?

Hi Stefan
You're right, the brain and fingers weren't in sync! The HRead etc should of course be from ds_test, sorry.

Regards

David