Bob’s World

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.

If you’ve been involved for technology for any length of time - you’ve seen enough “new paradigms” to know that everything “new” is really old - with a new twist.

Starting off with “dumb” terminals - where the user was connected to a mainframe and used a thin terminal with no internal storage - to access files and programs. All the storage, databases, and applications were maintained by infamously smug Information Technology “gods” who had a healthy distain for sharing information and a loathing of mere “users” who were viewed as parasites to “their” data and systems.

Then came the Personal Computer revolution. Where users could (GASP!) control their own files, download their own applications and take control over their computing tasks. This drove the IT priesthood nuts. They “lost” power to the individual (dare I say - “user”!). Power to the people - death to mainframes and “dumb” terminals.

After that we had (have) the Age of the Internet. Where people with PC’s could all attach to a giant server managed by IT gurus that also stored the content and had the databases…. hey, wait! It’s really the same stuffas before - but users can now clog their own drive with viruses and worms.

The “next” big thing is “offline” applications. Sort of like the stuff we had before the Internet…

The point of this whole post is this: Browser-based applications are not going away. Client/Server applications are not going away. The technology you choose for your product should be able to do BOTH and also be build on a platform that will be adaptable (in terms of platform and form-factor [e.g. mobile devices]) to handle it.

Otherwise, you may find yourself behind the technology curve and therefore vulnerable to other companies with similar offerings who are on the technology curve.

Some people must really have absolutely nothing to do all day, and have loads of money just sitting around so they can afford to just sit around and do nothing all day.

That’s the only conclusion I can come to about all the blogs and articles over the past week that have highlighted what’s inside the iPhone. They really went and spent $500 and then proceeded to rip the thing apart (taking photos all the way) to see what was inside it. Then they claim that it only costs Apple $250 to make the thing (adding up the value of the parts).

They buy the thing to take it apart. No really. Here’s an example. And they can’t wait to be the first one on the block to do it. What a BRILLIANT idea!

Then, there’s the story of the Norwegian hacker that has already figured out how to use the iPhone without activating it (i.e. paying for a phone plan through AT&T). Although the phone part doesn’t work, the iPod and wireless networking part does. He’s promised to release the hack - on his blog entry called “So Sue Me” (which, I just LOVE!).

Then, there’s the story of a rabid Mac fan who camped out to buy two phones - with the intent of selling one on eBay for a tidy profit. He bought the two phones, and then when he realized that he wasn’t the only one with the same idea and that phone prices on eBay weren’t astronomically high, he actually returned one of the phones. BRILLIANT!

Then there’s the stories of the other people intent on finding bugs - who harp about the Safari bugs, people bitching about how crazy Web 2.0 applications don’t work, people saying how easy it is to spoof the caller ID and listen to anyone’s voicemail, etc.

People, people - please! Get a life! And, if you really have nothing better to do with your $500 than to buy iPhones and bust them open - feel free to PayPal your money to me and I’ll pass it on to a worthy charity.