Filemaker and SQL Query

[#1]

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.

[#2 · Accepted solution]

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

[#3]

Hi Eric,

I made a simple SQL Query with Query Builder. It contains only one semi-coma. It didn't work as it. But work perfectly after removing the semi-coma.

So you are right. The semi-coma is incompatible with FileMaker (and/or the ODBC driver).

Hope this can be fixed in a future upgrade.

Thanks a lot for this quick response!!

Claude B.

[#4]

OK, I'll see what we can do.

Eric

[#5]

A co-worker with better knowledge of JMP's ODBC support pointed out that, officially, JMP does not support Filemaker Pro.  Here is a usage note on the topic.

46030 - JMP® no longer supports FileMaker Pro™ database on Windows operating systems

Apparently, we have had myriad problems with their ODBC drivers.  Is it within the realm of possibility for you to switch to a different database?

Thanks,

Eric

[#6]

What about Mac OS X users? I use Filemaker Pro as well since Access is not available on the Mac platform. What relational databases would you recommend instead of FileMaker Pro for us Mac users?

[#7]

Hey, jpmontagne,

Our suggestion for a free database with free ODBC support on the Mac would be MySQL.  ODBC drivers for Mac for MySQL can be found here: MySQL :: Download Connector/ODBC.

SQLite is another possibility.  ODBC driver can be found here: http://www.ch-werner.de/sqliteodbc/​  SQLite is perhaps more akin to FileMaker in that you don't need a database server; the database is just a file on disk.

There are also commercial ODBC drivers for these databases available from ActualTech if you are looking for something a little more "production".

HTH,

Eric

[#8]

Hi Eric and JP,

I certainly do not want to start a discussion in the form of recrimination here. I am very pleased of the JMP team support. Regarding FileMaker, the important thing for me is to be able to work around the problem by removing the semicolon and to have shared the trick.

You are correct in regard to the offer somewhat more limited for databases manager in Mac OS's world.. Personally, I mostly use FileMaker for my research for its design flexibility (and Rapid Development) which is more important for me than the raw power. I nevertheless manages with great efficiency (with FileMaker Server) some databases with hundred of tables, thousands of fields and millions of records. I also use SQLite (a user) and especially PosGres (PostGIS server) for geographic data.

The ODBC drivers are particularly capricious for francophones like me because of the accents. You absolutely have to proscribe accents and spaces in field's titles!

Thanks again,

Claude

[#9]

Hi all - i've been reviewing some of our issues where we had a request for improvement in the JMP Community, which was actually addressed in the software, and trying to match it back to the original requests. This is one of those requests. It looks like we added some support for FileMaker, in the form of stripping off the semicolon when appropriate, in the JMP 14 timeframe. Hopefully, anyone searching for this issue will be able to upgrade to a newer version of JMP and be able to work successfully with FileMaker. Or course, please let us know if you see new issues with it.

Thanks!
Audrey 

[#10]

i've been running into the same issue with the trailing semicolon when querying data on a Presto ODBC connection using JMP 15. work-around of switching to custom SQL mode and removing semicolon works fine, but switching back-and-forth between custom and interactive mode gets very tedious very quickly during data exploration.

Audrey, could you give some details on the semicolon removal that you mentioned in your post? does it only support FileMaker? i dug through the JMP preferences to see if there's a way to enable it but couldn't find anything. if it doesn't exist already, it would be great to have a checkbox somewhere that can manually toggle trailing semicolon removal on/off in a future release. thanks!