First look at the new FileMaker ODBC drivers

/in /by

FileMaker 11 has been released and it’s full of great new features. While not the most glamorous, one of my favorite new features is the new FileMaker ODBC drivers. The drivers have been rewritten and are much easier to install and configure on both Windows and Mac OS X. Here’s an example of one of the DSN configuration screens for Mac OS X: FileMaker 11 DSN Configuration

If you’ve struggled with DSN configuration with previous versions of the FileMaker ODBC drivers for Mac OS X, I’m certain you’ll appreciate the beauty of this new version. Also, note that the driver now supports a slow query log, which can be quite useful for troubleshooting performance issues in your application.

Higher Limits

One of FileMaker 10’s most frustrating ODBC limitations was the restrictive limit on the length of SQL queries. Most queries in FileMaker 10 were limited to a few thousand characters. FileMaker 11 supports longer queries and also parameterized queries, which allow very large text or binary data to be inserted into FileMaker fields, if your client supports them.

PHP

The new ODBC drivers work much better with PHP. Here’s a short PHP snippet which shows a few of the features that are now supported, including DSN-less connections and prepared statements:

$connection = odbc_connect( "Driver={FileMaker ODBC};Server=localhost;Database=FMServer_Sample;", $user, $pass );

$statement = 'UPDATE english_nature SET status=? WHERE "Quantity in Stock"<=?';

$query = odbc_prepare ($connection, $statement);
$status = "Backordered"; 
$quantity = 0;
$result = odbc_execute($query, array($status, $quantity));

One of the nice things about using ODBC instead of the FileMaker API for PHP is that an UPDATE like the one shown in this example will happen atomically…either all of the records are updated or none of them are. In this example, if any of the records with a quantity of zero is locked, none of the records will be changed and the odbc_errormsg() function will return:

[FileMaker][FileMaker ODBC] (301): Record is locked by another user

Accomplishing the same result with the FileMaker API for PHP would be extremely difficult and the performance would be much slower.

DDL

FileMaker 11 supports new options for some of the Data Definition Language (DDL) commands as well. For example, you can use the CREATE TABLE or ALTER TABLE commands to set some of the auto-enter options in FileMaker’s field options by using the DEFAULT keyword. Here’s an example that creates a new table named “log” with a text field named “account” and a date field named “creation_date”. The account field will have a field validation that limits the length to 30 characters and an auto-enter setting to insert the creation account name. The creation_date field will auto-enter the creation date for new records.

CREATE TABLE log (account VARCHAR (30) DEFAULT CURRENT_USER, creation_date DATE DEFAULT CURRENT_DATE)

The following examples show how to change some of the auto-enter options for existing fields:

ALTER TABLE log ALTER COLUMN account DROP DEFAULT ALTER TABLE log ALTER COLUMN account SET DEFAULT CURRENT_USER

Other Changes

Other new features include the ability to access repeating fields with the familiar square-bracket syntax (i.e., myField[3] ), the ability to set the contents of container fields from an ODBC client application, and support for LEFT JOIN and RIGHT JOIN in the FROM clause.

Be Careful When You Upgrade

Along with all the improvements come a couple of things to be aware of as you consider upgrading your systems. Data in FileMaker 11 can not be accessed from previous versions of the FileMaker ODBC drivers, nor will the new FileMaker ODBC drivers access data shared by FileMaker 10 or earlier. Also, FileMaker ODBC is now much more strict about data types. FileMaker scripts and calculations generally coerce data to the appropriate type and previous versions of FileMaker ODBC were similarly lenient regarding data type mismatches. Consider the following SQL statement:

INSERT INTO myTable (myTextField1, myTextField2) VALUES (10, DATE())

This worked fine in FileMaker 10 and earlier. In FileMaker 11, it will return an error:

[FileMaker][FileMaker ODBC] FQL0013/(1:40): Incompatible types in assignment.

The query must be changed to something like:

INSERT INTO myTable (myTextField1, myTextField2) VALUES (’10’, STRVAL(DATE()))

Conclusion

Overall, ODBC/SQL support in FileMaker 11 is much improved and the performance seems to be very good relative to previous versions. Improvements have also been made to JDBC, ESS, and the plug-in API that I’m sure will be leveraged in creative ways by many developers. If you’d like to see what’s possible with all of the ODBC/JDBC/SQL/ESS features in FileMaker 11, consider attending this year’s FileMaker Developers Conference. There are at least seven different sessions and workshops that are focused on these features. I’ll be leading two of the sessions, a pre-conference session titled, “Introduction to ODBC, JDBC, and SQL for FileMaker Developers” and a session titled, “Getting Started with the New FileMaker ODBC and JDBC Integration”. I hope to see you there!