Devart Forums

Posted: **Tue 16 May 2006 16:51**

Dear all,

take a look at the following SELECT statement (which doesn't make much sense the way it is shown here, but we have a very complicated, automatically created WHERE clause that follows the same principle):

SELECT COUNT(*) FROM mytable WHERE LCASE(myfield) = 'abc' AND myfield = 'ABC'

This will always return 0 when executes with MySQLDirect .NET, even if there is a row in mytable that matches myfield='ABC'!

If you separate the WHERE clause, the results are correct:

SELECT COUNT(*) FROM mytable WHERE LCASE(myfield) = 'abc'

as well as

SELECT COUNT(*) FROM mytable WHERE myfield = 'ABC'

shows the same correct result > 0.

The code is:

Code: Select all

MySqlConnection con = new MySqlConnection(YOUR SETTINGS);
MySqlCommand cmd;
con.Open();

cmd = new MySqlCommand("SELECT COUNT(*) FROM mytable WHERE LCASE(myfield) = 'abc' AND myfield = 'ABC'", con);
Debug.WriteLine(cmd.ExecuteScalar()); // always returns 0

cmd = new MySqlCommand("SELECT COUNT(*) FROM mytable WHERE LCASE(myfield) = 'abc'", con);
Debug.WriteLine(cmd.ExecuteScalar()); // returns 5

cmd = new MySqlCommand("SELECT COUNT(*) FROM mytable WHERE myfield = 'ABC'", con);
Debug.WriteLine(cmd.ExecuteScalar()); // returns 5
con.Close();

We are running MySql Version 5.0.21 on Suse 9.3, MySQLDirect .NET Version 3.50.10.0, .NET 2 with Csharp.

The same statement works fine for any other client, like the one integrated in php or the command line!

We'd be very grateful for help as we are about to finish a project.

Thanks, Olaf Schreiber, Berlin

Posted: **Wed 17 May 2006 06:41**

Any result you get, returns mysql server, but not MySQLDirect .NET. We just pass SQL statement to the server and receive results from it. Make sure your mysql server returns what you expect.

Posted: **Mon 22 May 2006 09:12**

For those who might be interested: the phenomenon described above occurs when UTF8_bin is used as table collation. The whole thing works fine with, i.e., latin1_general_cs.

Posted: **Mon 22 May 2006 09:28**

This must be happening because client charset and charset of a table are different.
Please execute tha following statement and send us its resultset

Code: Select all

show variables like 'character_set%'

Posted: **Mon 22 May 2006 12:55**

Indeed! We never really thought about that. The same thing happens with other clients as well unless they use utf8 as character_set_client.

Sorry for assuming it had to do with CoreLab

Is there a way to set the client character set with CoreLab? Something like

Code: Select all

cmd = new MySqlCommand("SET NAMES utf8;SET CHARACTER SET utf8;", con);
cmd.ExecuteNonQuery();

has proven unsuccessful.

Here's the list:

character_set_client - latin1
character_set_connection - latin1
character_set_database - utf8
character_set_filesystem - binary
character_set_results - latin1
character_set_server - latin1
character_set_system - utf8
character_sets_dir - /usr/share/mysql/charsets/

So far, we've solved the issue by modifying the character set of the field in the database.

Posted: **Mon 22 May 2006 13:08**

Code: Select all

Something like 

Code: 
cmd = new MySqlCommand("SET NAMES utf8;SET CHARACTER SET utf8;", con); 
cmd.ExecuteNonQuery();

This exactly is implemented in setting Unicode=true for the MySqlConnection component.

Posted: **Mon 22 May 2006 13:18**

It works!!!

Goodbye, and thanks...

Posted: **Tue 23 May 2006 07:41**

You are always welcome

Devart Forums

Strange bug with LCASE in SELECT statement - asking for support

Strange bug with LCASE in SELECT statement - asking for support