.com Solutions Inc. - Support - FmPro Migrator

FmPro Migrator Demo Available for download...

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):

[Figure 1 - Select MacOS X Updates on FileMaker Updates Web Page]

Figure 2 - xDBC Plug-In and ODBC Client Driver Download Files
[Figure 2 - xDBC Plugin and ODBC Client Driver Download Files as Listed on Updates Web Page]

Download and decompress the following files:
FileMaker Pro 7 and FileMaker Developer 7 xDBC Plugin and JDBC Client Driver MacOS X - The xDBC Plugin provides a programming framework for both JDBC and ODBC Client Drivers within FileMaker 7.
FileMaker Pro 7 and FileMaker Developer 7 ODBC Client Driver MacOS X - The ODBC Client Driver utilizes the xDBC Plugin to provide ODBC connectivity to FileMaker 7 databases.

Install the DataDirect SequeLink JDBC Plugin by launching the sljcinstaller.jar file [see Figure 3].

[Figure 3 - DataDirect JDBC Plugin Installer]

Follow the prompts of the DataDirect JDBC Plugin Installer [see Figures 4 - 8].

[Figure 4 - DataDirect JDBC Plugin Installer - Click the Next Button]

[Figure 5 - Accept License Agreement - Click the Next Button]

[Figure 6 - Select FileMaker 7 Application Directory - Click the Next Button]
[Figure 6 - Select /Library/Java/Extensions Directory - Click the Next Button]

[Figure 7 - Click the Install Button]

[Figure 8 - Click the Finish Button]

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.

[Figure 9 - SequeLink Bundle File - After Downloading]

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.]

[Figure 10 - /Library Directory in MacOS X]

[Figure 11 - /Library/ODBC Directory - After Installing SequeLink.bundle]

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.

[Figure 12 - Click "Download UDA" on Openlinksw.com Home Page]

[Figure 13 - Click the Continue Button]

[Figure 14 - Click the Single-Tier Edition Link]

[Figure 15 - Click the Radio Button for the Latest Release of Single-Tier Edition Software, then Click the Continue Button]

[Figure 16 - Click the Radio Button for ODBC, Select MacOS X Version Menu, Then Click the Check Availability Button]

[Figure 17 - Click the Checkbox for ODBC-JDBC Lite Bridge, Then Click the Proceed to Software Download Button]

[Figure 18 - Click the Download Link Closest to Your Location]

Open the OpenLink-JDBCLite.mpkg installer file to start the installation process.

[Figure 19 - Click the Continue Button]

[Figure 20 - Click the Continue Button]

[Figure 21 - Click the Continue Button]

[Figure 22 - Click the Continue Button]

[Figure 23 - Click the Agree Button for the License Agreement]

[Figure 24 - Click the Destination Volume then Click the Continue Button]

As part of the download process, OpenLink Software will send you an email with an attached jdbc_lt.lic license file.
This license file will be required during the software install process and should be installed within the /Library/Application Support/openlink/bin folder. This directory won't exist until the installer dialog prompts you to select the license file, as shown in Figure 25.

[Figure 25 - Click the Ok Button - Then Select the jdbc_lt.lic File]

[Figure 26 - Click the Cancel Button]

[Figure 27 - Click the Close Button]

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.

[Figure 29 - Launch OpenLink ODBC Administrator]

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.

[Figure 30 - Click Add a Driver Button]

Select the System radio button.

Enter the Description and the Driver file name for the driver:
Description of the driver: FileMaker 7 ODBC
Driver file name: /Library/ODBC/SequeLink.bundle/Contents/MacOS/ivslk18.dylib
Note: It is not possible to use the Browse button to select the ivslk18.dylib file, this full pathname needs to be entered manually. You can copy and paste the path directly from this web page.
There is no info required for the Setup file name and there are no keyword/value pairs required when adding the driver.
Click the OK button.

[Figure 30 - Select System as Driver Type, Enter Driver Parameters, then Click OK Button]

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.

[Figure 31 - FileMaker 7 ODBC Driver Listed in ODBC Drivers Tab After Installation]

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.

