You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@empire-db.apache.org by Rainer Döbele <do...@esteam.de> on 2009/11/29 13:25:00 UTC
re: Limit Query Results inside DBCommand
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
>
>
>
>
re: Limit Query Results inside DBCommand
Posted by 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.
> > >
> >
> >
> >
>
re: Limit Query Results inside DBCommand
Posted by Rainer Döbele <do...@esteam.de>.
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.
> >
>
>
>
re: Limit Query Results inside DBCommand
Posted by Amin Abbaspour <ab...@yahoo.com>.
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.
>
re: Limit Query Results inside DBCommand
Posted by Rainer Döbele <do...@esteam.de>.
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.
Re: Limit Query Results inside DBCommand
Posted by Francis De Brabandere <fr...@gmail.com>.
you might also want to implement paging, added a comment tot the issue
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.