FM won't commit changes to ESS?

[trevor_reznik]

Hi there,

First off, I'd like to thank everyone that contributes on here, I've found some great answers in the past.

I'll try my best to explain this problem, if there's any tests or anything I can do to get better info about the problem, just let me know. Running FileMaker Pro 9.0v1 on Mac OS X 10.4.

Basically, I have a brandspanking new version of FileMaker Pro 9. I have used FileMaker Pro 8 previously, to build an order management system for a small business I run. All pretty straightforward, calculations, invoices, some custom contact sheet-type print layouts, some basic scripting etc.

I am now trying to use FileMaker Pro 9 as a sort of front-end to access data from a MySQL database (I know, woe betithe those who use FM9 as an SQL frontend, but I thought I'd risk it)

After managing to get the MySQL ODBC connector (3.51.20) set up (the regular installer does not seem to work properly) I finally managed to access the SQL as an ESS in FM9. FYI, the process of installing the MySQL ODBC connector involved a *lot* of random crashes and malfunctions, until I eventually found instructions for manual installation that seemed to work.

All of the data came up as a shadow table. It allows me to do finds and sorts with no problem. I can view all the data. It occasionally allows me to make new records, although typically these will not show up on their own or with "Refresh Window", but only when FM is relaunched. Trying to edit anything typically results in the field contents being completely deleted. When I write something in a field, and leave the field to commit, it stays there for a brief moment before disappearing. I am fully aware of both the table "sync" function and the "Refresh Window" feature, neither are helping here.

I have tried using Actual Technologies ODBC Pack 2.7 (seeing as Filemaker seem to favour it over the open source variety). It installed with a lot less hassle and no random crashes, but after getting the ESS up, it behaved in pretty much the same way as before. I used the demo version, which does have usage limits, but I don't think these are the kind of functionality limitations they had in mind.

The MySQL database (150MB MySQL 5.0) which I've been using for testing contains one table (users) with 3 fields:

- user_id (small_int, unsigned zerofill, increment, not null)

FM says: "Can't modify auto, Numeric only". I cannot edit this field. However, when I manage to make a new record, the mySQL takes care of it.

- user (varchar, max 255)

FM says: Text, max 255

- pass (varchar, max 255)

FM says: Text, max 255

I don't think the problem is with FM, since I'm running a standard version, and my system is fully updated and meets minimum system requirements (iMac, PPC 1.8Ghz, 2GB ram, 500gb hd). Maybe the problem is the ODBC driver, but I've tried two different ones, and both managed to connect ok, view the data, sort, etc. It also lets me make *some* changes, i.e. randomly deleting field contents, so it's apparently working both ways.

The MySQL is being accessed by the Database Owner user/pass, so I don't think there's any permissions issues.

If somebody could offer any kind of advice, I would really appreciate it. As I said before, just let me know if there's anything I can check and report back with.

Trevor

[trevor_reznik]

Alright, thanks to everyone who had a quick read.

After 24+ hours working on this (I am a tidbit obsessive) I gave up, and did a clean system install, OS and everything. It was about that time anyway.

Problem appears to have been reference problems in the ODBC.ini files used by Macs ODBC administrator (there's two sets of these, in both the system library and user library, and apparently these can have all kinds of permissions problems, etc), which made the system try to use the MySQL ODBC Connector drivers (which do not seem to work very well) instead of the new Actual Technologies drivers (I guess there's a reason why FileMaker recommend them).

[Garry Claridge]

Thank you for your feedback ???

[Peter Bowen]

Hi Trevor,

I'm having the same problems. Would you mind being a little more specific about how you solved them so I can do the same.

Thanks

Pete

[elo]

Not sure what the OP (original poster's) situation was, but aside from installing drivers ODBC has the concept of a "DSN" (data source name).

Think of it this way, a DRIVER tells the computer how to talk to a specific flavor of database (MySQL, Oracle, etc) a DSN tells the computer how to use a given driver to talk to a specific database of a given type.

Further complicating things is that you can have "user" DSNs and "system" DSNs. (Also there are file DSNs and connectionless DSNs and more, but that's a digression)

Reading the OP's post it sounds like he managed to get two similarly named DSNs both trying to point at her (or his) MySQL database.

So when she tried to connect to MySQL within FileMaker it was confused about which DSN to use.

[Josh Ormond]

I was having some strange problems with FMP 9.0 and MySQL.

I ended finding an updated driver on the MySQL website...now it is working much better.

The original odbc connector that I installed was v3.51...they now have v 5.1 available.

Give a try...