ODBC Import Problem

[GManNAtl]

I have a solution that I am integrating to a customers existing case management system (Law Office). There are about 6 tables of information that I need to have access to which is using Sybase SQL Anywhere for the backend, so ESS is out. I have written a script that will perform an ODBC import on a recurring schedule. The Tables I need have modified timestamp fields, so my SQL statement searches WHERE the modified_timestamp is since the last import. In the import options I have it set to update records based on the key field and add remaining records as new. I have tested the script THOUROGHLY using the script debugger and everything seems to work perfect. Everytime I run it and watch the status with the debugger it works perfectly.

However, once I turned on the script to run unattended I ran into issues where it was duplication records and NOT MATCHING the key field as the script step indicates. Has anyone else experienced this behavior? I read somewhere with regards to XML import that you have to find the set that matches before running the import, is this true? If so, what is the easiest way to perform the find?

I am actually thinking of ditching this in favor of creating a trigger in the backend to push the record to FM via ODBC when a new one is created and or modified. Any thoughts or direction on this approach? I have read a little, and looked at some of the existing triggers and it seems somewhat straightforward, but my SQL is not great. I can see how they update records in their own dbase, but I am unsure how to push the record to a different DSN.

[comment]

I read somewhere with regards to XML import that you have to find the set that matches before running the import, is this true?

Yes. This applies to ANY import using the option "Update matching records in found set", not just XML. Only found records are updated, and if you have also selected "Add remaining data as new records" then any record matching an omitted record will be imported as duplicate.

If so, what is the easiest way to perform the find?

Show All Records.

[GManNAtl]

Thank you for clarifying this, and duh on the find I will do another clean import tonight and then have the script show all before running the imports and see how it goes.

Concerning the method I discussed at the end does anyone have experience with something like this in SQLAnywhere? To me it just seems like triggering the update from the source is much cleaner and actually provides for the data to be realtime.

[NYPoke]

It also depends what you are doing with the data. You could mount the SQL Server Database in FileMaker. It would just appear as a Data Source.

While FileMaker has some performance issues with external data (who doesn't), you can do quite a lot with it. Kind of depends on whether you are just going to display the data or do a lot of maniuplation to it. With Relationships set up, you could write a script to do the imports this way as well.

If you are mostly displaying the data, and FileMaker & SQL Server on on the same site, you can accomplish a lot by mounting SQL Server in FileMaker.

I've hit some performance issues with scripts I need to work fast, but over all, it is a very powerful tool, an excellent one.

I haven't tried the 64 bit version, but since you are running through ODBC, it shouldn't be a problem.

Just something to think about.