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