Jump to content
Sign in to follow this  
losferwords@outgun.com

Does FQL actually work?

Recommended Posts

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.

 

 

Share this post


Link to post
Share on other sites

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 by comment

Share this post


Link to post
Share on other sites

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 by Kevin Frank

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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!

  • Like 1

Share this post


Link to post
Share on other sites

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

beverly

Share this post


Link to post
Share on other sites

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 by ggt667

Share this post


Link to post
Share on other sites
On 11 mei 2017 at 2:54 PM, losferwords@outgun.com 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

  • Like 1

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

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?

 

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites
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! 

Share this post


Link to post
Share on other sites

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? 

Share this post


Link to post
Share on other sites

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.

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By sal88
      Hi all
      Apologies in advance if this is a vague question, or if it's been extensively documented, but I could do with some basic advice on moving filemaker tables to SQL. In terms of database development, I have only really known FileMaker.
      We have a 60 table, 25 user FileMaker database which takes care of most if not all of the company's operations. 
      There is now talk of allowing other platforms (such as PowerApps/PowerBI) access to the data. One such reason is other people are interested in making apps/applets that serve specific functions. This would be simple if the data were held on a SQL database.
      So if all the FileMaker tables were to be moved to SQL, what sort of a task is that in terms of keeping the existing FileMaker gui functioning?
      Or. is there a simpler way to grant other platforms access to filemaker tables?
      And of course, what are the downsides?
      Thanks
    • By schotja
      Looking for someone to help setup a simple connection to a sql database on cpanel for our Ranch to connect to cattle listings on our website..  I'll be using fmp12 server or content for now just establishing a connection via fmp12 client on windows 7 pro machines.
      Specifically I am having trouble establishing a DSN connection from any of my machines and have opened ports / given access in cpanel to my static IP.
      I have also updated MS ODBC drivers on one of the machines to see if that made a difference.  It may be something quick for an expert with experience or it might end up being a gremlin.
      I have set up some test databases for troubleshooting purposes and willing to do alot of the legwork if i'm able but also willing to pay someone for their expertise as well.
      thx.
    • By mike13
      I am in the process of connecting an existing FileMaker database to a SQL server that is behind a web interface.  I wanted to know if there is a way to send a command from the web interface to kick off a sync.
      For example, a user opens a record on the web, makes some changes, hits a save button.  
       How do I get that "Save" to trigger a sync?
       
      And can I get it to sync that single record or do I need it to sync all records that have changed?
      Thanks in advance,
      Mike
       
    • By Eli Walker
      Hi all,
      I'm trying to use execute sql to populate a field with the sum of all the values in a field of related records. All I get is a ? so obviously something is wrong with my syntax but I cannot figure it out. Any help much appreciated! This calculation is occurring within the 'Collections' table.
      Let ( [
      query = "
      SELECT SUM(S.Volume.used)
      FROM \"Straws_for.entry\" AS S
      JOIN \"Animals.to.Collections_join\" AS A
           ON \"S.fk_a.to.c.join\" = \"A.pk_join\"
      WHERE \"A.fk_collection\" = ? " ;
      collection = Collections::pk_collection ;
      result = ExecuteSQL ( query ; "" ; "" ; collection)
      ] ; result )
       
      Also, after some reading on similar threads I see people suggest not using this function in a calc. field... if there's any better way of doing it I'd love to hear it. Thanks for the help, really appreciate it.
    • By sal88
      Hi all
      I'm trying to export my filemaker records to my external sql database via the execute sql script step but am getting a number of errors.
      I can see my external ODBC source and can generally write to it (I can go to the respective layout and click 'new record'). My query basically as follows:
      "INSERT INTO dbo.Log ( dbo.Log.log_id, dbo.Log.log_case_idf, dbo.Log.Labour_TOTALS_Labour_Cost, dbo.Log.Labour_TOTALS_Item_Sale) SELECT  Log_Log.log_id, Log_Log.log_case_idf, Log_Log.Labour_TOTALS_Labour_Cost, Log_Log.Labour_TOTALS_Item_Sale, FROM Log WHERE Log_Log.Log_Type = '2'" My first question is: is it even possible to export to an odbc source with a 'insert INTO SELECT' query?
      Many thanks
×
×
  • Create New...

Important Information

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