Another approach to 2 might be to extend DBExpr - and implement addSql method. But I do not know what should be the implementation of addReferencedColumns (the other abstract method).
On Tue, Jan 19, 2010 at 3:50 PM, McKinley <[email protected]> wrote: > 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 >
