AnsweredAssumed Answered

FM12: SQL syntax bug (ExecuteSQL)

Question asked by arnoldkegebein on May 6, 2012
Latest reply on May 18, 2012 by TSGal

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.

Outcomes