Thursday, July 12, 2007

FileMaker 9 SQL Link... ish

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. And...

  • 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"

    UPDATE: The 100 connection limit is for INCOMING connections (FMP as a datasource). The limits on the new ODBC connection is not spelled out anywhere.

  • 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.

10 comments:

Jan Aleman said...

So on their website they say: "Based on future-proof open ODBC standards" ODBC future proof??? Come-on! Nobody uses ODBC anymore, Microsoft (more or less the inventor of the standard) stopped using it 5 years ago!

William Moseid said...

As ALWAYS, Bob Cusicks observations straighten crooked logic and shed the light of reason on the topic.

David J said...

Accurate...ish
"...AND you're on Mac or Windows..."
So, ummmmm, if you fit the profile of most end users...

" ...AND you can install ODBC drivers on to every client (or you're using FMP Advanced Server and only need UP TO 100 connections)"

Wrong. While you DO need FileMaker Server to connect a group of clients (and avoid setting up a connection on each), you do NOT need FileMaker Server Advanced, which is a more expensive product that provides additional PUBLISHING abilities (this is what you use to make FMP act as a back end to a website via PHP or XML).

Moreover, there is NOT a 100 connection limit to External SQL Sources. You are confusing the new ESS feature (which allows FileMaker to ACCESS data) with FileMaker acting as a host to SERVE data to SQL sources (which requires FM Server Advanced and does have a 100 connection limit).

FMP 9 is not intended to be a front end to SQL -- and they are very clear about that. However, the new ESS feature allows substantially streamlined access to SQL data in cases where FileMaker is otherwise the right tool -- particularly in cases where there is a substantial workgroup need overlapping with a very narrow web need (MySQL publication of specific datasets, etc.).

Otherwise, a very unibiased review.... ish

Chris Kubica said...

Bob, a few notes (I posted this same comment on Workman's blog, but the comments are just as relevant here).

1) Where have you gotten that FileMaker is trying to sell its products as enterprise-class tools? They are clear, and have been for years, that they are not at all interested in 500+ user systems. So you're comparing Apples to Oranges. 1-200 or so users is their sweet spot and in that space they are top dog, in my opinion.

2) FileMaker runs circles around Servoy in ease of use. Hands down.

3) I tried the Servoy demo after a Cusick webinar a while back. I found even your own test server with light load painfully slow. Even Cusick wasn't sure why. So what are you boasting about? I also thought your interface development tools were either unattractive or difficult to understand.

4) FileMaker 9 is just the beginning as far as ESS (External SQL Source) funtionality. You are being unfairly judgmental with the ESS features, in my view. But I guess you have an agenda, eh? ;)

Bob Cusick said...

Hi Chris and David - thanks for your comments!

Hey, as I have said 1,000 times before - FileMaker is a great tool for what it is. Servoy is a great tool for what it is.

I'm not sure I agree with David's assertion that there is a limit of 100 concurrent connections OUTGOING on the Advanced Server when connecting to an ODBC source. I couldn't find out any clear documentation on that - perhaps if you know the answer - you could post it? I DO know, however, that ODBC is not an "unlimited" technology. It does have a concurrency upper limit - or if they are trying to jam everyone through a single connection - then that will be interesting from a scale point of view as well.

As for Chris' assertion that FMP "runs circles" around Servoy in terms of ease-of-use - I think if you're talking about version 6 - you're right. After 7.0 - IMHO - no way. FMP is very convoluted - especially with the relationship graph and bi-directional joins, etc.

Basically - my "agenda" is to use the right tool for the right job. Servoy is for SQL - FileMaker is NOT. Servoy is for high concurrency and stability under load - FileMaker is NOT.

Bottom line: use the tool you like best for the job at hand.

Rodney said...

I can't believe FMP released this as a full point release. More like 8.6

Where are the rumored events like On data change/On entry/on exit of a field? A very disappointing release.

I totally get FMP as a development tool for the knowledge worker within a company for smaller projects. For this target market FMP is perfect. What i don't get are professional developers using FMP.

i doubt there are any FMP developers who have moved to servoy would ever go back, its just too limiting.

