AnsweredAssumed Answered

JDBC OFFSET and FETCH FIRST parameters binding causes a crash

Question asked by overseaslabs on Sep 17, 2018
Latest reply on Sep 20, 2018 by TSGal

Product and version: FileMaker 16 JDBC driver


OS and version: Ubuntu 16.04


Description: Filemaker 16 JDBC driver ( crashes when trying to prepare a statement with parameters binding for OFFSET and FETCH FIRST clauses.


How to replicate: run the following test (Java):


package com.example;

import com.filemaker.jdbc3.J3ResultSet;

import org.junit.jupiter.api.Test;

import java.sql.*;

import java.util.Properties;

import static org.junit.jupiter.api.Assertions.*;


class FMJDBCTest {


     void testConnection() throws SQLException {

         Properties connectionProps = new Properties();

         connectionProps.put("user", "***");

         connectionProps.put("password", "***");

         try (Connection conn = DriverManager.getConnection("jdbc:filemaker://***:2399/ec_data", connectionProps)) {

             String query = "SELECT * FROM FooBarTable OFFSET ? ROWS FETCH FIRST ? ROWS ONLY";

             PreparedStatement prep = conn.prepareStatement(query); //that's where the exception occurs

             prep.setInt(1, 5);

             prep.setInt(2, 5);

             ResultSet rs = prep.executeQuery();

             assertEquals(5, ((J3ResultSet) rs).getResultCount());





The test goes red throwing the following exception:


com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0001/(1:42): 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.<init>(Unknown Source)

     at com.filemaker.jdbc2.CommonJ2Statement.<init>(Unknown Source)

     at com.filemaker.jdbc3.CommonJ3Statement.<init>(Unknown Source)

     at com.filemaker.jdbc3.J3PreparedStatement.<init>(Unknown Source)

     at com.filemaker.jdbc3.J3Connection.prepareStatement(Unknown Source)

     at com.filemaker.jdbc2.CommonJ2Connection.prepareStatement(Unknown Source)

     at com.example.FMJDBCTest.testConnection(


The test goes green when binding parameters in other clauses (like WHERE a__IDPK_MarketingCategory = ? ) or when not using binding with offset/fetch first.