Jump to content

ExecuteSQL Custom Function


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

Recommended Posts

 I seem to be doing something that violates the syntax rules. What am I doing wrong(pic)?

 

Exactly what the error message says: the expression "Documents_Information" is not a field reference or a variable or a function or an operator - therefore Filemaker doesn't know how to treat it. The only way you can include it in a formula is to make it a text constant - i.e. enclose it in quotes. However, unlike a referenced field, a table name passed as a text constant will not update if the table is renamed.

 

Keep in mind that Filemaker is NOT a SQL database. It can respond to a limited set of SQL queries, but it is not a native operation and you'd be well advised to use this feature sparingly, if at all.

Link to comment
Share on other sites

Another aspect of ExecuteSQL() is that, if you perform a native find for records, FM will pre-fetch 25 of the found set at a time to serve up.  However, ExecuteSQL() will require that the entire found set be downloaded from the server all at once, even if thousands of records, so it can produce a heavy hit.

  • Like 1
Link to comment
Share on other sites

FileMaker Server will try to execute the SQL query on the server so you will only get the result.  If that result is a record set of thousands of records then obviously that is what FMS will have to send you.

 

There is one exception to FMS doing the query for you: if you have an open record in your session for the target table then FMS will send you the whole table so that your client can perform the query including the results of your open record.  That is usually not a problem unless there are more than say 10,000 records in the target table.  And the wait becomes exponentially longer the more records in the target table.

 

An example:

 

Table with 1,000,000 records.  You construct a SQL query that results in 1 record.  You have no open records in the target table (if other people have open records, that does not matter) --> FMS will do the query and return you the data for the one record.  And it will be fast

 

Table with 1,000,000 records.  You construct a SQL query that results in 1 record.  You DO have open records in the target table (if other people have open records, that does not matter) --> FMS will send you the data for ALL 1,000,000 records and your local copy of FMS will execute the SQL query.  It will be very slow.

  • Like 3
Link to comment
Share on other sites

So Wim, might it be wise to switch to a table with no records or a layout with no fields and no displayed calculations or conditional formats (or even tooltips) so no records are fetched and perform ExecuteSQL() there instead, even behind a Freeze Window?

 

Also, ExecuteSQL() downloads all fields for a record (just like FM) and not just the fields requested (added, I know you know this Wim but I thought I'd mention it since not everyone knows it).

Edited by LaRetta
Link to comment
Share on other sites

Hi L.,

 

Switching to a table with no records: not really IMHO.  The big benefit of ExecuteSQL that it is context free, so switching to a set context first feels like negating that benefit to me.

The only thing to do is to make sure all your records are committed in your target table before asking ExecuteSQL to fetch data for you from a large table.

  • Like 2
Link to comment
Share on other sites

Thank you, Wim!!

 

I have noticed inconsistent speed results with seemingly similar queries and I wonder if this might explain it - simply that a record was not committed and I was querying a larger record set at the time, even if I'm on a single-record table?!   And how might that translate to calculations?  If target table has any record ( even if committed ) and it contains a calculation with ExecuteSQL(), then all records are downloaded as well if the field is displayed (being unstored I would assume it would only calculate if displayed).   

 

I am not meaning to infringe on this thread, pctechtv, but it all seems pertinent and important.  I hope I'm forgiven but when we get a chance to ask under-the-hood questions, I can't help myself.   :crazy2:

 

added blue, my apology

Edited by LaRetta
Link to comment
Share on other sites

I haven't used ExecuteSQL in calculations, only in scripts, so I don't know how it would behave differently.  The likelihood of the SQL query firing when the record is not committed is probably higher than in the more controlled environment of script execution...

Link to comment
Share on other sites

This topic is 3790 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.