On Tue, Jan 19, 2010 at 12:49 PM, Jaco van Tonder <[email protected]> wrote: > 1. Would it be possible to implement LIMIT and OFFSET functionality > for the databases that support it. Basically to limit the amount of records > that are fetched from the database. Oracle unfortunately does not support > this, which brings me to 2.
It should be simple to do, but there are no stated goals for this right now. Here are links to the feature request and prior discussion. http://issues.apache.org/jira/browse/EMPIREDB-62 http://www.mail-archive.com/[email protected]/msg00545.html There are many responses in that thread toward the bottom of the page. > 2. Can anyone explain to me how to construct an empire-db query to > create the following SQL: > > SELECT last_name FROM > > (SELECT last_name, ROW_NUMBER() OVER (ORDER BY last_name) R FROM > employees) > > WHERE R BETWEEN 51 and 100; You cannot do this currently unless you extend the DBCommand class and probably your DBMS appropriate driver class. I did implement TOP support for SQL Server, but I will not submit a patch for it as I do not think it is a good idea to encourage its use. You might be able to introduce a function into the driver SQL definitions with the opening "ROW_NUMBER() OVER (ORDER BY " and the closing of ")". It might just work, but I don't know if it will support multiple columns and especially the DESC keyword. Look at multi-column functions in the Empire-db driver for your DBMS and see if there is something that matches closely. If you want to support paging without using DBMS support you can do it but it may be slow. Here is an example of what the second page query might be. Your DBMS may not optimize the following. SELECT UserID, FirstName, (SELECT COUNT(b.UserID) as PreviousCount WHERE b.FirstName >= 'McKinley' and b.UserID <> 45 and b.FirstName <= a.FirstName FROM User b) as PreviousCount FROM User a WHERE FirstName >= 'McKinley' and UserID <> 45 and PreviousCount < 10 In this example you will need to pass the last primary key value and the last sort value to the paging function (i.e. 45 and 'McKinley'). Regards, McKinley
