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.
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 ....
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.
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
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.
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.