Hi all,
We're using DynamicQL in our JBoss-application for a complex query. That works fine.
Now we want to use LIMIT and OFFSET to limit the resultset, because we want only a subset of the resultset (size of the subset should be modifiable at runtime).
We're using PostgresQL 7.3.4 that supports LIMIT and OFFSET.
The String we're calling 'ejbSelectGeneric' with, according to the JBossCMP documentation page 37, is as follows (shortened for readability):
ejbSelectGeneric("SELECT OBJECT(d) FROM Document d ORDER BY d.archivierungAm DESC LIMIT ?1 OFFSET ?2", args);
args is filled with the needed parameters and a integer for LIMIT, and a integer for OFFSET.
JBoss is accepting this statement at runtime but does not send the LIMIT and OFFSET clause to the database. Here's the statementlog of our database server (shortened according to the dynamicQL statement above):
SELECT t0_d.DOCUMENT_ID FROM document t0_d ORDER BY t0_d.ARCHIVIERUNG_AM DESC
So I'm missing the limit/offset clauses... I've read somewhere (don't ask where, I don't remember), that if the database supports LIMIT/OFFSET jboss sends these clauses to the database, otherwise does this limiting of the resultset itself. Seems that in our case JBoss does not know that our postgresql database is capable of supporting LIMIT/OFFSET.
So how can I make JBoss sending LIMIT/OFFSET to the database? I think it could be something in the 'standardjbosscmp-jdbc.xml' in our deployment, but I don't know exactly what...
We're using JBoss 3.2.1, Xdoclet 1.2b3, PostgresQL 7.3.4.
Any help would be highly appreciated.
Regards,
Silvio Matthes