Writing JDBC Code To Interact with FileMaker

Document created by fmpdude on Jun 5, 2018Last modified by fmpdude on Jun 12, 2018
Version 7Show Document
  • View in full screen mode

(Java) JDBC Programming with FileMaker

 

As a follow-on to the Using an SQL Tool in FileMaker article, this article attempts to answer the question … “what if you want to use the FileMaker JDBC driver itself to do more extensive automation?” (as in, no external SQL Tool!).

 

But why would you ever want to write a Java JDBC program for FileMaker when you can just write a FileMaker script?

 

Consider these possible reasons:

 

  1. JDBC Can use SELECT, INSERT, UPDATE, and DELETE
  2. JDBC programs run quickly in compiled Java code
  3. JDBC programs can be scheduled to run standalone
  4. JDBC programs can be used in micro-services, called From FileMaker
  5. JDBC programs can be called by other programs
  6. JDBC and the corresponding Java environment allow you to anything Java can do
  7. Much of what you can do with JDBC cannot be done in a FileMaker script
  8. JDBC is a specification and a standard supported by every RDMS vendor

 

Goals

This article won’t attempt to make you a Java expert (becoming an expert takes a while), but instead is meant to help describe the JDBC code you see in the FMP ODBC/JDBC guide and other samples you might find online. Hopefully using this code will enable you to explore what incredible power JDBC gives you.

 

For this example, you will see both a simple SELECT and an UPDATE example. Using much of the same logic described below, you can implement JDBC statements for your needs.

 

The code shown in this article mostly writes to the console, which is appropriate for testing or for single user use say, with an IDE. Most production uses would not write to the console at all, but instead return data to the user or, often, some other program in a desired format.

 

A Word (or two) Of Caution

USING SQL IN A JDBC PROGRAM IS EXTREMELY POWERFUL. IF YOU ARE NOT CAREFUL, YOU CAN DO MUCH DAMAGE TO YOUR DATA WITH A SINGLE SQL STATEMENT. THEREFORE, YOU SHOULD CONSIDER USING TEST DATA AND TEST PROJECTS UNTIL YOU ARE COMFORTABLE. AND IN ANY CASE, ALWAYS, ALWAYS, HAVE CURRENT BACKUPS FOR ALL YOUR DATA.

 

FMP JDBC Driver’s Limitations

Note that, unless you’re writing JDBC logic to work with FMS, FMI has, sadly, intentionally disabled the JDBC driver to only work with FMP databases on the local machine. You can easily work around this limitation by setting up a proxy-pattern, but for now, just be aware of this limitation.

 

Basic Setup

ODBC/JDBC Documentation

Before starting, it’s an excellent idea to have the most current documentation for JDBC and ODBC. FMI publishes an ODBC/JDBC guide you should download and keep for reference. This guide is extremely useful working with container data, for example, and for knowing exactly what SQL FMI supports—including usage examples.

 

You can download the SQL documentation, here:

https://fmhelp.filemaker.com/docs/16/en/fm16_odbc_jdbc_guide.pdf

 

Downloading the FileMaker JDBC Driver

Since external SQL tools often use JDBC, you need to get the free JDBC driver from the FileMaker website.

See this URL: http://www.filemaker.com/support/downloads/

 

FileMaker Permissions

For the FileMaker database you’re using, you need to allow JDBC connections. You can allow all or specify users with a particular permission set. For this simple example intro, we will allow “All users”.

FileMaker Menu: File…Sharing….Enable ODBC/JDBC…

 

 

(Our customer data is a layout in this solution.)

 

For local database access, the setup above is all you need to do to allow JDBC from the FileMaker side. If you have a solution on AWS, there are additional security settings to enable. Read the AWS documentation for more information.

 

Installing the FileMaker JDBC Driver

Once downloaded, where you put the JDBC driver is completely up to you. There is no formal installer that forces you through a wizard, installs registry entries, or anything complicated; the JDBC driver is just a file you download.

 

The FMP JDBC driver is called, simply, “fmjdbc.jar”.

 

 

