6 Replies Latest reply on Jun 22, 2017 7:41 AM by wimdecorte

    REST in FileMaker (CRUD operations )


      Hi All,


      I have recently was approached to integrate our product in FileMaker with MySQL. In the conversation and my research I have heard of two potential tools which I can use. ESS and REST.  My research continues on how to implement either of them maintaining the security of data used in our product.


      I would like to request for help / advise if anyone over here in the community have tried / is using either of them.


      Thanks in advance.

        • 1. Re: REST in FileMaker (CRUD operations )

          When you say integration do you mean MySQL would be the external data source for FM or the other way around? The same applies to REST. Using FM to access an external REST API or accessing FM from an external web service using the Data API or something like REST FM?

          • 2. Re: REST in FileMaker (CRUD operations )

            FM integrates well with MySQL and has for a long time through ODBC connectivity. ESS makes it feel like the tables are native FM ones even though they are not.  It tends to work best if both are on the same local area network. 


            REST is brand new and many of us are excited about it.  FM's first version is still a bit limited and basically CRUD only using FMSQL.  I find it a bit awkward that it doesn't support SQL directly nor can it call and run scripts.  RESTfm came out before FM's REST API and has a leg up on a few things like native SQL.  Still, I am glad to see FM getting started with a RESTful API and hope it can be a lot more in the future than it is now. 


            REST basically uses SSL security passing tokens.  SSL security is pretty standard and accepted as industry standards.  I'm sure at Devcon next month there will be a lot of talk about the new API and security such as long term tokens verses short term ones and other such things.  But it all is very new and I think most all of us developers are just learning the capabilities of FM 16's RESTful API. 


            The RESTful API is not going to have the tight integration that ESS with MySQL has and it won't be as easy to integrate.  But it may be faster.  If I wanted something easy to get going, ESS will be easier. 


            Any chance you're coming to Devcon?


            Also, check out some great videos just coming out on the API such as from Beeswax!

            • 3. Re: REST in FileMaker (CRUD operations )

              "Integrate" is a loaded term.

              • Any time you try to have FMS as the 'frontend' (interface) and any SQL as the 'backend' (data), you are trying to recreate (poorly) one of the features of FM (already integrated data and interface).

              • The tables in MySQL may be so tied to each other as to be improbable/impossible to use FM for control of the data in it. There may be NOT NULL columns, dependancies (relationships), datatypes not support in FM, encryption, permissions, etc. that a web interface, for example, may be able to handle.

              • You may be required to have root permissions to the MySQL db to make it work well. And anything must be done carefully to preserve the integrity of the SQL data (as otherwise handled by a web interface).

              • If there is a mechanism to exchange data then it is better to use it (API, import/export, etc.) These are not the "live connect" with ESS, but will have the means to keep both side safer.


              I'm not trying to say it "cannot be done", but merely pointing out the gotchas that might be a factor. As a Hosting Provider with control of not only FMServer, but several other SQL and web servers, these are the kinds of things I've run into.




              1 of 1 people found this helpful
              • 4. Re: REST in FileMaker (CRUD operations )

                Beverly has some good points.  Keep in mind that FM works best with its own data.  Sometimes that is not an option (e.g., I've integrated several Word Press sites with My SQL to FM).  And you need someone who knows MySQL and how to administer it because FM cannot do that.  Two servers talking to each other will always be limited by bandwidth and latency.  If they are on the same local area network, I've seen this work really well.  Over the internet... well, you have to have some patience or in some cases it is not really doable. 


                The RESTful API is the big uncertainty that we are all just now exploring and hopefully it will open some new doors.  However, RESTful APIs are not like working with live data in the way ESS does.  It means pulling data down and manipulating it in text arrays or storing it back in to tables/fields, which can be slow if not well designed.  ESS does all the pulling and pushing of data in a way that feels like it is native to FM which makes things a lot easier. 


                For a production system, I would first try ESS before the RESTful API, because it is simpler and there is lot of experience with it already.  But I certainly like to dabble with the new tools to see how they work and their benefits. 

                • 5. Re: REST in FileMaker (CRUD operations )

                  You can integrate FileMaker with MySQL using REST by writing a REST web service. In that REST service, you use the MySQL and FileMaker's (free) JDBC drivers. FileMaker does a GET or a POST using INSERT FROM URL and from there, you can read/write data back and forth between FileMaker and MySQL.


                  Or, externally to FileMaker completely, you could just write Java code that would read/write data between FileMaker and MySQL (again using both JDBC drivers).


                  I've used both methods extensively.


                  Update: You don't need FMS to use JDBC, but be warned that FMI cripples the JDBC driver so you need to be on the same machine where the FMP database is or it won't work (unless, that is, you miraculously have, wait for it....FMS). (Note: It's easy to "work around" that restriction, but shouldn't, IMHO, be necessary since no other company I can name restricts their software like this.)




                  You can also buy a MySQL ODBC driver into FileMaker and use MySQL tables directly in FileMaker.


                  Lots of options.

                  • 6. Re: REST in FileMaker (CRUD operations )

                    sborade wrote:


                    Hi All,


                    ESS and REST.


                    ESS is for FM to read and manipulate data that lives in MySQL

                    The new REST API is for anything outside to read and manipulate FM data.

                    So two different directions.


                    If you want to manipulate FM data then there are more options than the new REST API. There's the XML API (and by extension the PHP API) and there is ODBC and JDBC, all supported by FMS.

                    1 of 1 people found this helpful