FM12: SQL syntax bug (ExecuteSQL)
Operating system version
OS X 10.6.8
Description of the issue
The SQL syntax changed. With a SQL plugin I could run certain queries in FM10. But in FM12 the queries return only '?'.
(1) SELECT only query:
SELECT 'Hello World!', USER, CURRENT_DATE
(2) In-line comment at the end of the query:
SELECT TableName FROM FileMaker_Tables --Comment
(3) Exists function:
SELECT p.* FROM Professor p
WHERE EXISTS (
SELECT * FROM Seminar s WHERE p.ProfNo = s.ProfNo
(4) Extract function in group clause:
SELECT EXTRACT( YEAR FROM birthday ), COUNT(*)
GROUP BY EXTRACT( YEAR FROM birthday )
(5) * plus additional columns
SELECT COUNT(*), * FROM FileMaker_Tables
Steps to reproduce the problem
In the data viewer create a new expression:
ExecuteSQL( "query"; ""; "" )
Replace »query« with the queries from above.
A list with the names of all defined tables occurrences in the current database.
There is no problems if the inline comment is before the last line.
All professor records with a reference in table seminar.
A list with year numbers and the amount of students having birthday in these years.
A list with all records from FileMaker_Tables with the total number of tables in front of each line.
You can use * alone, but as soon you will add another column
In all five queries, the result in FM12 is always '?'.
In FM10, where I get proper results, I am using the FMSQL plugin from qutic development.