Connect to Filemaker with ODBC from the Mac

Introduction

This article explains how to connect to Filemaker Pro with ODBC from the Mac.

It covers the following topics:

  1. How to connect to Filemaker Pro (with code samples).
  2. Install unixODBC:

    This is used to manage ODBC on the Mac. The unixODBC install also include utilities isql and iusql.

  3. Configure ODBC using the odbc.ini file:

    We recommend using system DSNs (available to all users), which use the /usr/odbc.ini file. User DSNs can be setup in the $HOME/.odbc.ini file.

Connect to Filemaker Pro [top]

We strongly recommend using connection objects to connect to databases, and this is what we demonstrate in the code samples.

Follow these steps to connect:

  1. Create a new channel to try out the code:
    • Create a channel like this:
      • Name: DB Filemaker Connection (or similar)
      • Source: From Translator
      • Destination: To Channel
    • Alternatively use an existing channel:

      Your channel will need a From/To Translator or a Filter component.

  2. Connect to a database using ODBC:
    • Paste this code into the Translator:

      If your script uses connection object methods conn:query{} or conn:execute{}to connect to a remote Microsoft SQL Server database, the location of the remote host is specified as part of the name parameter in the db.connect{ } command. If you use conn:merge{} to connect the remote host is specified as part of the name parameter in the conn:merge{} command itself.

      function main()
            local conn = db.connect{   
            api=db.FILEMAKER, 
            name='your_odbc_server_name',
            user='',
            password='',
            use_unicode = true,
            live = true
         }
      
         conn:execute{sql='SELECT * FROM <your table>', live=true}
      end
    • The name is the name of an ODBC source that you create using the ODBC Administrator.
  3. Adapt the code to your requirements.

Setup ODBC [top]

To set up a new ODBC data source for Filemaker Pro:

  1. Install the latest xDBC update for FileMaker Platform:

    This contains the ODBC (and JDBC) drivers for Filemaker Pro.

    • Download the latest dmg install file from Filemaker Downloads.
    • Open the dmg file.
    • Then open the ODBC Client Driver Installer folder:
      filemake dmg
    • Run the FileMaker ODBC package to install the drivers:
      run filemaker odbc installer
    • The Filemaker driver bundle is installed here:
      filemaker driver bundle
    • And this is the driver inside the bundle:
      filemaker driver bundle
  2. Setup the unixODBC driver manager:

    We need to use unixODBC because the default Apple driver manager does not work with some databases, see Setup unixODBC on the Mac for more information.

  3. Enable ODBC sharing for your database:

    This may already have been done for you by your DBA — but this is how to do it if you need to.

    1. Run Filemaker.
    2. Go to the menu File>Sharing>Enable ODBC/JDBC.
    3. Choose the database from Currently open files.

      In this example there is only one database file called Contacts — usually there will be many files.

    4. Check On for ODBC/JDBC Sharing.
    5. Select who is allowed ODBC/JDBC access:

      I chose All users because it is the simplest option — you can also use Specify users by privilege set to control who is allowed access.

    6. Click OK to allow ODBC access.
      filemaker enable odbc access
  4. Create the odbc.ini file entries:

    Be aware that the ODBC parameters in odbc.ini entries can vary for different databases. In particular MySQL and Microsoft SQL Server use Server to identify the server name, whereas some other databases use Servername (for example Oracle and PostgreSQL). It is important to use the correct parameters for your database or the ODBC connection will not work correctly.

    1. Format your entries like this:
      • Bare minimum UNICODE driver (libmyodbc8w.so):
        Note: Change [My Filemaker Connection name] to the name you prefer for your ODBC connection.

        [My Filemaker Connection name] 
        Driver = /Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so
        Server = your_server_name 
        
      • With user details etc:
        Note: The <comments> in angle brackets should not be included.

        [My Filemaker Connection name] 
        Driver   = /Library/ODBC/FileMaker ODBC.bundle/Contents/MacOS/fmodbc.so
        Server   = your_server_name <network name or IP address for the server> 
        
        Database = dbname <if omitted the default database will be used> 
        Username = dbusername <if omitted then integrated security is used - needs kerberos> 
        Password = dbpassword
  5. Check the location of ODBC ini files on your system:
    1. Terminal Command: Check the locations:
      odbcinst -j
    2. In this case the ODBC ini files are in the default locations:
      • System ODBC ini file: /usr/local/etc/odbc.ini
      • User ODBC ini file: is in the user home directory (/Users/julianmuir/.odbc.ini):
        odbcinst -j
  6. Append your entries to the ODBC ini file:

    We generally recommend creating System DSNs (available to all users) rather than User DSNs (only available to the current user).

    1. Create a System DSN:
      1. Append your ODBC entry to the System ini file you identified in step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -l -f template_file

    2. Create a User DSN:
      1. Append your ODBC entry to the User ini file you identified step (5)
      2. Open the file with a text editor and paste your entry at the end.
      3. Alternatively: Use the unixODBC odbcinst utility to append your entry:
        1. Save your entry to a text file (unixODBC calls this as a “template file”)
        2. Terminal Command: Append the entry from your template file:

          odbcinst -i -d -h -f template_file

    3. An ODBC ini file will look something like this after a new entry is added:
      oddb.ini new entry
    4. If you have problems configuring the ODBC driver or connection speak to your DBA (database administrator).

    Troubleshooting [top]

    • Problems with connection settings:

      Speak to your DBA (database administrator) for general connection issues like: User name and password, Database Server network name etc.

    If you need more help please contact us at support@interfaceware.com.

     

    More Information [top]

Tagged:

Leave A Comment?