re: Limit Query Results inside DBCommand

[Amin Abbaspour <ab...@yahoo.com>]

Hi,

> first I want to welcome your offer to contibute.
> Contribution is always welcome and we're happy to check and
> accept any improvement that brings the project forward.
> Before you start we should talk abut what exactly we want
> to do.
Thanks.

> 1. I have not spend any time investigating myself but what
> makes you so sure that the JDBC-driver will not transmit the
> value supplied by setMaxRows() to the database just like if
> it is specified in the phrase?

Well, theoretically, JDBC can do this, but I have not seen this happen at least with the drivers I have used before. In fact I have never seen driver to perform self-decided modifications to SQL query which program supplies.

> 2. If there is a difference, is it worth supporting both
> i.e. limit in the phrase and the limiting the rows using
> setMaxRows() and why whould that make sense?

Yes, I think if we set limit inside Query, we can also setMaxRows(). Seems logical.

> 3. As far as the SQL Phase is concerned we have a solution
> for MySQL. But SQLServer and Oracle (through a rownum
> constraint) AFAIK only support a limit but no offset. Which
> databases do you know support a limit and which support an
> offset in the sql phrase. Please also let us know how? 

Based on SQLAlchemy I can say that MySQL, MSSQL (2005+), PostgreSQL and SQLite support both LIMIT and OFFSET. Only Oracle has no OFFSET support but seems that it can be simulated with 'ROW NUMBER OVER...' construct.

I will try to make the required change but as I get more acquainted with your code, I see that current inheritance based design is not much capable of handing so much diversity and maybe in future there is a need to overhaul in favor of a design similar to dialect in SQLAlchemy's or Hibernate/JPA.

Regards,
Amin

--- On Mon, 11/30/09, Rainer Döbele <do...@esteam.de> wrote:

