14 Replies Latest reply on Apr 21, 2017 8:02 AM by beverly

    Confusion running ODBC connected to a hosted remote fmp12 file

    wintertj

      My fmp12 file is running on a shared hosted remote server. The provider added a DSN to a MySQL database for me. I added the DSN under manage external data sources. I then went to the manage database relationships tab, chose add ODBC data source, selected the DSN add have been able to add several ODBC tables to the relationship graph and work with them at will for a couple weeks.

       

      Tonight I went to try to do an Execute SQL step, thinking the DSN would be an option, but it isn’t. The dialogue box says “Select one of the data sources that has been set up on YOUR MACHINE”. Which leads me to think perhaps the Execute SQL script step will only work with locally configured DSN’s perhaps?

       

      The most confusing part is that when I went back to see how I had set it up in the relationship graph, the Add ODBC data source dialogue uses verbiage that only references the host machine data source, it says “Select one of the data sources that has been set up on the HOST MACHINE”

       

      If running a hosted file, can ESS only work with the server’s DSNs and Execute SQL only work with the local DSNs maybe? My brain is nearing shut down mode, thanks in advance if anyone has run into this.

        • 1. Re: Confusion running ODBC connected to a hosted remote fmp12 file
          Mike_Mitchell

          "If running a hosted file, can ESS only work with the server’s DSNs and Execute SQL only work with the local DSNs maybe?"

           

          Yes, that's the way it works.

          • 2. Re: Confusion running ODBC connected to a hosted remote fmp12 file
            beverly

            Mike answered. Here's the FMPro 12 help for the script step:

            Execute SQL

            and

            Accessing external data sources (ESS)

             

            I tended to have two different DNS setups for ESS and local Scripted access to SQL (Execute SQL & Import). They have different uses.

            Beverly

            • 3. Re: Confusion running ODBC connected to a hosted remote fmp12 file
              wimdecorte

              Mike_Mitchell wrote:

               

              "If running a hosted file, can ESS only work with the server’s DSNs and Execute SQL only work with the local DSNs maybe?"

               

              Yes, that's the way it works.

               

              Except for server-side execution of the Execute SQL and Import From ODBC script steps.  If you use a server-side schedule or PSoS, the server's DSN will used for those

              • 4. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                Mike_Mitchell

                Yes, because in that case, the "client" is the server itself. Thanks for the clarification.

                • 6. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                  wintertj

                  Ok, very similar situation as this original thread, so reviving it, though it is over two years old......

                   

                  Have a FM Server 14 running under Windows Server (2008 R2 I think). Client's network admin has setup two MS SQL Server DSN's (a 32 bit and 64 bit) version on the server (pretty sure will need only the 64 bit.

                   

                  Now, I'd like to write scripts that will be ran on the server (first via PSOS, then after testing, they all will  be scheduled via FMS Admin Console) to run periodically to push FM data to a SQL Server instance.

                   

                  The DSN's aren't installed on any client machines nor do they need to be for the actual data push, server will handle that.

                   

                  The dialog in the Execute SQL script step after clicking the button to "Specify" ODBC data source says "select one of the data sources that has been set up on your machine" - no DSN's have been set up on the machine I'm developing from, nor any machines that are acting as clients at the customer site, because the script will only ever be executed from the server.

                   

                  Question 1a: will this work - Install FileMaker Pro client on the same machine that is running FileMaker Server, open the fmp12 file with FM Pro, configure the DSN in the Execute SQL script step, then log off the server machine and finish editing the script from one or more client machines, and the script step will maintain the DSN configuration entered from when it was set while logged in on the machine itself.

                  Question 1b. If this works, can you duplicate the Execute SQL step from a client machine that doesn't have the DSN configured, and have it retain the DSN settings on the duplicate iterations of the step?

                   

                  Question 2: If question 1a doesn't work, what does, short of configuring the DSN on a client machine knowing that it will never be used just to get the name of the DSN?

                   

                   

                  Thanks so much,

                   

                  Tony

                  • 7. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                    wimdecorte

                    Tony Winter wrote:

                     

                    Question 1a: will this work - Install FileMaker Pro client on the same machine that is running FileMaker Server, open the fmp12 file with FM Pro, configure the DSN in the Execute SQL script step, then log off the server machine and finish editing the script from one or more client machines, and the script step will maintain the DSN configuration entered from when it was set while logged in on the machine itself.

                     

                     

                    Yes.

                    • 8. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                      wimdecorte

                      Tony Winter wrote:

                       

                      Question 1b. If this works, can you duplicate the Execute SQL step from a client machine that doesn't have the DSN configured, and have it retain the DSN settings on the duplicate iterations of the step?

                       

                      No.  Because you don't have a DSN on the client, the step is incomplete so there is no 'retain the DSN settings' in play.

                      1 of 1 people found this helpful
                      • 9. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                        wimdecorte

                        Tony Winter wrote:

                         

                         

                        Question 2: If question 1a doesn't work, what does, short of configuring the DSN on a client machine knowing that it will never be used just to get the name of the DSN?

                         

                         

                         

                        Typically the developer would have the DSN on his/her machine in order to test and do the scripting.  You don't really want to do that kind of testing directly on the FMS machine itself.

                        1 of 1 people found this helpful
                        • 10. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                          wintertj

                          Thanks so much wimdecorte! For testing and development, I connect via VPN first to their network, then launch FM Client on my Mac and connect to their FM Server. I can launch a Windows 10 virtual machine under VMWare on the Mac, and connect just fine to their FM Server as long as the Mac's VPN client is connected, and work in a Windows environment. Since I know this works, do you suppose I could take that a step farther and test the Microsoft supplied SQL Server drivers by setting up the same drivers with the exact same DSN names as are set up on their FM Server on my virtual Windows 10 box, and test the DSN locally that way without having to use PSOS to test?

                          • 11. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                            wimdecorte

                            Yes, that will work.

                            1 of 1 people found this helpful
                            • 12. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                              beverly

                              Great answers from Wim! As SQL DBA and ISP, I want to add the caveat: your hosting provider may allow FMS to see the MySQL via ODBC, but may limit the access outside that networking of the two systems. It's a security thing.

                              If you have the permission, then yes, set up the same (exactly!) DSN on your home/office FMPro machine to create the connections/layouts/scripts. There may be a copy of FMPro on the network that does have permission to do this (connect FM to MySQL) and you can open the files (through remote only to the FMserver!) to set up your connections and scripting. You may be able to get there through VPN. Another alternative is to get temporary 'poke' in the firewalls to allow you to access to MySQL for the set up and then have them close it.

                              Check with the hosting provider to see what you can/cannot do.

                              beverly

                              1 of 1 people found this helpful
                              • 13. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                                wintertj

                                Thanks so much Beverly, not surprised to see your typically amazing wisdom especially in this area of integration. Spent a bit of time as SQL Server DBA, (never MySQL though) and had forgot that there is a setting on the instances that controls whether or not it displays itself as a choice  to connect to from SSMS (and presumably the setup ODBC utility and any other application that needs to connect to the instance) on the LAN, plus many other settings and security pieces that made trying to set it up via VPN unworkable, without having to have some assistance, and a greater investment in time than is necessary for the intended goal, so for now working on getting the driver's/DSN's installed on a physical machine on the physical network to do testing with exactly as your caveat suggests, will have fewer hurdles that way.

                                • 14. Re: Confusion running ODBC connected to a hosted remote fmp12 file
                                  beverly

                                  for example. we had a rack at the server farm (hour away) and limited the access. we could see from the office, but not at home. it's what IP's can see the servers and all others could not. office less than 3 miles away, not a problem for clients calling in the middle of the night.