How to create an ODBC DSN for FileMaker 7/8 - on MacOS X
Summary: This technical note describes the process for installing the downloadable FileMaker 7 ODBC driver for MacOS X along with the steps required for creating and testing the ODBC DSN. These instructions are written for a FileMaker Pro 7 installation, however the process is similar when using FileMaker Server 7 Advanced. [These instructions also apply to installing the ODBC driver for FileMaker Pro 8 and FileMaker Pro 8 Advanced. The ODBC driver is included on the FileMaker 8 installation CD.]
FileMaker Inc. has provided a downloadable DataDirect SequeLink ODBC driver for FileMaker Pro 7 for MacOS X. This driver is currently only available as a downloadable file from the FileMaker Updaters web page, because it is not currently included on the FileMaker 7 installation CD. This ODBC driver only allows an ODBC connection to the database from the localhost IP address - from the computer running the database.
There are two components which need to be downloaded from the FileMaker Updaters web page, after selecting FileMaker Pro 7 and Mac from the pull down menus (shown in Figure 1 and Figure 2):
Download and decompress the following files:
Install the DataDirect SequeLink JDBC Plugin by launching the sljcinstaller.jar file [see Figure 3].
Follow the prompts of the DataDirect JDBC Plugin Installer [see Figures 4 - 8].
Now that the DataDirect xDBC Plugin has been installed, it is necessary to install the FileMaker ODBC driver. There is no installer for the driver, it just needs to be dragged into the /Library/ODBC directory on your system boot drive, as shown in Figures 9 - 11.
After downloading, the FileMaker ODBC driver will be located within the ODBC Client Driver folder, within the FileMaker 7 ODBC Client Driver Mac OS X folder. The driver consists of one bundle file named SequeLink.bundle. This bundle should be copied to the /Library/ODBC directory [Note: You will need Administrative permissions to install this file on your computer.]
One additional software component from OpenLink Software needs to be installed at this time. The DataDirect FileMaker 7 ODBC driver for MacOS X requires additional software libraries to be installed in order to work correctly on MacOS X. These libraries are included within software shipped by OpenLink Software, and available as a free trial download from their website. The OpenLink Software Universal Data Access (UDA) ODBC-JDBC Lite Bridge driver software will be downloaded and installed as an example within these instructions. Even after the 30 day trial software has expired, the DataDirect FileMaker 7 ODBC driver will still continue to function correctly. If the OpenLink Software is not installed on MacOS X, the DataDirect driver will display the error message "Could not load the driver or translater setup library" whenever an attempt is made to create an ODBC DSN on MacOS X.
This installation process is shown in Figure 12 - Figure 27.
Open the OpenLink-JDBCLite.mpkg installer file to start the installation process.
As part of the download process, OpenLink Software will
send you an email with an attached jdbc_lt.lic license file.
This tech note utilizes a test FileMaker 7 database named fmpws_fmp01.fp7 which will be used for testing ODBC connectivity. In order to access a FileMaker 7 database via ODBC, the ODBC/JDBC Sharing Feature needs to be enabled for the FileMaker 7 database file. For testing purposes, launch the test FileMaker 7 database, then enable ODBC/JDBC sharing for All Users.
[Figure 28 - Enable FileMaker 7 ODBC/JDBC Sharing for All Users]
Open the OpenLink ODBC Administrator from the Applications/Utilities folder [see Figure 29] to create a new FileMaker 7 System DSN.
Before the new FileMaker 7 ODBC driver can be used, it needs to be added to the ODBC Administrator - this task only needs to be done once. This driver will be added as a System driver, so that System DSNs can be created. This is the preferred way to add the driver and ODBC DSNs in order for all applications on a computer to utilize the ODBC DSN. Click the ODBC Drivers tab, then click the Add a driver button.
Select the System radio button.
Enter the Description and the Driver file name for the
After Adding the FileMaker 7 ODBC driver, it now shows up in the list of drivers under the ODBC Drivers tab of the iODBC Data Source Administrator.
Now that the FileMaker 7 ODBC driver has been added, click the System DSN tab, then click the Add button to add a new ODBC System DSN for the FileMaker 7 test database file. Select the newly added FileMaker 7 ODBC driver, then click the Finish button.
Enter the name of the ODBC DSN in the first field. For
this tech note use: example_fmp_dsn.
For a production database, it is better to specify the exact database name in order to avoid accidentally accessing the wrong table or database file. Using the exact database name insures that duplicate database tables can be uniquely identified based upon the database file in which they are located.
There are two additional parameters which have been identified
by FileMaker Technical Support as currently unused parameters. These
two parameters are LoginID and Password. A future revision of the FileMaker
7 SequeLink ODBC driver is expected to utilize these additional parameters.
The lack of support for these parameters is not generally considered
a serious problem because most applications which utilize ODBC connectivity
will also give you the option to enter a Login Name and Password which
will then be passed on to the ODBC driver.
In order to verify that a connection can be made to the FileMaker 7 database, it is a good idea to test the ODBC connection before attempting to use the new ODBC DSN. OpenLink Software has provided a helpful Test button within their driver setup window which will enable testing of ODBC database connectivity.
Select the example_fmp_dsn, then click the Test button
shown in Figure 34 to verify that a connection can be made to the new
FileMaker 7 database.
[Figure 34 - Test the FileMaker 7 ODBC DSN - Click the Test Button]
The Connect dialog will contain default parameters from the ODBC DSN which is being tested, click the OK button to continue.
Enter Admin as the username, with no password, then click the OK button.
Click the OK button to close the test results dialog box.
Additional ODBC connectivity testing can be performed with the FmPro Worksheet utility. FmPro Worksheet is a free utility which sends SQL commands to FileMaker 7 databases via an ODBC connection. Results and error messages are returned within the program and can be saved to disk. All FileMaker 7 SQL commands are supported including CREATE TABLE, DROP TABLE, SELECT, INSERT, UPDATE and DELETE. Using FmPro Worksheet, it is also possible to create, save, and reload FileMaker 7 SQL commands as standard text files.
The default ODBC DSN listed within FmPro Worksheet is example_fmp_dsn, which matches the ODBC DSN created in this tech note. Therefore pressing the Execute SQL button within FmPro Worksheet will execute the default SQL code and create a new table named example within the fmpws_fmp01.fp7 FileMaker 7 database [See Figure 39 and 40]. There will be no results displayed in the Results field of FmPro Worksheet when a table is successfully created within FileMaker 7, but the new table will be visible within the Tables tab of the Define Database dialog.
Pressing the Execute SQL button a 2nd time will cause FileMaker ODBC Driver Error 12 "Duplicate Name" to be displayed because the table named example already exists in the database [see Figure 41]. This same error will also occur if the table exists only on the Relationship Graph. This is one reason why it is a good idea to delete a FileMaker 7 table from the database and the Relationship Graph at the same time.
If the FileMaker 7 Define Database/Fields window is open when creating a new database table, the result will be "Database Schema is Locked by Another User Error (303)".
First, open the MacOS X Console application (/Applications/Utilities/Console) to see if errors have been generated by FmPro Worksheet. An example error within the Console log may look like the following:
This error means that FmPro Worksheet is looking for the iODBC 3.51 framework but it was not found. This error could occur if the OpenLink Software Universal Data Access (UDA) ODBC-JDBC Lite Bridge driver software was not installed. This error also occurs after installing a later version of OpenLink Software with a higher version number than 3.51. To troubleshoot this problem further, use the MacOS X Terminal to verify which version of iODBC software was installed.
Using the Terminal, type the following:
it is likely that the following directory structure
will be displayed:
This means that the iODBC 3.51 framework won't be found by the FmPro Worksheet executable because the iODBC 3.52 framework has been installed. The solution to this problem is to create a symbolic link from the 3.52 framework to 3.51, which will then be found by FmPro Worksheet.
Type the following commands to create the symbolic link:
Now FmPro Worksheet should be able to find the iODBC 3.51 symbolic
link, which actually is mapped to the iODBC 3.52 framework as shown
in the following