Thread

This forum suggests that using the Actual Technologies ODBC driver for MySQL is also an option.

This forum suggests that using the Actual Technologies ODBC driver for MySQL is also an option.

You might also look at this page on the Filemaker web site.

Thanks Keith.

Yup, I’ve already explored all those avenues. The Actual MySQL driver is for OS X, and we are currently hosted on Windows. We might look at moving, and a coupla days ago i emailed Actual to check what control their driver gives over encoding.

Thanks for the assist, Keith!

Chris, let us know what you find out from Actual.

Sue:

If Actual confirm that their ODBC driver will allow us to insert UTF-8 encoded text into MySQL, and if we decide it’s worth the hassle of changing FM hosts, we will still be left with the need to get EE talking UTF-8 to MySQL.

The only way I have found thus far to achieve this is the small hack described in your wiki here. Can you/Ingmar confirm that this is the way to go to achieve that?

Chris

You’ve presumably read the MySQL - Filemaker partnership announcement, which surely means that the latest ODBC connectors are approved, and know that the (free!) connectors are available for many different platforms (i.e. surely no need to change FM hosts). The above-mentioned Filemaker Forum link confirms that the connectors work OK with German.

You’ve presumably read the MySQL - Filemaker partnership announcement, which surely means that the latest ODBC connectors are approved

You might expect so. But elsewhere on the FM site you will find this page on ESS. And towards the end you will find a list of supported drivers which is (at least in respect of MySQL) the same as this list.

From which you will see that FM does not support any SQL driver after 3.51.14, dating from early 2007. Some partnership!

Given the evidence that later, unsupported drivers do the trick, I am pressing my hosting firm to reconsider their position on use of these drivers. I am not very hopeful.

The “which ODBC drivers are supported” FAQ item is more than a year out of date. The newer page on ESS says that both MySQL 5.0 and 5.1 are certified, and explains that “The External SQL Data Source feature establishes a live two-way connection between FileMaker Pro 10 and SQL data sources”. So you can run synchronized FM Pro and MySQL databases on a host. You can periodically export the MySQL database, with character set intact, to EE.
But you probably want to design a system that logs changes in the FM database or its MySQL image, and periodically runs a custom query on the Filemaker or MySQL database to export just the changes since the last time the EE copy was updated.

As I said, the FAQ was in important respects the same as the page on ESS. I linked to the FAQ just because it was shorter. A year old does not necessarily mean a year out of date. Turns out there is another, later FAQ specifically for FM10 which gives the same information for MySQL under Windows. Here it is for your entertainment.

The issue is not which version of MySQL is supported, but which version of the MySQL ODBC connector.

It seems fairly clear that FM10 still officially supports only v 3.51.14, which dates from just before the time FM9 was introduced and this whole ESS thing was launched. This matches the understanding of our FileMaker hosts, who are FileMaker specialists and I hope are up to date in these matters.

Chris, at this point are you fairly certain that this is a FileMaker issue?  It seems so to me; but this thread has gotten very convoluted.

Lisa:

Keith and I have certainly used a lot of bandwidth on the FileMaker side. But I’d still appreciate a response to my post #20 timed at 09:41, to tidy up the EE end of the link.

Chris

Hi Chris,

First of all, let’s cut back on the Filemaker talk a bit—I am not familiar with this program, and I don’t think it’s the core issue here. Let’s take a look at the example you gave earlier, using your two special pages.

the section headed Testbed is my specially created field, which at present is set to a collation of macroman. (I have also tried utf9 - neither solves our problem.)

Macroman is quite an unusual collation, and I suppose utf9 was a tpyo, meaning utf-8? That said, let’s leave collation aside for the time being, we might not need to change anything there at all as long as the specified encoding is correct. I advise against using “Automatically Convert High ASCII Text to Entities” for now, by the way.

On the Norton St Philip page the text has been created in EE, and is displaying fine.

I can confirm that. It’s UTF-8 encoded characters, properly declared as such in your header.

On the Frome page the text has been written to the SQL database from FileMaker, and is displaying garbage fancy characters. This is the mechanism we need to get working properly.

Well, the problem is this: you are still declaring these characters as utf-8 in your HTML page, only they really are iso-8859-1 encoded. (If I force my browser to use the iso-8859-1 encoding instead of relying on what’s specified in the template, the page displays correctly, too).

I say again that our problem is about encoding in the SQL DB.

Actually, it’s more about declaring it correctly. As I’ve said, I have no experience with Filemaker, but I suspect that it can only write iso-8859-1 characters (or at least does so by default). That, in itself, is not a problem, you only need to specify the correct encoding in your HTML pages:

<meta http-equiv="Content-type" content="text/html; charset=iso-8859-1" />

We need to get EE and FM writing and reading the same encoding.

Quite so. I usually recommend to go “Unicode all the way”, as it were. In this case, however, you might want to give Latin-1 a try, using that encoding with EE as well. Just make sure that all parties concerned (FM and EE, in this case) use the same encoding, and specify it correctly, too.

Keith and I have certainly used a lot of bandwidth on the FileMaker side. But I’d still appreciate a response to my post #20 timed at 09:41, to tidy up the EE end of the link.

No hacking for now, please. Collation does not appear to be the issue here.

Hi Ingmar

Thanks for this thoughtful analysis. I’m still not clear how to proceed, but I sense that we may be nearly there.

I will remove the use of entities for high ASCII chars, which I was never keen to adopt.

You seem to have confirmed that EE is writing and displaying utf-8, and that FM is writing iso-8859-1/latin-1, and that this appears OK when your browser is forced to display that charset. This leaves me with two questions.

The first is academic, but something I would like to understand for future reference: why does the wiki have instructions on hacking EE to use utf-8 when in my case it seems to be doing so by default?

The second is the one I need to resolve in order to follow your suggestion to give latin-1 a try: how do I get EE to do that, so that EE and FM are writing the same stuff to SQL, and sending the same stuff to the browser?

Actually, there is a third, related question that I already raised before the FileMaker issues distracted us: what are the effects of all the variables shown in the EE SQL Manager, and how do I change them, if I need to? Screen shot from our CP attached, showing a worrying mix of the two charsets.

Time for some sleep over here. Hope to hear from you tomorrow.

cheers