MySQL Oddities

[Dagel]

I'm very new at the whole ODBC/ESS thing and have a couple of questions which are boggling my mind:

1. I have an ODBC connection to a MySQL database that when viewed in Sequel Pro, shows to contain 1148 rows. But when I look at that table in one FM client (served by Server 10 Advanced) shows to contain 1114 records and on another machine it shows to have only 1095 records. Why the discrepancy? I've tried refreshing the window and flushing the MySQL cache to no effect.

Furthermore, when I search for specific records I know to be in the MySQL table (using the find function in FM), it only retrieves 3 of the 5 records.

2. When simply switching from a regular FM layout to a layout with MySQL data, I get a "Find in progress.. processing query" status pop up which starts counting from -10 to the number of records in the table. I didn't think that just switching from one layout to another should take so long, especially since there's less than 1200 records.

I'm totally confused! Thanks for any and all assistance!

[fabriceN]

You're not confused, I think FileMaker is.

I couldn't find words to tell how much ESS sucks, but I would certainly never use it again. Instead, I rather use OS scripts to perform SQL queries, and sync tables this way.

Faster, more secure, more portable.

Jim McNeely's function is an excellent example of how simple it is:

http://www.briandunning.com/cf/975

[Josh Ormond]

I'm very new at the whole ODBC/ESS thing and have a couple of questions which are boggling my mind:

1. I have an ODBC connection to a MySQL database that when viewed in Sequel Pro, shows to contain 1148 rows. But when I look at that table in one FM client (served by Server 10 Advanced) shows to contain 1114 records and on another machine it shows to have only 1095 records. Why the discrepancy? I've tried refreshing the window and flushing the MySQL cache to no effect.

Furthermore, when I search for specific records I know to be in the MySQL table (using the find function in FM), it only retrieves 3 of the 5 records.

2. When simply switching from a regular FM layout to a layout with MySQL data, I get a "Find in progress.. processing query" status pop up which starts counting from -10 to the number of records in the table. I didn't think that just switching from one layout to another should take so long, especially since there's less than 1200 records.

I'm totally confused! Thanks for any and all assistance!

Just by chance, what driver are you using for the ODBC connector?

[Dagel]

I'm using the open source driver from Actual Technologies.

[Josh Ormond]

And you have the latest 2.9 update?

[Dagel]

I do.

[Josh Ormond]

Couple other things that can cause issues:

• Make sure the DSN is setup as a System DSN, not a User DSN.
  
  Passwords...if you have more than one password accessing the ODBC tables, make sure there are no filtering privileges or such that restricts access to certain records.
  
  FM Accounts and Privileges - same
  
  Check an open script for finds and such...Show all records??? (Just because its so easy to miss, do it to myself all the time).

I had some issue when I first started using the ESS, but it mainly came down to a driver issue.

Have you emailed Actual Technologies???

[Dagel]

Thanks for your time. Unfortunately, I already had everything configured as you suggested.

I have spoken to Actual Technologies before and they've been wonderful to work with. I'll give them another shot and hopefully post an answer.

[Josh Ormond]

I have spoken to Actual Technologies before and they've been wonderful to work with. I'll give them another shot and hopefully post an answer.

That would be good. Sorry, just so many variables that can cause issues when you are talking about ESS.

[Jonathan Monroe]

This sounds to me like you don't have your license key installed. Without the license key, the driver will only return the first 3 rows from any query. Since FM makes lots of little queries, it won't be obvious that this is the problem.

Can you double check that your key is installed (press "Licenses" in the driver's DSN setup assistant)?

Jonathan Monroe

Actual Technologies - ODBC for Mac OS X

http://www.actualtechnologies.com

[Dagel]

Don't I feel like an idiot! When I moved the driver from an old machine I completely neglected to type in the license key.

Thanks so much for your help!