Converting database from FM to MySQL

[BigMike]

I have just found out that FM v9 supports MySQL as the back end database. I am trying to figure out how to separate the data from my FM application to facilitate updates and make the solution easier to manage overall. Is this the way to go? If so, how do I take my existing FM application/data and "port" it over to MySQL. I just watched a demo of how to creat a MySQL database using FM from scratch, but how do you take an existing database and move it to MySQL?

[mr_vodka]

Perhaps this can assist you.

http://www.fmpromigrator.com/products/fmpro_migrator/index.html

[Vaughan]

"I am trying to figure out how to separate the data from my FM application to facilitate updates and make the solution easier to manage overall."

What makes you think that a MySQL database is easier to manage than a FileMaker database; or that a solution that uses both technologies would be easier than one alone?

[BigMike]

John,

Thanks for the tip. I've downloaded the app and will take a look at it.

[BigMike]

I'm pretty new at Filemaker, so let me tell you what I'm trying to do and maybe you can let me know if you think I'm going down the right path.

I'm looking to distribute a database to multiple customers. I'm at a loss as how to manage this as I go forward. How will I be able to update the software for, let's say, 50 different customers? If I make a change to the interface I'll need to be able to find a way to easily replace the interface on what would be 50 different FM databases.

My idea is to separate the database from the application by using MySQL. That way I can modify the front end app and then simply point it to the existing MySQL database. Then all I have to do is replace the FM app and there is no need to migrate data from the old database to the new.

Ideally I would like to be able to service all customers from the same database. My idea is to create a driving table that connects a login to a particular set of customer records (this is the way SAP does it - you log into a particular "client" and you get a distinct set of records). I'd like to do this with FM using MySQL. That way I can have just a single FM application and one database to service multiple customers. Do you know if anyone has tried this yet? Has it worked?

I'm also concerned about reliability and scalability. I've read numerous posts about the FM database becoming corrupted. I don't know if that's a legit concern or not. That would be another reason to move the database to MySQL.

Thanks in advance for any help you can give me!

[Søren Dyhr]

I've read numerous posts about the FM database becoming corrupted. I don't know if that's a legit concern or not.

If such matters concerns you, should you also consider a much more present danger to the integrity, 80% if not more, of all malicious attacks on databases presumes the php/mysql combo - if your solution should be supposed hosted publicly.

Another thing to consider is if these signs of corruption, you seems to have been stumbled over, is caused by attempts to tear filemaker out of it's realm - say attempts to make it be a spreadsheet.

There is a lot to be said about the deployer in this case. Often times are the corruption caused by misunderstood serving, where filemaker despite the name not is fireshared via the operating system, but as a protocol only.

The gear serving a solution should only maintain this single task and therefore have OS-level filesharing turned off. It happens quite often that filemaker are mistaken here, especially by busy sys-admins who think the tool as a Microsoft tool and takes chances not reading sufficiently - when the very nature of filemaker is different.

--sd

[Vaughan]

Think about the potential maintenance overhead of maintaining the FileMaker Pro and MySQL software on 50 different computers...

Updating is always a pain with any development environment. Some people have been using the "separation model" with FMP to achieve some degree of interface/data independence, but it's not easy or straightforward, and any gains are completely lost when the data model changes because both the front and back ends need to be modified.

The "multiple clients in one database" idea works better for the software developers more than the clients IMHO: there are many information privacy concerns to contend with, not to mention the fact that some clients want to control their own hardware and software. For them it could be a deal breaker.

[Søren Dyhr]

any gains are completely lost when the data model changes because both the front and back ends need to be modified.

Excellent point you bring here, if only relations were a matter of SQL statements, but say cascaded deletes or the bare existence of calc'fields in filemaker - makes the separation model a mixed blessing.

Since all database develoment have a perhaps only mild touch of, what I client of mine expressed this way:

I guess the problem lies with me continually adding functionality, had I been able to map out all my requirements from the very start then it would have been much easier for you.

Not even the most scrutinizing interrogation, can prevent the client from getting new inspiration, which very well could be the straw that broke the camels (relational structured ) back.

--sd

[BigMike]

Thanks for the thoughts. How do you manage multiple updates with all the data being in separate FM databases? I may be eventually be looking at dozens of clients (I hope!) who will need to have their apps updated from time to time. Also, I very much want to be able to host the database myself and allow users to access it remotely over the web. Is this not feasible?

[Søren Dyhr]

Perhaps this will work for you:

http://www.goya.com.au/UpdateManager

--sd