9 Replies Latest reply on May 31, 2010 11:52 AM by BreitenbushIT

    Automatic Queries of PostgreSQL Data

    BreitenbushIT

      Title

      Automatic Queries of PostgreSQL Data

      Post

      Hello wise forum folk,

       

      We have Filemaker Server Advanced and about 35 client machines (many of which don't have FMP installed, but use IWP to access some of our databases).   We also have a lot of our company's data stored in a remote PostgreSQL database which I have read_only access to.

       

      I have been asked to create a new database which queries the PostgreSQL database and provides various reports to the end users.  Many of the reports need to be utilizing "up to the minute" information.    

       

      I have the Actual Technologies Open Source ODBC driver configured on my own work computer, and also on the machine which is running Filemaker Server Advanced.  I am able to run queries locally on my work computer using the "import records" script step.  My approach so far as been to import most of the needed data from the PostgreSQL database to local FMP tables and then work with the data to generate various reports.  This works great for me using the database locally, but I am trying to create a solution that will be hosted on the server and will accommodate multiple simultaneous users.

       

      So my question is this:  How can I create a situation where the data is regularly being updated via ODBC queries such that I don't have to install the ODBC driver on every single client computer?  I also want an option for people to view reports via IWP, which doesn't work with the "import records" script step.    Unfortunately I can't utilize the ESS feature since the remote database is PostgreSQL (which isn't currently supported).

       

      My ideas so far are as follows:

       

      1> Have a server side script that runs automatically throughout the day which simply updates the local data by running an "import records" which utilizes the ODBC driver installed on the server machine.  I can't figure out how to make this work.  Is it possible?

       

      2> Add an "import records" step to the script which runs on opening of the file.  Then data is current as of when each user logs into the database.  I also cannot figure out how to make this work using the server's ODBC driver.  Possible?

       

      3> Some other solution that keeps data fairly updated and possibly allows users to manually refresh the data to be current.  I am not sure what options exist.

       

      I am open to whatever possibilities exist that would work well for the situation.  

       

      Thanks in advanced to anyone who can help in this matter!

       

      -shawn-

        • 1. Re: Automatic Queries of PostgreSQL Data
          BreitenbushIT

          I am still trying to figure this out and haven't made much progress yet (despite spending over an hour researching on the web).

           

          If I go into "Manage External Data Sources" for the database in question, it allows me to select the DSN that is configured on the server machine to access the PostgreSQL database.  However, that is about as far as I get.  That data source doesn't show up as an option when I try to configure an "import records" script step OR when trying to "Import Records -> ODBC Data Source" from the file menu.  

           

          Is it possible to import records via ODBC from a PostgreSQL Database using a server side script?  If so, how is it done?

           

          I am also still wondering if anyone can tell me what the best method of importing the data would be considering what I am trying to accomplish (outlined in my original post above).

           

          Many thanks to anyone who can help.  :)

           

          -shawn-

          • 2. Re: Automatic Queries of PostgreSQL Data
            aammondd

            In the Relationships tab you can add tables from the external data source. 

             

            • 3. Re: Automatic Queries of PostgreSQL Data
              BreitenbushIT

              Thanks for the suggestion, but I don't believe that method works with a PostgreSQL database.  I believe adding a external datasource table in the relationship graph utilizes a ESS connection.  I have done this successfully with a MySQL database, but not PostgreSQL.

               

              I just tried this again and when selecting the PostgreSQL data source I get a messages that says "The ODBC data source you have selected is not supported.

               

              -shawn-

              • 4. Re: Automatic Queries of PostgreSQL Data
                mrvodka

                PostgreSQL is not a supported type for ESS. However, you should be able to do an ODBC import. I am not sure why you are having issues with a straight ODBC import. It should work. You have specified a System DSN correct?

                 

                 

                 

                • 5. Re: Automatic Queries of PostgreSQL Data
                  BreitenbushIT

                  Yes, I do have a system DSN set up.  Here are all the exact details (using variables instead of actual names):

                   

                  * Machine "X" is our server computer (a Macintosh running OS X 10.6.3).

                  * We have Filemaker Server Advanced 11 installed on Machine "X".

                  * On Machine "X",  I have a system DSN set up using the Actual Technologies Open Source Database ODBC driver.   I have triple checked the settings and performed a test, and everything is correct and working.

                  * I have a FMP database called "Postgres_Query" hosted on our Filemaker Server.

                  * My computer is a Mac running 10.6.3 and Filemaker Pro Advanced 11.  My computer also has the same ODBC driver installed, but with a different name to identify it as being the one installed locally on my computer.  

                   

                  I open the "Postgres_Query" database (hosted on the server) using my computer:

                  * If I try a standard "Import Records -> ODBC Data Source", it says "Select one of the data sources that has been set up on your machine." and lists the driver installed locally on my computer.  

                  * If I "Manage External Data Sources", it only allows me to add the driver which is configured on Machine "X" (the server).

                  * If I create a script that uses the "Import Records" script step, and then "Specify Data Source, it acts just like the standard Import Records process (It says "Select one of the data sources that has been set up on your machine", and only lists the local driver on my computer).

                   

                  As you can see from the above info, I can add the server's driver as an "external data source" in the file, but I cannot figure out any way to actually access it to import data.   

                   

                  If anyone can provide specific instructions of how I would accomplish this goal, please advise.  :)

                   

                  Thanks!

                  -shawn-

                  • 6. Re: Automatic Queries of PostgreSQL Data
                    mrvodka

                    Name the DSN and have the same settings on the same on the Server and the Local Client. Then create the script using Import ODBC with those settings. Once you can import it locally, tlhen run it as a server side script. As long as the DSNs are the same then FileMaker should be able to do it based on the settings stored within the import script step.

                     

                    As said earlier, you dont need to do anything in manabe databases or set up an external source.

                     

                    If you are stll having issues after that, then you may want to contact Jonathan at Actual Technologies directly. Unfortunately I dont know too much about how his driver works server side as I mainly use Windows. 

                    • 7. Re: Automatic Queries of PostgreSQL Data
                      BreitenbushIT

                      Yay!  

                       

                      Thanks for the tip!  For some reason I didn't think that would work, but it indeed it does.  Thanks so much for pointing me in the right direction.

                       

                      My main question that remains is how to best implement the server side scripts with my solution.  I am assuming it is not possible to have a user trigger a server side script (am I correct?), so this would mean my only option is to schedule the imports.  Since users may be accessing the system throughout the day and will want "up to date" information, I should probably have the script scheduled to run somewhat frequently .  Perhaps every 10 minutes?  I am guessing this should work fine even with multiple users accessing the database simultaneously as long as they aren't directly accessing the tables that are being imported to?  Is there anything else I should consider when implementing this solution?

                       

                      I believe my only other feasible solution would be to install the ODBC driver on all client machines and configure the DSNs identically.  Then users could trigger the import process manually.  However, this wouldn't work with people accessing the database via IWP.

                      • 8. Re: Automatic Queries of PostgreSQL Data
                        mrvodka

                        Yes there is no way to trigger a server side script. However, you could have your script run every few minutes. If you dont want to run the import every time then you can store a flag field or record numbers in a table ( or find those records ) and then only update those records or import when the flag field has been set. Once it finished importing then it can clear the flag field .

                        • 9. Re: Automatic Queries of PostgreSQL Data
                          BreitenbushIT

                          Thank you for the great suggestions!

                           

                          I believe I now have enough information under my belt to go forward with implementing this solution.