Jump to content

ODBC (FM as data source) and Unicode table names


Buckie
 Share

This topic is 2601 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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.
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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:

 

post-100791-0-20224000-1403036371_thumb.

post-100791-0-64396300-1403036378_thumb.

 

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.

Link to comment
Share on other sites

This topic is 2601 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.