Importing from an ODBC Data Source

In addition to importing data from resources on a local disk drive, such as individual flat files or folders of text or image files, FileMaker Pro can also access data sources that may be visible only over a network of some kind. One such type of remote data source is represented by remote databases, which FileMaker can access using a widespread technology known as Open Database Connectivity (ODBC).

How ODBC Works

A full discussion of ODBC technologies is beyond the scope of this book. We'll have to be content with a fairly thorough overview.

Many database configurations are referred to as client-server configurations. Numerous clients (usually individual workstations) somehow connect to a database housed on a single master server (usually a powerful, centrally located computer). Each client interacts directly with the server to request data, or to submit changes to the database. Because there are ostensibly only two layers in this architecture (the client and the server), this kind of setup is also called a two-tier architecture.

FileMaker Pro, you may be aware, when coupled with FileMaker Server, represents a classic two-tier, clientserver architecture. FileMaker Pro is the client, of course, and FileMaker Server is, well, the server. FileMaker Pro and FileMaker Server communicate via a special FileMaker network protocol that isnt shared or understood by other applications. Figure 19.16 illustrates the FileMaker clientimages/U2260.jpg border=0>server architecture.

Figure 19.16. A sketch of FileMaker's two-tier clientimages/U2260.jpg border=0>server architecture.

There are, of course, many other kinds of relational database servers in existence. Like FileMaker Server, each other product communicates using its own specialized protocol. But to overcome all this rampant disparity, a number of database and software vendors worked together to develop some more standard protocols for accessing database servers, so that rather than access databases a dozen different ways, clients can rely on a more consistent access method.

The result has been the ODBC standard. With ODBC, rather than having a client and a server communicate via a specific vendor protocol, the two sides can agree to communicate via ODBC instead. In this way, a client application that can use ODBC (such as FileMaker), can potentially communicate with any of the dozens of database products that use ODBC as well. Figure 19.17 illustrates the notion that a single client can communicate with many servers.

Figure 19.17. Using ODBC drivers and protocols, a single client application can communicate with many different types of ODBC-enabled servers.

The ODBC standard is generally used with database servers that use the SQL (Structured Query Language) database language, but some non-SQL database servers (such as FileMaker Server, for instance) can also be accessed via ODBC.

This chapter covers techniques for bringing data into FileMaker via ODBC. Chapter 20, "Exporting Data from FileMaker," p. 595, covers how it's possible to extract data from FileMaker via ODBC.

 

Installing ODBC

For a client and server to be able to communicate via ODBC, both sides need some special preparation. Many database servers can communicate via ODBC with no additional configuration. Some may require that special modules be added or certain settings be configured. For the purposes of this chapter we're going to assume that you need access to a server that has already been correctly configured for ODBC.

To participate in an ODBC communication, the client side must also be configured correctly. Generally, an individual client machine needs to have a special driver installed to allow it to communicate with a particular kind of database server. The driver is a piece of software, installed via the client's operating system, that knows how to talk to a particular kind of data source.

For example, if you want ODBC access to an Informix database from a client machine running Windows 2000, two things need to happen. First, the Informix server needs to be configured for ODBC access. Second, you need an Informix ODBC driver that works under Windows 2000 to be installed on the client. If you want to perform similar access from a Mac OS X client, you need a separate Informix ODBC driver compatible with Mac OS X. ODBC drivers are not generally transferable, either across database servers or across operating systems: You need a driver that's specific both to the database you want to access and to the operating system you're working from.

Caution

One of FileMaker's most powerful appeals is that it's fully cross-platform (well, as long as the platforms under consideration are Mac or Windows). ODBC connectivity, by contrast, does not have as gleaming a record in the cross-platform arena. The Mac has historically lagged behind Windows in terms of ODBC support; it often used to take a long time, if ever, for the Mac to be able to use the latest ODBC drivers for a given database. The drivers for a version 8 of a certain database might appear on Windows, and not be followed by version 8 Mac drivers for months, or longer.

With the advent of Mac OS X, especially version 10.2 ("Jaguar"), the ODBC connectivity picture for the Mac is much improved. Mac OS X is of course a flavor of UNIX under the hood, opening the path for the porting and adoption of a wide range of UNIX-based ODBC tools.

