Limiting results in a custom FileMaker dialect for Hibernate

Question

I'm trying to access a FileMaker 16 database using Spring JPA 2.0.9 with Hibernate 5.3.5 using the official JDBC driver and this dialect for Hibernate.

I've made a modification to the dialect in order for it to support limiting the FileMaker result set adding a limit handler like this:

public class FileMakerDialect extends Dialect {

...

    private static final LimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
        public String processSql(String sql, RowSelection selection) {
            String soff = " offset ? rows";
            String slim = " fetch first ? rows only";
            StringBuilder sb = (new StringBuilder(sql.length() + soff.length() + slim.length())).append(sql);

            if (LimitHelper.hasFirstRow(selection)) {
                sb.append(soff);
            }

            if (LimitHelper.hasMaxRows(selection)) {
                sb.append(slim);
            }

            return sb.toString();
        }

        public boolean supportsLimit() {
            return true;
        }
    };

    public LimitHandler getLimitHandler() {
        return LIMIT_HANDLER;
    }

...
}

The limit handler simply adds offset ? rows fetch first ? rows only to the end of the select query.

Now I have a test which fails when trying to get paginated results from a Spring JPA repo:

com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0001/(1:338): There is an error in the syntax of the query. at com.filemaker.jdbc.FM_API.prepare(Unknown Source) at com.filemaker.jdbc.FM_API.prepareRS(Unknown Source) at com.filemaker.jdbc.FM_API.prepareRS(Unknown Source) at com.filemaker.jdbc1.CommonJ1Statement.(Unknown Source) at com.filemaker.jdbc2.CommonJ2Statement.(Unknown Source) at com.filemaker.jdbc3.CommonJ3Statement.(Unknown Source) at com.filemaker.jdbc3.J3PreparedStatement.(Unknown Source) at com.filemaker.jdbc3.J3Connection.prepareStatement(Unknown Source) at com.filemaker.jdbc2.CommonJ2Connection.prepareStatement(Unknown Source) at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:318) at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:146) at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:172) ... 87 more

However if I take the SQL from com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:318) which looks like select * from "MarketingCategories" marketingc0_ offset ? rows fetch first ? rows only, replace the question marks to numbers and execute it against the FileMaker database manually using the same driver, it successfully returns a paginated result set.

How do I go about this exception? I was hoping to get a clue how to debug it further. There are no sources of the fm jdbc driver nor the decompiled class has line numbers info, so I can't inspect it in a debugger.

Answer 1

While reporting the issue here, I've made a workaround:

public class FileMakerDialect extends Dialect {

...

    private static final LimitHandler LIMIT_HANDLER = new AbstractLimitHandler() {
        public String processSql(String sql, RowSelection selection) {
            String soff = String.format(" offset %d rows /*?*/", selection.getFirstRow());
            String slim = String.format(" fetch first %d rows only /*?*/", selection.getMaxRows());
            StringBuilder sb = (new StringBuilder(sql.length() + soff.length() + slim.length())).append(sql);

            if (LimitHelper.hasFirstRow(selection)) {
                sb.append(soff);
            }

            if (LimitHelper.hasMaxRows(selection)) {
                sb.append(slim);
            }

            return sb.toString();
        }

        public boolean supportsLimit() {
            return true;
        }
    };

...
}

It places placeholders for parameters binding in comments inserting the pagination parameters right in the limit handler.