5 Replies Latest reply on Jun 8, 2013 9:18 AM by taylorsharpe

    SQL as Backend

    ptrc

      Good Evening Everyone,

       

      Can anyone share any tidbits about setting up FIleMaker as a front end with a SQL backend?

       

      Thanks

       

      Peter

        • 1. Re: SQL as Backend
          ch0c0halic

          According to the FMI marketing materials FMS is not intended to be front end for SQL. Front end implied gathering data through the FMP interface and storing it in the SQL. That is usually the reverse of what is really desired, wanting FMP to get the data from SQL and analyze or process it.

           

          You can do a lot of things with the two together that you can't do alone. Please tell us what you want to do and maybe we can offer suggestions.

          • 2. Re: SQL as Backend
            PeterWindle

            This is something I have also been very interested in knowing more about...

             

             

            I have one client in particular, who wants to move a FileMaker run time solution over to a multi-user 'cloud' solution.

             

            He has 1000-2000 clients and it would seem that hosting this via FileMaker server to use via FileMaker environment will not cope very well... and having filemaker as the back end to a web-based solution is also not suitable.

             

            So, this looks like a re-build. I've been trying to find a way of keeping the user experience within a filemaker environment (go, runtime, pro I don't care how it happens), whilst keeping all the data on-line, within an environment that will cope with this many users via the internet. But this seems like mission impossible...?

             

            It would be great if FileMaker devleoped some technologies that would close this gap. SME environments is a good market, but I don't see that market being one that will stand the test of time.

             

            I know that FileMaker was never really designed to take on this sort of task, but to be honest, there are a lot of people now realising that because filemaker is not suitable for this, they tend to move away from using FileMaker, even before they start.

            • 3. Re: SQL as Backend
              Oliver_Reid

              I have done this with serval web deployed FM applications with great success.

               

              This a good solutions when

               

              1 an "in office" system has a complex business model and performs many functions, but transaction volume is moderate - FM is a superb tool for this situation.

               

              2 limited elements of the solution are to be exposed via a web interface: usually relatively simple functions, but high scalability is needed.

               

              Please heck out:

               

              edpsoccer.org

               

              This is a pro bono site, where mysql tables provide everything you see, and are maintained with a FM front end. Not how fast the game schedule page works -----and this could handle hundreds of hits per minute (does at times)

               

              http://edpsoccer.org/tier1/tier1.php?page=27

               

              and think how this would be using FM server .

               

              The strictures about using FM as a SQl front end, in my view,  refer to using FM has many-user, high transaction volume, system. But in tables which are write few times, read many, it's fine.

               

              In fact once you get over a thousand or so rows in table queries are faster if it's an ESS table. You just have to understand that  "push" auto fresh of screen data is not as automatic as with native FM and you need "commit, refresh / flush" steps here and there.

               

              One dramatic advantage I have made use of is the use of views. I use MySQL with  Navicat as a management tool. You can create "views" - virtual tables based on a complex query, and perfect them in Navicat and then have Filemaker treat those as  ordinary tables. Then a complex query pulling data from 4 or 5 linked tables with a complex filter is executed by the SQL server engine, server-side, rather than Filemaker Server chattering back and forth with the client . (Also SQL can read down a single column of data as fast as along a row in many cases - something FM cannot do yet)

               

              The results are displayed almost instantly in a Filemaker client, whereas using FM relationships + calculations the same listing or portal would take several minutes to render using a FM client/remote FM server set up.

              • 4. Re: SQL as Backend
                ptrc

                Thank you very much for your detailed response. I think I am going to setup some tests.

                 

                Peter

                • 5. Re: SQL as Backend
                  taylorsharpe

                  FM behaves most quickly with an ODBC import from a SQL source.  But what you are doing then is replicating what is already in a SQL table into a FM table.  Extended SQL Services (ESS) is FM's way of using SQL data without having to import it every time and then export changes.  ESS does this for you.  However, there is a fair amount of overhead making this all work.  FM's goal was to make this easy to use and they did a great job of this.  Their focus was not on making it optimized for speed.  So this works fine as long as the data does not get large. I've used it in many situations and find that it works pretty well.  One really big feature that FM did that is really elegant is allowing the FM clients to see all of the info the Server's ODBC connection sees.  In other words, you don't have to configure ODBC connection on each client computer, which is pretty slick.  FM did a great job of making it simple and elegant.  But it is not a solution for an enterprise system because it was not optimized for that.