4 Replies Latest reply on Mar 22, 2017 4:43 AM by Menno

    Exporting Records through ODBC


      I've been trying to locate a walkthrough of some sort regarding how to export records from multiple filemaker tables to multiple Postgre SQL tables (or similar situation, just so I can get the gist of the whole thing).


      At the moment I'm working with Postgre SQL to host the database and tables for a GIS project and I'm using filemaker for the user interface as well for additional data and reporting. Basically this is the other half of the one way I know at the moment on how to sync the data between the GIS map and the Filemaker Database... so besides my above question, if anyone knows of a better way to actually sync the data, please let me know. Thanks!

        • 1. Re: Exporting Records through ODBC
          Johan Hedman

          I would recommend you from your FIleMaker to setup a ODBC DNS to your SQL-database and then use ESS


          Then you can have the SQL-databases almost like a native FileMaker table inside your solution and set data in your SQL-database table using FileMaker scripts.


          Read more here

          Accessing External SQL Data Sources (ESS) Overview and Troubleshooting | FileMaker

          • 2. Re: Exporting Records through ODBC

            This is what we do;

            - odbc connection to a ms-sql database and an import records script step. the sql query is simple: select * from TBL

            - First allow FM to create a new table (sqltable) and this creates a custom fitted table. FM recognises filetypes.

            - Rename that table to something meaningful

            - edit the import record script step to the renamed table

            - rename the auto-created layout to something meaningful

            - add a truncate script: go to layout 'meaningful', enter browse mode, show all records, delete all records


            You'll have to create the relations between table manually once. Since you never actually drop fm tables (only truncate) relations will be preserved.


            So I can drop the FM data and reload in an instance.


            One thing, FM doesn't understand 'geometry' data type but ms-sql has a function to convert that to OGC which is read correctly by FM.

            (There may be more ways to tackle this; Beverly has been writing about more elaborate queries to get around that, search this forum)

            • 3. Re: Exporting Records through ODBC

              this is the second question you asked regarding FileMaker and ODBC.

              (* https://community.filemaker.com/message/650414 )


              1. take Johan's advice to study ESS ( be sure to click all the links on that knowledge base article )

              2. and these:

              (follow all the links!)


              Basically, ODBC and FileMaker work:

              - FileMaker as ODBC source

              - FileMaker looking at ODBC source

                   - through ESS

                   - through the two script steps (Import & Execute SQL)


              PostgreSQL is one of the "approved-for-ESS" databases with the correct driver(s). ESS is going to be the easiest (once the Data Source Name is set up), as the tables will be a "view into" the SQL from FileMaker and behave much like native FileMaker tables. You can script, find, sort, create, edit, delete, etc.


              A Caveat (or two): you must have all the permissions on the SQL db tables to do what you want this way. and the dependencies may be such that direct access "view" this way may not work as expected.


              • 4. Re: Exporting Records through ODBC

                You can certainly use ESS to "talk" to a Postgres database as is suggested johanhedman, makerbert and beverly


                Personally I prefer using Execute SQL. As beverly pointed out, you will need correct drivers, sufficient permissions and a lot of knowledge on the PG-DB.


                If you can check all the above boxes, you can do a lot more with Execute SQL then you can with ESS. For example: when the PG-DB designer has created a procedure called "SpecialProcedure" with which you can create records with in the PG-DB by using a SQL-query like:


                SELECT SpecialProcedure( Value1 , Value2 , Value3 )


                This imaginary example procedure in PG will then Evaluate the parameters and if subrecords are supposed to be created the procedure will do that for you. This is something ESS will never be able to do, in ESS a lot of the validation etc. will have to be done by you.


                Running Execute SQL directly is also much faster then ESS, because your own SQL only contains what you need and the ESS generates SQL for the data that you can see in the layout containing the data.


                If your PG-DB has a simple structure though, then you can surely use ESS, it is pretty neat and simple to use. It generates all the SQL for you in the background and you never need and get to see it :-)

                1 of 1 people found this helpful