8 Replies Latest reply on Apr 21, 2010 3:02 AM by marcherm

    How to setup Filemaker ODBC client connection on Mac?

    PeterGort

      Title

      How to setup Filemaker ODBC client connection on Mac?

      Post

      I'm trying to setup a Filemaker Pro 10 client to access a Filemaker Pro Server Advanced 10 database, via ODBC. (Why not just use the regular filemaker client to server connection? There's a very very good reason, but it's too long to go into here). The file has the [fmxdbc] extended privilege enabled, and is being served on a Filemaker Server Advanced server with ODBC/JDBC serving turned on.  Both Server and Client are Mac OS X 10.5.7.

      On the client machine I have installed the Sequelink.bundle file in the /Library/ODBC/ folder, and checked the permissions on the bundle, which are set to owner/group root/admin and read/write for owner and group and read only for others.  As per the fm10_odbc_jdbc_guide_en.pdf, page 13.

      I opened ODBC Administrator and attempted to create a system DSN, but Sequelink doesn't appear in the driver list.  So I went to the Drivers tab,  added and entry and called it "Filemaker" and specified the path /Library/ODBC/SequeLink.bundle/Contents/MacOS/ivslk20.dylib, again as per the fm10_odbc_jdbc_guide_en.pdf, page 13.  Unfortunately that document is NOT very clear on how to set up a mac ODBC client!  After I had created that driver entry, I was able to set up a DSN.  But, nowhere in this did it ask for IP address or any other connection details.  So I figured it must be in the keywords and values area of the DSN in ODBC Administrator.  Unfortunately if you add a keyword and value line, they are not editable in the ODBC Administrator!  Anyway, I figured out that drivers are specified in /Library/ODBC/odbcinst.ini, and DSNs are in the /Library/ODBC/odbc.ini file, so the next thing I had to find out what were the appropriate keywords.  The only thing I have been able to find that might help was page 17 of http://www.filemaker.com/downloads/documentation/fm8_odbc_jdbc_developer.pdf

      So I edited my odbc.ini file to look like this:

       

      [MyMacbook]

      Driver           = /Library/ODBC/SequeLink.bundle/Contents/MacOS/ivslk20.dylib

      Description      = 

      Host             = 10.0.0.101

      Port             = 2399

      ServerDataSource = ODBCTesting

       

      Now when I open ODBC Administrator and look at the DSN "MyMacbook" I see Host, Port, and ServerDataSource as the keywords and the values are there too.  I thought I was making progress. However when I try running iodbctest in the terminal, I get this response:

       

      1: SQLDriverConnect = [DataDirect][ODBC SequeLink driver]Error parsing connect string at offset 9.  (2704) SQLSTATE=HY000

      1: ODBC_Connect = [DataDirect][ODBC SequeLink driver]Error parsing connect string at offset 9.  (2704) SQLSTATE=HY000

       

      I'm lost.  The fm10_odbc_jdbc_guide_en.pdf is really really vague here, from which I construe that no-one at Filemaker actually tried it... or could figure it out.  Anybody got any ideas?

        • 1. Re: How to setup Filemaker ODBC client connection on Mac?
          TSGal

          theGrot:

           

          Thank you for your post.

           

          One of our Support Technicians tried this more than a year ago with FileMaker Pro 9 and FileMaker Server 9, and he was unable to get this to work.  According to him, it wasn't set up to work within FileMaker.  We have not tried this with FileMaker Pro 10 and FileMaker Server 10.

           

          You should not have to edit the odbc.ini file.

           

          You said, "Unfortunately, if you add a keyword and value line, they are not editable in the ODBC Administrator!".  That is not true.  You need to click the lock to make changes.

           

          For the Macintosh, download the iODBC Administrator at:

           

          http://download.cnet.com/iODBC-Driver-Manager-Administrator-SDK/3000-2070_4-51225.html

           

          After installation, you should find it:

           

          Hard Drive -> Applications -> iODBC (folder)

           

          Assuming your SequeLink.bundle is in the appropriate location (Hard Drive -> Library -> ODBC), create a new database file, "Test.fp7" with one field (Name) and one record (TSGal).  Pull down the File menu and select "Sharing -> ODBC/JDBC...".  Turn on ODBC Sharing, select Test.fp7, and set ODBC/JDBC access to "All Users".

           

          Launch iODBC Administrator and select the tab "ODBC Drivers".  Click "Add a driver", and enter the following information:

           

          Description of the driver: myODBCdriver

           

          Driver file name: /Library/ODBC/SequeLink.bundle/Contents/MacOS/ivslk20.dylib

          (Note: Be sure to manually type this.  Do not paste! )

           

          Click the System DSN tab, click the "Add" button, and enter the following information:

           

          Data Source Name (DSN): myTESTDB1

           

          a. Keyword: Host

             Value: 127.0.0.1

             (Click Add)

           

          b. Keyword: Port

             Value: 2399

             (Click Add)

           

          c. Keyword: ServerDataSource

             Value: Test

             (Click Add)

           

          Click "OK".

           

          You should now be set up.  Click the "Test" button.  This should return everything was successful.  If not, then let me know what error message you get and we'll proceed from there.

           

          We also have a Knowledge Base Article (6581) with some additional troubleshooting steps:

           

          http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=6581 

           

          TSGal

          FileMaker, Inc.

          • 2. Re: How to setup Filemaker ODBC client connection on Mac?
            PeterGort
              

            I followed these instructions, and set up two DSNs, one pointing to the locally shared via Filemaker test file, one pointing to the remote Filemaker Server.  Unfortunately, neither worked.  When I used the "test" funtion on the localhost DSN I got "HY000 [DataDirect][ODBC SequeLink driver][SequeLink Server]Authorisation failure".  When testing the remote one, I got "HY000 [DataDirect][ODBC SequeLink driver][SequeLink Client]TCP/IP error, connection refused".  iodbctest for localhost DSN returns 

            **

            1: SQLDriverConnect = [DataDirect][ODBC SequeLink driver]Error parsing connect string at offset 18.  (2704) SQLSTATE=HY000

            1: ODBC_Connect = [DataDirect][ODBC SequeLink driver]Error parsing connect string at offset 18.  (2704) SQLSTATE=HY000 

            ** 

             

            for the remote connection it returns  

            1: SQLDriverConnect = [DataDirect][ODBC SequeLink driver]Error parsing connect string at offset 9.  (2704) SQLSTATE=HY000

            1: ODBC_Connect = [DataDirect][ODBC SequeLink driver]Error parsing connect string at offset 9.  (2704) SQLSTATE=HY000

            **

             

            Any further ideas? 

            • 3. Re: How to setup Filemaker ODBC client connection on Mac?
              TSGal

              the Grot:

               

              I'm not sure what else to try.  I made sure i didn't leave out any step, and I followed along to make sure it worked correctly.  My test works fine, so let's assume something is wrong with either FileMaker Pro or the SequeLink driver.

               

              I uninstalled and reinstalled FileMaker Pro 10 Advanced on my Mac running 10.5.7, just to make sure I had a fresh copy.

               

              From the FileMaker Pro 10 Advanced CD, I opened the xDBC directory and copied the SequeLink.bundle into the Hard Drive -> Library -> ODBC (folder)

               

              I launched FileMaker Pro 10 Advanced, opened up a Test.fp7 file and made sure ODBC Sharing was turned on and access was set to all users.

               

              I launched iODBC Administrator and selected the tab "ODBC Drivers".  I then selected "Add a driver", and followed the rest of the instructions above.  My test was successful and I did not encounter any problems.

               

              If you are unable to test successfully on either computer, and Knowledge Base Article #6581 is of no help, then I'll need more information about your machine and what other applications are running.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: How to setup Filemaker ODBC client connection on Mac?
                PeterGort
                  

                OK, well the knowledge that someone else has got it to work now, means it must be something specific to my setup that is is causing my problem. 

                 

                Thank you for filling in the missing bits from the documentation, and can I humbly suggest that Filemaker update the knowledgebase with the specific step by step guidance, particularly including the creating of a driver, and the keywords necessary.  Those two items are not mentioned in either the documentation with the shipping product or in the knowledge base at the present time.

                 

                Meanwhile, I'll try wiping one of the machines and building it up from scratch.  Be next week or the week after before I get the chance to do that. 

                • 5. Re: How to setup Filemaker ODBC client connection on Mac?
                  hiatts
                    

                  Thought I'd add my 2c...

                   

                  Yes it seems trying to use FMP to access another FMP DB via ODBC does not work. Seems FMP wants you to connect to FMP as FMP.

                   

                  That said, ODBC is working a treat on Mac for Excel connectivity to FMP DBs. 

                  • 6. Re: How to setup Filemaker ODBC client connection on Mac?
                    PeterGort
                       Unfortunately, Filemaker Server cannot import from a Filemaker file or data source, only from local text files.  So my possibilities for rolling my own data sync (running as server side processes) between two Filemaker Servers are ODBC and XML, or setting up a dedicated robot machine.  So I'm using ODBC at the present time.  This works a treat in Windows environment, I'm just having enormous troubles getting it going in Mac environment.
                    • 7. Re: How to setup Filemaker ODBC client connection on Mac?
                      drb56
                        

                      I just spent a couple days figuring out how to get FileMaker Pro 10 to act as an ODBC data source on Mac OS 10.5.  I'm accessing it via a perl script using the DBI and DBD::ODBC perl modules, but I believe what I found relates to this forum thread.  I followed the documentation like others and kept getting connection errors (failure to find the driver and/or DSN).  Finally I changed my connection string to be a "DSN-less" one and it worked:


                       my $dbh = DBI->connect('dbi:ODBC:Driver=fmODBCdriver;Host=127.0.0.1;Port=2399', 'Guest', '');

                      ('fmODBCdriver' is what I named the driver.)  It appears that the DataDirect SequeLink driver now ignores the /Library/ODBC.ini file and wants the host and port (and driver?) information passed in the connection string.  This is actually nice if it works, I think, because you don't need a separate DSN for each FileMaker database file.  However I think you need to be able to get your client to pass a connection string like this, which apparently FileMaker as a client does not do (or did not do it for the original poster).  Also, nowhere in the FileMaker documentation is one told to do this, which as I say cost me a couple days of searching and experimenting.  So I definitely think FileMaker, Inc. should address these issues.  Finally, note that I still can't get iodbctest in Terminal to connect to FileMaker at all:

                       

                       iodbctest "DSN=CustomersDB"
                      iODBC Demonstration program
                      This program shows an interactive SQL processor
                      Driver Manager: 03.52.0406.1211
                      1: SQLDriverConnect = [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded (0) SQLSTATE=IM002
                      1: ODBC_Connect = [iODBC][Driver Manager]Data source name not found and no default driver specified. Driver could not be loaded (0) SQLSTATE=IM002

                       

                      iodbctest "DRIVER=fmODBCdriver;DSN=CustomersDB;UID=Guest;PWD=;HOST=127.0.0.1;PORT=2399"
                      iODBC Demonstration program
                      This program shows an interactive SQL processor
                      Driver Manager: 03.52.0406.1211
                      1: SQLDriverConnect = [DataDirect][ODBC SequeLink driver]Connect dialogs are not supported. All required parameters must be specified. (0) SQLSTATE=HY000
                      1: ODBC_Connect = [DataDirect][ODBC SequeLink driver]Connect dialogs are not supported. All required parameters must be specified. (0) SQLSTATE=HY000
                       





                      • 8. Re: How to setup Filemaker ODBC client connection on Mac?
                        marcherm

                        Thought I'd add my 1c...

                         

                        (Mac OS X 10.5) The value for the ServerDataSource key is case-sensitive and things like spaces should be escaped (%20).