Working with FileMaker container field data via JDBC

/in /by

In a previous article, we shared some of the significant changes in ODBC and JDBC-related features in FileMaker 11. This article takes a closer look at how to work with container field data via JDBC.

Inserting Image Files

FileMaker 11 is the first version that provides a way to manipulate container field data via xDBC. The new FileMaker ODBC and JDBC drivers provide support for inserting (and updating) files into container fields via SQL from a variety of development environments. You’ll need to use a prepared statement with the PutAs() function and stream the binary data from your program. Here’s a simple Java/Groovy example that shows how to create a new record in a FileMaker database and put a JPEG image into a container field. The example uses the FMServer_Sample.fp7 database that comes with FileMaker Server Advanced.

import java.sql.*; import java.io.*; def url = "jdbc:filemaker://localhost/fmserver_sample"; def driver = "com.filemaker.jdbc.Driver"; def user = "admin"; def password = ""; System.setProperty("jdbc.drivers", driver); connection = DriverManager.getConnection (url, user, password); filename = "/Users/Greg/Pictures/vacation/DSC_0202.jpg"; file = new File (filename); inputstream = new FileInputStream (filename); sql = "INSERT INTO english_nature (ID, img) VALUES (-1, PutAs(?, 'JPEG'))"; pstatement = connection.prepareStatement ( sql ); pstatement.setBinaryStream (1, inputstream, (int)file.length ()); pstatement.execute (); //cleanup pstatement = null; inputstream = null; file = null; connection.close();

Reading Data from Container Fields

The following example shows how to get an image from a container field in a FileMaker database using the GetAs() function.

import java.sql.*; import java.io.*; def url = "jdbc:filemaker://localhost/fmserver_sample"; def driver = "com.filemaker.jdbc.Driver"; def user = "admin"; def password = ""; System.setProperty("jdbc.drivers", driver); connection = DriverManager.getConnection (url, user, password); statement = connection.createStatement (); results = statement.executeQuery ("SELECT id, GetAs (img, 'JPEG') AS img FROM english_nature WHERE ID=23"); if (results.next ()) { imagedata = results.getBinaryStream ("img"); filename = "/Users/Greg/Desktop/image_" + results.getString("id") + ".jpg"; outputstream = new FileOutputStream (filename); int c; while ((c = imagedata.read ()) != -1) outputstream.write (c); outputstream.close (); results.close(); } connection.close();

You can also use the Cast() function to retrieve the name of the file that was inserted into a container field or a file path for a file that was stored as a reference.

More Information

For more information about the file types that can be used with the GetAs() and PutAs() functions, see pages 40-42 of the FileMaker ODBC and JDBC Guide. Have a quick question? Contact Greg Lane. If you need guidance or coaching through a particularly complex topic, shoot an email to Jason Thomas. He’ll help you determine if we have the right program to fit your needs. Greg Lane is Vice President of Application Developoment & FileMaker Certified Developer at Skeleton Key. About Skeleton Key Skeleton Key is an accomplished team of technology consultants who solve business problems. We specialize in the rapid development of custom applications, integrating Macs and PCs in the professional workplace, and providing personalized training. Despite our end-to-end technical skills, we are consultant first and technologist second. We know that you don’t just need technology. You need to know that the technology you choose to deploy will provide the results you desire. Skeleton Key is a Platinum Level FileMaker Business Alliance company, an Authorized FileMaker Trainer, a member of the Apple Consultants Network and a Microsoft Registered Partner.