As an example, suppose you have a FileMaker Pro system deployed at a university that manages student data in a centralized student information system (SIS) based in an Oracle database. Your system is used in the Engineering department, and must have access to current information on all undergraduate and graduate students enrolled in Engineering. Presently, the Registrar's office sends you a text-based export file of a limited field set, once per week, that you import into the FileMaker Pro system in Engineering. This process works, but it would be good to have access to a lot more information about the students. And the weekly update is just barely sufficient: daily would be much better, and up to the minute better still.

Using ESS, you could accomplish all that (at least from a technical standpoint). You could add the appropriate Oracle tables to the Relationships Graph in your Engineering system. As the data changes in the main SIS, you'd see the data change simultaneously in FileMaker Pro. You could create relationships between the Oracle tables and your FileMaker Pro tables. With appropriate permissions, you might even be able to add data directly to the main SIS.

That brings up a possible pitfall. Although this is all technically achievable with ESS, you might face some administrative hurdles. The SIS administrators would have to be willing to grant you direct access to the system, which might run foul of university IT policies. To achieve effective integration, they might have to create special "views" in the Oracle system, which would require time they might not want to spend. There might be other security or overhead concerns that could arise from direct integration, concerns you'd need to understand and manage.

This article won't help you negotiate the higher-level organizational issues that may arise in systems integration projects, but it will show you how ESS makes new kinds of integration technically feasible, and potentially quite simple. I'll show you an example scenario, then discuss the strengths and limitations of the technology, and examine which kinds of scenarios may be the best fit for ESS.

How ESS works

ESS uses a technology called Open Database Connectivity (ODBC) to connect to supported SQL data sources. ODBC is an industry-wide technology that connects a broad range of data sources. It's the technology behind the other two main methods for connecting FileMaker Pro systems to SQL data sources: the Import from ODBC Source command and the Execute SQL script step. If you've connected a FileMaker Pro system to a SQL data source using an earlier version of FileMaker Pro, odds are you've used ODBC.

Two things are required to connect to a SQL data source via ODBC: An ODBC driver, which is software that permits connections to a specific type of data source, such as Oracle or MySQL. The second is a Data Source Name (DSN), which is a bundle of configuration settings that pair up with a driver to connect to a specific instance of a data source. For example, "connect me to the student_info database on the University's Oracle server on the computer sisinfo.whereveru.edu using the user name fmpaccess and the password grundoon." Typically, the driver and the DSN must be located on the client computer (the one doing the querying).

Although there are ODBC drivers for a variety of SQL and non-SQL data sources, the ESS feature in FileMaker Pro 9 presently works with three types of data sources:

• MySQL 5 Community Edition
• Oracle Database 9i and 10g
• Microsoft SQL Server 2000 or 2005