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 .
   Please help me as soon as possible. Is it because of the driver I am using?? I have used MySQL-ODBC Bridge Version 3.51 for my application.

  Thanks.
Shubhabrata

What is your select statement? Did you run the query in QA to check that you are getting the proper values you expect from the query?

Hi benson,
Thanks for ur prompt reply. Yes , I have executed the query "select * from jobschedules" in the MySQL Query Browser. It is working fine there.But the problem occurs 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
)

Pls help me fast as I am running out of time.
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

You are trying to write the value of the time col, then you are checking it for null, then writing it again??
Why????


    Console.Write(" Value: "+ r[c].ToString());
           if(r[c]==System.DBNull.Value)
           Console.WriteLine(r[c].ToString());


I am not familiar with MYSQL. But you need to:
First Check if the type is a "time"
Then check if it is NULL,
If it isn't NULL write the value,
If it is NULL, write a message "The value is NULL"


Jim

Jim,
I was just trying to be very sure that the datatype 'System.TimeSpan' is equivalent to 'Time' in database. That's why I was printing it twice. But Now I have changed thecode for Null value & I am printing -"The Value is Null".

Please check my earlier message, where I have displayed the output of the code.Please have a look on that . 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