11 Replies Latest reply on May 16, 2015 3:14 PM by LSNOVER

    Actual Tech ODBC driver issue.

    bigtom

      So I know FMGo has an issue with ODBC connections after sleeping. This has been an issue for me for a while.

       

      Today along with a user having the connection error they also got a message that there were more than 10 connections and the scripted task could not be completed. It continued to throw out some network error. Dumping FMGo was the only way out.

       

      I went to a desktop and checked what was going on in FM Pro. At this particular time there were only 4 Pro clients and one Go client connected. Not all were making connections to the ODBC source. How could it ever hit a limit of 10? This has never happened before. I had one client on Pro showing the same over limit connection error and the others were fine. The Pro client was stuck throwing out the error and a force quit was my only option.

       

      Anyone have any advice on this? I did buy the pro pack and have 10 connections, but it is installed on the server and I have always had it treating this situation like one connection from the server and not as client connections. Is this a case of the server making more than 10 connections at one time?

       

      From Actual Technologies

       

      "When using External SQL Sources, the FileMaker application hosting the database makes all ODBC connections on behalf of the clients. Since the driver is only installed on the Mac hosting the FileMaker database, you only need a single "seat" license for the driver."

       

      But they also have something about connections per seat that is not really clear.

        • 1. Re: Actual Tech ODBC driver issue.
          bigtom

          Had this happen again to FileMaker Pro when only 3 clients connected.

           

          Restarted the server and seems to be ok for now. I checked the driver and it is up to date. This driver has been working well for about a year and now it is doing weird stuff.

           

          After that I guess I will have to email Actual Tech to see what is going on with this. I know a lot of you guys use these drivers and hopefully you will have some experience to share.

          • 2. Re: Actual Tech ODBC driver issue.
            IanJempson

            The issue is not with the driver. The problem is down to the way FileMaker manages connections to the back end server through ESS. It's been a few years since I ran into the same problem, so i may be a bit hazy on the details, but iirc FileMaker is creating a new connection to the SQL database for each new window. If memory serves it wasn't great at tidying up old connections once they weren't being used.

             

            You can test this easily by opening new windows and viewing different found sets in each window. You'll be able to see the new connections opening on the SQL backend.

            • 3. Re: Actual Tech ODBC driver issue.
              nicolai

              I can confirm that had a similar problem with one of the clients who uses FMGO. As far as I remember, I was closing and reopening his files only on the server instead of restarting the server.

              • 4. Re: Actual Tech ODBC driver issue.
                deninger

                The professional license for ActualTech's drivers is designed for a single machine / user connecting to multiple DB files over ODBC. 10 connections is a reasonable limit for this application. FM Go connects to FM Server. Even with a only a few of connections from FM Clients, FM Server will regularly exceed 10 overall connections.   There is s good reason that ActualTech sells a server license for their ODBC drivers.

                • 5. Re: Actual Tech ODBC driver issue.
                  bigtom

                  Ian, this would make sense as I had 4-5 windows open myself and another user had at least 2. We do not normally do this, but it was the case in the past few days.

                   

                  How are the connections opened? On file open or on active ODBC request. I am wondering if moving the any ODBC fields to a separate layout where they are only accessed ad needed.

                  • 6. Re: Actual Tech ODBC driver issue.
                    taylorsharpe

                    deninger:  There is s good reason that ActualTech sells a server license for their ODBC drivers.

                     

                    There are many issues trying to fake a service with an application (e.g., Peer-to-Peer sharing), which brings lots of other issues into the environment that servers don't have such as sleep issues.

                     

                    I can assure you that if you use FileMaker Server with the Actual ODBC Server licenses, then you won't have any of these issues. I do this on many customer machines and it works well.

                     

                    I've seen many people try to do what you are saying to save money on the Actual ODBC Server license. Yes, I personally think it is overpriced, but it works well. Alternatively if you want to do things less expensively, get a Windows Server because those ODBC drivers are usually free.

                    • 7. Re: Actual Tech ODBC driver issue.
                      bigtom

                      I have no problem paying for the server license and that is a viable option. A few hundred dollars to help make hundreds of thousands is not a difficult decision to make.

                       

                      I did not get the server license previously because many people on technet said it was absolutely unnecessary. Obviously they were wrong.

                       

                      I actually had many windows open working on using an API to interface with the external data instead of ODBC. It is getting there but ODBC seems like it is still faster with data updates.

                       

                      I appreciate the responses.

                      • 8. Re: Actual Tech ODBC driver issue.
                        IanJempson

                        bigtom wrote:

                         

                        Ian, this would make sense as I had 4-5 windows open myself and another user had at least 2. We do not normally do this, but it was the case in the past few days.

                         

                        How are the connections opened? On file open or on active ODBC request. I am wondering if moving the any ODBC fields to a separate layout where they are only accessed ad needed.

                        I think that it happens when you navigate to the layout, but i've not tested precisely where it happens in the sequence of events. Certainly by the time OnRecordLoad has fired a connection is open.

                        • 9. Re: Actual Tech ODBC driver issue.
                          deninger

                          I spoke with the ActualTech technical guys a few years back. Apparently ,, in certain cases FM will open multiple ODBC connections for a single instance of a table. This is generally out of our control as it happens behind the scenes. If you were to try to disconnect an ODBC connection by futzing (technical term) with FM to minimize a machine (server or client's) ODBC connection pool, there is no guarantee that a change down the line by FM won't break your workaround.

                           

                          I have been using the server license at two locations for quite some time now. It works quite well. my only issue is that for certain import FM applications  (NON ESS import script steps), I also need to get a non-server actual license for the client as well. The speed up that results, however, by doing an import as opposed to using ESS is worth it it in my solution.

                          • 10. Re: Actual Tech ODBC driver issue.
                            LSNOVER

                            You definitely should have the server license unless your use of ESS is minimal.   Each user can spawn numerous connections to the database.  Actual's costs are very reasonable, and they are about the only decent game in town. 

                            • 11. Re: Actual Tech ODBC driver issue.
                              LSNOVER

                              Have you tried running a server side script to do your import on the server?  Since FM13, we are able to use the ODBC driver from the client in this way including making direct calls to the SQL Database with the "old" Execute SQL SCRIPT Step.

                               

                              It works pretty slick.