No, you can't return a value from a SQL query using the Execute SQL script step. If your query can be expressed as a SELECT, consider using the Import Records (from ODBC data) script step instead.
Well, you could use a plugin.
e.g. with MBS Plugin, you can query selects to the FileMaker database:
or use plugin functions to connect to a SQL Server directly:
1 of 1 people found this helpful
First, deninger, lets clarify what you are trying to do.
1. Execute SQL  - Script step is for INSERT, UPDATE & DELETE when connected to an ODBC source through a driver and DSN set up. You communicate with the External SQL using SQL calls to perform the above statements. Note that IMPORT , script step, is for the SELECT calls to the same source. At no time are you "live" connected to the SQL db. see:
Execute SQL - note the space between the two "words" for the script step.
2. ESS is a way to "live connect" to select SQL dbs (with permissions, of course) through a DSN set up and no SQL calls are needed. You simply place the source on the Relationship Graph and make finds, edits, deletes as you would FMP native tables. Read more about ESS: FileMaker Software Deployment, Business Software Solution - FileMaker
3. ExecuteSQL() is a FUNCTION that is used to query (SELECT only) any table/table occurance whether FileMaker or ESS source. ExecuteSQL
for more information about this function, see my reference:
The Missing FM 12 ExecuteSQL Reference | FileMakerHacks (get the PDF as it's more complete than the blog and get the example file). There are links to other articles about ExecuteSQL and even links to SQL plug-ins for FileMaker. Yes, there are a few new features in FM13 for this function.
4. Also look at this documentation:
So.... it seems like you need to research the ESS and the function rather than the script steps. Performing the ExecuteSQL on an External SQL source may be overkill. But yes, you can make an ExecuteSQL() - SELECT only - call to and External SQL source and you can "parse" the text in a way that may be more useful. Again, it really depends on what you need to do with it. Perhaps the IMPORT script step would do the same thing more elegantly?
Just to be clear, I already know and understand ExecuteSQL(), ESS, and IMPORT(), and I leverage all three heavily in my projects.
In my projects, I am working with a third party back-end hosted on SQL Server. There are some calculations that I need made that would be MUCH easier to perform in SQL on the host DB because I don't actually need the data in FM.
ExecuteSQL() can be run on an ESS connected datasource and DOES work, it can be very slow thru the ESS channel, though.
consider an ESS table DISPENSE:
$mySQL= "SELECT SUM (dispense.DAYS_SUPPLY)
FROM ITEM LEFT JOIN NDCGPI ON ITEM.NDC_CODE = NDCGPI.NDC_CODE, DISPENSE, PATIENT, RX
WHERE PATIENT.PATIENT_ID = RX.PATIENT_ID
AND RX.RX_ID = DISPENSE.RX_ID
AND ITEM.ITEM_ID = DISPENSE.DISPENSED_ITEM_ID
AND PATIENT.PATIENT_ID = ?
AND NDCGPI.GPI_CLASS_CODE = ?
AND NDCGPI.GPI_GROUP_CODE = ?"
ExecuteSQL ( $mySQL ; "" ; "" ; $patientID ; $gpiClass ; $gpiGroup )
This is very slow over ESS
My desire is to be able run this on the SQL Server and just return the result. Obviously Execute SQL () is not designed for this as is appears to be limited to just INSERT, UPDATE and DELETE type calls.
I will look into the plugin
I just recently completed a project that integrated with MS SQL where we used this. Execute SQL was used to run a stored procedure... even better, with FMS 13, used with the perform script on server functionality so this could run from users on ipads.
There is nothing returned besides an error code when using the Execute SQL function. However, ESS to the same datasource allowed me to check what I needed to.
Hope this helps.
my reply by email (above) appears to be empty.
What I posted was that the info for d, was for all readers. And I also asked about views. I see that Mike Duncan mentioned "stored procedures". Calling views and stored procedures may be different. A view could narrow down your found set and even have joined results. It's like calling a table, but a query is set up on the SQL server to return a differrent set.
You can run a Perform Script on Server and when you exit that script, make sure the last script step is the Exit Script Step and click the option on the Exit Script Step and you can have the exiting script return the value of the variable you put in the Exit Script Step to the first script. Then in the first script, you'll have a Get ( ScriptResult ) script step to make use of the value of the SQL call on the server side script. Is that what you are asking for?
If it is, this is what I do a lot to get the performance of server side processing and only returning the result to the client application. It really does speed things up when used properly.
Again, the content does NOT seem to be appearing when reply-by-email. matthew_odell - something to research?
Here's what I said:
Here's a good explanation of the differences in Stored Procedures and Views (to SQL sources):
also this from Mike is excellent:
"There is nothing returned besides an error code when using the Execute SQL function. However, ESS to the same datasource allowed me to check what I needed to."
I'm sure he meant the "ExecuteSQL() function" - the spelling is the problem with this function vs. script step! The same can be said for a slight advantage at times to use the "Execute SQL " script step at times (for INSERT, UPDATE, DELETE) and the "Import " script step (for SELECT). They can all have advantages and disadvantages. That's why it's good to understand how they all work.
And sometimes plug-ins are the necessary connection for SQL<=>FM...
Yes, I meant the "Execute SQL" script step. Thanks for the clarification
Does anyone have an example script of how to create a view or table or layout using ExecuteSQL() ?
i.e. I want to query an existing table, and use a button-triggered script to fetch, e.g.
CREATE TABLE "CUSTOMERS" (INT PRIMARY KEY "CUSTOMER_ID")
INSERT INTO CUSTOMERS (CUSTOMER_ID (SELECT DISTINCT CUSTOMER_ID FROM TRANSACTIONS)
So a script to create a table from WITHIN existing FM tables only, populate it with a select statement and display it somewhere (portal?)
I'm new to FM - my experience is in SQL Server, Oracle, Teradata and C#.NET
Ideally, my client wants a layout with an edit box into which you can type:
SELECT x from y WHERE etc....
and that gets assigned as a variable, e.g. $SQL for use in a script that then puts the search results in a new table, or simply displays is on a layout.
FM is very user friendly, but for people used to simply writing code (C/SQL style) it is pretty awkward, especially as I don't have Advanced with the debugger facility.
Anyone have any solutions to hand?
I just want a layout, like in TOAD or SQL Server, where the user can write their own SQL select statement, and if it is syntactically correct, it displays data. If not, that's their problem
Thanks in advance
Daniel, let's be clear:
1. ExecuteSQL() is a function that works on tables IN FileMaker (as seen on the graph):
The result is TEXT and cannot be use to change data or schema in FileMaker
2. Execute SQL is a script step that works on EXTERNAL SQL sources to INSERT, UPDATE, DELETE
3. FileMaker can be used as an ODBC/JDBC source:
Perhaps the last three links will help the most.
You could pack your query into a stored procedure or a function on the SQL Server and call it with the ODBC import script step into a global field as mentioned in this thread: