Hi Joel;
Now you are back to my initial answer to your question.
Your "Limit" and "offset" is the basic idea behind "scrolling cursors". With
the PHP toolbox you can do something similar to the following C++ code
exec sql prepare sqlCmd into :*pSqlDa from :sqlSeleceStmt;
...
exec sql declare c1 insensitive scroll cursor for sqlCmd;
and finally a loop around:
exec sql fetch relative :startFromRow from c1 into descriptor :*pSqlDa;
If you want a direct result set based on the above - you have to dig a
little deep your self. My point is the underlying query engine supports it
with so you don?t have to write all kinds of unreadable select statement
even for the most simple query.
Best regards
Niels Liisberg
IceBreak Chief SW Architect
System & Metode Technologies
Håndværkersvinget 8, DK-2970 Hørsholm
Phone: +45 70 20 36 10
Fax: +45 70 20 30 11
Direct: +45 45 177 055
Mobile: +45 31 158 861
E-mail: nli@xxxxxxxxxxxxxxxxx
Web: www.system-method.com and www.Icebreak.org
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On
Behalf Of Burke, Joel
Sent: 22. oktober 2008 14:23
To: Web Enabling the AS400 / iSeries
Subject: Re: [WEB400] sql in php with paging
I would love to be able to use LIMIT and OFFSET but from what I have
read so far, it is not possible with DB2.
Joel
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx]
On Behalf Of Mike Eovino
Sent: Tuesday, October 21, 2008 10:31 PM
To: Web Enabling the AS400 / iSeries
Subject: Re: [WEB400] sql in php with paging
We've done most of the things suggested in the other messages, we've
written record-level access RPG stored procedures, we've even returned
the entire result set and just rendered the rows we want (for small
result sets only).
What I'm wondering is, will mySQL with the DB2 storage engine let us
use the mySQL LIMIT clause?
Mike E.
On Mon, Oct 20, 2008 at 2:14 PM, Burke, Joel <jburke@xxxxxxxxxxxxxxxxxx>
wrote:
I have written a php script that displays a list of individuals. The
script uses paging with 20 results per screen and searching is allowed
on the name field or id number. I am having an issue coding a case
insensitive search. Here is an example of my sql that works but is not
case insensitive:
SELECT * FROM (SELECT ROW_NUMBER() OVER(order by name) AS rownum, id,
name, address, city, state, zip FROM testlib.mastfile where (name like
'%Brooks%' or id = 0)) AS col WHERE rownum BETWEEN 21 and 40
The above code gives me records 21-40 based on the selection and sort.
Here is how I changed the code to allow for case insensitive searching:
SELECT * FROM (SELECT ROW_NUMBER() OVER(order by name) AS rownum, id,
name, address, city, state, zip FROM testlib.mastfile where
(upper(name) like upper('%Brooks%') or id = 0)) AS col WHERE rownum
BETWEEN 21 and 40
I do not receive any results back from the code above. I have testes
the following and it works properly:
SELECT * from testlib.mastfile where upper(name) like
upper('%brooks%')
Any idea what I am doing wrong? I am fairly new to sql so I may just
be overlooking the obvious.
TIA
Joel
--
This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/web400.
midrange.com
