Summary
FM12: SQL syntax bug (ExecuteSQL)
Product
FileMaker Pro
Version
FM12.0v1
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(*)
FROM Student
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.
Expected result
(1)
Hello World!,Admin,2012-05-06
(2)
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.
(3)
All professor records with a reference in table seminar.
(4)
A list with year numbers and the amount of students having birthday in these years.
(5)
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
Actual result
In all five queries, the result in FM12 is always '?'.
Configuration information
In FM10, where I get proper results, I am using the FMSQL plugin from qutic development.
AKegebein:
Thank you for your post.
I have forwarded your entire post to our Development and Testing departments for review. When I receive feedback, I will let you know.
TSGal
FileMaker, Inc.