I would start out asking such things as what version of FileMaker, but apparently FileMaker is working for you and it appears to be a MySQL issue. And I think this is generic SQL function issue with ODBC and not specifically ESS. The issue you seem to dismiss is the ODBC driver, which really can make a big difference on the version of SQL it supports. There actually are quite a number of different versions of SQL. FileMaker supports the SQL-92 standard and I assume that the Actual Technologies ODBC driver does also since it was made for FileMaker.
Does this work in Navicat?
If so, then it is the Concat function that does not work for you. It is not universally supported in all platforms (e.g., Postgres does not support it). I understand MySQL supports piping concactentation such as SELECT 'A'||'B'||'C'||'D'
Try this in Navicat on the Linux machine:
MONTH ( b_dob ) || '/' || YEAR ( b_dob )
See if the piping version works for you.
AS i said in my posty it all works perfclty with Navicat on both copis of th edb and with FMP on the copy of the Mysql databse hosted on OSX
I am using FmpA 13
The only case it does not work is when FMPA queries the Linux hosted copy -- as you can see from the atthachment teh fiels does not appear (syncing does not fix it)
I also have now hosted file on FMS13 using the Microsoft 64 bit driver, syced th table, and now the field shows up.
I suspect the ODBC driver version on your FMPA computer is not the same ODBC driver version on the FileMaker Server machine. Can you confirm what drivers? And I'm confused when you saying hosting on Mac OS X 6.8 which probably is 10.6.8 and you later talk about hosting FMS13, which doesn't work on anything prior to 10.8.5. Then you say you are using the Microsoft 64 bit driver, which to mean indicates your FileMaker Server 13 is on a Windows OS.
I guess I am still confused, but I suspect the ODBC driver on your FMPA machine that is connecting to MySQL on the Linux box. The current version is 3.1.16 and you said you were using 3.1.13. Maybe upgrade the driver.
Same machine actually (Windows 2012 R2.) FMPA13 in Windows only works with the 32 bit version. FMS 13 requires the 64 bit version. P.i.a.
I have 10.6.8 machine hosting one copy of the mysql db. (not running FSMP or FMS) . Linux hosting the other. Also have a client version of FMPA 13 running in Mavericks
I will try upgarding the Actual driver on that machine but the probel also arisea on the windows machine wth the 32 bit 0soft drver, which in the latest version. asd is where I want to run in production.
Ahhh, ok, I better understand now. Sorry if I'm a little slow sometimes. Yes, FMS 13 requires a 64 bit driver which is different than the 32 bit one for the FMPA. So it sounds like you'll do OK as long as you host the FileMaker database on the FileMaker Server, but it will not work through a locally hosted FMPA file. It doesn't answer your question as to why it does not work, but is a solution to the problem.
Actuall chnaging th calc slighty fixed it:
MONTH (`people`.`b_dob`) AS CHAR (2) charset utf8
YEAR (`people`.`b_dob`) AS CHAR (4) charset utf8
) AS `b_dob`
Still would like to know why though -- I think MONTH and YEAR a numeric type and perhaps MySQL 5.2 less less tolerant of that than v 5.5