Uploaded image for project: 'CUBRID APIs'
  1. CUBRID APIs
  2. APIS-618

[OLEDB] Cannot use OleDbDataReader.GetDateTime() on CUBIRD DateTime data type

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: OLEDB-9.2.0.0001
    • Fix Version/s: OLEDB-9.2.0.0001
    • Component/s: OLEDB
    • Labels:
    • Environment:
      • OS: Windows7 32bit
      • CUBRID: 9.2.0.0111
      • OLEDB: cubrid-oledb 9.2.0 (20130725)
      • .NET 4.0, VS 2010

      Description

      Actually the CUBRID DateTime data type is processed differently between ODBC and OLEDB drivers.

      • In ODBC driver, DateTime is coverted to the TIMESTAMP ODBC Type http://www.cubrid.org/manual/91/en/api/odbc.html, so we can use GetDateTime() to retrieve the data as a DateTime value
      • In OLEDB driver, DateTime is now coverted to the DBTYPE_CHAR OLEDB Type, so we can only use GetString() to retrieve the data but not GetDateTime()

      It may leads some midunderstandings to user, especially there isn't any description about "Data Mapping between CUBRID and OLEDB", the user may ask some questions like "why the column type is DateTime but I can't use GetDateTime() to retrieve the data in OLEDB?" or "why the CUBRID DateTime data type is treated as a DBTYPE_CHAR OLEDB type?"

      • Test Code
        1) In ODBC Driver {code}
        string strConn = @"Driver={CUBRID Driver};db_name=test_odbc;uid=dba;pwd=;server=10.34.64.57;port=33069;fetch_size=100;charset=UTF-8;";
        OdbcConnection connCubrid = new OdbcConnection(strConn);
        connCubrid.Open();

        OdbcCommand odbc_command = new OdbcCommand("", connCubrid);

        string strCreateTable = "CREATE TABLE t_type_datetime(col_1 DateTime)";
        odbc_command.CommandText = strCreateTable;
        odbc_command.ExecuteNonQuery();

        DateTime now = DateTime.Now;
        string now_string = now.ToString("yyyy-MM-dd HH:mm:ss");
        string strSqlInsert = string.Format("INSERT INTO t_type_datetime VALUES('{0}')", now_string);
        odbc_command.CommandText = strSqlInsert;
        odbc_command.ExecuteNonQuery();

        string strSqlSelect = "SELECT col_1 FROM t_type_datetime";
        odbc_command.CommandText = strSqlSelect;
        OdbcDataReader odbcReader = odbc_command.ExecuteReader();
        odbcReader.Read();
        Console.WriteLine(odbcReader.GetDataTypeName(0));
        Console.WriteLine(odbcReader.GetDateTime(0));
        odbcReader.Close();

        connCubrid.Close();{code}

      Output:

      TIMESTAMP
      2013/7/31 9:13:36
      

      2) In OLEDB driver (do the exact same thing)

      {code}
      string strConn = @"Provider=CUBRID OLE DB Provider;Data Source=test_oledb;Location=10.34.64.57;User ID=dba;Port=33059;Charset=utf-8;";
      OleDbConnection connCubrid = new OleDbConnection();
      connCubrid.ConnectionString = strConn;
      connCubrid.Open();

      OleDbCommand oledb_cmd = new OleDbCommand("", connCubrid);

      string strCreateTable = "CREATE TABLE t_type_datetime(col_1 DateTime)";
      oledb_cmd.CommandText = strCreateTable;
      oledb_cmd.ExecuteNonQuery();

      DateTime now = DateTime.Now;
      string now_string = now.ToString("yyyy-MM-dd HH:mm:ss");
      string strSqlInsert = string.Format("INSERT INTO t_type_datetime VALUES('{0}')", now_string);
      oledb_cmd.CommandText = strSqlInsert;
      oledb_cmd.ExecuteNonQuery();

      string strSqlSelect = "SELECT col_1 FROM t_type_datetime";
      oledb_cmd.CommandText = strSqlSelect;
      OleDbDataReader oledbReader = oledb_cmd.ExecuteReader();
      oledbReader.Read();
      Console.WriteLine(oledbReader.GetDataTypeName(0));
      Console.WriteLine(oledbReader.GetDateTime(0));
      oledbReader.Close();

      connCubrid.Close();{code}

      Output:

      DBTYPE_CHAR
      System.InvalidCastException : 指定的转换无效。
      
      • Summary: converts the CUBRID DateTime data type to the DBTYPE_CHAR OLEDB data type may leads some confusions to user (e.g. cannot use GetDateTime() to retrieve the data), based on the OLEDB data mappings http://msdn.microsoft.com/en-us/library/cc668759.aspx, I think DBTYPE_DBTIMESTAMP is may a proper OLEDB data type for CUBRID DateTime

        Issue Links

          Activity

          Hide
          cn15800 谢韦华[Bert] added a comment -

          After Modify:

          DBTYPE_DBTIMESTAMP
          2013/8/2 9:24:29

          Show
          cn15800 谢韦华[Bert] added a comment - After Modify: DBTYPE_DBTIMESTAMP 2013/8/2 9:24:29
          Hide
          ryin005 Ray Yin added a comment -
          • Test OS: Win7 32bit
          • CUBRID: 9.2.0.0111
          • Driver: cubrid-oledb 9.2.0.0001 (20130802)
          • .NET 4.0 frame work

          Test Result: Pass

          Close the issue

          Show
          ryin005 Ray Yin added a comment - Test OS: Win7 32bit CUBRID: 9.2.0.0111 Driver: cubrid-oledb 9.2.0.0001 (20130802) .NET 4.0 frame work Test Result: Pass Close the issue

            People

            • Assignee:
              cn15800 谢韦华[Bert]
              Reporter:
              ryin005 Ray Yin
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: