Bug #59806 DATE_FORMAT over a NULL value causes connector to abort
Submitted: 28 Jan 2011 21:14 Modified: 2 Feb 2011 17:30
Reporter: Felipe Castro Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:6.3.6 OS:Microsoft Windows (Windows 7)
Assigned to: CPU Architecture:Any
Tags: Connector/Net, date_format, SqlNullValueException

[28 Jan 2011 21:14] Felipe Castro
Description:
A SELECT DATE_FORMAT( field, '%m/%d/%Y' ) over a field that has a NULL value causes the connector to abort with this error when getting the value of the field into a C# variable:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
  at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean checkNull)
  at MySql.Data.MySqlClient.MySqlDataReader.GetString(Int32 i)

The same query with connector 1.0.7 works OK.  (I was trying to upgrade).

How to repeat:
Steps:

- Create a table with a date column.
- Enter a record that has a NULL value in the date field.
- With a C# program do a select that retrieves that specific record.
- Read the value into a string variable.

Specifically my code is something like this:

cmd.CommandText = " SELECT ..... DATE_FORMAT( EndDate, '%m/%d/%Y'), ....";
rdr = cmd.ExecuteReader();
...
String date = rdr.GetString(10);
...
[31 Jan 2011 16:21] Felipe Castro
I just noticed that it does not need to be the DATE_FORMAT function.

Any time that I attempt to do a reader.GetString(ix) on a date column that has a null value the connector aborts.

Unhandled Exception: System.Data.SqlTypes.SqlNullValueException: Data is Null. T
his method or property cannot be called on Null values.
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean
checkNull)
   at MySql.Data.MySqlClient.MySqlDataReader.GetDateTime(Int32 i)
   at Test1.Program.Main(String[] args) in C:\codetst\VisualStudio\Test1\Test1\P
rogram.cs:line 43
[2 Feb 2011 9:36] Tonci Grgin
Felipe, I am sorry but I do not see a bug here... What are you actually asking for? Is it:
  a) a way to write NULL-safe SQL query (as this is possible)
  b) a way to check for NULL return before reading (as this is possible)
  c) or for us to override default framework behavior?

The behavior of MySQL server in such cases is governed by SQL_MODE variable. Start from there and work your way up through null-safe SQL command and null-check in code. If you still think it's a bug, please elaborate more.
[2 Feb 2011 17:30] Felipe Castro
I just noticed this happens with integers too so maybe specifications changed between the two versions of the connector.  I am moving from Net Connector 1.0.7 to 6.3.6 so this must be a big jump.

Please note that I am not seeing this as a problem in MySQL but in the connector itself.  It works fine from MySQL tools.

Given the following:
- A table with NULL in a field defined as "int(10) unsigned DEFAULT NULL".
- I can read the contents of the table without problem using MySQL tools like mysql.exe or MySQL Workbench and the field shows as "(NULL)", which is expected.
- A C# program cannot read the field directly in the way it was possible in the old connector.

C# code is like this:

MySqlConnection conn - new MySqlConnection(CONNECTION_STRING);
conn.open();
MySqlCommand cmd = new MySqlCommand("",conn);

cmd.CommandText = "select age from table";
cmd.CommandType = System.Data.CommandType.Text;

MySqlDataReader reader = cmd.ExecuteReader();

while(reader.Read()) {
  Console.WriteLine(reader.GetInt32(0));
}

In the old connector this would print a zero, but in the new connector it throws an exception:

Unhandled Exception: System.Data.SqlTypes.SqlNullValueException: Data is Null.
his method or property cannot be called on Null values.
   at MySql.Data.MySqlClient.MySqlDataReader.GetFieldValue(Int32 index, Boolean
checkNull)
   at MySql.Data.MySqlClient.MySqlDataReader.GetInt32(Int32 i)
   at Test1.Program.Main(String[] args) in C:\codetst\VisualStudio\Test1\Test1\
rogram.cs:line 41

I just need to know if this is the expected behaviour for this level of the connector because it is different from the way it worked in the old one.  In that case i would just update the code to check for null.

If this is not the expected behaviour then it would be a bug.

Thanks for the help.

Felipe