3 Replies Latest reply on May 7, 2012 3:58 AM by LSNOVER

    Ominouse Omission in FMS 12?

    user12837

      The most important defects in FMS 11 ESS were, IMHO,

       

      - SQL INSERT triggers break when executed as a result of a FM operation
      - Execute SQL is unable to use ODBC connections on the server

       

      It appears that FMS 12 addresses neither of these issues. Does this mean that ESL was merely a one-time marketing gesture, and will remain too lobotomized to preclude serious integration with real database servers?

       

      - Tom

        • 1. Re: Ominouse Omission in FMS 12?
          LSNOVER

          Hi Tom:

           

          Can you elaborate on point 1?  I use many triggers in my ESS SQL database in conjunction with Filemaker 11 and 12.

           

          Point 2, you are absolutely correct.  I have been petitioning FMI about this since the release of ESS.  More voices are needed to get them to put the time into this.  I believe they were very fixated on the UI enhancements with V12.   It seems many of the features I care most about are towards the back of the list.  ESS, IWP and real enhancements to the reporting capabilities of Filemaker.  We just have to keep beating the drums and hope things will improve going forward. ;-)

           

          Regards,

          Lee

          • 2. Re: Ominouse Omission in FMS 12?
            user12837

            Hi Lee,

             

            In SQL,

             

            SET ANSI_NULLS ON
            GO

            SET QUOTED_IDENTIFIER ON
            GO

            CREATE TABLE [dbo].[InsertTriggerTest](
            [Datum] [int] NULL,
            [Comment] [nchar](10) NULL
            ) ON [PRIMARY]

            GO

            CREATE TRIGGER [dbo].[trig_Insert] ON [dbo].[InsertTriggerTest]
            FOR INSERT
            AS

            SET NOCOUNT ON

            UPDATE InsertTriggerTest   
               SET Comment = 'hooray'  -- this is vaccuous, of course.  In reality you would be inserting a record into a log table, for example.
             
            GO

             

            In FM, make a relation to InsertTriggerTest and insert something.  I get this:

             

             

            InsertTriggerError.jpg

            I work around it by creating an AFTER UPDATE trigger and using a two-step FM process (inserting a record, then modifying it), but this kind of kluge always obscures things for maintenance people, and it leaves something which might break if FileMaker ever gets serious about SQL triggers.

             

            - Tom

            • 3. Re: Ominouse Omission in FMS 12?
              LSNOVER

              Tom:

               

              I'm not up to date with my SQL Server triggers, but does this work with other applications? 

               

              What does the Set NOCOUNT option do? 

               

              Sorry to take so long to reply.

               

              Regards,

              Lee