Working with FileMaker container field data via JDBC

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*; 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*; 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 ( ()) { imagedata = results.getBinaryStream ("img"); filename = "/Users/Greg/Desktop/image_" + results.getString("id") + ".jpg"; outputstream = new FileOutputStream (filename); int c; while ((c = ()) != -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.

Business Resolutions this New Year

Ti’s the time of year to look back over the last four quarters with an eye towards improving the next. Skeleton Key has been engaged in a high intensity planning efforts to define company and individual performance goals and bonus plans. It’s part of our Open-Book Management and philosophy and The Great Game of Business approach we adhere to… I hope you’re engaged in that level of planning also and if you’ve not in the final stages of it, you’d better stop reading this blog post and get back to it ’cause your time is close at hand. 😉 This time of year – the week between Christmas and New Year’s should be about simpler, lower level, planning. Each year, I personally make a handful of commitments to myself that usual involve accomplishing seemingly simple tasks. Last year for example, I vowed that Skeleton Key would have at least one blog post per week. Taking a look back, we’ve not been perfect, but we’ve done well. Today I want to ask you, what New Year’s resolution(s) are you making for your business? Share your thoughts here. For myself, I’m committing to configure commenting on Skeleton Key’s blog. This time next year you’ll be able to answer this question right here! In the meantime, jump to our Facebook page to respond. Happy New Year!

Let The Best Kept Secret Out

ShhhHelp us let the cat out of the bag. Skeleton Key has been called a ‘best kept secret’ many times. You see, we’re usually referred to new customers by a trusted colleague. These referrals have accounted for the majority of our business growth and we rely on them heavily. That’s why we want to make it as easy as possible for you to refer your trusted colleagues who may find value in our application development or cross-platform IT knowledge. To that end, we’re initiating a customer referral program as a way to formalize our process and hold ourselves accountable for giving an appropriate ‘thank you’ for new referrals. Like most good things, it’s very simple; refer a new customer to us and we’ll give you 10% of their first prepayment, up to $1,000. You have our word that we’ll treat anyone you refer to us as a valuable contact. We will reach out to set an appointment and let them know that you’ve valued our insights enough to put us in touch. Rest assured, we will not annoy them with unsolicited phone calls and emails. There are a couple of other less direct ways you might want to let a colleague know about Skeleton Key. If you’ve not already, please take a moment to follow us on your favorite social networking platforms. You can certainly let the cat out of the bag on a best kept secret here! Skeleton Key on Twitter
Skeleton Key on Facebook

Case Sensitive Finds in FileMaker

Guest post by Jason Mundok, IT Solutions Consulting, Inc.

Performing a Find in FileMaker is an easy, yet very robust feature. FileMaker’s query functionality is very user friendly and intuitive, allowing users to simply change to Find mode by clicking the Find button in the Status Toolbar and enter search criteria in any fields visible on the current layout. After entering search criteria, clicking Perform Find in the Status Toolbar will return a found set of records that match those criteria.

If you ever needed to search for case sensitive data in FileMaker, you probably discovered that out of the box, that is not a default behavior or FileMaker. It is however, very easy to setup, but you need Full Access to your system to do it.

In the example below we have codes entered in the Code field as both lower case and all caps. If a user enters “abc” in Find mode, the default behavior for FileMaker is to ignore case and return all records that contain either “abc” or “ABC”.

If the desired result is only “abc”, you can change the indexing language for the code field to Unicode, which will allow FileMaker to recognize the upper and lower case letters as different characters.

Setting up a field

From the File menu in the FileMaker window, select Manage, and then Database to view the Manage Database window. Navigate to the Fields tab and make sure the correct table is selected in the Table drop down list. Double click the appropriate field, in this case the Code field, and navigate to the Storage tab. Select Unicode in the Default Language drop down and click OK to close the Field Options box and the Manage Database window.

The next time you perform a Find for “abc” only the records that contain that exact value will be returned. The records that contain “ABC” in the Code field will be omitted from the resulting Found Set.

The crew at Skeleton Key would like to thank Jason Mundok for making the first ‘guest post’ to our blog. If you have any questions or comments about this tip, feel free to contact Jason at