Glorp DB2Platform and LMIT/OFFSET

[Joachim Tuchel]

Hi Glorp community

(I am sending this message to the glorp group for anybody interested and
to Instantiatons support for inclusion in the VAST version of Glorp)


On my 9.1-based version of Glorp, DB2Platform doesn't support
LIMIT/OFFSET like in

select * from customer limit 10 offset 5


A query like this:

            q := Query read: Customer.
            q
                orderBy: #id;
                limit: 10;
                offset: 5

creates a faulty statement for DB2:

select [fiels] from CUSTOMER ORDER BY ID FETCH FIRST 10 ROWS ONLY

and completely ignores the offset. This is not only wrong but is
potentially dangerous because it can make your DB2 server very busy by
fetching the same 10 rows over and over again...


But lo and behold: it is easy to fix.
First, we need to get rid of the old "FETCH X ROWS ONLY" syntax that
DB2Platform uses for #limit:


DB2Platform>>#printPostLimit:on:

"    aCommand nextPutAll: ' FETCH FIRST '.
    anInteger printOn: aCommand.
    aCommand nextPutAll: ' ROWS ONLY '"


    aCommand nextPutAll: ' LIMIT '.
    anInteger printOn: aCommand.

This LIMIT has been implemented in DB2 quite a few versions ago, so I
guess this is a safe thing to do. I am mentioning this because you can
always use limit: without an offset: to fetch the first x rows. I tested
that this limit without offset still works with DB2 11.5 after my changes.

The second required change is the additiion of DB2Platform>>#printOffset:on:

printOffset: anInteger on: aCommand

    aCommand nextPutAll: ' OFFSET '.
    anInteger printOn: aCommand


This creates the correct statement

select [fiels] from CUSTOMER ORDER BY ID LIMIT 10 OFFSET 5


If anybody wants to test if this is also wrong on VisualWorks or Pharo,
please feel free to test it and implement these changes and report back
if things are better for you. I don't have access to a current
VisualWorks version, so I can't do it myself. Maybe someone from Cincom
happens to read this and checks.
I cannot tell if a similar problem exists on, say, ODBC. I've seen that
other DBPlatform subbclasses implement something, so maybe this is only
DB2 specific.


Joachim





--

-----------------------------------------------------------------------
Objektfabrik Joachim Tuchel mailto:jtu...@objektfabrik.de
Fliederweg 1 http://www.objektfabrik.de
D-71640 Ludwigsburg http://joachimtuchel.wordpress.com
Telefon: +49 7141 56 10 86 0 Fax: +49 7141 56 10 86 1