5 Replies Latest reply on Apr 11, 2013 11:48 AM by jormond

    Is it possible to use ExecuteSQL as part of record rollback

    MEC

      Hi all,

       

      I am trying to build a better mousetrap. I have a layout the users use to edit only, and it has related data in it from 4 TO's.

       

      I read somewhere that ExecuteSQL can be used to gather ALL of the data and field names on a layout to be placed in a text field that I can later use to "Rollback" if the user decides to abort their edit.

       

      Is this possible? If so, can someone please point me in the right direction for putting the statement together?

       

      Much appreciated!

        • 1. Re: Is it possible to use ExecuteSQL as part of record rollback
          taylorsharpe

          Yes... and it'll take a lot of work and planning and isn't as simple as you might think.  The much easier way is to use CNS Audit plugin or fmDataGuard plugins.  These plugins use "Execute SQL" to do this for a long time (not to be confused with ExecuteSQL).  If you want to go for it yourself, great.  But my personal experience is that if you want full transactional recordings and rollback capabilities, these plugins are the easier way to go. 

          • 2. Re: Is it possible to use ExecuteSQL as part of record rollback
            jbante

            I agree with Taylor that plug-ins are the easier approach if the constraints of your solution allow for them. Plug-ins can make SQL Insert and Update statements (the ExecuteSQL function cannot), which makes things easier. Ray Cologon's UltraLog is an alternative method that works without plug-ins or ExecuteSQL.

             

            If you're planning to build your own plug-in-free method along similar lines to UltraLog, I'm not convinced that ExecuteSQL necessarily leads to a real benefit. ExecuteSQL can be used to get all fields for a given table by querying the schema metatables, but I wouldn't necessarily want to make an audit log based on that. I'd want to explicitly identify which fields to include so I wouldn't audit globals, calculations, or the audit log data field; and I'd want to identify each table's primary key field(s), which ExecuteSQL can't tell you. I suppose ExecuteSQL could be used to retrieve field data once you have a list of fields to audit, but that's only really helpful if you're calculating from a context other than the record being audited — I don't seem to be thinking far enough outside the box right now to imagine how an audit log solution might work well that way.

            1 of 1 people found this helpful
            • 3. Re: Is it possible to use ExecuteSQL as part of record rollback
              LSNOVER

              An easier way is to implement something like Todd Giest's transactional control method.  It all works in the scope of Filemaker.   You can search around here for info. and I believe he has a web site.  Basically you are using a "transaction record" to tie everythign together and using explicit commits and/or rollbacks for transactional integrity.

              1 of 1 people found this helpful
              • 4. Re: Is it possible to use ExecuteSQL as part of record rollback
                MEC

                Thanks all.

                 

                I have been using Ray's Ultra Log for auditing, and was looking for something a bit more manageable for rollbacks. I've read Todd's article, and may go with that for this need. Also, I prefer not to use anymore plugins than I need to.

                 

                Thanks again to taylorsharpe, jbante, and LeeSnover.

                • 5. Re: Is it possible to use ExecuteSQL as part of record rollback
                  jormond

                  The one really nice thing that a SQL plugin can do that is used by CNS and fmButler, is the ability to write the changes to an unrelated table.  Hopefully, that type of functionality will come standard in a future release of FM.

                   

                  Although, here is the really funny thing I know about plugins.  If enough people like them, FileMaker will add the functionality.  And often, it is actually the plugin, just baked into the FM code and licensed to who originally owned the plugin.