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
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)
this is the second question you asked regarding FileMaker and ODBC.
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.
1 of 1 people found this helpful
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 :-)