Subscribe Bookmark RSS Feed

Filemaker and SQL Query

claude_bellavan

Community Trekker

Joined:

Nov 5, 2014

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.

1 ACCEPTED SOLUTION

Accepted Solutions
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

7 REPLIES
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

claude_bellavan

Community Trekker

Joined:

Nov 5, 2014

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.

12899_Custom_Query.jpg

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.

Eric_Hill

Staff

Joined:

Oct 1, 2013

OK, I'll see what we can do.

Eric

Eric_Hill

Staff

Joined:

Oct 1, 2013

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

jpmontagne

Community Trekker

Joined:

Oct 7, 2014

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?

Eric_Hill

Staff

Joined:

Oct 1, 2013

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

claude_bellavan

Community Trekker

Joined:

Nov 5, 2014

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