11 Replies Latest reply on Aug 6, 2014 1:50 AM by chr0n

    Problem performing successive ODBC imports

    chr0n

      Product: Filemaker 13

      Environment: Windows 7

       

      Hi all,

       

      I encountered this problem initially via some import scripts but have since found out that the problem also applies to imports run manually.

       

      On Filemaker Pro 13 I am trying to run a script that performs two Import Record actions (both from an ODBC data source). The script is performing the imports into the same fm database. Only the first import works. The second import runs does not process any records.

       

      When I run the imports separately, only one at a time, either scripted or manually (File>Import Records>ODBC...) they both process records successfully. However if I run them one after the other the second import does not process any records.

       

      Literally the only way I have managed to workaround this problem is to run the first import, completely close the FM database and then re-open to run the second import. This works, both via a standalone import script or through the File menu. This leads me to believe the problem is the way Filemaker handles ODBC imports, however I've got no idea on what to try to resolve.

       

      Can anyone help at all please? Thanks in advance

      Rich.

        • 1. Re: Problem performing successive ODBC imports
          beverly

          have you tried a Pause script step in between imports to allow the data to "refresh"? Your time to pause may vary.

           

          Beverly

          • 2. Re: Problem performing successive ODBC imports
            wimdecorte

            Is there an error generated on the 2nd import?  Could it be that the context is not set correctly when you combine both imports in the same script?

            • 3. Re: Problem performing successive ODBC imports
              taylorsharpe

              Beverly and Wim's advice will probably help you more.  But is this the same ODBC source or a different one?  What ODBC driver are you using and version?  What type of ODBC source is it (MS SQL Server?  MySQL?  Oracle?). 

               

              Personally I think the pause that Beverly suggested will fix your problem assuming you are not having a context issue. 

              • 4. Re: Problem performing successive ODBC imports
                chr0n

                Hi,

                 

                Thanks very much for your input.  I have tried adding a pause to the script and this has not made a difference.

                 

                The problem also occurs when the ODBC imports are run manually in succession so it doesn't appear to be a scripting problem.  Following your logic I also tried waiting for 5 minutes before performing another import through the File>Import Records menu options.  Again same problem unfortunately.

                 

                When I close the FM database straight after an ODBC import and then immediately re-login and perform another import this works.  It appears that the FM database must not be releasing something correctly following an ODBC import.

                 

                As so how I get around this without asking the user to run an import, quit, run another import, quit, etc. I've no idea though!

                 

                Thanks,

                Rich.

                • 5. Re: Problem performing successive ODBC imports
                  chr0n

                  For information it's the same ODBC source each time.  It is an Oracle 11g database using an Oracle in OraClient11g_home1 driver installed via an Oracle client install.

                   

                  There's no error generated, the import runs successfully however it doesn't update any records (using the show dialog options in import).

                  • 6. Re: Problem performing successive ODBC imports
                    taylorsharpe

                    Out of curiosity, maybe try running both scripts at the same time in parallel? 

                     

                    I used to do a nightly update to FileMaker from Oracle with about 50 tables from the same ODBC source and it worked just fine.  So something is wrong here. 

                     

                    Would you maybe share your script here so we could look at it to verify things like context?

                     

                    Can you see the tables OK with ESS as a table occurrence?

                    • 7. Re: Problem performing successive ODBC imports
                      chr0n

                      That's great thanks Taylor.  The fact you've performed what I'm trying to do successfully means I'm doing something wrong which we can hopefully figure out!

                       

                      When you say 'in parallel', how would I do this please?  I've got one script that runs the two Perform Script options on successive lines but I don't know how I specify a parallel run.

                       

                      No problem sharing a script however I couldn't see any options to export script and I coudn't find the physical file location as to where they would be stored.  Could you help me out with this please?

                       

                      I'm afraid I also don't understand your last sentence at all unfortunately, sorry

                       

                      Apologies for my uselessness and many thanks indeed for your suggestions and patience.  It's very much appreciated!

                      1 of 1 people found this helpful
                      • 8. Re: Problem performing successive ODBC imports
                        user19752

                        Did you try 'Show All Records' before second import ?

                        If you want to 'update' all records (not records imported on first import) you need it.

                        • 9. Re: Problem performing successive ODBC imports
                          chr0n

                          Good idea.  I am actually performing the import into a different Filemaker database/table than the script is running on.  I am going to create this script on the other database and then try a 'Show All Records' action before running the 2nd import.  I'll let you know if this solves it!  Thanks.

                          • 10. Re: Problem performing successive ODBC imports
                            beverly

                            You can print the script (as PDF) and copy/ paste to forum (makes it text so could be in email) or include as attachment (forum). altho, IIRC, attachments to this forum must be graphic (JPG, PNG, GIF).

                             

                            Beverly

                            1 of 1 people found this helpful
                            • 11. Re: Problem performing successive ODBC imports
                              chr0n

                              Yay!  Moving recreating the scripts in the same database that is being updated and performing a 'Show All Records' has sorted it.  I did want to have the extract and import process I was using outside of the target database, but moving this into the same environment to get the imports working in succession successfully is a worthy sacrifice for me.

                               

                              Thanks ever so much for your help and suggestions, I would not have been able to resolve my problem without you!  Great community.