Nearly 5 years later, I still get emails about that post, and in particular about this:
The “Execute SQL” script step allows you to call stored procedures, but it doesn’t provide you with a way to access the output from them. (There are ways to work around this. If you are interested in how I’m doing it, drop me a line.)
I thought I’d write up a brief follow-up to that post, and discuss the options that are available to you if you want to get data into FileMaker from stored procedures. There are several techniques available, and the best one depends on what you’re trying to do.
You can use ExecuteSQL and a dynamic SQL statement to call a stored procedure that manipulates data. However, with ExecuteSQL, you won’t have access to any record sets or output parameters that are returned — and this drives me crazy. If all you need to do is call a stored procedure, and don’t need access to the results, then this might be an option for you.
With the “Import Records” script step, you can call a stored procedure (and a dynamic query to do so) and import the results. I’ve used this approach for several years, and while it is reliable, I often run into weird issues involving how the ODBC driver connects to the database. For example, when calling a stored procedure, you might find that during the “handshaking” that the ODBC driver does with the database, it actually calls the stored procedure multiple times. The results can be… Well, let’s just say that things can get ugly.
The ESS / Trigger Technique
The most reliable technique that I’ve found (which is also the most complicated) is this:
• Create a table in the SQL database, with columns for the input and output parameters of the stored procedure. Let’s call this the “interface table.”
• Put a trigger on the table that fires on insert, and have it store the return values back into the row.
• Use ESS to add a row to the interface table, which will in turn cause the trigger to fire.
• After adding the record, use ESS to look at the row, and get the return values.
So you have a lot of options… Again, it just depends on what you are trying to do.