FileMaker Pro ODBC Primer
By Clint Roberts (clint@filemakermagazine.com)
RATING: Intermediate
VERSION: FileMaker 5
PLATFORM: Macintosh & Windows
TECHNIQUE: ODBC_Primer.FP5
Why has FileMaker, Inc. undertaken the enormous task of adding ODBC connectivity to FileMaker Pro? In today's multi-platform, multi-database business world, Open DataBase Connectivity (ODBC) is a must. The days of stand-alone legacy systems are rapidly coming to an end. The ability of a Relational Database Management System (RDBMS) to survive in the real world - the real business world - is directly proportional to its ability to share data. Currently the best common standard for sharing data between multiple RDBMS's is ODBC
ODBC is an application interface (API) that exists between a database and an application. I like to think of ODBC as a conduit pipe through which data can pass from one environment to another, much like a water pipe that allows water to pass from a reservoir into a house.
This article will cover how FileMaker Pro implements ODBC, including how to configure your Driver Manager and enable ODBC in FileMaker Pro. I'll then present an example of how to pull data from FileMaker Pro into Microsoft Excel
I'll start by covering some of the basic terminology used for ODBC:
- ODBC Driver: An ODBC Driver is a component that interacts directly with a database. Because each database system uses different commands to accomplish everyday database functions (such as creating a new record), something must exist to translate SQL commands into commands that the database system can understand. This is the role of the ODBC Driver.
- Driver Manager: This is a system-level component which coordinates which data sources interact with which drivers. The Driver Manager acts as the ODBC Control Panel.
- RDBMS: Relational DataBase Management System. A RDBMS is a database system that organizes table data into columns and rows. It also has the capability of relating multiple tables to one another using a key field. If you are using FileMaker Pro as your database system then it is important to understand that FileMaker Pro is an RDBMS, however, it lacks the higher end features required to allow it to serve as a "full" RDBMS that can be queried using a query language like SQL. In most cases FileMaker Pro will serve as a "front end" or "client" which will access a more powerful RDBMS.
- SQL: At the heart of most modern RDBMS's is a programming language used for database management, reading data, writing data, updating data, etc. This programming language is known as Structured Query Language (SQL). SQL was originally designed for use on mainframe computers by IBM. FileMaker Pro does not understand all SQL commands itself by default.
- SQL-92: A standard that was published in 1992 to help standardize SQL command names and their functions. SQL-92 divides an application into three-levels, depending on the set of SQL commands implemented by the Relational DataBase Management System (RDBMS). It is important to note that the higher the level of compliance, the more advanced the command set is.
FileMaker Pro's ODBC Implementation
FileMaker Pro ships with platform-specific ODBC Drivers. They are:
For Windows: Text, Oracle 8, SQL Server 7
For Mac OS: Text, Oracle 7
Of course, FileMaker Pro works well with many third-party ODBC drivers. While different vendors make ODBC drivers, I have had the most experience with Merant International, Inc.'s drivers. If you are in need of additional ODBC drivers that are neither provided by your Operating System, nor by the FileMaker Pro package, check out the following commercial resources:
Windows -- http://www.merant.com/products/datadirect/
Mac OS -- http://www.metrotechnologies.com/eStore/eStore.lasso
Merant's DataDirect Connect ODBC and DataDirect SequeLink drivers are available for both platforms. Choose which product you go with depending on which RDBMS's you wish to connect to.
FileMaker Pro implements ODBC using a plug-in called Local Data Access Companion. Data is passed back and forth from FileMaker Pro in the following sequence:
From FileMaker Pro:
FileMaker Pro ? Plug-In ? ODBC Driver ? Driver Manager ? ODBC Driver ? RDBMS
To FileMaker Pro:
RDBMS ? ODBC Driver ? Driver Manager ? ODBC Driver ? Plug-in ? FileMaker Pro
FileMaker Pro's implementation of ODBC is at Level 1, with some Level 2 commands. Supported SQL statements are: select, insert, update and delete. In other words, you can query columns and rows, add new records, change field data, and delete records. You can easily import records from any ODBC compliant RDBMS.
However, the limitations are as follows:
- You cannot create new databases, delete databases, create an index on a field, or delete an existing field index. Unsupported SQL statements are: create table, drop table, create index, drop index.
- You cannot export data directly into an ODBC-compliant RDBMS. But, direct importing is supported. A work around is to pull information into a RDBMS from FileMaker Pro (the SQL statement is sent from the RDBMS to FileMaker Pro and data is returned).
- The Local Data Access Companion is not multi-threaded. Therefore, all SQL statements must wait their turn to connect to the database and perform their function. This means that only one connection to a database is supported at time. While only one connection can be made to a particular FileMaker Pro database, multiple SQL statements per connection can be sent.
- Data cannot be pulled from related fields.
- Speed is an issue. Where the Local Data Access Companion is not multi-threaded, and all ODBC connections must be funneled through this plug-in, FileMaker Pro's response to SQL statements can seem slow.
On a positive note, the FileMaker Pro ODBC features that are supported, I've found, are implemented well. Let's now take the steps necessary to enable ODBC connectivity. First we will configure your Driver Manager, and then we will configure FileMaker Pro.
Configuring the Driver Manager
Preparing your system for ODBC connectively varies widely depending on the Operating System you are using. If you are using Windows, you already have everything that you need. However, the Mac OS does not contain a system-level Driver Manager. If you are using Mac OS, you will need to do one of the following:
- Ensure that you have installed the ODBC Support Files that came with FileMaker Pro 5 (part of an Easy Install),
- Install the ODBC Package that came with Microsoft Office (check out the Value Pack installer), or
- Download the Merant DataDirect Connect ODBC evaluation package from http://www.metrotechnologies.com/eStore/eStore.lasso (you will need to fill out a form).
Please follow the accompanying documentation that comes with each of the above options to ensure that you properly install the ODBC package. Now that you have properly prepared your System, we are all on the same playing field.
Our next step in System preparation is adding a File DSN. Think of this as creating a conduit pipe between FileMaker Pro and any available ODBC Source. It is through this pipeline that we will pull data from FileMaker Pro and populate a Microsoft Excel Worksheet.
To set up a File DSN, double-click on the ODBC Control Panel. For Windows, this Control Panel is named ODBC (32-Bit). For Mac OS, this Control Panel is called ODBC Setup PPC. Once you have opened the ODBC Control Panel, there are a few steps you must perform, as follows:
1. Add File DSN -- Click on the File DSN Tab. Click Add to add a File DSN.
2. Create New Data Source -- From the Create New Data Source window, click once on "ODBC 3.11 FileMaker Pro PPC" and click on the Next button. You are now instructed to name your New Data Source. I have chosen the name "ODBC_Primer" for my pipeline. Click the Next button to continue.
3. Click Finish -- You are now given an opportunity to verify your options. Review the File Data Source configuration displayed. If you are satisfied, click the Finish button.
4. Verify File DSN -- You will notice that the File DSN that you have added is now listed in the File DSN list box. Click the OK button to exit the ODBC Control Panel.
To review, so far we have made our System ready for ODBC Connectivity by ensuring that the ODBC Control Panel is installed. We have then set up a File DSN which will act as a data conduit pipeline. We will use this File DSN later to pull data from FileMaker Pro into a Microsoft Excel Worksheet.
Now it is time to enable ODBC Connectivity in the ODBC_Primer.FP5 file that I have provided.
Setting Up FileMaker Pro for ODBC
Setting up your FileMaker Pro database for ODBC connectivity is a simple, two-step process. First, the Local Data Access Companion plug-in must be enabled. Second, each Database that you wish to access via ODBC must have the proper File Sharing option enabled.
When FileMaker Pro 5 is installed, the default setting is to have the Local Data Access Companion plug-in disabled. This is a precaution taken to ensure that your data is securely protected. Enabling the Data Access Companion plug-in for access by the FileMaker Pro client is an easy task; simply do the following:
First, choose menu command Edit:Preferences:Application, then click on the Plug-ins Tab. Next, click on the Check Box next to the Local Data Access Companion plug-in. (See Screen Shot titled, "1 FM App Prefs.jpg".) Finally, click on the OK button to exit the Preferences window.
When a FileMaker Pro database is created, the default setting is to have the Local Data Access Companion File Sharing option disabled. Again, this is a precaution taken to ensure that your data is securely protected. To enable this File Sharing option, do the following:
1. Open the enclosed ODBC_Primer.FP5 Technique File.
2. Choose the File:Sharing... menu command. (See Screen Shot titled, "2 FM File Sharing.jpg".)
3. Choose to enable the Local Data Access Companion by clicking on the Check Box next to the Local Data Access Companion option.
4. Click on the OK button to exit the Sharing... window.
If you have followed along step-by-step, we have now prepared our System for ODBC connectivity, created a File DSN, enabled the Local Data Access Companion plug-in for FileMaker Pro, and enabled Local Data Access Companion file sharing for the ODBC_Primer.FP5 database. Our next step is to actually pull the data contained in ODBC_Primer.FP5 into a Microsoft Excel Worksheet.
Pulling Data from FileMaker Pro into Microsoft Excel
I have populated ODBC_Primer.FP5 with 10 records. I will now show you how to pull these records from FileMaker Pro into Microsoft Excel. Do the following:
1. Launch Microsoft Excel.
2. Select the Add-Ins... command from the Tools menu.
3. Enable the ODBC Add-In by clicking on the Check Box next to ODBC Add-In. Click OK. (See Screen Shot "8 Add-ins.jpg".)
4. Select the Get External Data:Create New Query command from the Data menu.
5. In the Choose Data Source window that appears, choose the ODBC_Primer File DSN that we have previously setup. Click OK. (See Screen Shot "9 Choose Data Source.jpg".)
6. In the Query Wizard -- Choose Columns window, select the columns that you wish to import data from. You may double-click on the ODBC_Primer table to add all columns to your query. Click Next. (See Screen Shot "10 Choose Columns.jpg".)
7. In the Query Wizard -- Filter Data window, you may narrow your import by specifying a specific range to import. For instance, you may choose to import only those records which contain an Employee ID of greater than "5". For purposes of this demo, however, do not filter the data. Click Next. (See Screen Shot "11 Filter Data.jpg".)
8. In the Query Wizard -- Sort Order window, you may choose to sort your data either in ascending or descending order for each column that you have chosen to import. For this demonstration, I have chosen to sort in ascending order by Employee_ID. Click Next. (See Screen Shot "12 Sort Order.jpg".)
9. In the Query Wizard -- Finish window, click the Radio Button "Return Data to Microsoft Excel". This will return the imported data into Microsoft Excel, as opposed to keeping the data in Microsoft Query. Click OK. (See Screen Shot "13 Finish.jpg".)
10. In the Returning External Data to Microsoft Excel window, choose to put the data into the Existing worksheet in the "=$A$1" relative location. Click OK. (See Screen Shot "14 Return ED.jpg".)
If you have followed each of the above steps, you will now be looking at the imported data. There should be 10 records, sorted from Employee_ID "1" to Employee_ID "10". (See Screen Shot "15 Finished SS.jpg".)
Congratulations! You have now successfully pulled data from FileMaker Pro into an ODBC compliant application.
Conclusion
FileMaker Pro 5 can be used to effortlessly push and pull data from other ODBC compliant databases, provided that the necessary ODBC drivers are available for your platform.
While FileMaker Pro's inherent ODBC connectivity is not all-encompassing, there is something to remember: FileMaker Pro's implementation of ODBC is rather new. Considering how new this technology is to FileMaker Pro, FileMaker Pro's implementation is very easy-to-use and stable. FileMaker, Inc. has done a good job of providing ODBC resources to its loyal users. Check out FileMaker, Inc.'s ODBC White Paper at http://www.filemaker.com/products/odbc_backgrounder.html. Also, a lot is possible by using third-party plug-ins and ODBC Drivers. We have barely scratched the surface of the amazing possibilities of ODBC and FileMaker Pro!
In next month's article, we will revisit FileMaker Pro's ODBC capabilities, and expand on how to take advantage of third-party plug-ins and ODBC drivers. Also, I will show you step-by-step how to connect a FileMaker Pro database to a Microsoft SQL 7 server! In the meantime, become comfortable with FileMaker Pro's inherent ODBC capabilities. Practice pulling various types of data into Microsoft Excel from FileMaker Pro. If you feel comfortable exploring Microsoft Excel's Charting and Macro features, design a template that data from FileMaker Pro can be pulled into using ODBC and email the template to me at croberts@metrotechnologies.com. I will collect them all, and will report on the most innovative solutions in next month's issue. I will be sure to give you the credit you deserve!
Happy FileMaking!