7 Replies Latest reply on Jul 13, 2009 2:40 PM by etripoli

    Sync a Filemaker Database with a SQL database

    nemampojma

      Title

      Sync a Filemaker Database with a SQL database

      Post

      I'm trying to live sync a Filemaker Database with a SQL database to automatically update a website list. The SQL database contain the same fields as it is in FileMaker database. I would like to be able to write a script in Filemaker to update the SQL database once the Filemaker database is updated. Could someone please point me in the right direction about how to accomplish this. Thank you in advance for your assistance.

        • 1. Re: Sync a Filemaker Database with a SQL database
          MikeyG79
             Why maintain both? What about removing the FM database, and just have it access the SQL one?
          • 2. Re: Sync a Filemaker Database with a SQL database
            etripoli
               We're doing basically the same thing, and must use a Mac OS X 'robot' running FMP, and an Automator script.  Could also use cron and an applescript, if you're on a Mac.  Not sure about 'bots running Windows.
            • 3. Re: Sync a Filemaker Database with a SQL database
              nemampojma
                

              I'm on Windows platform (XP Professional) and I'm using FileMaker 8.5 Advanced. Sync is neccessary only in one direction: FM to SQL (Oracle). My existing Query (scripted in FM script as Execute SQL command) is using ODBC connection that is in function - at least, Windows recognize connection and Oracle database send correct response that is linked with ODBC driver.

               

              Execute SQL command has value:

              INSERT INTO 'Oracle_table_name' ('Field1', 'Field2')
              SELECT 'FileMaker_table_name'.'Field1', 'FileMaker_table_name'.'Field2'
              FROM 'FileMaker_table_name'

               

              Script is not executed with following message:

               

              ODBC Error: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist

               

              Does this means that FM cannot use ODBC connection (which is correctly tested under Windows)? Or I need to use syntax that is suitable in relation FM to SQL?

              • 4. Re: Sync a Filemaker Database with a SQL database
                etripoli
                  

                Try removing the single quotes around the table and field names.  With FM8.5, you'll need to setup a calculated SQL command, based on the records in the current found set.  You can't use 'SELECT' on the FM records.  Example:

                 

                Go to Record [First]

                Loop

                Set Variable: [$sql] = $sql & "'" &  FileMaker_table_name::Field1 & "','" & FileMaker_table_name::Field2 & "'"

                Go to Record [Next, exit after last]

                Set Variable: [$sql] = $sql & ","

                End Loop

                Execute SQL: Calculated = "INSERT INTO  Oracle_table_name (Field1,Field2) (" & $sql & ")"

                 

                My Insert SQL is a bit rusty, but I think the syntax is 90% correct

                • 5. Re: Sync a Filemaker Database with a SQL database
                  nemampojma
                    

                  Thanks you on answer.Single quotes in my example was only for purpose of marking a field names in this message - in a script there were no single quotes. 

                   

                  Please I don't understand why SELECT is not possible for usage? Is there some special syntax that is necessary to use for SQL query when it is written from FileMaker?

                   

                  And also I don't understand why is not possible to move all data to Oracle and only use loop that will do it 'one-by-one' (as you explained in your post). Inserting in SQL (Insert into) is something very basic operation. FileMaker declares compatibility with Oracle, but without this command (Insert into) - it is meaningless compatibility. 

                   

                  What is purpose that I can only 'see' data from Oracle (through portal row in FileMaker 9), if I can't work with this data (use or update)?

                   

                  Executing my SQL script I have screen message:
                  "ODBC Error: [Oracle][ODBC][Ora]ORA-00942: table or view does not exist"


                  This message describe main problem - FileMaker can't EVEN RECOGNIZE any table in Oracle, although in the same time I can see all of Oracle data in FileMaker (External Data Source properly setup, Oracle table added as table in FileMaker and joined in relationship).

                   

                  Please, if somebody can help. 

                  • 6. Re: Sync a Filemaker Database with a SQL database
                    mrvodka
                      

                    The issue is not the INSERT INTO. It is the SELECT because FileMaker does not understand what you mean by SELECTING the FileMaker records.

                     

                    Try doing a single insert such as:

                     

                    INSERT INTO  Oracle_table_name (Field1,Field2)  VALUES ('Mickey', 'Mouse')

                     

                     

                    If that works, then try doing a  scripted version:

                    Set Variable [ $sq; "'"] //single quote

                    Execute SQL [ "INSERT INTO  Oracle_table_name (Field1,Field2) VALUES (" & $sq & field1 & $sq & "," & $sq & field2 & $sq & ")" // where field1 and field2 are the FileMaker fields

                     

                    If that works, then you can just loop through your FileMaker found set.

                     

                     

                    Another option is considering that you are using ESS, you could just use Set Field.

                     

                     

                    • 7. Re: Sync a Filemaker Database with a SQL database
                      etripoli
                        

                      When you're using Execute SQL within Filemaker, you can't use SQL to talk to the FM databases.  Most of what FM does is at the per record level, except for imports/exports.  And while it's easy to import from ODBC, there's no method for exporting directly to the data source.  Guess that's why they call them sources, instead of destinations.

                       

                      With that said, ESS is a little different, because it allows you to see, and update multiple records in a table directly based on an ODBC data source.  But you can't use SQL commands with ESS tables, FM takes care of the SQL for you.