The bottom line, though, is still buyer beware. If you want or need to delve into ODBC, be aware that there remain differences in the nature and extent of ODBC support on the different FileMaker target platforms.

Creating a DSN

After your ODBC drivers are installed, the next step is to create one or more DSNs (Data Source Names) on the client computer(s). This is also somewhat beyond the scope of a book on FileMaker, but we'll touch on some of the major points.

Note

The exact mechanics of installing ODBC drivers vary depending on the particular drivers being installed and on the target operating system. Going into great detail on driver installation is outside the scope of this book. We're going to assume that your FileMaker client machine or machines have ODBC drivers installed on them for the databases or data sources you want to access via ODBC.

Both the Mac OS X (version 10.2 and greater) and Windows operating systems have built-in tools for working with ODBC drivers and DSNs. On the Mac, Apple ships a tool called ODBC Administrator, which can be found in the Application, Utilities folder. On Windows, there's a shortcut to the ODBC Data Administrator located in Start, Settings, Control Panel, Administrative Tools, Data Sources (ODBC).

To create a DSN to access a particular ODBC-based data source, you need to have a driver for that particular data source installed on the computer you're working from. Figure 19.18 shows the Mac and Figure 19.19 shows the Windows ODBC tools with a list of available drivers. Windows systems tend to have a great many ODBC drivers installed by default. Mac OS X, by contrast, doesn't install any ODBC drivers by default.

Figure 19.18. The Mac OS X ODBC Administrator.

Figure 19.19. The Windows 2000 ODBC Data Source Administrator.

You need to create a DSN that uses the appropriate driver for the data source you're trying to work with. You probably also need to do some configuration of the DSN. Depending on the type of data source you're using, the DSN configuration can vary widely. To access a SQL database, for example, you'd generally need to configure the DSN with the hostname or IP address of the database server, a username, a password, and possibly the name of a specific SQL database. We show a sample DSN configuration screen in Figure 19.20, but in general, each DSN needs to be configured differently, and we can't give any general guidelines in a book of this nature. You'll need to consult the documentation for the particular ODBC driver you're using, and you may need to enlist the aid of someone, such as a database administrator, who's aware of the configuration settings for the data source you're trying to access.

Figure 19.20. Defining a DSN for the PostgreSQL open source database in Mac OS X.

Note

You have a choice between creating a User DSN and creating a System DSN. These two kinds of DSNs differ only in who can access them: System DSNs are meant to be accessible to all users on a system, whereas a user DSN is meant to be accessible only to the user that created it. FileMaker can work with either type, though it respects the access restrictions: You'll be able to access a user DSN from FileMaker only if the DSN is accessible to you.

 

Performing the ODBC Import

After you have a working DSN for the ODBC data source you want to access, importing from the data source is simply a matter of selecting File, Import Records, ODBC Data Source. You'll see a list of available DSNs, as shown in Figure 19.21.

Figure 19.21. When you begin an import from an ODBC data source, FileMaker prompts you to choose an ODBC DSN.

Here, two DSNs are available: a user DSN and a system DSN, both of which make connections to a PostgreSQL database. (PostgreSQL is an extremely powerful open source SQL database.)

If the data source to which you're connecting requires authentication, you might see a password prompt like the one shown in Figure 19.22.

Figure 19.22. Some ODBC data sources may prompt you for authentication.

After you pass the authentication prompt, you'll likely see some additional dialog boxes, depending on the type of ODBC data source you're accessing. For SQL-based data sources, for example, you'll likely see a dialog that helps you formulate a SQL query, such as the one shown in Figure 19.23.

Figure 19.23. When importing from a SQL-based ODBC data source, you'll probably need to formulate a SQL query via a wizard like this one.

Here we're fetching data from a table called city in a remote PostgreSQL database. The SQL query instructs the ODBC driver to fetch all fields from the city table.

After this query is run, you'll be confronted with FileMaker's Import Field Mappings dialog, with which we've dealt extensively in this chapter. The dialog lets you map the fields from the SQL data source onto the fields in the FileMaker table.

Again, the options for ODBC import can vary widely depending on the particular driver and data source you're using. It's a bit beyond the scope of this book to provide a full overview of ODBC, SQL, or particular drivers. You may need to experiment a bit, or consult with the oft-mentioned local systems expert (in case it isn't you!) to arrive at the correct configuration for importing ODBC data.