MySQL to Filemaker 7

[pigslow]

I have a series of scripts that I use to bring records from a MySQL 4.0 database into a Filemaker 7 database.

About every couple of months I have to go back to the import portion of the scripts and step through them again. What I mean by this is editing the script, selecting the ODBC, verifying the logon and password to the MySQL DB, looking at the files to transfer and selecting OK.

Nothing seems to change as far as what was placed in the script it just seems to loose it way.

In the master script there are about 6 different Import scripts, all importing from a different MySQL table into a different Filemaker Table, and when this happens only one of the 6 will fail. It is always random as to which of the 6 is going to fail, not the same import failing time after time.

Has any one else seen this happing or have any possible solutions?

[Reed]

I've been importing from SQL server 2000 into Filemaker 7 every hour for the past 9 months without a hitch....

Have you tried making a clean file with the same script to see if it still happens there? This could confirm or rule out possible corruption of your filemaker file. If it's not corruption, maybe try a different ODBC driver.

Just throwing out guesses...

[pigslow]

The scripts are actually run against a separate filemaker database. What happens is first thing off all records in this middle filemaker database are deleted then the import repopulates it. After that is complete there is some data scrubbing that takes place. The duplicates are omitted and the new records are then imported from one filemaker database to the other to bring in only new records.

I tried the whole recover the database then create a compact copy thing. It helps with some speed issues but I still have this script issue every couple of months. I do that about once a month to keep the size down and allow the scripts to run faster.

When you say a clean file you mean create a clone and import into that? It would probably work because this script will work most of the time in a fully populated database.

If it helps the MySQL database is running on the same WIN 2003 server as Filemaker Advanced Server 7.

[Reed]

I tried the whole recover the database then create a compact copy thing. It helps with some speed issues but I still have this script issue every couple of months. I do that about once a month to keep the size down and allow the scripts to run faster.

I wouldn't use a database that's been recovered. The recover... command is pretty harsh and is meant to extract the most data possible from a corrupt file. The best use of recover would be to use it to recover data, then export the data to a text file, then import it into a clone, that has never been "recovered"

Deleting large blocks of records and then importing again can cause performance and database size problems. This is what the file maintenance tools in filemaker developer are for.

What I meant to say is... create a new empty file and recreate the scripts from scratch. Run this for a while (don't do any recovery on the file) and see if the same problem pops up.

I used to synchronize data between the SQL server and filemaker by deleting records and importing new, but I've found that doing imports using the "update matching records in found set" option is much better. Just use the primary key field as your import match field. Also, if you check the add remaining data as new records option, then your filemaker data will always match the SQL data.

Dana

[pigslow]

Thank you I will give that a try.

[cjaeger]

...> I've found that doing imports using the "update matching records in found set" option is much better. Just use the primary key field as your import match field

yes, but it's slooooooow.

Why not just adjust your query to select only records changed since last import....

[Reed]

In most cases, the systems I'm importing from don't have modification timestamps on the records. Also, my department won't allow me to make any modifications to the SQL server database, or even use the execute SQL step to write any data to their system, I can only import to FMPro.

It is kind of slow, but I just run the script every hour (it takes <1minute) on my "utility" machine... the one that sends emails, runs reports, and imports data... etc. It doesn't tax the server at all, it seems like the weak link is the SQL server ODBC drivers for the Mac.

[pigslow]

Why not just adjust your query to select only records changed since last import....