6 Replies Latest reply on Jul 2, 2010 12:54 AM by IwaoAVE!

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

    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)