13 Replies Latest reply on Apr 8, 2013 9:28 AM by Padster

    Filemaker Server Import Script from ODBC

    Padster

      Hi,

       

      I'm hoping that some-one might be able to help me with something that is confusing me.

       

      Setup information;

      FMS12v1, Actual Tech Oracle ODBC Drive, OSX 10.7.5

       

      I have built a database that has a link to an outside source, 3rd party Oracle Database that I have no control over. I set up the ODBC connection the to Oracle Database, and managed to get the connection up and running. From here, I then set up a script that will perform the import, "select * from db.tablename". I set the import so that it would update existing records and add any new records. So, nice and simply, my script was;

      Go To Layout[]

      Import Records[]

      I tested this on my local machine, everything works fine. I then moved the DB to the FMS and set up a schedule task to run this every 30 minutes.

       

      For the first couple of months, this worked perfectly without any problems.

       

      Now come my problem; for no apparent reason, the Scheduled Task on the server is 'exceeding time limit', (I set this to 15mins, though the import actually only takes about 1min).

       

      There has been no change to the Server on my side, and there has been no change to the 3rd party Oracle Server. I can run the Script locally and as expected, the import completes perfectly. So there is just something on the server side that is causing the problem.

       

      I have tested the ODBC connection on the server, this is still working and successfully connecting (I have also deleted and re-created this just to be sure).

       

      Has anybody else come across this type of problem with FMS?

       

      Pad

        • 1. Re: Filemaker Server Import Script from ODBC
          taylorsharpe

          Could it be that over the time, the database has continually grown and now it takes more than 15 minutes to import?  It could also be a network problem (router dropping packets, etc.).  Also, to avoid all those imports, I would probably try ESS instead. 

           

          Also, why are you still on FMS12.1? 

          • 2. Re: Filemaker Server Import Script from ODBC
            Padster

            Hi Taylor,

             

            Thanks for the responce.

             

            There is only ever 250-350 records in the table, it is a suppliers stock information.

             

            I can't see any network issues, I could understand if 1-2 times a day didn't complete, but its never completing. Plus, if I run the process on my machine, it connects and runs every time.

             

            My understanding of ESS is that you are basically performing a lookup to the Data Source, am I understand this right? if this is the case, then I cannot do this, 1 minute to refresh every time some-body just wants to look up an item.

             

            the reason for 12.1, If it ain't broke, don't try and fix it. Until now of course, but I'd rather find the problem than just doing an update, as that is no guarentee that it will fix it.

             

            Pad

            • 3. Re: Filemaker Server Import Script from ODBC
              Oliver_Reid

              Not sure if this will help, but "update matching" takes longer and longer as the table grows as every incoming record has to be matched up against one of a growing set of existing records. You may reach a point where straight delete all and reimport without matching is faster. You could do this in holding table (with only teh UID feild indexed), then searchthat  for new records only, and import those table to table.

               

              You could also try createing an ESS TO of the external source and search it for new records, and the do an import, table to table of just the new ones.

              • 4. Re: Filemaker Server Import Script from ODBC
                Oliver_Reid

                Just read your last post. 250 records should not be a problem unles there are many, many feilds or large numbers of storec calcs in teh destination table.

                 

                Try installing FMPA on the server and running the script with the debugger on and see where the hold up is.

                • 5. Re: Filemaker Server Import Script from ODBC
                  wimdecorte

                  Padster wrote:

                   

                   

                   

                  the reason for 12.1, If it ain't broke, don't try and fix it.

                   

                   

                   

                  But that's just the point: 12v1 IS broken.  There were a lot of issues with it, FMI does not unfortunatley keep a detailed list of all the bugs it fixed in v2, v3 and now v4; but maybe there is something there that will resolve your problem.

                   

                  If you called FMI support, the first thing that they would insist on is upgrading to the latest patch before they would go on.  It is the sensible thing to do.

                   

                  As to the issue at hand: do you have a performance baseline in general of the machine as you deployed it?  Something that you can compare to now?  Clearly something has changed on the machine, the network, the 3rd party database or in your solution.  FMS is your only constant so that is not the factor.

                  • 6. Re: Filemaker Server Import Script from ODBC
                    taylorsharpe

                    ESS is bit more complicated than you make it.  It actually is a shadow table.  Since you already have the driver set up, why not try it out and create a separate table occurrence for the same data via ESS and see how it works.  It will make things a lot simpler for you. 

                    • 7. Re: Filemaker Server Import Script from ODBC
                      greglane

                      Hi Pad,

                       

                      Does your database have a startup script enabled (an OnFirstWindowOpen script trigger defined in the File Options dialog)? If so, it's possible that something in this script is causing your schedule to fail before the Import Records script step ever executes.

                       

                      Greg

                      • 8. Re: Filemaker Server Import Script from ODBC
                        EvanGoldstein

                        Does the import consistently take this long regardless of when it runs? I find that if there is heavy usage on the server from client requests, a scheduled script takes longer.

                        • 9. Re: Filemaker Server Import Script from ODBC
                          Padster

                          Hi All,

                           

                          Sorry for the delayed responce;

                          @wimdecorte;

                          I understand your point about the application as a whole being updated, but this is a production environment and in all of my experience, you don't make changes to a production environment unless you need to or have complete extensive testing. The import worked before, and does work if manually triggered (Running FMPA on Same Machine).

                           

                          The Machine that this is running on is a 2011 Mac Mini Server with an attached Pegasus for DB Storage and is serving several other db without any problems, it is just this single scripted import causing me problems.

                           

                          At this time, I am currently doing further development and upgrades to my DB, which I am doing on FMS12v3, Once this development was complete I was going to be updating the whole system to 12v3 anyway (which will likely be 12v4 by the time I finish).

                           

                          @taylorsharpe and @Oliver_Reid;

                          The Import is run in two seperate parts, there is a 'through the day' run, that happens every 20 mins, update existing, and add new. There is then also a nightly run, that happens at 3am, that purges the whole Table, and re-imports a daily baseline to start the day.

                           

                          I could change this to an ESS table, but I still have concerns about updates to this table? to help me understand this better, how does the Shadow TO that is locally cached get updated? Is this through the Refresh Window [Flush Cached External Data] when the user goes to view the information on said table, would the there still not be a delay while the information is updated?

                           

                          As this will be a Server Side Script, as FMS does not facilitate Table to Table Imports, I guess this will have to be done through a Export to File from ESS table, and then Import to Local Table?

                           

                          @EvanGoldstein;

                          Yes, the script always times out, either on the 'Through the day runs' or the nightly run.

                           

                          @Greg Lane;

                          There are OnOpen Triggers, but all of the scripts in the database have Allow User Abort[Off] which allows the server to by-pass incompatable script steps.

                           

                          Pad

                          • 10. Re: Filemaker Server Import Script from ODBC
                            taylorsharpe

                            I do not understand what you mean by "FMS does not facilitate Table to Table Imports".  You can do a  FILE > IMPORT RECORD > ODBC SOURCE and import directly into the table of the layout you are in without having to save things locally to an intermediate file such as a txt file.  Let me know if I misunderstood you. 

                             

                            FileMaker did an elegant job with ESS and pretty exactly for the purpose you have described here.  Once you try it, you are going to wonder why you didn't do it before.  Like most other things FileMaker did... it does not spend time explaing how things work under the hood.  They just show you how to use the feature.  ESS was designed for ease of use and implementation so that as a developer, you mostly can't tell the difference an external SQL table and a local FileMaker table.  It works very seemlessly with FileMaker with a few limitations such as you can't alter a SQL schema and this was designed for ease of use and not performance (e.g., if you get big tables, the performance can be undesireable compared to FileMaker native tables).  Give it a try and see what you think!

                            1 of 1 people found this helpful
                            • 11. Re: Filemaker Server Import Script from ODBC
                              Padster

                              In regards to the Import on FMS, I do currently use the ODBC source as you mention.

                               

                              I think i might have confused you with the way that Oliver_Reid talks about, which is have the Shadow Table (Table A) and then a local table (Table B). and then import from Table A to Table B

                               

                              On FMS you cannot import from from one local table to another, it is an incompatable script step, unless I have missed something?

                              • 12. Re: Filemaker Server Import Script from ODBC
                                pmconaway

                                Pad, this is probably a horribly wrong suggestion. I went to one of mine served databases and created a script. Used the Import Records step (switched "Show Compatibility" view to Server. My only choices like you said are File... and ODBC. I selected file. Click the add file button in the dialog box and selected "remote" and selected the same database the script is in. My guess is that at this point I have database a linking to itself remotely. (the horribly wrong part). Now I don't know what this will do for performance or if it will solve your problem. It could be a temporary work around until your new version is finished.

                                 

                                Paul

                                • 13. Re: Filemaker Server Import Script from ODBC
                                  Padster

                                  All,

                                   

                                  Oddly, this has started working again...!!!

                                   

                                  The only thing that I can think is that the 3rd Party DBA mush have made a change, as I have not touched anything at my end.

                                   

                                  I have asked the question, unsurprisingly, nobody wishes to accept responsibility for looking at, or changing anything at their end.

                                   

                                  Thank you all your helpful comments and suggestions.

                                   

                                  Cheers,

                                  Pad