ODBC (FM as data source) and Unicode table names

[Buckie]

Help me out guys.

 

The config is as follows: FMS 13 on OSX 10.9.

Second machine: OSX 10.8 server running a web server and ODBC connector with a driver recommended in FM's manual.

 

So I managed to do a connection to a database and was able to retrieve some test data successfully, however I immediately ran into a huge problem: all my real table names contain non-latin characters, Unicode if you will, and whilst trying to issue

 

$sql = "SELECT * FROM Genuß";

 

I get a response:

 

Warning: odbc_exec(): SQL error: [FileMaker][FileMaker] FQL0001/(1:19): There is an error in the syntax of the query., SQL state 42000 in SQLExecDirect in /Library/Server/Web/Data/Sites/Default/connpro.php on line 6 Warning: odbc_fetch_row() expects parameter 1 to be resource, boolean given in /Library/Server/Web/Data/Sites/Default/connpro.php on line 7 Warning: odbc_result() expects parameter 1 to be resource, boolean given in /Library/Server/Web/Data/Sites/Default/connpro.php on line 8

 

Here's my code:

 

$name = 'FM';

$conn = odbc_connect($name, 'name', 'password');

$sql = "SELECT * FROM Genuß WHERE Field2 LIKE 4";

$result = odbc_exec ($conn,$sql);

odbc_fetch_row($result, 2);

$res1 = odbc_result($result, 1);

print ("$res1");

odbc_close($conn);

 

Is there a workaround? Maybe CAST can help? But I don't know how to use it. Actual results from test tables with names using normal "white man" characters output field contents with unicode text inside without problems, it's only the table names (and perhaps field names as well) that I'm having problems with.

[doughemi]

Did you try quoting the table name?

 

$sql = "SELECT * FROM "Genuß" WHERE Field2 LIKE 4";

 

The backslashes are needed to escape the literal quote marks

[Buckie]

Interesting idea but errors out nonetheless:

 

Warning: odbc_exec(): SQL error: [FileMaker][FileMaker] FQL0002/(1:14): The table named "Genuß" does not exist., SQL state HY000 in SQLExecDirect in /Library/Server/Web/Data/Sites/Default/connpro.php on line 6 Warning: odbc_fetch_row() expects parameter 1 to be resource, boolean given in /Library/Server/Web/Data/Sites/Default/connpro.php on line 7 Warning: odbc_result() expects parameter 1 to be resource, boolean given in /Library/Server/Web/Data/Sites/Default/connpro.php on line 8

 

The quotes are fine because once I put non-unicode name inside the quotes, it works.

[Buckie]

There, fixed it. So for anyone stuck in a similar situation... good old guesswork and "try every setting" worked. The problem lied in the way ODBC connector/driver were configured. Here are the screenshots with the correct settings that allow you to query tables with unusual characters in their names:

 

 

Quotes around the name are also necessary.

 

Curiously enough neither "Auto-detect language" which I chose first nor "wide API" settings were appropriate here.

"Describe text field as long varchar" that I enabled later just in case didn't make a difference either.

 

You must specify the multibyte text encoding (UTF-8) by hand.