Jump to content

Adventures with FileMaker & SQL


This topic is 4761 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi Everyone -

At the generous invitation of Kevin Frank, I have been asked to share my wisdom (really, it's just my experiences) with FileMaker and Internal SQL Calls.

I was fortunate to have had the opportunity to present on this topic at the most recent meeting of FMDiSC (FileMaker Developers in Southern California) on March 11th. The preparation for this presentation was, in brief, quite an adventure.

In preparing the presentation I toyed with the idea of testing all the available plug-ins, but settled on ScriptMaster Advanced (360Works) and DoSQL (SH Partners / MyFMButler). After the meeting (i.e., today) I spent some time experimenting with the MMQuery plug-in from Comm-Unity (CNS). I was also fortunate to have Chris Dragon of Dracoventions presenting at the meeting as well, who has another SQL Plug-In, SQL Runner. While I did not test his plug-in, I did get some helpful and sometimes valuable insights from him during my presentation.

I did not test a huge variety of SQL statements so as to keep my presentation simple. I tested the following: CREATE, INSERT, SELECT, SELECT DISTINCT, and UPDATE. I tested the WHERE clause using the IN, LIKE, and BETWEEN clauses. I also tested the UNION clause.

In the course of my presentation and my preparation, I did discover some interesting things. I offer them here for your consideration:

1. Not all the plug-ins support creation of tables. In my tests, ScriptMaster and DoSQL created the table but crashed FileMaker. MMQuery created the table but asks me to check the "I Accept the License Agreement" box every time I ran the SQL statement (even though I had properly registered the plug-in).

2. Sub-Queries do not appear to be supported by any of the plug-ins I tested.

3. Doing a search using wild-cards was, in most cases, slower than in native FileMaker.

4. Not all the plug-ins communicate or interact with the FileMaker UI, so that it was sometimes difficult or impossible to tell if a query was actually running.

5. Because of #4, it was impossible to stop an SQL query from running once it was started. That is, unless you want to forcibly quit FileMaker.

6. Performing the exact same commands took much longer with some plug-ins than others. I do not know why.

7. Because of #4, some of the plug-ins would report errors and some would not.

8. FileMaker's Internal SQL API only supports basic SQL commands and, according to Chris Dragon, using the SQL-92 standard. That means that any vendors specific extensions to SQL, such as those currently used in Oracle and MS SQL Server, are not supported.

The most powerful aspect of using SQL in FileMaker, in my opinion, is the lack of dependence on context. In other words, you can execute a SQL command from any layout and it will run fine regardless of the context of that layout. This single fact offers much promise and, from where I sit, almost limitless possibilities.

I am far from an expert on SQL, although I do have enough experience to get myself into trouble. It's a miracle that the demo file I used for my presentation still works, considering the fact that I've had to force-quit FileMaker on it at least a dozen times.

I hope this posting adds my voice to the others already here in looking forward to FileMaker adding even more SQL capabilities and improving the existing API to perform better, provide better error reporting, and to allow more of the available SQL commands to be used.

Cordially,

--

Michael Frankel

FileMaker Certified Developer

Wizard Consulting Group

Office: (818) 706-8877

Skype-In: (818) 483-4686

Skype: michaelfrankel

Website: www.wizardconsultinggroup.com

Link to comment
Share on other sites

Hi Michael,

Great posting, and lots of food for thought there... for the moment I just want to respond to this:

> 1. Not all the plug-ins support creation of tables. In my tests,

> ScriptMaster and DoSQL created the table but crashed FileMaker.

With doSQL, the CREATE, ALTER and DROP commands require a "1" or "True" as the executeOnIdle parameter, in which case you shouldn't get a crash, although support for these commands is "experimental" according to their documentation. I'm pretty sure ScriptMaster requires a similar parameter.

Regards,

Kevin

Link to comment
Share on other sites

Hi Michael,

Great posting, and lots of food for thought there... for the moment I just want to respond to this:

> 1. Not all the plug-ins support creation of tables. In my tests,

> ScriptMaster and DoSQL created the table but crashed FileMaker.

With doSQL, the CREATE, ALTER and DROP commands require a "1" or "True" as the executeOnIdle parameter, in which case you shouldn't get a crash, although support for these commands is "experimental" according to their documentation. I'm pretty sure ScriptMaster requires a similar parameter.

Regards,

Kevin

Thanks, Kevin. I might have gotten the parameter incorrect with DoSQL and I'll have to check the ScriptMaster documentation more carefully as I didn't see any parameters. Thanks for your input.

--

Michael Frankel

FileMaker Certified Developer

Wizard Consulting Group

Office: (818) 706-8877

Skype-In: (818) 483-4686

Skype: michaelfrankel

Website: www.wizardconsultinggroup.com

Link to comment
Share on other sites

Hi Michael,

Great posting, and lots of food for thought there... for the moment I just want to respond to this:

> 1. Not all the plug-ins support creation of tables. In my tests,

> ScriptMaster and DoSQL created the table but crashed FileMaker.

With doSQL, the CREATE, ALTER and DROP commands require a "1" or "True" as the executeOnIdle parameter, in which case you shouldn't get a crash, although support for these commands is "experimental" according to their documentation. I'm pretty sure ScriptMaster requires a similar parameter.

Regards,

Kevin

Hi Kevin -

Another thought. I remember seeing comments about the CREATE statement being "experiemental" in doSQL, however I could not find any specific documentation on the using SQL with ScriptMaster. Can you give me an idea of where to look (or if Jesse Barnum is lurking here, Jesse?)...

Link to comment
Share on other sites

MMQuery created the table but asks me to check the "I Accept the License Agreement" box every time I ran the SQL statement (even though I had properly registered the plug-in).

Just a note to let you know that the problem with MMQuery was a version issue. CNS had revised the plug-in after it's release for the Mac and I had downloaded the plug-in before a small bug was fixed.

Special thanks to Daniel at CNS Plug-Ins for advising me about this and directing me to re-download and re-install MMQuery. Now it all works beautifully.

Thanks...

Link to comment
Share on other sites

Sorry, Michael, I was confusing ScriptMaster with MMQuery. I meant to say, "I'm pretty sure MMQuery requires a similar parameter."

Thanks, Kevin. I might have gotten the parameter incorrect with DoSQL and I'll have to check the ScriptMaster documentation more carefully as I didn't see any parameters. Thanks for your input.

Link to comment
Share on other sites

This topic is 4761 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.