Understanding FileMaker ESS (And Convincing Your SQL DBA That It Is Safe!)

I've been using the ESS feature in FileMaker 9 for awhile now. I've used it to develop several FileMaker-based, mission critical solutions that integrate very well with SQL-based systems. I'm living proof that FileMaker can be integrated with SQL databases, and it can be done securely and efficiently.

One of the most common complaints that I hear from SQL DBAs with regards to ESS is that they don't trust it to perform INSERTs, UPDATEs, or DELETEs against the tables in their databases. I don't trust it either (and neither should you!). That's not a knock against FileMaker -- I simply don't trust any external process to interact directly with the tables. If that is what your DBA is doing (or proposing to do), you should ask them why.

Instead, they should provide a set of stored procedures for all of the INSERT, UPDATE, and DELETE operations that you'll need. That way, the data can be evaluated, business rules can be applied, and so on by the stored procedures -- and the stored procedures can perform the INSERTs, UPDATEs, and DELETEs.

Ideally, when getting things setup for you to access it via FileMaker, your SQL DBA should:

(1) Provide you with a SQL account that allows you read-only access to the data. If possible, your account should have SELECT privileges on a set a of VIEWs -- not on the base tables themselves. That way, through the definition of the VIEWs, they can limit the data that you can see and have already worked through any issues with regards to joining data in multiple tables. Using ESS, you can integrate these views into your FileMaker database (as table occurrences).

(2) Setup the SQL account so that it has EXECUTE privileges on a set of stored procedures that you can use to insert, update, and delete records. You can use the FileMaker "Execute SQL" script step to call the stored procedures and pass parameters to them. The issue is getting feedback from the store procedures. (For example, was the request successful, or did it fail, and if it failed, then why?). Some stored procedures will return parameters to you after they've executed and/or one or more data sets. The "Execute SQL" script step allows you to call stored procedures, but it doesn't provide you with a way to access the output from them. (There are ways to work around this. If you are interested in how I'm doing it, drop me a line.)

So, to summarize:

• Use ESS to view the data in the SQL databases. Never use it to insert, update, or delete records!
• Use the Execute SQL script step to call stored procedures to update data in the SQL databases.

I hope this helps others in their attempts to "sell" FileMaker to their SQL DBAs and IT departments.

-- Tim

FileMaker Bug: Problem with "Selecting Unique Key"

I think I've found a bug in FileMaker Pro 9's ESS function, specifically with regards to how it deals with SQL views as datasources. Here's the story...

I was working on a FileMaker / SQL Server integration project, and run into a problem. I was trying to include a view in the relationship graph. I selected the datasource, selected the view, and was prompted to select a column to use as the unique key. So far, so good...

I selected the column that represents the primary key, clicked ok, and then got a message indicating that the "selected columns contain some non-unique values and therefore cannot be used to uniquely identify each and every record in this table of view."

I confirmed that the column that I am selecting really is the primary key of the table that the view is based on. I queried the table to confirm that there were no duplicate values in it, and that no records had NULL values in the column. So I had no idea why FileMaker didn't think that the column could serve as a unique key.

I've never had this problem before. I've been able to include SQL Server views in the past, and when FileMaker prompted me to select the unique key, after doing so, it always worked.

I was stumped. So I started experimenting...

I took the view apart, piece by piece. One of the columns in the view was the result of a subselect, so I commented it out, and then tried to use the view in FileMaker. That didn't help.

I tried removing some of the column aliases that were used in the view. That didn't help either.

The view that I was trying to work with is defined using a query that spans two databases (sometimes referred to as a "cross database query"). The view itself is defined in Database A, while the tables that it selects from reside in Database B. So I decided to move the entire view from Database A to Database B. And finally, that did the trick!

My conclusion: FileMaker is unable to work with views when they are defined using cross database queries.

I now think that the error that I was getting ("selected columns contain some non-unique values and therefore cannot be used to uniquely identify each and every record in this table of view") wasn't accurately describing the problem that FileMaker was running into. I think that, during it's evaluation of the column that I had selected as the "unique key," FileMaker couldn't generate the SQL that it needed in order to query for duplicate data.

My advice: Keep the views that you are going to use in FileMaker as simple as possible, and try to avoid cross database queries.

I hope this helps someone else who runs into the same (or a similar) problem.

-- Tim

FYI: I'm running FileMaker Pro Advanced 9.0v3. The SQL Server database that I am working with is running SQL Server 2005 Standard Edition (64-bit) with SP2 installed.