AnsweredAssumed Answered

Some functions throw an exception when being used in java.sql.PreparedStatement.

Question asked by IwaoAVE! on Apr 28, 2010
Latest reply on Jul 2, 2010 by IwaoAVE!

Summary

Some functions throw an exception when being used in java.sql.PreparedStatement.

Description of the issue

Tested environment: FileMaker Server 11.0.1.99 / Mac OS X 10.5.8FileMaker Pro Advanced 11.0v1 / Mac OS X 10.5.8 Description:The following functions do not work in java.sql.PreparedStatement.RTRIM, TRIM, LTRIM, UPPER, LOWER, LEFT, RIGHT, SUBSTRSteps to reproduce: Create new database (test.fp7).Create two fields (id: number, name: text).Create a record with data [id = 1, name = john]Publish the file using ODBC/JDBC protocol.Create a java program (see below).Compile (javac LowerTest.java).Execute (java -cp .:fmjdbc.jar LowerTest.java) import java.sql.*;

public class LowerTest {
    public static void main(String[] args) {
        try {
            Class.forName("com.filemaker.jdbc.Driver");
            String url = "jdbc:filemaker://127.0.0.1:2399/test";

            Connection con = DriverManager.getConnection(url, "admin", "");
            String sql = "SELECT id FROM test WHERE name = LOWER(?)";
            PreparedStatement stmt = con.prepareStatement(sql);
            stmt.setString(1, "John");
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                System.out.println("Result: " + rs.getInt(1));
            }
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}  Expected result:"Result: 1" is output.Actual result:  com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC] FQL0021/(1:33): Parameter number 1 to the function "LOWER" is not of the correct type.
     at com.filemaker.jdbc.FM_API.getAllResults(Unknown Source)
     at com.filemaker.jdbc.FM_API.execute(Unknown Source)
     at com.filemaker.jdbc.FM_API.execute(Unknown Source)
     at com.filemaker.jdbc1.CommonJ1Statement.execute(Unknown Source)
     at com.filemaker.jdbc2.CommonJ2Statement.execute(Unknown Source)
     at com.filemaker.jdbc1.CommonJ1Statement.executeQuery(Unknown Source)
     at LowerTest.main(LowerTest.java:13) 

Outcomes