When a geological rift forms, scientists see discovery and possibility. What will you discover?

FileMaker 11 and ODBC Changes


Here is an article that I thought anyone using ODBC would want to take note of. Take special note of the lack of back-ward/for-ward compatibility between the different driver versions. Also, mind you, I haven’t tested everything in this article. I just wanted to pass the info along.

This is from a blog post over at SkeletonKey.com

FIRST LOOK AT THE NEW FILEMAKER ODBC DRIVERS

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:

odbc11_config.png

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!

Submitted by greg.lane on Tue, 03/09/2010 – 20:07

Advertisements

5 responses

  1. jcrossfield

    We upgraded our server to FM 11 this weekend. We use tODBC to share data between FileMaker and our .asp website. There were major issues with the new ODBC drivers. Basically we found that there is a problem getting TEXT values out of the database – TEXT values are not returned from the found records in a SELECT query unless the max characters validation is set on that field. I have been going thru our website all week tracking down all of our text fields and setting up the max chars validation so that those values will come thru via ODBC.

    There is no workaround that I have found yet for CALCULATION fields, since there is no way to set a max characters validation. I know what you’re thinking, but in my case these fields cannot be simply changed to text fields with a specified calculation, as they need to be not-stored. Anyway, I have submitted a bug report to FileMaker and a paid support ticket and they have yet to respond, as they are still researching the issue since Monday when I reported it. But I just wanted to bring it to others attention in case you’re thinking of upgrading to server 11 – if you’re using odbc and text fields you might want to wait.

    April 1, 2010 at 2:46 PM

  2. James Powell

    Hi jcrossfield,
    Having just upgraded to Filemaker 11, I’ve immediately run into your described TEXT problem. Did you manage to resolve the issue and how?
    — James

    February 8, 2011 at 7:26 AM

  3. I haven’t really come across anything that solves the problems mentioned in the article. And at the same time, I don’t rely heavily on the ODBC driver myself. We use a driver, but it is actually the MySQL driver. We created an ESS connection from our FileMaker solution to the MySQL web server and just import the records that way. It’s not the most efficient way to handle it, but we also don’t require a lot of data transfer.

    What are you using to hold the data for the site? MySQL? MSSQL? Oracle? or something else?

    How are you using the connection? Is FileMaker or the Web Server the datasource? Or are you going 2-way?

    If you give me a few more details about how you are using the ODBC connection, I can try to help some. Otherwise, you could post your question in the ODBC forum at FileMakerToday.com…good chance someone over there can help you out.
    http://filemakertoday.com/com/forumdisplay.php/22-ODBC

    February 8, 2011 at 9:48 AM

  4. James Powell

    As a follow up to my above post, the eventual solution to the TEXT field problem was going under “Advanced Language Options”, turning off “auto-detect language…” option, and manually selecting UTF-8 as the text encoding. This worked (at least when using the latest driver version (11.03.76) with the “Describe text fields as long varchar” option set).

    — James

    February 9, 2011 at 12:49 PM

  5. philmc

    Running Win7 64 bit….Just downloaded FM 11 demo – cannot find xDBC directory to install ODBC drivers. Have tried FMSA11.0v3_xDBC_Update_11.3.76 and 11.3.81 – these “seem” to install but the install window closes very quickly and I get no message to say it was successfull. When i run ODBC config (either 32 or 64) I do not see any drivers for FM. Maybe i’ll try running those installs as administrator. Any ideas?

    June 7, 2011 at 12:26 AM

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s