FM8 to external MySql

[Gerd Muller]

hi all

My FM8 solution manages a regional volleyball competition. Periodically, I want to update the games, results and other things on the web, where the data is stored in MySql tables.

For now, I'm calculating SQL queries and copy/paste them in phpMyAdmin.

But there should be this "perform SQL query"-thing in ScriptMaker for this, shouldn't it?

I was able to set a ODBC-DSN on my Mac (10.4.7) to a local hostet MySql server. But I didn't manage to create a working DSN to put my FM data to a external MySql Server hosted on my ISP.

I'm sure somebody has this done before.

Thanks for help

Gerd

[Ocean West]

using fx.php i am able to gather data from Filemaker and with a little php set that data into an sql table. I use that here to update FMPug usergroups. As this data is maintained in the FMPug filemaker data and we are using MySQL here.

[Gerd Muller]

Thanks Stephen. The FM solution runs on a win. So there is no integrated webserver. And if there would be a script on my ISP's webserver, how can this open a connection to my local PC?

Isn't it possible to PUT the data from FM to MySql instead of GET them in MySQL from FM? I thought, setting up a DSN would be easy, but FM is searching the table on localhost instead of the server given in the DSN-settings.

Am I missing some basics?

Thanks for help

Gerd

[Jonathan Monroe]

Using FMP to push to and pull from a MySQL database is straightforward.

To pull data from MySQL, you use the Import Records script step to execute a SELECT statement against an ODBC data source configured to connect to your MySQL database. Part of configuring the step is to map columns from the returned recordset to the your defined FMP fields.

To update the MySQL database, you use the Execute SQL step to execute an UPDATE or INSERT statement against the ODBC data source. You would build the SQL string by concatenating values from your FMP fields, like this:

Execute SQL [No dialog; DSN: myDSN;

    Calculated SQL Text: "UPDATE products_description SET products_viewed=" 

   & myFMPDatabase::Products_Viewed 

   & " WHERE products_id=" 

   & myFMPDatabase::Products_ID]

You will need an ODBC driver for MySQL, such as the one available for OS X from Actual Technologies:

www.actualtechnologies.com/product_opensourcedatabases.php

Jonathan Monroe

Actual Technologies - ODBC for Mac OS X

www.actualtechnologies.com

[Gerd Muller]

Thanks very much Jonathan. I tried the ODBC drivers from actualtechnologies and I could establish a connection to a external MySql Server. As you mentioned, I use the "Execute SQL" step doing an INSERT IGNORE and a UPDATE query. I'm familiar with SQL-queries, so no problem here.

The dilemma is this:

With the ODBC drivers from actualtechnologies, I can establish a connection anywhere, but the special characters (umlauts) are hosed. I tried some settings in the "Advanced language", but no go...

with the ODBC drivers from mysql.org, I can only establish a connection to a local MySql Server on the same machine. But the umlauts are stored correctly.

So: Is there anybody firing INSERT- and UPDATE-SQL-queries from FM to a external MySql database with working encoding?

Thanks for help

Gerd

[Jonathan Monroe]

The correct settings in the Advanced Language settings panel for use with FMP 8 are as follows:

[ ] to send text

[ X ] to receive text

[ ] Return Unicode data types

[ X ] Set application locale to match driver encoding

For FMP 7 or earlier, you should use:

[ X ] to send text

[ X ] to receive text

[ ] Return Unicode data types

[ ] Set application locale to match driver encoding

After restarting FMP, your umlauts should be correctly inserted and retrieved using the Actual Technologies driver.

Jonathan Monroe

Actual Technologies - ODBC for Mac OS X

www.actualtechnologies.com

Edited July 28, 2006 by Guest

[Brian12568]

Hi all,

I kinda have a similar problem to the above chap so thought i would post here

i am just trying to understand the syntax for inserting a record into mysql from filemaker

here is the syntax

"INSERT INTO customer (title,first_name,sur_name)

VALUES ('customer::title','customer::first_name','customer::sur_name')"

if i leave the single quote out round the values mysql moans about incorrect syntax with the single quotes in it it treating the text as litteral as appose to values from the database (current record of course)

any help appreciated.

Brian