Does FQL actually work?

[[email protected]]

I've been working with SQL databases for about 12 years now, and have stumbled across this monstrosity in a new position - and I really don't get the translation between Filemaker and SQL.

 

This should work according to the documentation:

ExecuteSQL ( " SELECT Customer_EVENT::Inv_dollars )  FROM Customer_Event WHERE Customer_EVENT::FlagReview = \" " ; "" ; "" )

...or something. I really can't even get a simple SQL Statement just pulling anything more than 1 field to work. Is there a reference out there (not Filemaker or FM14 Bible) that actually explains in detail WHY FQL statements are made the way they are and how each piece interacts?

I'm not sure I can do this with a calculation, but it's entirely possible. I'm just trying to get a SUM on a certain set of records to display SOMEWHERE in a layout. The fields are from a related table, but have nothing to do with the related records. It's just a way to SUM the objects in a portal by "filtering" the amount.

 

 

[comment]

A few things jump out:

1. Your parentheses are unbalanced;
2. You need to use unqualified field names, without the TO prefix;
3. You need to enclose character constants in pairs of single quotation marks; what you have now is an opening double-quote character, with no content and no closing.

 

Quote

 Is there a reference out there

There is a fm13_sql_reference.pdf document available from FMI site.

 

 

Edited May 10, 2017 by comment

[Kevin Frank]

Beverly Voth and I have both written extensively on ExecuteSQL, e.g.,

• The Missing FM 12 ExecuteSQL Reference
• PDF version of FM 12 ExecuteSQL Reference
• FM 12 ExecuteSQL, part 1
• FM 12 ExecuteSQL, part 2
• FM 12 ExecuteSQL: Dynamic Parameters, part 1
• FM 12 ExecuteSQL: Dynamic Parameters, part 2
• FM 12 ExecuteSQL: Robust Coding, part 1
• FM 12 ExecuteSQL: Robust Coding, part 2

[Kevin Frank]

In addition to Comment's observations, I will add that if you're looking for records where FlagReview is empty, you should use IS NULL, e.g.,

ExecuteSQL ( 

"SELECT Inv_dollars  
 FROM Customer_Event 
 WHERE FlagReview IS NULL" 

; "" ; "" )