I used to be one of those FMP developers waiting for new versions to fix all its shortcomings, they never did, and (unbelievably) still don't.

Bevil said...

I was a Filemaker developer for 10 years from v. 2.1 onwards. It was a great experience.

I have now been a Servoy developer for about 6 and would NEVER consider building a new solution in Filemaker. All of the things that Filemaker got wrong over the years were solved at a stroke with the first version of Servoy - which has gone through 3 iterations since that.

For anyone considering what Filemaker might do for development, Servoy is truly king and has none of the compromises that Filemaker has had to make.

I'm not bashing Filemaker per se, I am just someone who was pressed against a glass ceiling with the limitations of Filemaker, and have found myself at the bottom with a big open sky ahead with Servoy. Servoy is quite Filemaker like in its approach, but has javascript methods, can use ANY (*this is important*) SQL back end - think being able to relate the default Sybase IAnywhere database (which has all sorts of enterprise class features just by its-self) to a MySQL database which drives your web content, while also relating to an existing Oracle system which handles your accounting, with all three databases in different locations, and being able to make it available (*this is also important*) OVER THE INTERNET to 50,000 clients (clients, records and database file size are limited by the hardware, not the software. Servoy has a concurrency based licensing system. You apply 10 licenses to the server software and 10 clients can connect, whichever machines, locally or over the internet... No CDs in boxes, or serial numbers to manage, or licenses being tied to specific computers etc.

Servoy does have a bit of a learning curve - but is very familiar to Filemaker developers, it is cheaper than Filemaker (no Servoy server costs and a cheaper client), has a Java smart client mode, a headless client mode (clients are spawned on the server), has an Ajax webclient mode, (see the Servoy forums to note how quickly they implement changes, and how quickly the Servoy community helps to solve problems.)

Filemaker was a great product but has been scuppered by architectural programming issues which were never solved. They're trying to dig themselves out of a hole but must rely exclusively on existing business re-buying their software so every couple of revisions they change the file format to force an upgrade on everyone... Awful practice...

No, I don't work for Servoy, I'm just a developer and cannot find faults with their product, where I could find almost nothing but faults (or at least serious limitations) with Filemaker. Probably 50% of my development work now is converting Filemaker solutions (some of which I built myself, years ago) into Servoy ones.. I'd never look back.

Jacob Avila said...

Hello everyone,

I tried to use FileMaker Server Advanced 8 as a backend of a JSP/Servlet application connecting via JDCB, but when records increased to almost 20,000 in two related tables, executing an inner join query for those tables was enough for FileMaker. Those kind of queries never finish returning data. I see that cartesian product via ODBC/JDBC is really poor suported in FileMaker.

Does anyone know if this situation was fixed in version 9?

Homer said...

David J said...
"Otherwise, a very unibiased review.... ish"

Homer said...
For different reasons, we all have our gods and I don't want to start a subjective discussion about RAD that aren't playing in the same level.

But I would say that the webinar given by Rich Kalman on june 2007 the 12th was demoing a "front end" to a SQL database back end.
ref.: http://filemaker.com/support/webinars/seminars.html
See the webinar named "Connecting FileMaker and MySQL to Quickly Create Database Solutions".

Skewed montage made this video 16 minutes shorter than the original footage and no, it is not the marketing stuff who's being removed.

Things were cut...
Maybe it is the two(2) live crashes that Mr. Kalman got with his 'in the lab' software. Maybe it is the part where we can see that the SQL db is actually hosted on the same local machine that runs FileMaker?

I don't know but the unbiased thing to ask here is : Is FileMaker 9 using third-parties ODBC drivers can really be usable with a remote SQL database?

With the objective tests I done here, my conclusion is that SQL on FM is a marketing hype.

zimbabwe said...

Btw we encountered exactly the same thing and it is highlighted in our blog at

http://alphasoftware.blogspot.com/2007/09/sql-performance-alpha-five-vs-filemaker_20.html

it would appear that the good folks at filemaker decided that "client-client" computing is superior to "client-server" computing:)

Web Analytics