Part of the setup will be telling your Java IDE where the JDBC driver is. We’ll come to that in a bit.

 

Possible Error When Attempting to Access Dataa

 

If you get a nasty looking error dump returned like the one below, remember that for local FMP connections, your FMP database must be open and you must have JDBC enabled as discussed above:

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/java.sql.DriverManager.getConnection(DriverManager.java:677)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)

at net.hurricane.FMPBasicJDBC.main(FMPBasicJDBC.java:31)

Exception in thread "main" java.lang.NullPointerException

at net.hurricane.FMPBasicJDBC.main(FMPBasicJDBC.java:59)

 

(We’ll add an error trap for this problem later.)

Data we’ll be using

In our customer table in our sample database, we’ll have this data:

 

dob,firstname,lastname

"1-10-2009","Phil","Richardson"

"1/1/1953","Pete","Smothers"

"2/2/2017","Phil","Conners"

"4-15-2005","Phil","Sanders"

"12-1-1999","Pete","Smith

 

 

IDE Setup - Load the FileMaker JDBC Driver

 

Part of this example assumes you have a Java IDE set up. If you need additional help with that, find an IDE you like, say the free Eclipse or the paid Intelij and search for setup help. There is a lot of help available online for setting up an IDE and loading drivers. You can also read the article “Create Micro-Services Using Java and the Spark Java Framework” (https://community.filemaker.com/docs/DOC-8871) for more information on IDE set up.

 

Below, you’ll see a high-level description for loading the JDBC driver, which assumes you have Intellij and can have the menu displayed as it is by default.

 

1. Click the button in the toolbar to open project settings.

 

2. Click the Libraries link on the left side shown.

 

 

 

3. In the next column to the right from the Project Settings shown above you should see, at the top a + button. Click that + button and select “Java” (assumes you are not using a Maven project). The “Java” selection tells the IDE you will be loading the library from a file on your computer.

 

4. In the dialog that then pops up, navigate to where you added your FileMaker JDBC driver and then click Open.

 

5. You will then get a dialog asking you to “Choose Modules”. You will probably only have a single module so just click OK.

 

6. Then click Apply and OK to finalize adding the FMP JDBC JAR file to the project.

 

You should then see the FMPJDBC driver shown as part of the project in the library settings in the third pane on the right.

 

Parts of a JDBC Program

 

Now that we have the JDBC driver loaded in our Java IDE, let’s write a simple JDBC program to SELECT the customers we created in the recent SQL Tool article (see CSV data above if you want to import).

 

Writing JDBC uses lots of boilerplate. If you ever use a framework, much of this boilerplate is hidden (abstracted) so you don’t need to deal with it. However, when learning, it’s a good idea to use JDBC at least once. You can also store the basic JDBC boilerplate in an IDE-saved snippet you can later paste it into your code and make changes.

 

Much of what you set up in JDBC is the same as your setup for RazorSQL — except we’re doing that setup in code!

 

Note: In our example, we will use hard-coded setup strings in the code. In a production application these settings would possibly be in configuration files or even “injected” via special techniques (not discussed here).

 

The example below uses the simplest technique to get data via a SELECT statement.

 

Basic JDBC Java Program Steps:

 

1. Identify the JDBC driver, JDBC format string, and the user.

 

final String FMP_JDBC_DRIVER = "com.filemaker.jdbc.Driver";

final String DATABASE_URL_LOCAL = "jdbc:filemaker://localhost/ZipCodeLookup;

final String fmp_user = "Admin"

(Assumes your FMP solution is called ZipCodeLookup.fmp12)

(Also, no password for simple example, so we don’t define it.)

2.Create a connection and other variables

Statement statement ;

ResultSet resultSet;

Connection connection;

 

3. Create a string variable to hold our SQL Statement (could be SELECT, INSERT, UPDATE, or DELETE)

// SQL to get all the customers

String sqlGetSourceData = "Select  * from customers";

 

4.Have the program load the JDBC driver and get a connection to our database

Class.forName(FMP_JDBC_DRIVER);

connection = DriverManager.getConnection(DATABASE_URL_LOCAL, fmp_user, “”);

(Pass double quotes as shown above, for the empty password)

5. Create a Statement Object. We will use this statement to issue our query.

statement = connection.createStatement() ;

 

6. Create a ResultSet and pass our SQL query to FileMaker via JDBC:

resultSet = statement.executeQuery(sqlGetSourceData);

 

7. Now, if we have any rows returned (remember, your SQL statement may not return anything if there’s no matching data—even if it’s a SELECT, we need to walk through the ResultSet object.

 

  1. For display only, the code below has some very simple tabs in the output for “reasonable” testing output.

System.out.println("\nCustomers:\nFirst Name:        Last Name               DOB");

System.out.println("------------------------------------------");

while (resultSet.next())

{

    System.out.println( resultSet.getString("firstname") + "\t\t\t\t\t\t\t\t\t" +

                                  resultSet.getString("lastname") +  " \t\t\t\t\t" +

                                  resultSet.getDate("dob")) ;

}

 

8. Run the program using our FileMaker Database!

Customers:

First Name:     Last Name              DOB

-------------------------------------------------------

Phil Richardson 2009-01-10

Pete Smothers 1953-01-01

Phil Conners 2017-02-02

Phil Sanders 2005-04-15

Pete Smith 1999-12-01

The Complete Java Program So Far

 

Part of programming in Java is dealing with Exceptions. In Java, if an Exception is called a “checked” exception, you MUST have a try-catch clause for it or your code will not compile. Other environments, such as .NET, are not as picky with Exceptions. An IDE automates most of the Exception handling logic for you so you don’t have to code it manually.

 

Our full Java program, so far, is below:

 

public class FMPBasicJDBC

{

    public static void main(String[] args)

    {

  

        Statement statement = null;

        ResultSet resultSet = null;

        Connection connection = null;

        final String FMP_JDBC_DRIVER = "com.filemaker.jdbc.Driver";

        final String DATABASE_URL_LOCAL = "jdbc:filemaker://localhost/ZIpCodeLookup";

        final String fmp_user = "Admin";

        // SQL to get all the customers

        String sqlGetSourceData = "Select  * from customers";

       

        try

        {

            Class.forName(FMP_JDBC_DRIVER);   // LOAD JDBC DRIVER

            connection = DriverManager.getConnection(DATABASE_URL_LOCAL, fmp_user, "");

            statement = connection.createStatement() ;

            resultSet = statement.executeQuery(sqlGetSourceData);

            System.out.println("\nCustomers:\nFirst Name:        Last Name               DOB");

            System.out.println("------------------------------------------");

            while (resultSet.next())

            {

                System.out.println( resultSet.getString("firstname") + "\t\t\t\t\t\t\t\t\t" +

                                              resultSet.getString("lastname") +  " \t\t\t\t\t" +

                                              resultSet.getDate("dob")) ;

            }

        }  // end try

        catch (SQLException e)

        {

            e.printStackTrace();   // THIS WOULD BE LOGGING STATEMENT IN PROD.

        }

        catch (ClassNotFoundException e)

        {

            e.printStackTrace();   // THIS WOULD BE LOGGING STATEMENT IN PROD.

        }

        finally

        {

            // close everything!

            try

            {

                statement.close();

                resultSet.close();

                connection.close();

            }

            catch (SQLException e)

            {

                e.printStackTrace();

            }

        }

    }

}  // end class

 

Now, let’s fix that issue we got when we received that FMSQLException when we forgot to have the FileMaker database open. The error we got was an 802, but the Exception was a “FMSQLException”. Let’s just add that to the catch blocks and print out a reasonable message so the user won’t get a “stack trace dump” if they forgot to open the FileMaker database.

 

(Note: in a production application, you would add logging and other items we are not doing in this quick intro. Also, in a production application, you wouldn’t be adding tabs for display. Instead, you would almost always be passing data back to a program caller in some format or data structure. For FileMaker, you would possibly pass data back in a return-delimited list.)

 

Below’s the new Exception catch clause we’ll add (which comes from the FileMaker JDBC driver!). The idea is not to dump a stack trace on the user which tells them basically nothing, but instead, to catch the Exception in code and give the user a better message. This approach also lets you gracefully work with a particular Exception.

 

catch (FMSQLException e)

{

    System.out.println("\nCould not get data....Is your FileMaker Database Open?");

}

 

The message you pick, of course, is up to you. (We are only writing testing logic, not production code yet….).

So, after adding this catch block to our code above, now if the FileMaker database is not open, we get a much friendlier message:

 

 

And, once we open our FileMaker database, we again just get the data as before since no Exception was thrown.

 

Customers:

First Name:     Last Name              DOB

-------------------------------------------------------

Phil Richardson 2009-01-10

Pete Smothers 1953-01-01

Phil Conners 2017-02-02

Phil Sanders 2005-04-15

Pete Smith 1999-12-01

 

Updating a Customer

Using the exact same UPDATE statement we used in the recent “Using an SQL Tool in FileMaker” article (https://community.filemaker.com/docs/DOC-9132), we can put that statement in code for the update. There are just a few subtle differences in the JDBC logic for an UPDATE vs. a SELECT statement.

 

Let’s modify the code we wrote initially to create two methods we can call from the ‘main’ method.

 

Our new main method will look like this (with two method calls):

 

public static void main(String[] args)

{

    selectCustomers();

    int numUpdated =  updateCustomers("PHIL", "Richardson");

    System.out.println("\n" + numUpdated + " customers updated.");

}

 

Note that by using the “numUpdated” variable we will display how many customers the code potentially updated.

 

Now, the selectCustomers() now calls a separate method with the same code we wrote earlier. We just moved that code to its own method.

Since we already loaded the JDBC driver during the SELECT customers logic, we don’t  have to re-load it. Thus, the new updateCustomers(…) method is shorter.  To understand what the call to updateCustomers(…) above is doing, we need to first look at the UPDATE statement.

 

Using a “PreparedStatement” which will be compiled for extra speed for multiple query re-use, we will use this SQL statement:

String updateSQL = "UPDATE CUSTOMERS SET FIRSTNAME=? WHERE LASTNAME =?";

 

This statement might look a lot like an FMP SQL syntax, but it’s really an enhanced (compiled) UPDATE statement you’d only get using JDBC. This UPDATE statement says to UPDATE (change) the customers FIRSTNAME to the first parameter passed where the LASTNAME is equal to the second parameter passed.

 

Below is the entire updateCustomers() method (note the two parameters in the method definition):

 

This method declares the generic SQL UPDATE statement we want without hard-coding values (those values come in the parameters at the top).

We then get a connection to the database and create our PreparedStatement. (Note: In a production application, you would probably get your database connections from a “connection pool” not like we’re showing below.)

 

Next, we call the prepareStatement on the connection object we created, which pre-compiles the statement. We set the parameters in the next two lines (you can have as many parameters as you need) and then we execute the update.

Finally, we pass back the number of rows we updated.

 

We catch the REQUIRED (by Java) SQLException and for the demo code just print a stack trace if we get an Exception. An SQLException would include a syntax error like field names that don’t exist.  In a production application, you would never use a stack trace. Instead, you would log errors and potentially take other action. However, a stack trace is fine for development and debugging (and demos!).

 

public static int updateCustomers(String currentFirstName, String newFirstName)

{

    int numUpdated = 0;

    String updateSQL = "UPDATE CUSTOMERS SET FIRSTNAME=? WHERE LASTNAME =?";

    try

          (

                Connection con = DriverManager.getConnection(DATABASE_URL_LOCAL, fmp_user, "");

                PreparedStatement ps = con.prepareStatement(updateSQL)

           )

    {

        con.prepareStatement(updateSQL);

        ps.setString(1, currentFirstName);

        ps.setString(2, newFirstName);

        numUpdated = ps.executeUpdate();

    }

    catch (SQLException e)

    {

        e.printStackTrace();

    }

    return numUpdated;

}   // end updateCustomers()

 

Pretty easy, right?

 

In this updateCustomers() method, we also used the newer version of the “try-catch” introduced in Java 7. You can lookup “Try with Resources” to learn more about it, but basically this new try-catch makes closing resources much easier without all the messy try-catch blocks you saw in the selectCustomers() method. We could have used this newer try-catch technique in the selectCustomers() method as well.

 

In the code above, we create the PreparedStaement and then set the parameters. Finally, we execute the Update. Once we’ve prepared this statement, it’s compiled and ready for fast re-use.

 

Example:  Let’s update the FileMaker database by changing “Phillip” to “PHIL” for last name = “Richardson”.

 

We would call this from our main method as you saw above:

 

    int numUpdated =  updateCustomers("PHIL", "Richardson");

 

When we run our code, we now get:

 

It works!

 

Of course, how you implement your update methods is up to you. This example was made up for demonstration. Also, as previously mentioned, in production you would not use hard-coded strings and you might not be using JDBC directly at all (or using simpler JDBC via a framework). Most frameworks highly abstract what it takes to write JDBC code, but it’s good to understand what’s going on.

 

ADVANCED TOPIC: (Building first and then) Scheduling Code to Run!

 

So, we created a JDBC program (or any Java program) and we would like to schedule it so it will run automatically. Can we do that? Absolutely.

 

Depending what IDE you’re using, you may have to do a manual “compile” or if you’re writing Java from the command line, you already have the compiled file.

 

When you write your Java source code, you have a file (called a class) that ends with “.java”. The “compiled” version of that code, the code that runs, ends with “.class”.

 

Thus, we just need to run that “.class” file using the Java program at the command line.

 

For this example, though, we have an added wrinkle (which is what makes this section a bit more advanced). Specifically, we need the JDBC driver to access the FileMaker database so the JDBC driver has to someplace our Java code can find it (called the “classpath”).

 

Remember the special Exception we added to handle the FMP database possibly not being open? That Exception class comes from the FileMaker JDBC driver also so we need the JDBC driver for that, too.

 

The solution to this “multi-file” dependency problem is often to “JAR-up” (make an executable JAR) from our compiled project files. Once we have that executable JAR file, which contains our Java compiled code and the FileMaker JDBC driver file, we can run it.

 

For our example here, we created that JAR file and named it: “FMPJDBCExample.jar

 

Creating a JAR is not difficult, but is outside the scope for this quick intro. There are numerous online resources that will show you how to “JAR up” your dependencies.

 

When you have your working JAR file (remember a JAR file is just a ZIP file by another name), you might have contents that look similar to this:

FMPJDBCExample.jar

 

Of course, how you created your project directory structure could be different so this structure above just gives you a rough idea.

 

Since your JAR file now has all the dependencies (your JDBC code and the FMP JDBC driver), once you have your JAR file, you can just run it directly as shown below—right from the command line (no IDE needed):

 

Cool!!!

 

Not shown in the example above, but you could also pass parameters to the JAR file so you could specify, in our example case, which first name to update from which last name.

 

So, assuming you added a little more code to your JDBC logic to handle incoming parameters, you might use the Java command above, like this when calling the JAR:

 

$ java -jar FMPJDBCExample.jar PHIL Richardson

 

Now that we can run the code from the command line, we just use this command (the “java -jar FMPJDBCExample.jar” above when we want to schedule this code to run at one or more times.

 

Conclusion:

 

Once you write a JDBC program for FileMaker, you’re then able to write a JDBC program for MySQL, for Oracle, or any JDBC-supported (all of them) database. There could of course be SQL differences, field type differences, and other usually minor differences. You would also need to download the JDBC driver for those platforms. However, JDBC itself is mostly generic so it’s highly reusable.

 

Thus, learning to code JDBC is a transferable skill you can apply to other databases often using very similar logic to what you wrote for FileMaker.

In this example, we did both SELECT and an UPDATE example both of which shows you the incredible power over your data JDBC gives you.

 

Some of this article got a bit technical, but with a little research and practice you’ll find it’s really not difficult to work with JDBC.

 

Please post comments for future updates to this article.

10 people found this helpful

Attachments

    Outcomes