MySQL Bugs: #7487: String dates don't serialized

Bug #7487	String dates don't serialized
Submitted:	22 Dec 2004 16:01	Modified:	26 Sep 2006 14:33
Reporter:	V. Geovanny Fiallo Carranco	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.0.4	OS:	Windows (Windows XP)
Assigned to:	Reggie Burnett	CPU Architecture:	Any

Description:
I have this.
ArrayList arlParametros = new ArrayList();
//Some code
arlParametros.Add("1976-05-20");
//More some code
MySqlParameter[] parCliente = {new MySqlParameter("@i_FechaNacimiento",MySqlDbType.Date)};
parCliente[0].Value = arlParametros[0];
myCom = new MySqlCommand(strQuery, myCon);
myCom.CommandType = CommandType.StoredProcedure;
myCom.Parameters.Add(parCliente[0]);
myDa = new MySqlDataAdapter(myCom);
myDa.Fill(ds);
//More code

When i run this, the following error apear "Only DateTime objects can be serialized by MySqlDateTime"

Regards

How to repeat:
See description

Can you post the text of the query you used and the signature of the stored procedure?

This is my sp:

Create Procedure sp_cliente_crear
	(IN i_id_cliente Integer,IN i_Cedula Char(10), IN i_Nombre VarChar(60), IN i_Apellido VarChar(60)
	,IN i_LugarNacimiento Integer, IN i_FechaNacimiento Date, IN i_EstadoCivil Char(1)
	,IN i_Edad TinyInt, IN i_Instruccion Integer, IN i_LugarTrabajo VarChar(59), IN i_Ocupacion Integer
	,IN i_CargasFamiliares TinyInt, IN i_Hijos TinyInt, IN i_HijosEdad VarChar(20), IN i_Vivienda Bool
	,IN i_Terreno Bool)
Begin
	Insert Into cliente
		(cl_id_cliente, cl_cedula, cl_nombre, cl_apellido
		,cl_id_lugar_nacimiento, cl_fecha_nacimiento, cl_estado_civil
		,cl_edad, cl_id_instruccion, cl_lugar_trabajo, cl_id_ocupacion
		,cl_cargas_familiares, cl_hijos, cl_hijos_edad, cl_vivienda
		,cl_terreno) 
	Values (i_id_cliente, i_Cedula, i_Nombre, i_Apellido
		,i_LugarNacimiento, i_FechaNacimiento, i_EstadoCivil
		,i_Edad, i_Instruccion, i_LugarTrabajo, i_Ocupacion
		,i_CargasFamiliares, i_Hijos, i_HijosEdad, i_Vivienda
		,i_Terreno);
End

Is a simple insert into a table.

Here is a test case I just wrote to test this.  It passes.  Can you test your code with the latest released version of the connector (1.0.4) and see if you still have the problem.  I am unable to reproduce.

		public void UsingDatesAsStrings()
		{
			MySqlCommand cmd = new MySqlCommand("INSERT INTO test (dt) VALUES (?dt)", conn);
			cmd.Parameters.Add("?dt", MySqlDbType.Date);
			cmd.Parameters[0].Value = "2005-03-04";
			cmd.ExecuteNonQuery();

			MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM test", conn);
			DataTable dt = new DataTable();
			da.Fill(dt);
			Assert.AreEqual(1, dt.Rows.Count);
			DateTime date = (DateTime)dt.Rows[0]["dt"];
			Assert.AreEqual(2005, date.Year);
			Assert.AreEqual(3, date.Month);
			Assert.AreEqual(4, date.Day);
		}

Hi Reggie, sorry for delay.
I'm test with .Net connector 1.0.4 and error persist.
"Only DateTime objects can be serialized by MySqlDateTime"

This is my sp:
CREATE PROCEDURE `test`.`ps_pru104_1` (IN i_detalle VarChar(45), IN i_fecha Date)
BEGIN
  Insert Into pru104
  Values(i_detalle, i_fecha);
END

This is my c# code:
private void button1_Click(object sender, EventArgs e)
        {
            IDbConnection miConeccion = new MySqlConnection();
            miConeccion.ConnectionString = "Data Source=Uio-SqlServer;Database=test;User Id=desarrollo;Password=";
            IDbCommand miComando = new MySqlCommand();
            miComando.Connection = miConeccion;
            miComando.CommandText = "ps_pru104_1";
            miComando.CommandType = CommandType.StoredProcedure;
            IDbDataParameter[] miParametro = {new MySqlParameter("@i_detalle", MySqlDbType.String, 45)
                ,new MySqlParameter("@i_fecha", MySqlDbType.Date)};
            miParametro[0].Value = textBox1.Text;
            miParametro[1].Value = "2005-07-21";
            miComando.Parameters.Add(miParametro[0]);
            miComando.Parameters.Add(miParametro[1]);
            int intResultado;
            miComando.Connection.Open();
            intResultado = miComando.ExecuteNonQuery();
            miComando.Connection.Close();
        }

Hi,
your error message comes from the failure of connector to serialize a string representation of a date as a date. Instead you have to pass the paramater as datatime object. System.Datetime is parsed without problems to MySqlDateTime type, so in your code you can do a few things:

myparameter.value = CDate(stringdate)

or

myparamater.value= object (Ej. datable.row(j)("mydate") where the column "mydate" must be a System.dateTime datatype. In this last example, if you have a dataset mapping a Mysql structure, this mean that the date column is mapped as MySqlDateTime type instead of System.dateTime, you also will have an error  (that it is my experience), because the serializer expect a System.datetime type of object)

Mauro,

I tested on 1.0.6 and got the follow message:

Additional information: Parameter '?in' must be defined

in intResultado = miComando.ExecuteNonQuery();

As far as I can understand this is another error. But I think this is a bug.

This bug does not appear in 1.0.7.  Please try a later GA version.