...which will give you a return separated list of all the matching Inv_dollars amounts (which I'm guessing is not what you actually want).

You mentioned trying to get a sum -- perhaps this will do the trick?

ExecuteSQL ( 

"SELECT SUM ( Inv_dollars ) 
 FROM Customer_Event 
 WHERE FlagReview IS NULL" 

; "" ; "" )

 

Edited May 11, 2017 by Kevin Frank

[beverly]

The fm_sql_reference (versions 13, 14, 15, & 16!) is only partially helpful. It applies more for using FM as an ODBC source. The "missing" reference is a compliation of many developers pounding (testing) the heck out of the ExecuteSQL function. What works, what doesn't. Get the PDF and the example files. Read the comments from others on the blog. Kevin (and others) have a ton more blogs on ExecuteSQL!

And there are a few "helper" files that will guide you in writing the queries correctly (here are the current/correct links):

SQL Builder (this forum) http://fmforums.com/forum/files/file/25-sql-builder/

SQL Explorer (seedcode) https://www.seedcode.com/filemaker-sql-explorer/

 

And if you need a Japanese version of the "missing" reference? http://notonlyfilemaker.com/2014/01/missing-executesql/

beverly

[[email protected]]

Thanks Beverly and Kevin - you've both been a big help. I think I have the correct information in front of me now. I'm going to have to wrestle with the syntax a bit, but these reference are much better than the FM documentation! 

...and I think I'm okay without the Japanese version, Beverly. I'm confused enough as it is!

[beverly]

Use the files (builder or explorer) to help you write the queries.

beverly

[ggt667]

There is also SQLEditor: http://www.malcolmhardie.com/sqleditor/ visually create SQL queries for the following dialects:

• generic
• OpenBase
• SQLite
• sybase
• postgres
• FileMaker
• SQLServer
• Redshift
• MySQL
• Oracle
• Rails
• Rails Schema
• Django

IIRC there is free trial if you just download. See if it does what you'd like, I only used this app for presentations this far.

Edited May 29, 2017 by ggt667

[Lee Smith]

Hi Bev,

This link has been changed from

SQL Builder (this forum) http://fmforums.com/forum/files/file/25-sql-builder/

To

 http://fmforums.com/topic/83765-solutions-sql-builder/

 

[typewriter]

On 11 mei 2017 at 2:54 PM, [email protected] said:

Thanks Beverly and Kevin - you've both been a big help. I think I have the correct information in front of me now. I'm going to have to wrestle with the syntax a bit, but these reference are much better than the FM documentation! 

...and I think I'm okay without the Japanese version, Beverly. I'm confused enough as it is!

indeed these blog entries and manuals are well worth reading.

I might add my two cents in that you should consider keeping your FileMaker solution 'SQL clean'. By that I mean three things:

1. use names for tables and fields so that you don't need escape codes. It makes your SQL much nicer to read and maintain.

2. use a separate set of Table Occurrences for your SQL. SQL operates without the Filemaker context, so there there's no need to stick to your schema and you don't need cryptic names in your SQL statements.

3. don't use SQL in field definitions. You will run into record locking conflicts without knowing it.

HE

[Wim Decorte]

While I agree with #3 (and I would expand that to include conditional formats, hide conditions,... anywhere that you don't have control over the open-state of the target table - given the documented penalty with using ExecuteSQL() against a table that you have an open record in), I don't agree with #2.  There's no point in adding TOs just for SQL.  Because SQL is context insensitive you can use whatever TOs you already have.  The less schema you have the better your solution will perform.

And I only partially agree with #1:  all tables and fields should already be mostly SQL safe if you follow best practices, but even with that I would strongly suggest that people use one of the many custom functions that allow field references to be used in SQL instead of hard-coded TO and field names.  Not only does that automatically make any TO and field name safe to use but it also (and more importantly) protects against TO and field name changes.

[typewriter]

Well, the point here is this: for Table Occurrences I always make a separate set of 'reference TO's' which are only used for SQL, and 'behind the scenes' tasks like track and trace functions. Once you have these TO's it doesn't make sense to rename them, and you can blindly type their names in SQL code, knowing that they'll be there and represent what they're supposed to represent.

For field names it's different, but frankly I don't see the point in changing names later on, especially once you've started scripting. I know that Filemaker is so nice to allow this, but using SQL in a professional manner I would suggest treating the rest of the database in a professional way too. Noblesse oblige...

Lately I have had to take over solutions that other developers made and I came to hate wading through layers of custom functions, it is a real pain and at the end of the day it will cost a ton of money too.

I wish FMI paid more attention to best practices...

By the way: are you suggesting that FQL may be responsible for holdups when loading records this way?

 

[Wim Decorte]

3 hours ago, typewriter said:

By the way: are you suggesting that FQL may be responsible for holdups when loading records this way?

 

Not sure I understand what the "this way" is...

FQL will be extremely slow if you so a query against a table and you have an open record in that table (in your session) and you have a semi-large number of records in that table (you'll seriously start noticing it with a few ten-thousand records.  That's because FMS sends the client all the data for that table so that the client can resolve the SQL query, including the uncommitted data.  With no open record in the target table, FMS executes the query and that is blazingly fast.

 

3 hours ago, typewriter said:

 

Lately I have had to take over solutions that other developers made and I came to hate wading through layers of custom functions, it is a real pain and at the end of the day it will cost a ton of money too.

I wasn't taking about layers of nested CFs, just a few simple CFs that allow you to easily use fields and TOs by reference and avoid any hard coding.  No matter how you turn it, hard-coding anything is  a potential source for errors and thus something to avoid.  At Soliant it's a big part of our coding standards.

[comment]

11 minutes ago, Wim Decorte said:

just a few simple CFs that allow you to easily use fields and TOs by reference and avoid any hard coding.  No matter how you turn it, hard-coding anything is  a potential source for errors and thus something to avoid.

You can accomplish this quite easily even without custom functions. And of course I completely agree that hardcoding field/TO names is a no-no.

 

[typewriter]

On 2 augustus 2017 at 5:26 PM, Wim Decorte said:

FQL will be extremely slow if you so a query against a table and you have an open record in that table (in your session) and you have a semi-large number of records in that table (you'll seriously start noticing it with a few ten-thousand records.  That's because FMS sends the client all the data for that table so that the client can resolve the SQL query, including the uncommitted data.  With no open record in the target table, FMS executes the query and that is blazingly fast.

Did some testing on a table with 100K records, running on a 4core MacMini w/FMS16: depending on the client and network, the speed difference is at least 10fold.

I also did some testing in a local file (no network, single user) and in a peer-to-peer setup. When running locally the 'record in use' effect is still there, but only a factor 2. My guess is that Filemaker can't use the index when a record is in use, resulting in a slowdown. In a peer-to-peer setup, the diffrence is much greater, up to 700x! 

[Wim Decorte]

My test file from the devcon presentation a few years back can be found here:

http://www.soliantconsulting.com/blog/2016/04/bag-goodies-executesql-named-buckets-relationinfo

[typewriter]

Well, I repeated my comparison tests many times, with simple queries and queries that require a lot of processing and although I get consistently longer process times, results are not always as bad. Sometimes the difference between a normal run and a 'record in use' run is only 30%, which I do not consider dramatic. Which leaves me wondering how and when Filemaker executes a FQL query on the server. When a record is locked, will it still use the index? And what information is cached? 

[Wim Decorte]

FQL is executed on the server as a rule.  It is executed on the client if that client has an open (locked, uncommitted,...) record in the target table, any table that is part of the SQL query.

Only the client's open record state counts.  If other users have open records then that does not matter.  Obviously their data will not be in the result set since neither server nor the executing client knows about that data yet, it has not been committed yet.

When the server executes the FQL, nothing is cached on the client because no actual record data was sent down.  If the client executes the query then server sends *ALL THE DATA FOR ALL THE RECORDS* in the target table, and that is cached at the client as much as the client's cache allows.  It is that sending of all the data from the server to the client that is responsible for the slowdown.  You can actually see this in action by looking at the FMS stats log, the "Network KB Out" counter.

If there are not a lot of record in that target table then the penalty is not high, but it is very linear with the record count.  In that demo file I linked to earlier I have two tables to test with, one with 100,000 or so records and one with 1.5 million.