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 (https://support.filemaker.com/s/answerview?language=en_US&anum=12921) 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 {

     @Test

     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(FMJDBCTest.java:23)

 

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

Outcomes