10 Replies Latest reply on Apr 4, 2012 6:11 PM by paulatkins

    Unreliable external connection.

    paulatkins

      We are running FMP11 in paralell to a Sybase7, being Sybase7 it is not ESS compatible, so we run an import script that timestamps when run, and the script only pulls the modified records after the timestamp. The import process or 4000 records can take 4 hours if the timestamp is not used!

       

      If I run the query that the FM database generates on the Sybase server directly, it works 100% of the time.

      When I run the FM import script, it sometimes ignores new records. I cannot find a consistent reason.

       

      Can anyone offer assistance please? This is an odd one, I wish the Sybase could be re-written in a more modern MySQL so ESS would work.

       

      FMP11 is run off a Mac OSX 10.6.8 Server

      ODBC driver is ODBC Manager 1.0.7

      The Query looks like this:

      SELECT CUSTOMERINFO.ACQUIRED, CUSTOMERINFO.ALTPHONE, CUSTOMERINFO.BILLINGTYPE, CUSTOMERINFO.CONTACTNAME, CUSTOMERINFO.CUSTOMERID, CUSTOMERINFO.CUSTOMERRATING, CUSTOMERINFO.EMAIL, CUSTOMERINFO.LASTMODIFIED, CUSTOMERINFO.NAME, CUSTOMERINFO.NOTES, CUSTOMERINFO.PHONE, CUSTOMERINFO.PRICINGMODULE, CUSTOMERINFO.WEBSITE, ADDRESSINFO.ADDRESS1, ADDRESSINFO.ADDRESS2, ADDRESSINFO.ADDRESSTYPE, ADDRESSINFO.CITY, ADDRESSINFO.Country, ADDRESSINFO.SHIPPINGINSTRUCTION, ADDRESSINFO.SHIPPINGMETHOD, ADDRESSINFO.STATE, ADDRESSINFO.ZIP, ADDRESSINFO.SHIPPINGNUMBER, CUSTOMEREMAILINFO.NoSolicitation FROM CUSTOMERINFO, ADDRESSINFO, CUSTOMEREMAILINFO WHERE CUSTOMERINFO.CUSTOMERID = ADDRESSINFO.CUSTOMERID AND CUSTOMERINFO.CUSTOMERID = CUSTOMEREMAILINFO.CUSTOMERID AND AddressType = 'B' AND MEMBER >='2012-01-13 20:57:52.000'

        • 1. Re: Unreliable external connection.
          RayCologon

          paulatkins wrote:

          ...When I run the FM import script, it sometimes ignores new records. I cannot find a consistent reason.

           

          Hi Paul,

           

          One of the most likely reasons an import may ignore certain records is if there are field validations within the target table that are set up to "Validate: Always", and values in some records in the import data set that fail validation in one or more fields.

           

          Is that a possibility?

           

          Regards,

          Ray

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

          R J Cologon, Ph.D.

          FileMaker Certified Developer

          Author, FileMaker Pro 10 Bible

          NightWing Enterprises, Melbourne, Australia

          http://www.nightwingenterprises.com

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

          • 2. Re: Unreliable external connection.
            paulatkins

            Thanks Ray, but I checked all fields for validation, and none are set to "Validate: Always".

             

            Good thinking though, it would appear 'random' then.

             

            Thank you,

             

            Paul

            • 3. Re: Unreliable external connection.
              beverly

              MEMBER >='2012-01-13 20:57:52.000'

               

              When trying to "sync" with ODBC, using the date as precise as you have it sometimes would be the problem for me. I opted for a time stamp that would be to the 0 hour on the date. You have to make sure that's what you have in your field, so that you don't miss or duplicate records.

               

              So, for example, on Thursday I'd grab whatever was entered on Wednesday (regardless of time). Much more reliable. Just my experience with ODBC on non-ESS SQL dbs.

               

              Beverly

              • 4. Re: Unreliable external connection.
                RayCologon

                paulatkins wrote:

                Thanks Ray, but I checked all fields for validation, and none are set to "Validate: Always".

                 

                Good thinking though, it would appear 'random' then.

                 

                Hi Paul,

                 

                Good to have ruled that possibility out I guess.

                 

                Nevertheless, I doubt it's random. ALong with Beverly's suggestion, the next thing I would look at is the character set of the import files (eg are they , and whether there are any potential 'problem' characters or symbols in the records that are being skipped).

                 

                Regards,

                Ray

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

                R J Cologon, Ph.D.

                FileMaker Certified Developer

                Author, FileMaker Pro 10 Bible

                NightWing Enterprises, Melbourne, Australia

                http://www.nightwingenterprises.com

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

                • 5. Re: Unreliable external connection.
                  Mike_Mitchell

                  Another thing to look at would be the timestamp format. Some versions of SQL are picky about how the timestamps are formatted and don't always parse them out correctly (I've run into that).

                   

                  Mike

                  1 of 1 people found this helpful
                  • 6. Re: Unreliable external connection.
                    paulatkins

                    Hi Beverly and friends. I am thrilled with this help, thank you.

                     

                    The Sybase7 requires the elongated timestamp, do I invoke a rounded time, so it is to the hour, or to the minute and the seconds become .000 etc?

                     

                    I do need some precision, becase staff will add the client to one Sybasedb, hit the import and fill in the additional info in FM. They are likely to do more than one per hour, so we need the minutes.

                     

                    How do I go about rounding the time, do I convert it to a number and round and back to a date? I have a conversion I 'taped' together to get the format: '2012-01-13 20:57:52.000' from a normal insert timestamp script command.

                     

                    Paul

                    • 7. Re: Unreliable external connection.
                      paulatkins

                      We have been looking for a consistence over the past months, but staff have not found it yet. We thought we had it when a new client weas merely entered, and did not import, but when we edited their record, it then imported. But this was when the query was based on a "modified >="  instead of the current "member >=".

                       

                      We had to make the mod to member change, because the import was taking up to 4 hours!

                       

                      So I will look into Beverly's suggestion first, if I can work out the rounding.

                       

                      Thank you again,

                       

                      Paul

                      • 8. Re: Unreliable external connection.
                        paulatkins

                        Thanks Mike, this was an early problem that stopped it working completely, so you are right.

                         

                        I think Beverly's rounding suggestion may help make it reliable.

                         

                        Paul

                        • 9. Re: Unreliable external connection.
                          beverly

                          Paul, this part of your reply intrigued me.

                          I do need some precision, becase staff will add the client to one Sybasedb, hit the import and fill in the additional info in FM. They are likely to do more than one per hour, so we need the minutes.

                          HOW are they adding the client to the Sybasedb? Is it returning a primary key that can be searched (much more reliable than a time stamp)?

                           

                          As I said, the request to retrieve by date is not as consistent (in my experience - ymmv). If you can import based on a SELECT for the unique primary key of the newly created row in the Sybase, then you know that you have gotten the correct record.

                           

                          Beverly

                           

                          Message was edited by: Beverly Voth, to include the quoted portion

                          • 10. Re: Unreliable external connection.
                            paulatkins

                            Apologies for the slow response, testing this in an on-line system is slow and tricky.

                             

                            It seems as though it may be the query that is mostly to blame. If all of the fields that are in the WHERE statement:

                             

                            WHERE CUSTOMERINFO.CUSTOMERID = ADDRESSINFO.CUSTOMERID     AND CUSTOMERINFO.CUSTOMERID = CUSTOMEREMAILINFO.CUSTOMERID AND AddressType = 'B'

                             

                            are not populated, which they are not always populated in actual pratice, then the query returns nil. And it 'appears' random.

                             

                            However, that being said, It still is not ideal. Sometimes entering in these fields does not fix it, sometimes the customer record needs some use before it appears...

                             

                            This does not seem right, digital is on or off is it not? Other things must be happening that we cannot fathom.