Re: sql in php with paging -- WEB400

Dave,

MySQL is rapidly joining the ranks of "world class" RDBMs. Sun owns
it. Google uses it. Oracle bought the maker of one of the storage
engines for it (InnoDB). It's one of the core components of the LAMP
stack. They are writing a new storage engine (Falcon) that will take
advantage of multi-core processors for higher performance and better
scaling.

http://forge.mysql.com/wiki/Falcon

It's no flash in the pan. I use it on non-I jobs. It works. There's
even a clusterable version of it.

Mike E.

On Wed, Oct 22, 2008 at 8:48 PM, Dave Odom <Dave.Odom@xxxxxxxxxxxx> wrote:

Dean,

I see your point as long as there is good "mapping" between all DB2/ORACLE/SQLServer functions/capabilities and the MySQL interface. If not, it seems MySQL could be the latest in the "open" world hype that doesn't really meet expectations and diverts attention to the tried and true. I've just seen too many of these types of "silver bullets" over many decades (hell, I used to sell some as well) that lead people astray and for too long. So, I'll wait and see and in the mean time concentrate my code on tried and true world class RDBMs.

Dave

"Dean, Robert" <rdean@xxxxxxxxxxxx> 10/22/2008 16:24 >>>

The primary value for MySQL on the i is that there are a ton of open
source PHP software packages that were written to the mysql set
of APIs, which happened because there weren't any database
agnostic drivers at the time.

Now that PDO and other agnostic drivers are available, it makes
more sense to code against those APIs and let the backend deal
with the intricacies of how to translate what the developer needs
into the optimal SQL for that database.

________________________________________
From: web400-bounces@xxxxxxxxxxxx [web400-bounces@xxxxxxxxxxxx] On Behalf Of Vern Hamberg [vhamberg@xxxxxxxxxxx]
Sent: Wednesday, October 22, 2008 4:15 PM
To: Web Enabling the AS400 / iSeries
Subject: Re: [WEB400] sql in php with paging

MySQL is important enough for us to use it as one option for the
database in at least one of our products - just as someone can use DB2
on i, SQL Server, MSDE, SQL Server Express, etc. - all suitable RDBMS
for us to use.

Dave Odom wrote:

Vern, et al,

What's the "noise" about MySQL in the first place?

Further, is MySQL somehow capturing a large market share
away from all DB2 or ORACLE or SQLServer platforms all of a sudden? Is it projected to do so? Or, is MySQL just another fad? Or, is MySQL a "template/adapter" that can be wrapped over a DB2, ORACLE or SQLServer RDBMS so applications only have to be written to MySQL and the data is really stored in one of the leading RDBMs?

From my reading, it seems MySQL is another in a long line of supposedly "cheap" and "open" offerings that may lead folks down a primrose path. Sort of a Linix for the database world. Is there any other real value?

Dave

Vern Hamberg <vhamberg@xxxxxxxxxxx> 10/22/2008 07:05 >>>

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.

--
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.

--
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.

--
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.