8 Replies Latest reply on Sep 14, 2016 4:24 AM by beverly

    Using ODBC of server machine for Execute SQL script step

    nrobinson

      Howdy,

       

      I have a server-hosted Filemaker database as an ETL between our local DBs and the mySQL db that runs our outward facing web-app. 

       

      Quick question here:

      On a server-hosted database with an ODBC connection, is there a way for the ODBC source that is available as an External Data Source to also be available in the Execute SQL step?

       

      Things to know:

      1)  I have a working System DSN connection on the server machine to the mySQL db.

      2)  Using ESS works just fine.

       

      ESS works like a charm, just slower than I'd like.  Going to the layout for the ESS table, making a new record, setting field data and committing does fine in place of the INSERT command.  Ditto for creating a relationship to ESS records and setting fields in place of the UPDATE command.  

       

      The reason I post this question is:

      1) Execution is a good deal faster when using SQL commands directly sent to the mySQL db using Execute SQL script step and

      2) It seems weird that when Execute SQL asks for an ODBC source, it only lists your client machine's ODBC sources and not the server's ODBC sources when the Filemaker db is hosted on the server.

       

      Thanks in advance,

      Nat from Santa Cruz, CA

        • 1. Re: Using ODBC of server machine for Execute SQL script step
          beverly

          short answer: Yes

           

          HOWEVER... my experience, has been to have TWO DSN (sources) set up for this kind of thing. One for ESS and One for the Import & Execute SQL script steps.

           

          And you may need to set up a DSN on any machine using the script steps instead of using the FMServer set up.

           

          beverly

          3 of 3 people found this helpful
          • 2. Re: Using ODBC of server machine for Execute SQL script step
            nrobinson

            Thanks Beverly.  Judging form the documentation, I don't think that Execute SQL script step's ODBC source was meant to use the server's DSN.

             

            Setting up a separate Execute SQL DSN on each machine would totally work.  However, the group using this ETL db might be upwards of 30 and would want to access it from work, home, and/or laptop.  Just can't install a separate DSN on each possible client machine.  If I was working with a smaller team then I'd consider.

             

            I'm still curious why those ODBC sources are client based even when the file is hosted by the server. 

             

            Thanks for the suggestion.  I'll mark it correct in a bit if nobody else has any miracle solution to offer and my hack doesn't work.

             

            I'm going to try something:

            1) Open the DB from the Server machine

            2) Change the Execute SQL script step to use the ODBC DSN from of the Server Machine

            3) Run the Script as PSoS

             

            Hopefully this doesn't crash the server.  Fingers crossed.

             

            Nat

            • 3. Re: Using ODBC of server machine for Execute SQL script step
              FabriceNordmann

              Execute SQL script step is now server compatible, which means you can perform scripts on server (PSoS or scheduled scripts) that use this step. So you only need to install the DSN on the server (and on the developer client machine, with a matching name DSN).

              It's a very interesting, not so well known evolution. We use it a lot to talk to external data sources, but also on FileMaker itself. Very powerful, straight forward, transactional…

              2 of 2 people found this helpful
              • 4. Re: Using ODBC of server machine for Execute SQL script step
                beverly

                Thanks for the update, Fabrice. I missed the evolution. I think because ESS became the favorite and those script steps became the less-used methods.

                 

                Sent from miPhone

                • 5. Re: Using ODBC of server machine for Execute SQL script step
                  FabriceNordmann

                  which is a real pity because ESS is only good at 1 thing and 1 thing only: displaying 'almost-live' external data on layouts.

                  Importing using Import records from ODBC or an ESS table are two completely different things in terms of performance.

                  And SQL allows a lot of things to ease your processes, remain naming independent, create combined unique keys on the fly… a world has opened to us and no one is talking about it

                  1 of 1 people found this helpful
                  • 6. Re: Using ODBC of server machine for Execute SQL script step
                    nrobinson

                    Thanks Fabrice.

                     

                    Yeah so it did work to create the ODBC DSN from the Server Machine!  I just VNC'd into my server, opened the DB in Filemaker Pro and set that Execute SQL script step to use the server's DSN.

                     

                    I removed the DSN from the client machine to test it and ... voila!.  it worked great as PSoS.  Very sneaky evolution as you put it Fabrice.

                     

                    Things I need to watch out for:

                    1)  Can only run the Execute SQL script step as PSoS now.

                    2)  Don't mess with that script step because once i change the ODBC on it, i'll have to VNC into the server again to change it back.

                     

                    Putting a DSN of the same name on at least my own client machine is a good idea so that i can run some scripts locally when need be.

                     

                    Thanks for you input both of you, glad to know i'm not the only on thinking of these things!

                     

                    Nat

                    1 of 1 people found this helpful
                    • 7. Re: Using ODBC of server machine for Execute SQL script step
                      wimdecorte

                      nrobinson wrote:

                       

                      2) Don't mess with that script step because once i change the ODBC on it, i'll have to VNC into the server again to change it back.

                       

                      As long as you have a DSN by the exact same name on your developer workstation, you can create / change / test the script step locally.  When it gets executed on the server it will just look for the DSN by name.

                      1 of 1 people found this helpful
                      • 8. Re: Using ODBC of server machine for Execute SQL script step
                        beverly

                        Ah yes! now I recall why I put the DSN on my machine too.

                        double-warning. be sure all* things are equal when setting up the DSN on server and desktop, then you can revise the script locally (on hosted file) and the PSoS or server scheduled script will work.

                         

                        * all - does not mean same driver, as I can set up MAC desktop to speak to SQL and Win FMServer to speak to SQL (different drivers)