I believe that you need the ExecuteSQL function, not the Execute[space]SQL script step. The function is good for SELECT statements on FM tables (and without TO's).
I want to add to my response to 28222 that "without TO's" means that you don't use the TO names when using SQL. Use the table names. Watch for spa ces.
Thanks planteg and David Moyer for the answer. I wish I could mark 'correct' for both, but the system will allow only one!
I can now go ahead and read the documentation (I felt lost when I noticed the syntax being different and attributed that to difference in versions instead of being two different use cases)
Any advise on performance? I am actually very fluent in SQL but don't want to go overboard and slow down my client's performance.
For all practical purposes, table names do not exist in any FileMaker function.
ONLY TO names are usable.
However, the context of a table occurrence never applies in ExecuteSQL.
It's gotta be on the graph.
yes ... I think that it's good standard practice to include a TO representing the table all by itself. I've done this in the past, but forgot, which is true.
1 of 1 people found this helpful
So much information, so much to clarify!
A table on the relationship graph (FM or linked External Source) is named with an "alias" and we call them Table Occurrences.
1. they (TOs) must be used with the ExecuteSQL() function in the FROM clause
2. the alias name "on the graph" (TO) can actually be the original name of the defined table
3. only the SELECT statement is used with ExecuteSQL()
4. the result of the query is TEXT (even if a number is returned) and placed into a text field or variable.
A table in SQL is the table name (as defined in the Manage External Sources), unless you specifically alias it in your SELECT query.
1. the Import script step is used (via ODBC) to query ( SELECT ) the external SQL data using the name defined in Manage External Sources
2. the Execute SQL script step is used (via ODBC) to query ( INSERT, UPDATE or DELETE ) the external SQL data using the name defined in Manage External Sources
3. IF you have an approved SQL source and ODBC driver, you can directly connect as ESS, such that the external table on the graph (which can also have an alias/TO name) and this ESS shadow table can be accessed as if a native FM table/TO (native finds, sorts, etc.) and does not use the SQL queries by the user
Another guru I met thanks to my question - hi Beverly!
I sincerely appreciate the support and sharing - this is accelerating my Filemaker learning curve tremendously.
I should probably start another thread for my follow-up (but decided not to because the whole context ties in nicely with this follow-up question)
Is it good practice to use Execute SQL (via ODBC) on Filemaker, given that this approach allows for the use of full breadth of SQL commands? Most of the update, insert, and such types of commands could just as easily be done in scripts but I wanted to get the Filemaker gurus' views on this.
On a related note there anecdotal evidences that point to performance degradation using one vs the other (with respect to how it's implemented in Filemaker Pro 15)? Are there further perofrmance nuances to consider when implementing with Filemaker Server? My end-user is a 5-employee company where there would be entering high-volume of orders during a fixed time window every day (e.g. 4-8 AM everyday.) So don't want to annoy them if some queries(likely to interactive) will take seconds rather than microseconds.
Thanks again to the wonderful FM community!
As noted by Bev, this NOT necessary. No separate "original name" TO needs to exist. Any TO, no matter what it's name, can be used in the ExecuteSQL statement, as long as it refers to the correct base table.
IF your users are well versed in SQL statements, then allowing them to write them is ok. Typically I will SELECT (import) so I have rows of data on which to write the SQL UPDATE or DELETE. To INSERT, of course the table and columns need to be known and here the imported data may help.
Can you define "using one vs the other"? Do you mean the difference between the script steps (via ODBC) vs the ESS 'view-into-the-SQL-table"?
The size of the SQL database, of course is a factor. If the SQL database is not on the "approved list" and you have a driver that works, you cannot use ESS, only the script steps. Being able to join and more features (as the drive allows) in your SQL queries would be a distinct speed boost over relationships on the Graph between FM and SQL or SQL & SQL. There will still be limitation of some of the column-types in SQL.
And another thought might be to use a SYNC method to allow rapid entry IN FileMaker (similar table structures) and then push to SQL with the script steps.
well that's embarrassing that that answer was marked as correct. I wish I could edit it.
Thanks for clarifying Beverly.
The SQL (external source) Table names are as they are in the Manage External Data Sources.
If they are used for ESS, they can be aliased (TOs) on the graph.
The ExecuteSQL() function only uses TOs, whether internal FM sources or External SQL Sources.
You were brief, David, not necessarily incorrect.