Filemaker and SQL Query

claude_bellavan

Hello,

Since the introduction of the SQL Query builder, I tried many times to use this tool without success. So I use the old way (open database) which force me to build manually the SQL queries. Now that we can mix external databases and JMP tables, I'd like to give a second chance to Query builder. It still doesn't work. The error message :

The connexions with the Database works fine (I made an update script and I used it without problems for few months).

SQLSTATE=42000 means synthax error. But it is Query Builder who wrote the SQL Query...

Any idea?

Thanks,

Claude B.

Eric_Hill

Hey, Claude,

I'm sorry you are experiencing frustration with Query Builder.  One of the frustrating things about SQL is that, even though there is a standard, there are many vendor-specific differences in SQL support.  Query Builder has knowledge of *some* of these vendor differences and uses that knowledge when generating SQL.  However, Filemaker is not a database that we have tested with nor taught Query Builder about its idiosyncrasies.

For example, some databases really don't like the trailing semicolon at the end of the SQL statement.  One thing you could try would be to use the main red triangle menu and select "Convert to Custom SQL", and then see if you can edit the SQL in a way that makes Filemaker happy.  The first thing I would try is removing the trailing semicolon.  If that solves the problem, we can perhaps look into adding a special case for Filemaker that leaves off the trailing semicolon.

Thanks,

Eric