> From: Rainer Döbele <do...@esteam.de>
> Subject: re: Limit Query Results inside DBCommand
> To: empire-db-dev@incubator.apache.org
> Date: Monday, November 30, 2009, 7:06 PM
> Hi Amin,
> 
> first I want to welcome your offer to contibute.
> Contribution is always welcome and we're happy to check and
> accept any improvement that brings the project forward.
> Before you start we should talk abut what exactly we want
> to do.
> 
> You seem to be really expericend with this problem.
> But I still have a few questions:
> 
> 1. I have not spend any time investigating myself but what
> makes you so sure that the JDBC-driver will not transmit the
> value supplied by setMaxRows() to the database just like if
> it is specified in the phrase?
> 
> 2. If there is a difference, is it worth supporting both
> i.e. limit in the phrase and the limiting the rows using
> setMaxRows() and why whould that make sense?
> 
> 3. Als far as the SQL Phase is concerned we have a solution
> for MySQL. But SQLServer and Oracle (through a rownum
> constraint) AFAIK only support a limit but no offset. Which
> databases do you know support a limit and which support an
> offset in the sql phrase. Please also let us know how? 
> 
> Regards
> Rainer
> 
> 
> Amin Abbaspour wrote:
> > re: Limit Query Results inside DBCommand
> > 
> > Dear Rainer,
> > 
> > JDBC setMaxRows is not equivalent to SQL LIMIT from
> the database's
> > point of view. setMaxRows sets the max number of rows
> ResultSet can
> > contain and AFAIK ResultSet ignores the results more
> that that limit,
> > while SQL LIMIT informs database engine about the
> count of the rows
> > client wants hence engine does not perform more work
> as soon as it gets
> > to requested number of rows.
> > 
> > As Francis mentioned, pagination is important too.
> This is particularly
> > useful when we have paged view (e.g. LOV pattern).
> > 
> > Besides limit and pagination which is occasionally
> required based on
> > application logic, limiting has tangible performance
> results in some
> > databases engines such as DB2, hence its always said
> that one would
> > better append a 'FETCH FIRST n ROWS ONLY' if he is
> sure his query has a
> > clause that will return only N row(s).
> > 
> > I personally don't see much problem in implementing
> this. If you want,
> > I can do it and sent diff files.
> > 
> > BTW I also want to add some LOCKING feature to
> SQLServer SELECT but
> > would like to know your point of view to see how to
> implement that we
> > both preserver polymorphism/OOP and database specific
> features.
> > 
> > Regards,
> > Amin
> > 
> > --- On Sun, 11/29/09, Rainer Döbele <do...@esteam.de>
> wrote:
> > 
> > > From: Rainer Döbele <do...@esteam.de>
> > > Subject: re: Limit Query Results inside
> DBCommand
> > > To: empire-db-dev@incubator.apache.org
> > > Date: Sunday, November 29, 2009, 11:47 PM
> > > Francis De Brabandere wrote:
> > > > Re: Limit Query Results inside DBCommand
> > > >
> > > > you might also want to implement paging,
> added a
> > > comment tot the issue
> > >
> > > Not sure whether we should to do this.
> > > If we solve it using the setMaxRows() on the
> statement,
> > > which is what I prefer, then there does not seem
> to be a way
> > > to specify an offset.
> > >
> > > At the moment I cannot see a solution for the
> paging that
> > > will work for all databases.
> > > If this is a special feature of MySql then we can
> provide
> > > it as a particular extension for MySql (just like
> the
> > > connectByPrior that we support for Oracle).
> > >
> > > Rainer
> > >
> > > >
> > > > On Sun, Nov 29, 2009 at 1:25 PM, Rainer
> Döbele <do...@esteam.de>
> > > > wrote:
> > > > > Hi Amin,
> > > > >
> > > > > unfortunately I must confess that we
> currently
> > > don't have the ability
> > > > to limit the number of rows returned.
> > > > >
> > > > > This shows how different people work
> with
> > > databases.
> > > > > In my many years of database
> programming I have
> > > never really felt the
> > > > need for it since I always found a
> constraint to work
> > > with.
> > > > > I only sometimes use it for interactive
> queries
> > > to the database.
> > > > > But you are right, this is a thing we
> definitely
> > > have to add.
> > > > >
> > > > > I have created a JIRA issue for that
> and I will
> > > implement that ASAP.
> > > > > Unfortunately this feature is handled
> differently
> > > by each database.
> > > > > e.g.
> > > > > SQL Server:
> > > > > SELECT TOP 10 id, name, ...
> > > > > FROM contacts
> > > > >
> > > > > MySQL:
> > > > > SELECT id, name, ...
> > > > > FROM contacts
> > > > > LIMIT 10
> > > > >
> > > > > ORACLE:
> > > > > SELECT id, name, ...
> > > > > FROM contacts
> > > > > WHERE ROWNUM <= 10
> > > > >
> > > > > Another option is to use setMaxRows()
> before
> > > executing the statement.
> > > > > This looks like a better idea to me
> since the
> > > JDBC driver can handle
> > > > it.
> > > > > Does anyone know if this is really
> equivalent?
> > > > >
> > > > > Rainer
> > > > >
> > > > >
> > > > > Amin Abbaspour wrote:
> > > > >> re: Limit Query Results inside
> DBCommand
> > > > >>
> > > > >> Hello to everyone,
> > > > >>
> > > > >> This is my first email in
> empire-db's users
> > > list :)
> > > > >>
> > > > >> One small question; How can I limit
> the
> > > number of rows returned in
> > > > >> select statements (i.e. SELECT ....
> LIMIT x,
> > > or SELECT TOP x ...)?
> > > > >>
> > > > >> Right now I do this manually by
> appending a
> > > "LIMIT x" to my query's
> > > > >> string but this is such a frequent
> > > requirement that I am pretty sure
> > > > >> this is implemented but I failed to
> find it.
> > > > >>
> > > > >> Regards,
> > > > >> Amin Abbaspour
> > > > >>
> > > > >>
> > > > >>
> > > > >>
> > > > >
> > > >
> > > >
> > > >
> > > > --
> > > > http://www.somatik.be
> > > > Microsoft gives you windows, Linux gives you
> the whole
> > > house.
> > >
> > 
> > 
> > 
>