9 Replies Latest reply on Mar 28, 2014 12:56 PM by LSNOVER

    Execute SQL (The Script Step) ODBC Workaround in a Large Deployment

    james_ludwig

      Greetings everyone!

       

      I've seen a number of posts now about how wonderful it would be if "Execute SQL" used the server's ESS ODBC setup, but none specifically about complete workarounds, so I thought I'd get the conversation rolling. Please feel free to comment/correct/question.

       

      I'm perfectly willing to do the work for scripting the creation/deletion/updating process so that the user just works with the FM system like they always have and our SQL based accounting system receives all the information it needs. I expected to put in the long hours for that kind of functionality. Being able to make this kind of "magic" is one of the reasons I love FileMaker so much. I was not expecting to give all my users a direct access point to our accounting system, exposing it to Excel data mining and such, which it seems FM is directing me to do. I keep expecting to find I'm missing some easy option, because it seems so odd that FM can find and use the SQL data source for any user up until I use one specific script step.

       

      I've managed to avoid using plug-ins so far, and would prefer to keep it up. I'm certain they are safe and stable; my avoidance comes from attempting to limit possible points of failure and/or complication. That being said, it woud be preferable over the security risk of providing data connections for everyone. I could set up a "robot" system with the data connection that checks for triggers to make the records, but that goes back to adding a major point of possible failure.

       

      Anyone out there already come up with a brilliant solution to get around the issue?

       

      Looking forward to responses,

      James Ludwig

        • 1. Re: Execute SQL (The Script Step) ODBC Workaround in a Large Deployment
          wimdecorte

          I'm a little confused... If you have ESS going then you don't need the "Execute SQL" script step for updates and inserts.  You can do it natively in FM.

           

          If you do not have an ESS connection then you can use the "Exeucte SQL" script step on the server through "Perform Script on Server" and use just one DSN that has been set up on the server instead of setting up that ODBC driver / DSN setup on each workstation.

          That's one of the big sleeper functions in FM13.

          • 2. Re: Execute SQL (The Script Step) ODBC Workaround in a Large Deployment
            james_ludwig

            Aha! So it seems my biggest issue is needing to upgrade from 12 to 13 so that I can get access to the server's DSN. We'd been holding out for the first patch, but I'll push that forward as soon as possible now.

             

            With the ESS setup, creating records in the SQL table using FileMaker functions (New Record and Copy Record) caused errors. Specifically, I was getting the message "The target table '<<name of table>>' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause." My assumption is that there are rules in place on the SQL side of the system to protect itself. Using Execute SQL provided whatever was missing from the way FileMaker was creating the record, giving me the power to create a record without the error.

             

            I'm also looking forward to bundling the deletion of records from multiple SQL tables into a single step. For example, the employee system in the accounting software uses a main table and several supporting tables. The main table will not allow deletion if there are related records in the supporting tables, so my SQL query deletes from all supporting tables first and then the main table. Doing this with one Execute SQL step, in the same manner I do it in one query in the SQL Management Studio, should give me the "all or nothing" process I've enjoyed. I have not specifically tested this point yet, since record creation was the top priority (there should be very few cases where deletion is needed), and I had not yet hammered that out to a smooth finish.

             

            Thank you very much for the FMS13 idea, and I'll report back once we have it up and running! I'll also be keeping my eye on this discussion if there are any other questions, comments, and/or ideas.

             

            Sincerely,

            James Ludwig

             

            Message was edited by: james_ludwig to mention the current system is on FMS12

            • 3. Re: Execute SQL (The Script Step) ODBC Workaround in a Large Deployment
              LSNOVER

              James:

               

              As Wim mentioned, the combination of Execute Script on Server and the old Execute SQL Script step provides some great functionality.   I'm a little confused as to why your native ESS inserts and deletes don't work.   Are you running against Views vs. regular tables by chance?  What SQL DB are you using?

               

              There's no problem with not using ESS, it can often be faster, especially calling stored procedures, but your basic ESS functions should work barring some really odd triggers.

               

              Regards,

              lee

               

              Also, FM13 has in our experience been very solid so far.   Especially FM Pro.   WebDirect has been decent as a first release and I think we will see improvements there with patch releases.  If you were burnt by the intial release of FM12, FM13 is magnitudes more solid, IMO.  

              • 4. Re: Execute SQL (The Script Step) ODBC Workaround in a Large Deployment
                user19752

                ESS inserting issue is reported here.

                http://help.filemaker.com/app/answers/detail/a_id/7870

                I heard that this occurs on table with trigger, not only view.

                • 5. Re: Execute SQL (The Script Step) ODBC Workaround in a Large Deployment
                  james_ludwig

                  Hi Lee,

                   

                  User19752 hit it on the nose. The tables I'm hitting have triggers on them.

                   

                  We're using Deltek's "Vision" database for accounting purposes. It integrates much better with our project based business than QuickBooks did, but the functionality of the project portion can't come anywhere near what we're used to in our FileMaker database. As a result, we're pushing the project (and supporting) information up to Vision so that the accounting department doesn't have to duplicate it for their work and our users can stay on the system they've grown to love.

                   

                  Thanks for your input on 13 being solid. I'll be upgrading the server this weekend, and testing the new step all next week. We actually had to wait quite a while before we could move to 12 because of the file extension change, so it looks like we missed the burn.

                   

                  Thanks,

                  James

                  • 6. Re: Execute SQL (The Script Step) ODBC Workaround in a Large Deployment
                    james_ludwig

                    Following up on everything...

                     

                    PSOS is definitely the saving grace for the development. Developers will need to have a mirror of the ODBC connection on their system to make the initial Execute SQL step, but it can then be copied and reused throughout the system as needed.

                     

                    After looking up the chatter about PSOS performance benchmarking, I'm much more comfortable with having these little record creation scripts running as often as they'll need to in order to keep the two systems reasonably synced. I'm also happy to have seen Wim's statement out there about the FMS setting for max concurrent PSOS sessions. That limit of 25 would have tripped me up quickly with over 100 users signed in at any time. Also of note for anyone out there first using PSOS, you must provide the full context/perspective as you would for a scheduled script. I know a lot of other people have said this, but it doesn't hurt to say it again.

                     

                    A huge thanks to everyone for your help in leading myself and others onto the right path. If anyone encounters this thread and would like to discuss my experience/solution/development, I'll be happy to talk.

                     

                    Sincerely,

                    James Ludwig

                    • 7. Re: Execute SQL (The Script Step) ODBC Workaround in a Large Deployment
                      LSNOVER

                      James:

                       

                      So how many users to you have in your system now?

                       

                      What is this limit of 25 for PSOS sessions?  This isn't something I was aware of. 

                       

                      Thanks,

                      Lee

                      • 8. Re: Execute SQL (The Script Step) ODBC Workaround in a Large Deployment
                        wimdecorte

                        Lee: it's a setting in the FMS admin console. The default is 25, max is 500.

                        • 9. Re: Execute SQL (The Script Step) ODBC Workaround in a Large Deployment
                          LSNOVER

                          Thanks Wim:

                           

                          That went right by me.  I'm sure I bumped it up.  Glad it's not a hard default.