I have three tables in my database that all use this ExecuteSQL statement so I thought that I would just put it into an Custom Function, but I seem to be doing something that violates the syntax rules. What am I doing wrong(pic)?

http://pctechtv.com/show/exsqlgb.png

Hi pctechtv, 

 

I moved your topic from "Custom Functions" to "FQL or FileMaker Query Language”. Because the Custom Function Topic is for question about the Custom Function.

 

This is the topic area we have devoted to SQLExecute Function.

 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.

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.

That is good to know, when you say serve up and download does this just apply to FileMaker server? Thanks

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.

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 November 10, 201312 yr by LaRetta

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.

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.  

 

added blue, my apology

Edited November 10, 201312 yr by LaRetta

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...