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 <[email protected]> wrote: > From: Rainer Döbele <[email protected]> > Subject: re: Limit Query Results inside DBCommand > To: [email protected] > 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 <[email protected]> > wrote: > > > > > From: Rainer Döbele <[email protected]> > > > Subject: re: Limit Query Results inside > DBCommand > > > To: [email protected] > > > 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 <[email protected]> > > > > 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. > > > > > > > > > >
