7 Replies Latest reply on Dec 1, 2011 6:49 AM by beverly

    Taking file to MySQL

    RPaulH

      We are thinking about taking our file to MySQL and using FMP for the front end. This will provide more features on the web side and leave the client side intact. Does anyone have any feedback in the pros/cons area about this sort of move? Just looking for some input before we decide to pull the plug.

       

      Thanks,

      Paul

        • 1. Re: Taking file to MySQL
          ptrc

          I have been debating this same issue for the last year. The buggest PRO to going MySQL would be the speed of data recovery in case of the worst case scenario occurs and ther database goes corrupt. You could have your data restored within minutes. This would be benefical for larger solutions that can take some time to restore using FMS.

           

          The biggest CON for me would be losing of the built in field calculations etc that SQL does not support. I have found some calculations and summary fields will not transfer and have to be calculated and inserted by script.

           

          I decided to slowly implement SQL on new solutions and then go full scale once I am conviced that the user experience will not change.

          • 2. Re: Taking file to MySQL
            RPaulH


            The biggest CON for me would be losing of the built in field calculations etc that SQL does not support. I have found some calculations and summary fields will not transfer and have to be calculated and inserted by script.

             

             

            That is something I did not know. I thought SQL would just house the data and I would use the FM front end like normal. Is that not the way it works? I am not familiar with SQL other than knowing it is a database engine and quite versatile.

            Paul

            • 3. Re: Taking file to MySQL

              Hi Paul,

               

              From his DevCon presentation 2 years ago, I distinctly recall Galt Johnson from FMI stating that their implementation of the ESS had never been intended as a tool for managing any SQL database. The ESS was designed primarily for access.

               

              With this proviso, you may want to reconsider your plans.

               

              Regards,

               

              John

              • 4. Re: Taking file to MySQL
                Malcolm

                Perhaps one of the Filemaker Engineers will comment. Initially we were advised that this would not be a good thing to do. I haven't been aware that FMI have changed that advice. If we could reliably use the worlds best database front-end on top of SQL databases I think we would all be interested.

                 

                Malcolm

                • 5. Re: Taking file to MySQL
                  beverly

                  Not an FMI engineer, just a SQL dba. I work with MS SQL and MySQL (and FileMaker) daily. I have solutions that use ESS, I have solutions that use Import[] & Execute SQL[] script steps to communicate with ODBC sources that are not "ESS", I have solutions that use FileMaker as an ODBC source. Used properly, these work well.

                   

                  The ESS communication with MySQL and MS SQL (from a Filemaker interface) can be used with the functions that work as intended. There are some suggestions that make it function more efficiently, but you should not plan to use FileMaker as just a "front-end" to a SQL source. There are just too many variables if you have no control over the SQL source(s) as well as the FileMaker database(s).

                   

                  For example, many MySQL & php web solutions rely on the php to make joins and validations and other restrictions that make it difficult to manage from FileMaker. That doesn't mean it can't be done, it's just not worth the bother when the browser interface gives you an easier access to the MySQL.

                   

                  Another example: The data set through ESS may be much larger and a "list view" may be excessively slow. I advise using a script (or manually) go to find mode, go to the layout (while in find mode), perform the find narrowing down the result, going to form mode, if the set is small enough: then and only then switching to list view.

                   

                  The SQL source may have restrictions (set by the dba - database administrator) that prevent you from using it completely. If, on the other hand, you have control of the SQL databases, you may be able to create views that will narrow the data, allow access to specific fields (column) and otherwise grant an easier access to the data through FileMaker.

                   

                  You can create calculated fields and summary fields and relationships in FileMaker based on the SQL source fields/columns, but why bother to manage both sources?

                   

                  ESS is great and if you have cooperation with the DBA, you can do some things that make it easy to use the SQL data within FileMaker. You just have to be careful what you are trying to do, or you can soon get into trouble!

                   

                  Beverly

                  • 6. Re: Taking file to MySQL
                    RPaulH

                    Ok, Beverly what I am understanding is that if I control the MySQL and FM data it works as I envision, but I will need some SQL know-how to fully utilize it. Is that correct?

                     

                    Paul

                    • 7. Re: Taking file to MySQL
                      beverly

                      Paul, yes, it helps greatly IF you have control of the SQL source for design, permissions, dependencies, validations, etc. This has to be done from an interface that allows full access to the SQL database(s).

                       

                      Again, heed the "tread carefully" advice. Don't rely on FileMaker as an interface/front-end to SQL sources. Use both AS INTENDED and they can communicate & co-exist.

                       

                      Beverly