Hi!

I'm using DBISAM 4.30B2 and Delphi 7 Pro...

Is it possible to select, for example, records from 1 to 10?

I mean...
I'm developying a PHP web application which connects to a DBISAM database. Everything is working great, but I need to limit the amount of data being showed on each html page.

MySQL and other databases allow to LIMIT the number of records and specify in which of them start.
For example:
   select * from table limit 0,5 will show the first 5 records begining from the first one.
   select * from table limit 5,5 will show the next 5 records.

Is there any similar function in DBISAM or workaround?

Thanks!

Luis,

<< Is it possible to select, for example, records from 1 to 10? >.

Yes, use the TOP clause:

select * from table top 10

However, you'll have to do some manual navigation to do subsequent "pages"
of data.   For example, to do records 11 through 20, you'll have to use:

select * from table top 20

and then manually navigate to record #11.  Are you using the PHP ODBC data
access layer with the DBISAM ODBC Driver ?

--
Tim Young
Elevate Software
www.elevatesoft.com

> Are you using the PHP ODBC data
> access layer with the DBISAM ODBC Driver ?

Yes, Tim...
I'm using PHP ODBC functions and DBISAM ODBC Driver.

I also tried with the following:

   select * from table where RecordID between 1 and 10
and on next page

   select * from table where RecordID between 11 and 20

...but I'm afraid it is not a good idea.

I think navigating from 11 to 20 manually, on a remote tcp session from a web page, will affect the speed.
Are there any other alternatives?

Thanks!


"Tim Young [Elevate Software]" wrote:

Luis,

<< Is it possible to select, for example, records from 1 to 10? >.

Yes, use the TOP clause:

select * from table top 10

However, you'll have to do some manual navigation to do subsequent "pages"
of data.   For example, to do records 11 through 20, you'll have to use:

select * from table top 20

and then manually navigate to record #11.  Are you using the PHP ODBC data
access layer with the DBISAM ODBC Driver ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Hi Lui,

I know little about PHP so maybe I'm way off but what about a server side procedure GetNext() which would return the primary key for the record 5 rows on from the last record you received. So you call your first 5 records using TOP 5. Then pass the key of the 5th record to the server side procedure which returns to you the key of the 10th record. Then you send a query for records where the key is greater than the 5th key but not greater then the 10th key and so on. That way there are only 2 round trips to the server for each page of 5 rows.

Or perhaps the initial query could store the ordered keys in a string which you could keep on the client and parse to make parameters for a query which would return the next 5 records each time you called it? Maybe not so good if you have 1000 records but who is going to click on the Next button 200 times anyway?

Tony.

Luis,

<< but I'm afraid it is not a good idea. >>

No, that's not a good idea.

<< I think navigating from 11 to 20 manually, on a remote tcp session from a
web page, will affect the speed.  Are there any other alternatives? >>

Actually, not with the DBISAM ODBC Driver.  It sets the remote read size to
match that of the row set size for the statement:

http://www.php.net/manual/en/function.odbc-setoption.php

The option you want to set on the result is:

SQL_ATTR_ROWSET_SIZE = 9

That will ensure that any fetches populate a row set of X number of rows,
and will also ensure that DBISAM reads X number of rows from the result set
on the server at one time.

--
Tim Young
Elevate Software
www.elevatesoft.com