I've never done it that way with the Java console. I have used plugins with FM that supported SQL and some of them give SQL error help which is nice.
What I would like is a SQL Client like SequelPro to connect to FMS and have a whole UI for playing with SQL in nice big windows with error codes and the results can be displayed much better than in FM's data viewer. I think I tested SQuirrel, but it wasn't as convenient as other SQL Clients connecting to other SQL databases. Most SQL clients don't seem to connect to FMS, but some use ODBC and those work. I'm a Mac guy looking for a Mac client, but have noted there are more SQL clients in the Windows world.
Does anyone have a favorite Mac SQL Client that they use with FMS?
One potential issue though is that you don't necessary want to open up the solution for external ODBC or JDBC access, from a security point of view. So make sure to nail the privileges down.
You can get the same type of verbose explanation internally:
What I would like is a SQL Client like SequelPro to connect to FMS and have a whole UI for playing with SQL in nice big windows with error codes and the results can be displayed much better than in FM's data viewer.
+10 on this, Taylor. I love using Sequel Pro (and even PHP MyAdmin works for mySQL). I'd appreciate it being able to connect to FM!
The deal is having FMS set up as ODBC/JDBC source, but I haven't tried to connect with Sequel Pro, have you?
Thanks to morkus for starting this thread.
I think you also get a little more verbose error in the data viewer, in addition to evaluation error or get last error. However, it is important to realize there are differences in your typical SQL and the SQL you write for the executeSQL function. Apart from the lack of INSERT, UPDATE, DELETE, Etc... you can't use single quotes or tick marks to quote names, but the notation to use a question mark for dynamic content is automatically quoted for you, so in that sense can be much easier.
Basically, the SQL you write via the ODBC connection may not be compatible with the executeSQL function, so make sure to test
That's true...you may need to tweak a few things, but not the basic query.
Since I'm primarily a Java dude, I use Intellij IDEA for the database, but free tools like Eclipse are also available.
Just the database part of Intellij by itself, IMHO, justifies its cost.
I've also used "MyEclilpse", a subscription-based, but not expensive tool for data access.
Using the same JDBC driver, your "remote" code can interact completely with the FM database. Very cool.
I have high hopes that someday even FMP will have a powerful SQL interface, but it seems that most users don't want that or may not be comfortable with it.
Fortunately, these other powerful options exist.
Yes, that's exactly what you would get using something like Intellij. It's quite easy to set up.
My basic gripe about many FM third party tools is that they cost an arm and a leg when in many other environments plug-ins are either free or nearly free.
Here's an update with a screen shot from a zip code database I imported from a text file into FM and then did some SQL to it from the remote DB console. This data console shown below is a single tab in a professional IDE and is integrated with the development environment.
For real data analysis, nothing beats a powerful console like this. Imagine this console taking up an entire monitor -- if you wanted it to.
- Real time SQL assist
- JDBC Console/Driver supports SELECT / INSERT / UPDATE / DELETE
- Real time SQL query error reporting (no "?")
- All windows resizable!
- Edit data directly in console
- Data or DDL mode
- Step through datasets
- Keep track of multiple queries
- Uses FileMaker-supplied JDBC driver - very easy to set up
- Enable JDBC sharing in FM
- Create simple connection configuration
- Point remote JDBC console to JDBC jar file
- Add URL to FileMaker database
- Example: jdbc:filemaker://220.127.116.11/ZipCodeDB
- Start doing stuff.
What's not to like?!
(Now that you have a JDBC connection, you could also write code (Java in this case, but not limited to Java in the general sense) to interact with the FM database remotely.)
See my new screenshot below.
Here's an updated graphic from above showing the query created via a JDBC connection to FileMaker then pasted into an actual ExecuteSQL statement in FileMaker (newly added graphic at bottom below).
From my experience, it's at least an order of magnitude faster to do the query work outside of FM. Once it's working, I paste in the query, add, if necessary, query parameters, adjust, if necessary, Field/Row separators, and I'm done.