7 Replies Latest reply on Oct 14, 2015 8:08 AM by taylorsharpe

    Filemaker and ESS  ... Android , Mysql and RESTfm ... what is the  best approach ?

    binu.alexander

      I am planning a new solution and want to integrate   filemaker and Android ;  also do CWP via PHP /  Ruby to Filemaker adapter gem ....

       

      I have 2 options

       

      FIRST

      Create Native Filemaker Database Tables in the solution  and then

      a)  Use RESTfm to expose APIs for my Android developer to connect to filemaker

      b) Use Filemaker PHP API and create CWP site  or  use the Ruby adapter and create CWP

       

      SECOND

      Create all required tables in Mysql and then

      a) Use ESS and integrate with Fileamaker solution via ODBC

      b)  Connect Android Application to Mysql Database and create Android App

      c)  Use PHP or Ruby   to connect to Msql Database and create  Custom Web Site

       

       

      Any thoughts on what is a better approach ?  What are the pros and cons of either one ?

       

      Thanks

        • 1. Re: Filemaker and ESS  ... Android , Mysql and RESTfm ... what is the  best approach ?
          bigtom

          I guess this depends on where the bulk of your users are coming from. If you are looking to primarily have Android devices connected second option might be better. Also depending on what the users are doing, (just getting data or updating data and how much of each) the speed of ESS might not be what you want depending on how many tables and records you have.

           

          Don't try to make it too complicated with too many things in between.

           

          If you you have mostly android users and run MySQL tables with an Android app for mostly data reads this will be fast and effective. If you have mostly reads and not many updates this will also be good. If you use ODBC keeping your FMS in close proximity or connected with a god connection (VPN), makes a big difference in speed.

           

          I think It may be a better answer to:

          a. Use ESS and integrate with Fileamaker solution via ODBC to MySQL tables

          b)  Use PHP to connect to Msql Database and create  Custom Web Site with its own REST API

          c)  Connect Android Application to Custom Website API and create Android App

           

          Why? Well there will be some big chunk of coding for the functions that operate on the website and you will likely need the same function in the app ass well. Why do it twice? At that point if you can get that far why do you need FM at all? You could just access the MySQL database via PHP directly and have your own little platform. If you do not want to get that involved just connect both to the MySQL tables. This also supports a lot more users than CWP.

           

          From what I have experienced there is much better success with PHP over Ruby.

           

          If you want to start quick and easy option one is good and you may be able to get by using only the FM PHP API.

           

          Just depends on how big and how many people and what it is doing.

          • 2. Re: Filemaker and ESS  ... Android , Mysql and RESTfm ... what is the  best approach ?
            binu.alexander

            Thanks ....

            Android users will be doing minimal updates ...   Just marking attendance for students in a class

            I will most probably be doing PHP

            Our FMS VM and Mysql VM  are on the same physical server .. so connection is good ..

             

            Please share any more thoughts if any ....

            • 3. Re: Filemaker and ESS  ... Android , Mysql and RESTfm ... what is the  best approach ?
              wimdecorte

              binu.alexander wrote:

               

              SECOND

              Create all required tables in Mysql and then

              a) Use ESS and integrate with Fileamaker solution via ODBC

              b)  Connect Android Application to Mysql Database and create Android App

              c)  Use PHP or Ruby   to connect to Msql Database and create  Custom Web Site

               

               

              Any thoughts on what is a better approach ?  What are the pros and cons of either one ?

               

               

              Too many variables to give you a simple "choose x" answer.


              Take "c)" for instance: the choice between PHP or Ruby as the web scripting language is a major choice so you should only go down the route that you can support the best.  I personally like Ruby and work in it a lot but if both Ruby and FM are fairly new to you or the team then I would stick with PHP because you'll find more resources and help in the forums.

               

              On "a)", why do you mention both ESS and ODBC?  Are you talking ODBC to MySQL from Android?

              Also keep in mind that if you keep all the tables in MySQL and use ESS to make a FM frontend to MySQL you will have to make a fair number of compromises for things that do not quite work the same on an ESS table as they do on a native FM table.  If you have not worked with ESS in a big way yet, then be very careful what you expect and promise.  Read chapter 9 of the FTS Advanced book very carefully.

              • 4. Re: Filemaker and ESS  ... Android , Mysql and RESTfm ... what is the  best approach ?
                binu.alexander

                Oh .. I thought ESS and ODBC meant the same ...  I will read the chapter ..

                 

                I will be sticking to PHP , but I have a good ruby developer in my team . So I was just thinking about that option.

                 

                I will do some experiments and see what works best

                 

                Sent from my iPhone

                • 5. Re: Filemaker and ESS  ... Android , Mysql and RESTfm ... what is the  best approach ?
                  beverly

                  ODBC is the 'connection' between two different databases (or even applications, such as PHP, Excel, etc.). This can also be JDBC (Java used).

                   

                  You set up a Data Source Name (DSN) via the control panels/utilities ON the system making the query to the other source. You use the appropriate DRIVER TO the system you will be querying.

                   

                  FileMaker has 3 "ODBC" paths:

                   

                       1. FMP/S as ODBC Source (other systems/apps can make queries to it). The Driver is provided by FMI with the install. From the odbc_jdbc_guide:

                  "The ODBC client driver is available through a separate installation on your FileMaker installation disk or electronic download in the xDBC folder."

                   

                       2. FMP/S as CLIENT to SQL (FileMaker can 'see' other sources)

                   

                            a. as one of the approved sources to use as ESS (External SQL Source), with the corresponding approved driver. FileMaker has the sql tables on the Relationship Graph (RG) and these are "shadow tables" giving you a "real-time" view into SQL. You can find, sort, add, edit, delete, in short many of the same functions as internal FM tables. You can add calculation and summary fields to these tables and these are not added to SQL, but can act upon the SQL columns/fields and other tables/fields. You do NOT make SQL queries to these tables.

                   

                            b. and/or set up a DSN to one of the the approved External SQL Sources and sometimes to other "non-approved" ESS databases. The use of two script steps can interact with these tables (also on the graph), but the data is not "real-time". SQL queries must be used to:

                   

                                 1) SELECT - using the IMPORT script step

                   

                                 3) INSERT, UPDATE, or DELETE - using the Execute SQL script step (note! this is not the ExecuteSQL function)

                   

                   

                  I often have two distinct DSN set up if I have the need to use ESS & the SQL calls between FMP/S and another ODBC source.

                   

                  HTH,

                  Beverly

                  • 6. Re: Filemaker and ESS  ... Android , Mysql and RESTfm ... what is the  best approach ?
                    binu.alexander

                    Thanks beverly ... Your answer helps understand this better ...

                    • 7. Re: Filemaker and ESS  ... Android , Mysql and RESTfm ... what is the  best approach ?
                      taylorsharpe

                      I am not a PHP developer, but have coordinated several jobs with PHP developers.  One thing I have found is that in general, PHP works faster with MySQL than FileMaker.  Granted FileMaker has made improvements in the last several versions, it still is not as fast as with MySQL.  I cannot speak for Ruby on Rails, but if speed is critical, I would look at hosting the outward facing tables on MySQL and I've done this for a number of clients.  FileMaker works very well with ESS and MySQL, especially if the two servers are on the same LAN.  Another thing is that it is a lot easier to find a PHP developer familiar with MySQL than FileMaker.  But I will say I have had a lot more issues in such situations getting someone to take responsibility for configuring and maintaining security on MySQL.  MySQL is hacked a lot more than FM and there are more hacking tools to get into it than FM.  For example, FM is not susceptible to SQL injection attacks like MySQL.