Hi All,

  I have developed a Migration Tool that migrates data from MySQL to MSSQL. But I am finding a problem to read the time field value from MySQL database . I was using DataSet(.Net framework) for my application to retrieve the records from database & storing them into that. Although the values are there in MySQL DB , the value I got while reading was System.DBNull.Value .
   Is it because of the driver I am using?? I have used MySQL-ODBC Bridge Version 3.51 for my application. I have executed the query "select * from jobschedules" in the MySQL Query Browser. It is working fine there.But the problem occured when I executed the following code -

static void Main(string[] args)
{
    OdbcConnection oConn =new OdbcConnection("DRIVER={MySQL ODBC 3.51 Driver};SERVER=delta;Database=afndbpdev;Uid=afndbp dev;Pwd=afndbpdev;");
    OdbcCommand oComm=new OdbcCommand();

    String tabname=String.Empty;
    oConn.Open();
    oComm.Connection=oConn;
    DataSet ds =new DataSet();

    oComm.CommandText="select * from jobschedule";
    OdbcDataAdapter oDA=new OdbcDataAdapter(oComm);
    // OdbcDataReader dr=null;
    try
    {
         oDA.FillSchema(ds,System.Data.SchemaType.Source);
         oDA.Fill(ds);
         // dr= oComm.ExecuteReader();
    }
    catch(Exception err)
    {
          Console.WriteLine(err.Message);
    }
   /*int i=0;
    while(dr.Read())
    {
    if(i>=dr.FieldCount)i=0;
    Console.WriteLine(dr.GetDataTypeName(i)+" "+dr.GetValue(i).ToString());

        i++;
    }*/
    foreach(DataRow r in ds.Tables[0].Rows)
       foreach(DataColumn c in ds.Tables[0].Columns )
       {
        Console.Write(c.DataType.ToString()+" ");
        Console.WriteLine(r[c].ToString());
        if(c.DataType.ToString().Equals("System.TimeSpan") )
        {
             Console.Write(" Value: "+ r[c].ToString());
           if(r[c]==System.DBNull.Value)
           Console.WriteLine("The Value is null");

        }
        }
}

    I am getting the values for all other fields from the code except the time field(i.e. scheduletime) . Here is the schema details for the table 'jobschedule' -

CREATE TABLE jobschedule
(
  jobid int(11) default NULL,
  assnnbr int(11) default NULL,
  emails text,
  jobtype varchar(255) default NULL,
  jobdescription varchar(255) default NULL,
  jobarguments varchar(255) default NULL,
  lastuserbatched datetime default NULL,
  lastautobatched datetime default NULL,
  scheduletype char(1) default NULL,
  scheduledate tinyint(4) default NULL,
  scheduletime time default NULL,
  enabled char(1) default NULL
)

  The output is as follows -
System.Int32 1
System.Int32 295
System.String [email protected]
System.String DownloadMembership
System.String Daily Nightly Downlad
System.String
System.DateTime 2/8/2005 2:28:31 PM
System.DateTime 10/24/2005 11:06:37 PM
System.String D
System.Int16 1
System.TimeSpan
   Value: The value is Null
System.String Y

        The content in the database was something like this (in the same order of the columns as discussed while creating the table)-
1, 295, '[email protected]', 'DownloadMembership', 'Daily Nightly Downlad', '', '2005-02-08 14:28:31', '2005-10-24 23:06:37', 'D', 1, 23:00:00, 'Y'.

   Hope it will help you to figure out the problem.
Bye
Spc

While creating the table if you keep the 'scheduletime' as datetime, your problem should be solved.

Later on you can optimise the retrival of data in the schema setting it up to retrieve only the time part
like this
<AttributeType name="scheduletime" dt:type="time" />

I would like see, though, if it could be solved without changing the design of the table. Do update the forum if you come up with some solution to it.

Regards
Ankur Verma

Hi Ankur,
  Thanks for ur reply. I appreciate your idea but the datatype of the source table can't be altered. Otherwise it could have solved the problem very easily. Please think of some other alternatives. Meanwhile I am also trying to fix the problem.

with regards
Shubhabrata

Try this:

string myTime = row["myTime"] != DBNull.Value ? Convert.ToDateTime(row["myTime"].ToString()).ToString("HH:mm") : "";