1 Reply Latest reply on May 4, 2010 1:25 PM by MartinBoswell

    unix script to query fillemaker?

    Jos3Andr3s

      Title

      unix script to query fillemaker?

      Your post

      Hi, first of all thank you for any help. I have been using filemaker server for about a month.

       

      Filemaker 10 server is running on a machine with the following specs:

       

      MAC OSX 10.6 Snow Leopard

      Intel chip.

      RAM: 8GB

       

      So, here is what I need help with. I am wondering if one can write script that use a filemaker or a filemaker file to query for data. For example, an appleScript(or any language) that connectsto filemaker or loads a filemaker file and retrieves all the new or updated records since some date and transfer the data to a, for example, sqLite database. The script would be run in the same machine file maker is installed on and where the filemaker files are.

       

      Thank you!

        • 1. Re: unix script to query fillemaker?
          MartinBoswell

          (When I preview this post, it inserts emoticons into the code blocks.  I can't figure out any way to turn this off. It also seems to be mangling the code blocks in other ways, too.)

           

          Yes, there are a number of ways to do this.  Essentially, you need to choose one of FileMaker's interfaces to retrieve/insert your data, and chose a scripting language that can address that interface.  There are a number of requirements and limitations, so look into them before you start down one of the paths.  Here are a couple of them in order of my preference...


          -----------------------

          1) Use the XML interface via the fabulous rfm (http://sixfriedrice.com/wp/products/rfm/) and write a ruby script like this:  Note that if you're trying to retrieve large amounts of data in your text fields (starting about about 40k), the XML interface will become unusably slow (i.e. fail).

           

           

          #!/usr/bin/ruby
          require 'rubygems'
          require 'rfm'
          
          SERVER_CONFIG = {  
             :host         => "server.domain.com",
             :port         => 80,
             :account_name => "username",
             :password     => "password"}
          
          # Setup a layout with fields needed to to extract data
          DATABASE        =  "DB_file"
          LAYOUT          =  "layout_name"
          myServer = Rfm::Server.new(SERVER_CONFIG)[DATABASE]
          dataLayout = myServer[LAYOUT]
          aResult = dataLayout.find({"id" => "10005"})
          
          puts aResult.inspect

           

           

           

          -----------------------

          2) If you're using Server Advanced, use the ODBC interface and write a ruby script like this (you'll need to install the FileMaker ODBC driver and ruby's odbc bindings - http://www.ch-werner.de/rubyodbc/odbc.html):   This is also easily done in other scripting languages such as perl, php, etc.

           

           

          #!/usr/bin/ruby
          require 'odbc'
          
          c = ODBC::connect('LOCALDSN' , "username", "password")
          q = c.prepare("SELECT id,amount FROM yourtable WHERE id > 2000")
          q.execute
          
          x = "1"
          until x == "nil" do
            x = q.fetch_hash.inspect
            # puts x.class
            puts x
          end

           




          -----------------------

           

          3) If you can run FileMaker Pro as a user who is logged in to the console of a machine, you can call an AppleScript from the command-line like this:  Note that this syntax won't works for me with FileMaker Pro versions 8, 9, 10, but not with version 11 (I don't know why and haven't had motivation/opportunity to look into it).  Due to the requirement to be logged in at the console, I consider this kind of solution to be a last hope (such as when I have to insert data into a container field, which you can't do though odbc...until version 11), and I only mention it since you asked specifically.


          /usr/bin/osascript insertNewCompany_AS.txt 'My Company Name'

           

          ...where your AppleScript script saved as a text file (not as a compiled script/application):

           

          (* Preparation:
               setup the FileMaker file, XRD_Data_Upload.fp7
               make sure that it's at the path path in 'open alias...' statement below
          
               run as follows: 
                    osascript insertNewCompany_AS.txt 'My Company Name'
          *)
          
          on run argv
               set companyName to item 1 of argv
          
               tell application "FileMaker Pro"
                    activate          
                    open alias "Macintosh HD:Users:macuser:Documents:data_upload.fp7"
               
                    tell database "data_upload.fp7"
                         do script "Upload_Init"
                         set aRecord to create new record
                         tell aRecord
                              set cell "Company" to companyName
                         end 
                    end 
               end      
          end