[Figure 32 - Select FileMaker 7 ODBC Driver]
[Figure 32 - Select the FileMaker 7 ODBC Driver]

Enter the name of the ODBC DSN in the first field. For this tech note use: example_fmp_dsn.
Enter information for each of the following parameters:

FileMaker 7 ODBC Parameters
Keyword	Value
Host	127.0.0.1
Port	2399
ServerDataSource	Default or fmpws_fmp01
ServerDataName	Default or fmpws_fmp01

For each parameter, enter the Keyword name exactly as it is listed here, enter the value then click on the Add button to the right of the Keyword field. Either enter the text Default or the FileMaker 7 database filename for the ServerDataSource and ServerDataName (without the .fp7 file extension). Using the Value Default is an undocumented technique which can be easier to use while developing FileMaker databases. The Default value enables developers to easily switch among different database files simply by launching a new file within FileMaker 7 and avoids the requirement to update the ODBC parameters in the 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.
Note: The operational features of the FileMaker 7 ODBC Driver are very different from previously shipped FileMaker ODBC drivers. The DataDirect SequeLink FileMaker driver included with FileMaker Pro 7 only permits ODBC connections to be made to a local FileMaker 7 database running on the same computer. This is why the SequeLink Server Host field must be filled in with the TCP/IP address of 127.0.0.1 or the hostname "localhost". The feature of allowing external computers to make an ODBC connection to a FileMaker 7 database is reserved for the FileMaker Server 7 Advanced product.

[Figure 33 - Enter Parameters for the ODBC System DSN]

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.

[Figure 35 - Click Ok to Default Values]
[Figure 35 - Click OK to Use Default Values]

Enter Admin as the username, with no password, then click the OK button.

[Figure 36 - Enter FileMaker 7 Admin Account Name, Click OK Button]

Click the OK button to close the test results dialog box.

[Figure 37 - Successful Test Dialog]

Note: If the connection test fails, verify that ODBC/JDBC sharing is turned on and that an older version of FileMaker is not running with the Local or Remote Data Access Companion plug-ins enabled. The HY000 error shown in Figure 38 resulted from having FileMaker 6 and FileMaker 7 ODBC sharing enabled at the same time, thus causing a conflict between the two database applications.

[Figure 38 - HY000 Error Dialog - Due to Sharing Conflict]

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.

[Figure 39 - Creating New Table With FmPro Worksheet]

[Figure 40 - New Table Shown in FileMaker 7]

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.

[Figure 41 - Duplicate Name Error (12) Shown in FmPro Worksheet]

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)".

[Figure 42 - Database Schema is Locked by Another User Error (303) Shown in FmPro Worksheet]

If FmPro Worksheet displays the "Can't open database revdberr, invalid database type" error shown in Figure 43, then use the following troubleshooting instructions:

[Figure 43 - FmPro Worksheet revdberr, invalid database Error Dialog ]

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:

2005-08-20 10:32:54.968 FmPro Worksheet 1.02[2853] CFLog (21): Error loading /Users/<loginaccount>/fmpro_worksheet/FmPro Worksheet 1.02.app/Contents/MacOS/externals/database_drivers/dbodbc/Contents/MacOS/dbodbc: error code 4, error number 0 (Library not loaded: /Library/Frameworks/iODBC.framework/Versions/3.51/iODBC

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:

cd /Library/Frameworks/iODBC.framework/Versions ls -l

it is likely that the following directory structure will be displayed:

drwxrwxr-x 5 root admin 170 Oct 3 2004 3.52 lrwxr-xr-x 1 root admin 4 Aug 19 16:30 Current -> 3.52 $loginaccount /Library/Frameworks/iODBC.framework/Versions

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:

sudo ln -s 3.52 3.51

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 /Library/Frameworks/iODBC.framework/Versions directory listing:

lrwxr-xr-x 1 root admin 4 Aug 20 10:58 3.51 -> 3.52 drwxrwxr-x 5 root admin 170 Oct 3 2004 3.52 lrwxr-xr-x 1 root admin 4 Aug 19 16:30 Current -> 3.52 $loginaccount /Library/Frameworks/iODBC.framework/Versions