importing MySQL via mysqldump

[rmstar]

FMP 9 on OSX

We have a situation where a FreeBSD-based MySQL 5 system generates data; we would like to then import that data into a FMP system.

Now, we've read of the ODBC route, but for security reasons, the MySQL host will not accept queries from outside; we would need to tunnel over SSH or similar.

We do cron a nightly mysql dump. We could easily scp that file to the FHP host system. Is there any tool to read the data in such a mysql dump, and import it into the FHP system? Or another {unix-based, as the MySQL is..} tool that reads the MySQL and creates a file that FMP can then read?

[rmstar]

I was asked why we don't use ESS approaches.

If I understand correctly {always a dubious assumption...}, ESS is realtime, requiring FMP to be able to query the isolated MySQL server. That gets us into the ssh tunnel issue.

Further, we would have to migrate from FMP 9 to 10. Is 10 even available{1}? Our FM programmer advised against 11 because "they changed the user interface and your users will get freaked.." [Likely true. Besides, we have multiple Mac PPC users, and I think 11 is Intel-only.]

Plus there's the speed issue; she has told us the ODBC will be slow; will ESS be faster? The MySQL server is fast but there are many milliseconds between it and the FMP box.

{1: I'd love to find a copy of FM 10 Server to buy.}

[comment]

I think you are mixing up the versions: ESS was introduced in version 9. The status area has been redesigned in version 10 - if that's what's supposed to get your users freaked.

We could easily scp that file to the FHP host system. Is there any tool to read the data in such a mysql dump, and import it into the FHP system?

I am not sure what "scp" or "FHP" mean. Couldn't you export your data in one of the standard plain-text formats?

[comment]

This seems to be a parallel thread to:

http://fmforums.com/forum/topic/77026-using-mysqldump-to-import-mysql/page__pid__361875

[rmstar]

This seems to be a parallel thread to:

http://fmforums.com/forum/topic/77026-using-mysqldump-to-import-mysql/page__pid__361875

MY FUBAR; I posted there initially before I realized it was a Legacy area. I could not see a way to delete it.

And "FHP" is shorthand for "I can't type FMP without getting it wrong..."

"scp" is the standard unix secure copy program. We use cron to create a new mysqldump file every night.

We then scp it to offsite archives.

I would like to find a utility that would read data from the dump and load it into FMP.

Or is there is another unix utility that dumps mysql in a form that does FMP like?

We've tried a CSV export with PHPMyAdmin, and that's not just hardly scriptable, but it also fails on some of the text fields;

clearly an embedded delimiter issue. It is not an issue within MySQL so I'd rather improve the export than alter the working database.

[comment]

is there is another unix utility that dumps mysql in a form that does FMP like?

I know very little about Unix and MySQL, but a quick Google search found:

http://dev.mysql.com/tech-resources/articles/xml-in-mysql5.1-6.0.html

Fileemaker can import XML, provided it's either in its own FMPXMLRESULT grammar, or a suitable XSL transformation stylesheet is available. I would presume that the transformation can also be easily automated on the source side.