Jump to content

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
comment    1,381
Posted (edited)

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
Kevin Frank    15
Kevin Frank    15
Posted (edited)

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
beverly    22

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
beverly    22

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

beverly

Share this post


Link to post
Share on other sites
ggt667    7
Posted (edited)

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
typewriter    1
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
Wim Decorte    455

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
typewriter    1

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
Wim Decorte    455
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
comment    1,381
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
typewriter    1
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
typewriter    1

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
Wim Decorte    455

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

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


  • Similar Content

    • By Hrcap
      Greetings All
       
      I am looking to add a line into the following code into the 'prepare payload for client' section to select only records that match the following criteria
      I have a variable set called: $additional_settings The field that needs to match with $additional_settings is called _kf_uuid_companys the code that i have already is as follows, i need to add a line into this code to pick records where the field _kf_uuid_companys matches $additional Settings.
      Any help would be greatly appreciated:
       
      "SELECT "
      & $dyn_sql & ", '"
      & $$record_delimiter & "'"
      & " FROM \\"" & $sync_table & "\\""
      & " WHERE " &
       
       
       
      // Exclude records flagged for sync exclusion.
      "( COALESCE ( BS_Exclude, 0 ) = 0 ) " &
       
       
       
       
      // If the client is merging new/updated data with data already on the device...
      // Only include records that have been added/updated since the last pull...
      // And only include data that they did not just push (i.e. no "round tripping!")...
       
      If ( ( $$sync_method = "Merge" )
      and
      ( $last_pull_utc > 0 ) ;
       
      "AND ( BS_UTC_Time > " & $last_pull_utc & " ) " &
       
      "AND ( COALESCE ( BS_Device_ID, 'X' ) <> '" & $client_persistent_id & "') "; "" )
    • By sal88
      Hi all
      I'm trying to find the Client that has the most Job time over the last 30 days, divided by the number of seats that they have. The tables are:
      Clients
      Client_ID, Seats
      Jobs
      Client_IDF, Job_time, Job_Date
      The best I've managed so far is to find the distinct Client_IDF from all Jobs:
      ExecuteSQL ( "SELECT DISTINCT j.log_client_IDF FROM Jobs J WHERE j.Log_date >= ?"; ""; "";  $date_30_days_ago) And then loop through the list and perform the 'divide by seats' calculation for each line
      Or to do something similar but from the Clients table:
      ExecuteSQL ( "SELECT SUM (j.labour_units), c.Name FROM Clients C JOIN Jobs J ON j.log_client_IDF = c.Client_ID WHERE l.Log_date >= ?"; ""; ""; $date_30_days_ago) But no joy in either case when it comes to actually performing calculations within SQL.
      Is this possible in FM SQL?
      Many thanks
    • By Mark Stuller
      Hello
      We are going to take the plunge and setup a sync between our Oracle tables and 'shadow' FileMaker tables and we're seeking input.
      One thing we are wondering about is a 'one-way' sync. We don't want changes to be sync'd back to Oracle. The bigger issue is that we don't want deletions in the Oracle table to be deleted in the FileMaker table. There often are children to these records we don't want deleted or orphaned.
      As well, are there any gotchas we should consider? I've read the Advanced topics and feel mostly comfortable but you never know what you don't know!
      Thanks, Mark
    • By scain101
      I need some help with ExecuteSQL.

      I have a TO “Sales” that is related to Estimates, Inspections, and Proposals via ID_SALES:id_sales.

      I created some simple ExecuteSQL calculations that work on Estimates and Inspections, but the same calculations will not work with the related Proposals TO. 

      ·         I know that the relationship is good because I can get the SUM and COUNT from Proposals with a basic calculation (not ExecuteSQL).

      ·         I know there is not a problem with the name on the related Proposals TO (like a space between words). I've copied and pasted the working calculations and I have started from scratch, and neither works.

      This statement works:

      ExecuteSQL ( "SELECT COUNT

      (ID_ESTIMATES) FROM TO40_ESTIMATES

      WHERE ID_SALES = ? “ ;

      "" ; "" ;

      TO13_sales_ESTIMATES||id_sales::id_sales )

      ---

      This statement does not work:

      ExecuteSQL ( " SELECT COUNT

      (ID_PROPOSAL) FROM TO26_PROPOSALS

      WHERE ID_SALES = ? ";

       "" ; "" ;

      TO13_sales_PROPOSALS||id_sales::id_sales)


       
      Any help would be appreciated.

      SC

    • By Ben Moore
      I am trying to create a list of dates from multiple tables to create a gantt chart.  My projects have Shipping dates, Installation dates and task item dates.  Each of which come from their own tables.  Is there a good way to import all of these dates into one table related to the project, each with it's own record of Start date and end date.
      I'd rather have:
      Project | Item | Start_Date | End_Date|
      than:
      Project | Install_Start_Date | Install_End_Date| Ship_date | Ship_Arrive_Date | Task_Start_Date | Task_End_date
×

Important Information

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