1 2 Previous Next 17 Replies Latest reply on Dec 14, 2012 8:35 AM by BenHutson

    FM12 connecting via ODBC

    BenHutson

      Hi All,

       

      Before converting our current solution to FM12 and comitting to that, we are running numerous tests with our solution to see what differences we can expect and pre-empt.

       

      After getting back from DevCon in July I converted the solution and had found few to no problems, testing across different platforms and tasks.

       

      However, after coming back to it yesterday I now seem to have a number of problems. These issues may relate mostly to hardware rather than software, apart from one, which is my main issue. Whenever I try and view the MySQL tables through FileMaker now, it freezes up. It refuses to even load the layout in Find mode or Browse mode. Also when going to Manage Database and selecting the external tables, the same thing happens - FM12 just freezes up.

       

      I've tried this on Mac, and PC with the same results. When this happens, if I leave it to sit, the FM12SA actually crashes too.

       

      This wasn't an issue before - I've tried re-installing FMSA, FPA, restarting machines, all to no avail.

       

      Not sure if it's related to the password / username dialog box, which at the moment I am not even getting that for the external tables. I did see it yesterday toward the start of my testing, and clicked cancel because I didn't have the access details handy. After that I saw it again and tried connecting to one of the ODBC connections with the correct details but was getting too many hosts connected and needed to run mysql hosts flush. Which I should be able to do today, but I haven't seen a password / username dialog box for the online tables since then.

       

      Has anyone else experienced of this type of problem?

       

      Appreciate any feedback.

       

      Best Regards

       

      Ben

        • 1. Re: FM12 connecting via ODBC
          PSI

          Hi Ben,

           

          Not sure if this will help but if you are running FM Server 12 on a 64 bit OS it needs the mySQL 5.1.7 64-bit driver. If you are on windows 7 and maybe XP you need the 32 bit version

           

          John Morina

          Pueblo Systems

          • 2. Re: FM12 connecting via ODBC
            BenHutson

            Hi John,

             

            It's a good point you make. Running FMSA12 on a 32bit machine so all mySQL drivers are 32 bit.

             

            I got caught out on that one before, but the other way round, which is actually easier to get caught out on. 64bit operating systems show the 64bit version external data sources manager in the Start menu. You have to go and hunt for the 32bit version. So if you installed a 32bit app on a 64bit platform and you go to the default menu item on the server to manage the external data source, which doesn't tell you it's the 64bit, and add your connections in there, then you find your ODBC connections don't work in your app...!

             

            Thanks for the feedback, I will be able to try the Flush hosts in a few weeks (testing at weekend's only). So we'll see if that's the issue, which i'm really hoping it is, if not, there's trouble on the horizon.

             

            Best Regards

             

            Ben

            • 3. Re: FM12 connecting via ODBC
              ch0c0halic

              It's been my experience that this is the result of an out-of-date driver. We use the latest Actual ODBC driver's.


              http://www.actualtech.com


               

              You might want to check the FMI Knowledge base for supported drivers.


              Which ODBC data sources/drivers are supported with External SQL Data Sources?

              Which ODBC drivers are supported with External SQL Data Sources?

              Answer ID: 6420

              • 4. Re: FM12 connecting via ODBC
                BenHutson

                Hi ch0c0halic

                 

                Good info, thank you, this will be the first thing I try.

                 

                At the moment we are utlising 5.1.11 on a Windows platform. I will uninstall and try the 5.1.7 first.

                 

                Once I have the results I will post back, which will be a couple of weeks from now.

                 

                Thanks again.

                 

                Best Regards

                 

                Ben

                • 5. Re: FM12 connecting via ODBC
                  steve.winter

                  Hi Ben

                   

                  My 2c worth is the same - use the ActualTech MySQL drivers and your problems will go away - anything else has proven to be somewhat unreliable...

                   

                  Cheers

                  Steve

                  • 6. Re: FM12 connecting via ODBC
                    BenHutson

                    Hi Steve

                     

                    Thanks, I had a look at the ActualTech MySQL drivers - looks like they are Mac only?

                     

                    We are using Windows Server for FileMaker Server.

                     

                    Best Regards

                     

                    Ben

                    • 7. Re: FM12 connecting via ODBC
                      steve.winter

                      Hi Ben

                       

                      My bad... didn't realise it was a windows setup - my issues were as you describe, but on OS X, and the Actual drivers resolved things there.

                       

                      Looking at the KB article which ch0c0halic metions above, it seems that the MySQL Connector/ODBC version 5.1.7 (32-bit and *64-bit) drivers should be the answer that you need (though I know that it was with the 'current' OS-X version of that driver that I was having problems - perhaps it's specifically driver v5.1.7 with MySQL v5.1.24 which has been tested - it looks like the current version of the ODBC driver is 5.1.11, so perhaps you need to locate the approrpiate 32/64 bit version at 5.1.7 and try that...?)

                       

                      Cheers

                      Steve

                      • 8. Re: FM12 connecting via ODBC
                        BenHutson

                        Hi Steve,

                         

                        Hopefully you're right, though you'd think that the latest drivers would be better than older ones, considering it's essentially the same version just with fixes.

                         

                        It will be the next thing I try when I'm testing.

                         

                        That's my fault, i should have been more clear in my original post that it was a Windows based setup.

                         

                        Thanks for your help.

                         

                        Best Regards

                         

                        Ben

                        • 9. Re: FM12 connecting via ODBC
                          BenHutson

                          Hi All,

                           

                          Having tested the drivers recommended above (5.1.7) and setup up data source connections using 32bit (32bit only platform), I am still unable to connect the external data sources using FileMaker 12.

                           

                          This time the error is slightly different when I try and connect after it prompts me for a username and password: "Data source name not found and no default driver specified".

                           

                          Does anyone have any ideas? This was working fine when FileMaker Server Advanced 11 was on the machine, it's also working fine on our other server running FileMaker Server Advanced 11.

                           

                          Seems like FileMaker Server 12 doesn't like it, though that is likely just coincidence.

                           

                          Welcome any thoughts.

                           

                          Best Regards

                           

                          Ben

                          • 10. Re: FM12 connecting via ODBC
                            jwagner

                            Ben,

                            I wish I had an answer.  I am having similar problems connecting to a Progress OpenEdge database from FileMaker Server Advanced 12.  I'm running on WIndows Server 2008 and trying to utilize FM Server scripts to import data from the OpenEdge database.  I have installed the 32-bit ODBC driver but FMSA still can't find the data source.  Interestingly, I can use Excel running on the server to pull the OpenEdge data using the same System DSN, but FMSA 12 can't handle it.  I'd like to know if anyone has had success using ODBC from FMSA12 via a server-side script.

                            Regards,

                            John

                            • 11. Re: FM12 connecting via ODBC
                              LSNOVER

                              Ben:

                               

                              Can you test your ODBC DSN in the ODBC control panel?  Can you use it with any other programs?  Are you running Filemaker as the Administrator on the computer, or as another user?  It is possible you are running into a permissions related issue.  Does the ODBC source show up in Filemaker when setting up access.  Are you using ESS or doing someting else like importing via ODBC?  If you are using Server, can you open a copy of Filemaker on the Server and open the app. up as a stand alone file and get access to your ODBC data, or is it only an issue when the file is being served?

                               

                              Regards,
                              lee

                              • 12. Re: FM12 connecting via ODBC
                                greglane

                                Hi John,

                                 

                                Importing from an ODBC data source definitely works in a scheduled script in FMSA 12. I have enountered some ODBC data sources that can't be used with a scheduled script. Usually that's due to the driver's authentication requirements or an issue with requiring access to a network share.

                                 

                                You said you're able to use the DSN from Excel on the server. Are you able to use the DSN from FileMaker Pro on the server? If not, I would suspect that Excel is using a 64-bit DSN. FileMaker Pro 12 and scheduled scripts in FMSA 12 both require a 32-bit DSN when importing from a non-ESS ODBC data source. A 32-bit DSN has to be created using C:\Windows\SysWOW64\odbcad32.exe.

                                 

                                Greg

                                • 13. Re: FM12 connecting via ODBC
                                  pmconaway

                                  I have FMPSA 12 running on a windows virtual machine. (not sure 32 or 64bit) I had the IT tech install ODBC drivers to connect to our Oracle databases. We have read-only IDs that we must use. When I setup a connection, I store the user-name and password when I connect to an ODBC data source.  Now for server side - I have a user with a Mac that the company won't provide oracle ODBC drivers for so I tested to see if he could connect to oracle table in a test database I setup using the server's ODBC connection. It worked perfectly no problem. This could still be a driver issue.

                                   

                                  Paul

                                  • 14. Re: FM12 connecting via ODBC
                                    BenHutson

                                    Thank you all for your replies and input.

                                     

                                    This morning there was some development. Opening FileMaker up on the server machine and remoting to the tables, the fields in the external SQL source were showing <Primary Key Missing>. Having opened up the Database Manager and gone to that table, I clicked 'Sync...'. That then prompted a window saying a primary key wasn't set and to select a field to set Primary Key. I did this and the tables seemed to work after that, even from another computer.

                                     

                                    I had tried the Sync before and it had no effect but kept asking me for a password and telling me it couldn't connect for some reason or another. It seems somewhere along the way something I have tried has  remedied the issue, perhaps it was installing new ODBC drivers (for the fourth time).

                                     

                                    However, the tables that are used in the ODBC are tables we were using in 11, and we never had to do that initially when adding the tables as an ODBC in 11. Not sure why I've had to do this with 12, has anyone else experienced this when converting to 12? I cannot imagine it's unique to our conversion.

                                     

                                    Again, thank you all for your input, ideas, and help. Hopefully, when we do the live install on our live server, this will all go smoothly... Just wish I knew the exact steps that were taken to remedy it, this morning is the first time I looked at it in a week and solved the issue in a few minutes. Though I did see other tables saying Primary Key Missing, I'm sure I can fix this by Syncing the other tables and setting the PK... otherwise I'll be back here again.

                                     

                                    Best Regards to all,

                                     

                                    Ben

                                    1 2 Previous Next