5 Replies Latest reply on Dec 21, 2016 5:12 AM by fmpdude

    Filemaker 14 Server xDBC

    bmts81

      Hello Filemaker Community,

      I am working in a filemaker server v14 and trying to establish a JDBC connection to the server.

      The ODBC/JDBC sharing is enabled and the users have the right permissions (fmxdbc, fmapp).

      The database does not contain no spaces, no |, no _, no -, no special characters, and it contains a single table with 2 fields.

      The server OS is Mac OS X Yosmitte, the server is listening on port 2399.

      I installed in the  client (Mac OS X El Capitan) the JDBC and ODBC client drivers version 15.

      I tested the connection and i can reach the server on port 2399.

      When finishing the ODBC system DSN the test returns success.

      At this point I am also able to see the shared databases at the server on the dsn wizard.

      So far so good, the problems start here.

      If i try with excel to use system DSN to retrieve data from server when it raises an unhandled exception and excel crashes.

      Using a tool to test ODBC connections it connects, but i cannot execute any SQL commands.

      When using a simple java project it returns:

       

      com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC]  (802): Unable to open file

        at com.filemaker.jdbc1.CommonJ1Connection.openConnection(Unknown Source)

        at com.filemaker.jdbc.Driver.connect(Unknown Source)

        at java.sql.DriverManager.getConnection(DriverManager.java:664)

        at java.sql.DriverManager.getConnection(DriverManager.java:270)

       

      I am without ideas...

      I hare read some topics about but i am not being able to find a solution.

      Can you help me?

        • 1. Re: Filemaker 14 Server xDBC
          fmpdude

          If you're sure you have JDBC sharing enabled, then it should work.

           

          Your JDBC connection string should look something like this:

           

          jdbc:filemaker://<IP_ADDRESS>/<DB_NAME>

           

          I use JDBC with FileMaker all the time and I'll try to help if I can.

           

          Post back more information (like your Java code). Note: By my "post your Java code" suggestion -- I'm assuming you have the DB user and passwords externalized in a properties file --> IOW --> do NOT post that sensitive account information here on a public forum.

           

          What IDE are you using?  Are you sure you have the FileMaker JDBC driver in the Java Classpath?

           

          ---

           

          You might also try RazorSQL (razorsql.com) as an interim measure. Razor works fine with JDBC/FileMaker so you can take your Java code out of the mix for the time being.

           

          Will try to help you further if I can.

          1 of 1 people found this helpful
          • 2. Re: Filemaker 14 Server xDBC
            bmts81

            I am following the filemaker sample code and after instantiation of com.filemaker.jdbc.Driver (jdbc is in the class paths)

            and when executing the command:

               connection = DriverManager.getConnection("jdbc:filemaker:XXX.XXX.XXX.XXX:2399/jdbc?user=XXXXX&password=XXXXXX");

            i receive that exception.

            com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC]  (802): Unable to open file

              at com.filemaker.jdbc1.CommonJ1Connection.openConnection(Unknown Source)

              at com.filemaker.jdbc.Driver.connect(Unknown Source)

              at java.sql.DriverManager.getConnection(DriverManager.java:664)

              at java.sql.DriverManager.getConnection(DriverManager.java:270)

             

            I am using jdk 1.8 and using intellij  CE 2016.3 and sparkjava restfull api

             

            Code:

            get("/hello", (req, res) -> {

              Driver.setLogLevel(Driver.DEBUG);

               driver = new Driver();

              DriverManager.registerDriver(driver);

              Connection conn = DriverManager.getConnection("jdbc:filemaker:XXX.XXX.XXX.XXX:2399/jdbc?user=XXXXX&password=XXXXXX");

              Statement statement = conn.createStatement();

              statement.execute("select * from jdbc");

               return statement.getResultSet().getFetchSize();

            });

            • 3. Re: Filemaker 14 Server xDBC
              bmts81

              Hi fmpdude,

              I tried with RazorSQL and i was able to connect and retrieve data... no Error.

              .... I will do a test using a different JDK (1.7) and using a console application. to see the results...

              • 4. Re: Filemaker 14 Server xDBC
                fmpdude

                I use JDK 8  as my default (java version "1.8.0_111") so that is not  the problem.

                 

                I also use the same IDE (and version).

                 

                What I would also suggest is to try to connect to a LOCAL FMP test database with JDBC enabled.

                 

                Also, I would unwrap this code from the Lambda or whatever that is above and just run plain JDBC code. Step through the code in the debugger to make sure objects are being created.

                 

                Just be aware that for non FMS (that is for regular FMP apps), FileMaker, Inc. has crippled their JDBC driver to ONLY work on the local machine. Since no other JDBC driver I've ever used has this ridiculous restriction, I can only assume that it's a "marketing" restriction to "nudge" you toward FMS. (Such an apparently transparent "nudge" just annoys most people instead.)

                 

                I'll check back in the morning to see if you've gotten it working.

                 

                Good luck.

                • 5. Re: Filemaker 14 Server xDBC
                  fmpdude

                  I created a Spring-Boot application as a quick example and was able to connect fine to a local copy of FMP and print out fields in a ...

                   

                  while(rs.next())

                   

                  loop.

                   

                  --------

                   

                  I DID, however, experience the exact same error you reported.

                   

                  com.filemaker.jdbc.FMSQLException: [FileMaker][FileMaker JDBC]  (802): Unable to open file

                      at com.filemaker.jdbc1.CommonJ1Connection.openConnection(Unknown Source)

                      at com.filemaker.jdbc.Driver.connect(Unknown Source)

                      at java.sql.DriverManager.getConnection(DriverManager.java:664)

                      at java.sql.DriverManager.getConnection(DriverManager.java:247)

                   

                  When I looked at the possible cause, I saw that although FileMaker was open, I had closed the actual FileMaker database. (Not sure how this could happen on FMS, which is a good reason to test locally.)

                   

                  Once I restarted the FileMaker solution, this code works fine:

                   

                  final String FMP_JDBC_DRIVER = props.getProperty("FMP_JDBC_DRIVER");

                  final String FMP_DATABASE_URL = props.getProperty("FMP_DATABASE_URL");

                  final String fmp_user = props.getProperty("fmp_user");

                  final String fmp_password = props.getProperty("fmp_password");

                   

                  // load driver
                  Class.forName(FMP_JDBC_DRIVER);

                   

                  // establish connection to database- FileMaker Pro
                  Connection fmpConnection = DriverManager.getConnection(FMP_DATABASE_URL, fmp_user, fmp_password);
                  Statement fmpStatement = fmpConnection.createStatement();
                  ResultSet rs = fmpStatement.executeQuery("SELECT COUNT(*) as cnt FROM " + "<The Table>");

                   

                  while (rs.next())

                  {
                    System.out.println((rs.getString("cnt" )+ " records in table." ));

                  }

                   

                  ---

                   

                  With the simple output:

                   

                  47 records in table.

                   

                  --

                   

                  Now, there may be a timeout or other issue going on (like another FileMaker JDBC bug), but the only time I had the same error, FilieMaker was "open", but the actual database was not.

                   

                  HOPE THIS HELPS.

                  2 of 2 people found this helpful