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