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