1 2 Previous Next 15 Replies Latest reply on Nov 5, 2015 12:29 PM by rebyerly

    Importing data from ODBC source on Filemaker Server

    rebyerly

      Although I have a workaround to the problem I'm hoping someone has a better solution.

       

      I am writing some scripts for my department which will run on Filemaker Server and which will (on a scheduled basis) import data from a company Oracle database into Filemaker tables.  (There are several reasons we can't go the ESS route on this.)  ODBC drivers are installed on the machine hosting FM server (both the 32 and 64 bit versions with the same name, as recommended somewhere) and they work.

       

      The problem is developing the scripts. Since I don't normally have physical access to the machine hosting FM Server I do the development work on my desktop or laptop.  When I'm editing a database hosted on the server and try to write an import-data script step, the only ODBC sources that I'm offered are the ones configured on my desktop, not on the server.  (In contrast I can see the ODBC sources configured on the server when I go to "Manage external data sources".)

       

      So far the only workaround I've found is to log into the server machine using a remote desktop connection and run a trial copy of FM Pro there in order to get the scripts written.  This is a nuisance and we really hadn't budgeted for an extra copy of FM Pro for the server machine.  Is there a better way?

       

      Thanks!

      Bob

        • 1. Re: Importing data from ODBC source on Filemaker Server
          beverly

          as you have found, you need to have remote access to the server hosted files to make the connection valid for FMS to write the script.

           

          how do you develop on your laptop and then get these file(s) to FMS?

           

          You can try creating the same DSN (by name & as system DSN) on both your laptop and the Server, but I would be wary of trying these to be the 'same'.

           

          beverly

          • 2. Re: Importing data from ODBC source on Filemaker Server
            bigtom

            If you can connect for development and upload whats the issue?

             

            When you are editing a file hosted o the server and the DSN is setup properly on server you should be able to access the Oracle tables directly as if they were FM tables. Did you add the ODBC tables to the graph?

             

            If I'm missing something have you tried VPN or Dynamic DNS for connection?

            • 3. Re: Importing data from ODBC source on Filemaker Server
              rebyerly

              Thanks, Beverly.  I guess there's probably not a better way of doing it then.

              I probably could get permission to access the Oracle database from my machine (access is restricted by machine) but like you I feel nervous trying to get the DSN's to be the same.

               

              Bob

              • 4. Re: Importing data from ODBC source on Filemaker Server
                rebyerly

                Thanks.  The only issue is that this way is a nuisance and it seems to require having an extra copy of Filemaker Pro, that is, both FM Server and FM Pro running on the server host (a rack-mounted machine in the basement).

                 

                I can set up some of the ODBC tables on the graph, but not all, because most of the tables don't have a primary key defined.  We don't have control of the Oracle database.  The import-from-ODBC is what's working for us.

                 

                We're new to Filemaker Server, having just had a number of individual copies of FM Pro running on individual machines sharing data as needed.  One of the attractions of FM Server was that it was advertised that ODBC drivers need only be set up on the server host, not on client machines.  That may be true once we get everything set up, but not during the development phase.

                • 5. Re: Importing data from ODBC source on Filemaker Server
                  bigtom

                  rebyerly wrote:

                   

                  Since I don't normally have physical access to the machine hosting FM Server I do the development work on my desktop or laptop. 

                  This is usually how it works.

                  When I'm editing a database hosted on the server and try to write an import-data script step, the only ODBC sources that I'm offered are the ones configured on my desktop, not on the server.  (In contrast I can see the ODBC sources configured on the server when I go to "Manage external data sources".)

                  Seeing the ODBC sources configured on the server when you go to "Manage external data sources"is good. 

                  Is there a better way?

                  Yes. Go to the relationship graph. Click the button to add a table. You will see a selection box that is defaulted to Current File: Filename. You will be able to select Add ODBC Data Source if you do not already see the name setup for the Oracle database. After you setup or select the data source you will see all of the tables in the oracle database. Add the needed ones to the graph.

                   

                  You can now create a layout based on the ODBC data and add fields from the Oracle DB on your FM layout. You can script copying data just as if it were from one FM table to another. Not exactly the same as Import, but it can work. As well you can run the same sort of script via PSOS.

                   

                  I believe you can run an Import ODBC data via PSOS as well.

                   

                  I am not sure what exactly what you are accomplishing with the import, but if you have access to the ODBC tables on a layout you may not need the import?

                  • 6. Re: Importing data from ODBC source on Filemaker Server
                    bigtom

                    If you know what you are looking for you do not need a primary key. Just use ExecuteSQL and/or filtered portals to grab what you want.

                     

                    No extra copy of FMP required.

                    • 7. Re: Importing data from ODBC source on Filemaker Server
                      beverly

                      ExecuteSQL() - the function - would be of little use if the actual data cannot be imported (or available in a shadow table).

                      beverly

                      • 8. Re: Importing data from ODBC source on Filemaker Server
                        rebyerly

                        Thanks, Beverly and bigtom.  I think I'll keep on the current path unless something else turns up.

                         

                        Incidentally, we really need to import data anyway since some of it is destined for non-FM applications.

                        • 9. Re: Importing data from ODBC source on Filemaker Server
                          bigtom

                          Maybe a workaround: I created another FM file with the ODBC tables on the graph and used Import from File instead of ODBC and I had access to import all the ODBC tables this way. Need to host both file on FMS, but this might be better than what you are doing. I did not actually do an import, but it seems like it will work just fine.

                          • 10. Re: Importing data from ODBC source on Filemaker Server
                            rebyerly

                            Well, maybe.  If we could ever get the ODBC tables on a FM graph -- that seems to be the problem.

                             

                            Some of the data we need is taken from several tables using an SQL join, so it's easier to get it directly from the ODBC source.  Could probably simulate the same thing in FM by using relationships between tables, but it seems easier in SQL.

                            • 11. Re: Importing data from ODBC source on Filemaker Server
                              beverly

                              Yes, if you can set up a View in SQL, these can be used to already give you the joined data you may need. If you can get external tables (from the SQL) on the RGraph, you can also get Views there!!

                               

                              beverly

                              • 12. Re: Importing data from ODBC source on Filemaker Server
                                bigtom

                                Correct, you do not get the Query builder for import from a file.

                                 

                                I still want to say there should be a way you can get all of the needed tables on the graph and use ExecuteSQL to make the same query. But it is a bit more work to do if it is possible and likely slower.

                                 

                                Maybe try writing the script, as Beverly suggested, with the same named DSN locally and then run it via PSOS to get a server side client to access the server system DSN. I suppose the true test would be to remove the local client DSN after the script is written and see if everything imports.

                                 

                                All out of ideas.

                                • 13. Re: Importing data from ODBC source on Filemaker Server
                                  rebyerly

                                  Thanks to both of you for your help.  At least I know that what we need to do is possible, even if I don't have the best solution!  I'll experiment with the other ideas, but go ahead and continue what I'm doing for now.

                                   

                                  BB

                                  • 14. Re: Importing data from ODBC source on Filemaker Server
                                    actualjon

                                    Sometimes it is more efficient to use the "Import Records" script step to directly import records from a SQL database directly into a native FileMaker table (and not use ESS at all).  As you mentioned, you can craft the SQL statement you want to execute, which may involve a multi-table join with custom criteria.

                                     

                                    When using "Import Records", FM will look for the System DSN on the machine where the FM script is executing.  So, for normal development practices, you would create the same Oracle DSN (a DSN with an identical name) on both your development machine and the server.  That way you can execute your script locally during development.

                                     

                                    When you are ready to have your script execute on FM Server, you will have to make the script a "server-side script" that executes periodically (or explicitly as a "perform on server" step).  When the script executes on the server, it will look for the DSN with the name you specified - this time on the server.

                                     

                                    It's definitely easier to integrate FM with Oracle using ESS - you don't have to worry about the SQL at all, and you really only have to configure the DSN on your server.  But "Import Records" gives you more speed and flexibility, with the price that you give up ease-of-use.

                                     

                                    Jonathan Monroe

                                    Actual Technologies - ODBC for Mac OS X

                                    1 of 1 people found this helpful
                                    1 2 Previous Next