Re: sql in php with paging

[Vern Hamberg <vhamberg@xxxxxxxxxxx>]

Here we go again - mySQL will be mySQL - it is NOT DB2 and is not 
especially "limit"ed by DB2. Having the DB2 storage engine merely means 
that the data will be in physical and logical files (tables and views 
and indexes) on the iSeries - not much more. This does not mean, AFAIK, 
that mySQL is all of a sudden going to look like DB2.

Now having said that, yes, different storage engines now in use with 
mySQL do have different characteristics, so perhaps the jury is still out.

I just googled "mysql db2 storage engine" and found several hits - this 
one is a good starting point - refers to a Redbook 
(http://www.redbooks.ibm.com/abstracts/sg247398.html) on the subject.

http://krook.net/archives/190

From the Redbook

"Other storage engines can eventually include an i5/OS DB2 storage 
engine to store MySQL data in DB2 on i5/OS."

"A number of features exist only in certain storage engines including 
full-text search, referential integrity, and the ability to handle 
spatial data."

"Most MySQL functions operate in the same way, regardless of the storage 
engine that is used. All the usual SQL commands are independent of the 
storage engine."

HTH
Vern

Burke, Joel wrote:
> 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.
> >
> >
> >