1 Reply Latest reply on Jan 5, 2012 1:39 PM by LSNOVER

    Run SQL Stored Procedure In ESS Table?


      in the archives there is a message from lsnover@tplus.com which says


      I use a utility record for each user to "Trigger" the call to the Stored Procedure. The record just has the name of the Stored Proc to call, along with generic fields to hold parameters that you want to pass to the stored procedure.

      The only major caveat is that since a record is being used to Trigger the stored proc, it is part of a transaction, so you can not perform Commits or Roll Backs in the called stored procedure. Also, if you have a parameter to get errors back, it will roll back without any new information, if the transaction rolls back.


      I don't understand the text highlighted in red. Does it refer to a FileMaker transaction of some sort?





        • 1. Re: Run SQL Stored Procedure In ESS Table?

          Hi Tom:


          Sorry, I've been offline for a bit.


          The part in red refers to a SQL transaction.  So if you have a stored procedure you want to call via a Trigger (this is a Oracle Trigger not a FM Script Trigger), that procedure can not Commit or Rollback any changes to the database in the scope of the stored procedure.  When you commit the change to what I call the "Utility" record (the record you use to cause the SQL Trigger to execute) from with in Filemaker, any changes that the stored procedure made will be committed at the same time.  Same applies if you "rollback" (Revert in Filemaker Lingo). 


          There are some ways to get around this limitation but they are database specific, i.e. in Oracle you can make a Stored Procedure that goes off an runs independantly of the procedure that called it (called an Autonomous Transaction).  You can also do tricky things like scheduling a Job in the database that will run immediately.  There are potential side effects to these techniques especially if you are making changes to records you or other users are actively working with in the scope of the Filemaker interface.


          Hope that helps.  If you have more questions, please let me know and I'll try to clear up any confusion.


          Happy New Year!

          Lee Snover