Import from MySQL to FM

[rich williams]

OK i just posted a message below this and started reading a bunch of other posts. I thought i knew what i was doing.... Can SOMEONE please confirm, what the most practiced way of importing from MySQL to FM is.

It is my understanding that a bridge app must be made that will grab from MySQL either with JDBC or ODBC and import into FM using JDBC or ODBC.

Is there some way through the FM Pro scripting interface that one can import directly from MySQL into fm itself? And specifically from FM server served files?

Thanks.

[Vaughan]

You could export the MySQL data to a text file and simply import it into FMP.

[Anatoli]

It should work via slow ODBC.

Bridge application can be made in Lasso. It will be fast, executing automatically every minute or on demand.

[rich williams]

Vaughn - I need to automate it to some degree, would probably be easier to just use ODBC i think.

Anatoli - I'm not to concerned with speed. I am looking for cheap, fast development. I don't know anything about lasso but it looks like more money and more technology i don't want at this point. Given unlimited time and money i'd love to look at it. According to the FM Pro client manual it is possible to script ODBC imports... I have my MySQL ODBC datasource setup and i'm going to give this a try. My next problem will be getting it from fm client to Server in a way that the end user can understand and actually use. Is it possible to host files from Client and open network Served fm files on the same client?

Thanks guys

[Anatoli]

RE: Is it possible to host files from Client and open network Served fm files on the same client?

Yes. It is not the best scenario, but it will work.

Sure, Lasso is for this job the same as Ferrari Testarossa as groceries delivery vehicle.

IMHO ODBC can be scripted in Import script in FM.

[bruceR]

Posting and retrieving MySQL data can be done direct with applescript. No other apps required, though some are helpful. MySQLBridge is being developed to do this. Also see "mysql interface for AppleScript" on VersionTracker.

[cellmaker]

I'm passing data back and forth between FM and MySQL without any ODBC or other linking mechanism.

To move data from MySQL to FM, one scheme is to export a CSV file from MySQL with the following export settings:

{no field enclosure, no field escape, a field terminator of t and a line terminator of n}.

I use CocoaMySQL as a simple GUI front end to MySQL, and these settings are possible in the CSV dump.

Afterwards, change the extension on the .csv file to .tab and import from within the target FM file.

Unfortunately, FM will not recognize the field names as field names, but you get the fieldnames in the first record, so you can match them up, import, then erase the record with the fieldnames.

You also have to change dates to FM format because they come through as text [yyyy-mm-dd].

This seems to be fairly clean and not too difficult.

Charlie