1 Reply Latest reply on Oct 25, 2003 5:34 AM by Juha Lindfors

    LIMIT and OFFSET with DynamicQL

    Silvio Matthes Newbie

      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

        • 1. Re: LIMIT and OFFSET with DynamicQL
          Juha Lindfors Master

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

          The LIMIT/OFFSET support in JBossQL is dumb one that works on all app servers, so it doesn't send the LIMIT/OFFSET to the database. You need to use declared SQL or ejbFind overrides to send uninterpreted SQL to the database.

          -- Juha