Infomix limit handler support for offset

Description

The current Informix limit handler doesn't have an offset support. But Informix does.
There is a skip keyword for that. Without offset support a scrollable result will be used. I've identified the scrollable result as a performance problem in my application.

*select SKIP 10 FIRST 10 FROM table. *

I've create a custom limit handler below.
If required I'll provide a pull request on github and integrate it directly in the InformixDialect.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 public class CustomInformixDialect extends InformixDialect { @Override public LimitHandler getLimitHandler() { return new InformixLimitHandler(); } private final class InformixLimitHandler extends AbstractLimitHandler { @Override public String processSql(String sql, RowSelection selection) { final boolean hasOffset = LimitHelper.hasFirstRow(selection); String sqlOffset = hasOffset ? " SKIP " + selection.getFirstRow() : ""; String sqlLimit = " FIRST " + getMaxOrLimit(selection); String sqlOffsetLimit = sqlOffset + sqlLimit; String result = new StringBuilder(sql.length() + 10) .append(sql) .insert( sql.toLowerCase(Locale.ROOT).indexOf("select") + 6, sqlOffsetLimit) .toString(); return result; } @Override public boolean supportsLimit() { return true; } @Override public boolean bindLimitParametersFirst() { return true; } @Override public boolean useMaxForLimit() { return true; } @Override public boolean supportsLimitOffset() { return true; } @Override public boolean supportsVariableLimit() { return false; } } }

Environment

None

Status

Assignee

Chris Cranford

Reporter

meleagros

Fix versions

Labels

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

backportDecision

None

Components

Affects versions

5.2.7

Priority

Critical