07.12.07

FileMaker 9 SQL Link… ish

Posted in General Rant at 2:29 pm by Administrator

All data below is based on the public technical brief entitled “Introduction to External SQL Sources” on FileMaker’s Support Website. You can view it http://www.filemaker.com/downloads/pdf/public_techbrief_ess_en.pdf.

FileMaker Pro 9.0 now allows users to connect to external SQL data sources - which is great news for FileMaker Pro developers - and very useful in limited situations.

If you’re going to include a few fields from a SQL source - and that source is MS SQL Server 2000 or 2005; Oracle 9g or 10g; or MySQL 5.0 Community Edition - AND you’re on Mac or Windows - AND you can install ODBC drivers on to every client (or you’re using FMP Advanced Server and only need UP TO 100 connections) - then it’s great.

It would be also helpful if you didn’t have to do large searches or sorts on the SQL data.

And it would be good if two people didn’t update the same record at the same time.

Other than that - there are more caveats, exceptions and notes that I’ve outlined below. Each item is explained in greater detail below the table:

SQL Linking Comparison Overview

• No data-broadcasting of ESS datasources…just “periodic refreshes” which can result in inconsistent data.
  Reference: Pg 24
  “FileMaker Pro 9 periodically refreshes its view of SQL data, but there are times when the data visible in FileMaker Pro may be out of step with the most current SQL data.”
• No Record Locking When a user saves a record that has been altered since a refresh, they are given an option to overwrite the data, but the user has no idea what has changed. The only option is to overwrite another user’s changes, or lose their own changes.
  Reference: Pg 24
  “Two users may edit an ESS record simultaneously. Whichever user submits the record first will be able to send their changes to the database. If the second user submits their copy of the record, they will see a warning like this one: “
• It doesn’t scale past 250 users (or 100 ODBC/JDBC remote connections using FileMaker Server Advanced). Using old ODBC technology - and is limited to using specific ODBC drivers for specific SQL databases.
  Reference: Pg 4
  “ESS is not designed as a means to allow a FileMaker Pro solution to scale beyond the limits of a purely FileMaker Pro based solution”
• It isn’t a SQL front end Because of this, changes to columns and tables can be out-of-sync. FileMaker uses “Shadow Tables” and “Shadow Fields”
  Reference: Pg 4
  “The emphasis with ESS should be on integration. The ESS feature set is not intended to allow FileMaker Pro to act as a ‘front end’ to SQL data sources.”
• You can’t run manual SQL queries with ESS You must rely on FileMaker to generate all the SQL queries for you. A custom SQL query is not an option.
  Reference: Pg 4
  “ESS does not allow a FileMaker Pro developer to compose their own SQL queries and pass them to the server”
• Compatibility Issues Because ODBC drivers are being used Mac users and Windows users need different drivers and the ODBC setup is completely different for each platform. Mac ODBC drivers are limited, so FileMaker has worked with Actual Technologies to create drivers for the Mac, but these are brand new ODBC driver that haven’t gone through much testing certainly have some bugs. Also, they are 3rd-party drivers, not the drivers written by the database vendor. This means you also have to purchase the ODBC drivers separately for the Mac. Also, when there is a problem with ESS connectivity, there will now be a bunch of finger-pointing…Is it FileMaker, Actual Technologies, or the DB Vendor’s problem?

  Reference: http://www.filemaker.com/support/technologies/sql.html

• No guarantee of proper data-type mapping FileMaker doesn’t support all of the different data-types. For example, all SQL databases store integer and floating point numbers differently, but FileMaker handles them the same.
  Reference: Pg 23
  “SQL data sources, in general, support a greater number of data types than does FileMaker Pro. Often these data types are more specific than FileMaker Pro data types”…”FileMaker Pro will do its best to impose validation on the shadow fields that will enforce these limits”
• Some Slow Searches FileMaker doesn’t fully utilize SQL to all of its searches because some of the types of finds you can do in FileMaker aren’t fully supported in SQL. Also, FileMaker users were previously used to being able to search any field, and FileMaker would create an index for it. This no longer works with ESS.
  Reference: Pg 28
  “Certain searches may not be fully supported in SQL, in which case FileMaker Pro will ‘finish’ the query processing itself”
  “You may want use FileMaker Pro’s layout features to inhibit users from entering non-indexed ESS fields while in Find mode.”
• Slow Sorts FileMaker doesn’t utilize the SQL database for sorting. Instead it retrieves all the rows in the data set and does the sort itself, which is very ineffecient on medium to large data sets.
  Reference: Pg 28
  “All sorting of ESS data is performed within FileMaker Pro itself. Because of the way FileMaker Pro queries for ESS data, sorting a large ESS data set is likely to be inefficient, and should be avoided.”
• Can’t update the SQL schema You can’t alter the SQL schema - only the “shadow tables” that FileMaker is importing data into.
  Reference: Pg 25
  “It’s been mentioned several times, but bears repeating: the ESS features do not afford FileMaker Pro developers any means to edit the schema of an SQL-based table or database.”

There are other things to be aware of - but these are the highlights. It’s all about using the right tool for the job. If you need only a couple of fields or have “light” needs to view data in SQL tables - use FileMaker. For a true “front end” to SQL - or more advanced needs like data broadcasting, locking, fast performance, scalability, reliability, and/or you don’t want to have custom ODBC drivers installed and maintained- then you owe it to yourself to download and try Servoy.

LEGAL NOTE: Servoy and the Servoy logo are trademarks of Servoy, Inc. registered in the U.S. and other countries. All other trademarks are the property of their respective owners.

Permalink