Execute SQL[ ] and ESS

[brian rich]

We are running Filemaker 9 Advanced on a Windows 2003 server. We have ESS set up through an ODBC driver and this allows us to read and write in the tables of a mySQL database.

However the SQL database is now getting very large, with in excess of 1.3 million records in one table. We need to be able to regularly analyse data in this table, but everything just grinds to a halt when we try to run Filemaker finds on it.

One possible solution would be to offload the analysis task to the mySQL database, by generating suitable SQL statements in Filemaker, sending them to mySQL for execution and then using Filemaker to present the results. However the Execute SQL [] command does not see the ESS ODBC link, only the local ODBC data sources. Is there a way round this. or perhaps another way of sending the SQL and getting the results from within Filemaker?

Thanks

[mr_vodka]

Running an Execute SQL will not allow you to view the record with a layout setup to display ESS data. Although both functions reference your mySQL database, they are not the same thing. To get the results of an Execute SQL into FileMaker, usually you can import into a table or create a new file.

However, perhaps you can use it to update a mySQL view and then use the ESS capability to only look at the view. Then record set should be much smaller. Also if it is a reoccurring thing, you could look into setting up a stored procedure to handle creating the view all together.