I spent lots of time searching the archives to find the equivalent of Limit
and Offset, as they are commonly used in MySQL and PostgreSQL. The solution
are obviously scrollable cursors. Althought many references are being made
to the archives there are actually no simple examples, but mostly references
and quotes from the manual, which is not that easy to understand for
beginners. The syntax in the manual would be much easier to understand if
there would be also example code like in the PHP manual. Maybe something
that can be added in the future? Since there seem to be also others having
this problem, I decided to post what worked for me:
It was this post that offered me a viable solution:
>Most of the programming interfaces have a method to move
>to a specific record in the result set. The rows skipped are
>not copied to the client. If you stop reading rows after row 31,
>then the remaining rows are also not copied to the client (except
>for a few which are already copied to the client).
>
>This will not solve all your problems as the database might
>still create a temporary result set of all the 10000 records.
>
>Daniel Dittmar
So this suggests that the code for LIMIT and OFFSET, when using MaxDB,
should be in the client (PHP), not in the MaxDB.
Here's a bit of code that works and takes care of going through the
resultset:
<?php
$query_count = 'SELECT COUNT(1) FROM ... WHERE ...';
$num_rows = odbc_exec ($db_connection, $query_count);
if ($num_rows == 0) { // no results found
echo '<p>No records found.</p>';
} elseif ($num_rows > 1000) { // too many results found
echo '<p>More than 1,000 records found. Please refine your search.</p>';
} else {
$query = 'SELECT * FROM ... WHERE ...';
$result = odbc_exec ($db_connection, $query);
$limit = 10; //limit resultset to specified value
//you have to pass a GET variable with the name 'offset' the value 0, 10,
20, 30 etc...
if ($_GET['offset']+10 > $num_rows) {$limit = substr($num_rows,-1);}// when
reaching the last set, limit resultset last digit of $num_rows, otherwise
the last record will be shown multiple times until $limit is reached
for ($i = $_GET['offset']+1; $i <= $_GET['offset']+$limit; $i++) {
odbc_fetch_into($result, $record, $i);
echo odbc_result ($result, 'column');
}
?>
I hope this helps others to live with MaxDB.
-Zavier
--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
