External SQL Data Source Overview

Create live connections to Microsoft® SQL Server, Oracle or MySQL data sources with FileMaker Pro 10

What is it?

The External SQL Data Source feature establishes a live two-way connection between FileMaker Pro 10 and top SQL data sources.

If you do not want a live connection, but wish to import or exchange data with a SQL data source, use the ODBC/JDBC features.

Which external SQL data sources have been certified to work with this feature?

• MS SQL Server 2000
• MS SQL Server 2005
• MS SQL Server 2008^*
• Oracle 9i
• Oracle 10g
• Oracle 11g^*
• MySQL 5.0 Community Edition (free)
• My SQL 5.1 Community Edition^* (free)

*New in FileMaker Pro 10

At this time, these are the only SQL data sources that work with this feature.

FileMaker and MySQL are partners. Connect FileMaker and MySQL for easy, rapid database solutions.

What does it allow you to do?

• Create solutions in FileMaker Pro 10 that include data from:
  • An external SQL data source
  • Multiple external SQL data sources
  • FileMaker Pro and external SQL data source(s)
• Provide an alternative to ODBC/JDBC for importing and exporting data from FileMaker Pro 10
• Enable the use of FileMaker Pro 10 as a reporting tool for external SQL data sources

Choose the FileMaker 10 product that is right for your needs

FileMaker Pro and FileMaker Pro Advanced	FileMaker Server and FileMaker Server Advanced
Great when you want FileMaker users to directly read and/or write data to a SQL data source. You will need to have an ODBC driver for the SQL source configured on each client machine.	Great when you want all access to a SQL data source to flow through a single FileMaker Server. Setup and configure the ODBC/JDBC driver once on the host computer.  All users share the connection to the SQL data source.

Before you get started

• Install the appropriate certified ODBC driver.
• Know the IP address or domain name of the external SQL data source.
• Know the user name and password for the external SQL data source as supplied by the database administrator.  The access that the database administrator provides will determine if you will be allowed to read/write or if you will have read only access.

Real-world use case examples

Streamlining sales processes

• A sales force automation system, built on FileMaker Pro was importing order data from a SQL based enterprise finance system each night.  Order information available to Sales was up to 24 hours old.  When customers would call in, the salesperson was not always sure of the status of their order.  Using the External SQL Data Source feature, Sales will now see the orders the minute they are processed by Operations.  Customer satisfaction is up and sales are growing.

Better reporting

• A company sells photographic equipment through an online web store.  Transaction data from the store is captured in a SQL database. However operations and management has a difficult time getting reports from the SQL database because it requires a programmer.  By providing a read-only link from FileMaker to the SQL database, employees in the company will be empowered to create their own reports.
• Many bosses would prefer to receive reports via email in PDF or Excel format.  Using the External SQL Data Source feature, you can manipulate data from FileMaker Pro and external SQL data sources and then use PDF Maker or Excel Maker to email it to your boss in the format he or she prefers.

Enhanced workflows between departments

• A human resources department, whose data is housed in a SQL data source, needs to link its processes with the finance department.  Using the External SQL Data Source feature, this information can be made accessible to knowledge workers who can build FileMaker Pro reports and solutions that meet the unique needs of their department and give the organization an edge over the competition.

More granular information

• One federal department has nationwide information in a SQL database and local-level information in databases throughout the country.  Using the External SQL Data Source feature, the information in the local databases will be combined with information in the nationwide database for in-depth analysis.

Resources

ODBC/JDBC Drivers

This feature requires that you use one of the certified drivers listed below.

Oracle

• For Oracle 9i Release 2 (9.2.0.1) on Microsoft® Windows use:
  Oracle Database Client version 9.2.0.6.5
• For Oracle 10g Release 2 (10.2.0.1) on Windows use:
  Oracle Database Client version 10.2.0.3.0
• For Oracle 11g Release 2 (11.1.0) on Windows use:
  Oracle Database Client version 11.1.0.6.0
• For all versions of Oracle supported above on Mac OS X use:
  Actual Technologies, Oracle version 2.9
  Download: EN, DE, ES, FR, JP

MS SQL Server

• For MS SQL Server 2000 SP3a (8.0.761) on Windows use:
  Microsoft SQL Server version 2000.85.1117.00
• For MS SQL Server 2005 SP2 (9.0.3042) on Windows use:
  Microsoft SQL Native Client 2005.90.3042.00
• For MS SQL Server 2008 (10.00.16000.22) on Windows use:
  Microsoft SQL Native Client 2007.100.1600.22
• For all versions of MS SQL Server supported above on Mac OS X use:
  Actual Technologies, SQL Server version 2.9
  Download: EN, DE, ES, FR, JP

MySQL

• For MySQL 5.0 Community Edition (5.0.51b-community-net) and MySQL 5.1 Community Edition (5.1.24-community-nt) on Windows use:
  MySQL Connector/ODBC version 3.51.14
• For all versions of MySQL Community Edition supported above on Mac OS X use:
  Actual Technologies, Open Source Databases version 2.9
  Download: EN, DE, ES, FR, JP

Documentation and Notes

• External SQL Data Sources: Chapter 5 of the FileMaker Pro 10 User's Guide
• External SQL Data Sources - What a DBA should know

Videos and Web Seminars

Introduction to Using External SQL Data Sources web seminar

Search Knowledge Base

View a list of common External SQL Data Sources questions and answers