mcs/class/System.Data.OracleClient/Test/System.Data.OracleClient.jvm/OracleType/OracleType_Date.cs

   1 //
   2 // Copyright (c) 2006 Mainsoft Co.
   3 //
   4 // Permission is hereby granted, free of charge, to any person obtaining
   5 // a copy of this software and associated documentation files (the
   6 // "Software"), to deal in the Software without restriction, including
   7 // without limitation the rights to use, copy, modify, merge, publish,
   8 // distribute, sublicense, and/or sell copies of the Software, and to
   9 // permit persons to whom the Software is furnished to do so, subject to
  10 // the following conditions:
  11 //
  12 // The above copyright notice and this permission notice shall be
  13 // included in all copies or substantial portions of the Software.
  14 //
  15 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  16 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  17 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  18 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  19 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  20 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  21 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  22 //
  23
  24 using System;
  25 using System.Data;
  26 using System.Data.OracleClient ;
  27
  28 using MonoTests.System.Data.Utils;
  29
  30
  31 using NUnit.Framework;
  32
  33 namespace MonoTests.System.Data.OracleClient
  34 {
  35         [TestFixture]
  36         public class OracleType_Date : GHTBase
  37         {
  38                 private string dateColumnName;
  39                 private string dateTableName;
  40
  41                 private OracleConnection        con;
  42                 private OracleDataReader        dr      =       null;
  43
  44                 private DateTime TestedDate1;
  45                 private DateTime RetDate;
  46
  47                 [SetUp]
  48                 public void SetUp()
  49                 {
  50                         Exception exp = null;
  51                         BeginCase("Setup");
  52                         try
  53                         {
  54                                 dateTableName = ConnectedDataProvider.EXTENDED_TYPES_TABLE_NAME;
  55
  56                                 dateColumnName = "T_DATE";
  57
  58                                 con = new OracleConnection(ConnectedDataProvider.ConnectionString);
  59                                 con.Open();
  60                                 Compare("Setup", "Setup");
  61                         }
  62                         catch(Exception ex)     {exp = ex;}
  63                         finally {EndCase(exp); exp = null;}
  64                 }
  65
  66                 [TearDown]
  67                 public void TearDown()
  68                 {
  69                         if (con != null)
  70                         {
  71                                 if (con.State == ConnectionState.Open) con.Close();
  72                         }
  73                 }
  74
  75                 public static void Main()
  76                 {
  77                         OracleType_Date tc = new OracleType_Date();
  78                         Exception exp = null;
  79                         try
  80                         {
  81                                 tc.BeginTest("OracleType_Date");
  82                                 tc.SetUp();
  83                                 tc.run();
  84                                 tc.TearDown();
  85                         }
  86                         catch(Exception ex){exp = ex;}
  87                         finally {tc.EndTest(exp);}
  88                 }
  89
  90                 [Test]
  91                 public void run()
  92                 {
  93                         Exception exp = null;
  94                         OracleCommand cmd = new OracleCommand();
  95                         string rowId = "54416_";
  96
  97                         try
  98                         {
  99                                 // clean the test table
 100                                 cmd = new OracleCommand(string.Format("DELETE FROM {0} WHERE ID like '54416_%'", dateTableName));
 101                                 cmd.Connection = con;
 102                                 cmd.ExecuteNonQuery();
 103                         }
 104                         catch(Exception ex)
 105                         {
 106                                 exp = ex;
 107                         }
 108
 109                         #region         ---- testing parameterized query with a simple date ----
 110                         try
 111                         {
 112                                 BeginCase("testing parameterized query with a simple date");
 113                                 rowId = "54416_" + TestCaseNumber.ToString();
 114
 115                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values ('{2}', :date1)", dateTableName, dateColumnName, rowId));
 116                                 cmd.Connection = con;
 117                                 TestedDate1 = new DateTime(2001, 1, 13);
 118                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 119                                 cmd.ExecuteNonQuery();
 120                                 // checking that value returned correctly;
 121                                 cmd.CommandText = string.Format("select {0} from {1} where ID='{2}'", dateColumnName, dateTableName, rowId);
 122                                 cmd.Parameters.Clear();
 123                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 124
 125                                 Compare(TestedDate1,RetDate);
 126                         }
 127                         catch(Exception ex)
 128                         {
 129                                 exp = ex;
 130                         }
 131                         finally
 132                         {
 133                                 if (dr != null)dr.Close();
 134                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 135                                 {
 136                                         CleanTestRow(rowId);
 137                                         con.Close();
 138                                 }
 139
 140                                 EndCase(exp);
 141                                 exp = null;
 142                         }
 143
 144                         #endregion
 145
 146                         #region         ---- testing parameterized query with a 1753 date min  ----
 147                         try
 148                         {
 149                                 BeginCase("testing parameterized query with a 1753 date min");
 150                                 rowId = "54416_" + TestCaseNumber.ToString();
 151
 152                                 con.Open();
 153                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId));
 154                                 cmd.Connection = con;
 155                                 TestedDate1 = new DateTime(1753, 1, 1);
 156                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 157                                 this.Log(cmd.CommandText);
 158                                 cmd.ExecuteNonQuery();
 159
 160                                 //' checking that value returned correctly
 161                                 cmd.CommandText = string.Format("select {0} from {1}  where ID='{2}'", dateColumnName, dateTableName, rowId);
 162                                 this.Log(cmd.CommandText);
 163                                 cmd.Parameters.Clear();
 164                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 165                                 Compare(TestedDate1.Date, RetDate.Date);
 166                         }
 167                         catch(Exception ex)
 168                         {
 169                                 exp = ex;
 170                         }
 171                         finally
 172                         {
 173                                 if (dr != null)dr.Close();
 174                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 175                                 {
 176                                         CleanTestRow(rowId);
 177                                         con.Close();
 178                                 }
 179
 180                                 EndCase(exp);
 181                                 exp = null;
 182                         }
 183
 184                         #endregion
 185
 186                         #region         ---- testing parameterized query with a future date ----
 187                         try
 188                         {
 189                                 BeginCase("testing parameterized query with a future date");
 190
 191                                 rowId = "54416_" + TestCaseNumber.ToString();
 192                                 con.Open();
 193                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId ));
 194                                 cmd.Connection = con;
 195                                 TestedDate1 = new DateTime(2500, 1, 13);
 196                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 197                                 cmd.ExecuteNonQuery();
 198                                 //' checking that value returned correctly
 199                                 cmd.CommandText = string.Format("select {0} from {1}  where ID='{2}'", dateColumnName, dateTableName, rowId);
 200                                 cmd.Parameters.Clear();
 201                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 202                                 Compare(TestedDate1, RetDate);
 203                         }
 204                         catch(Exception ex)
 205                         {
 206                                 exp = ex;
 207                         }
 208                         finally
 209                         {
 210                                 if (dr != null)dr.Close();
 211                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 212                                 {
 213                                         CleanTestRow(rowId);
 214                                         con.Close();
 215                                 }
 216
 217                                 EndCase(exp);
 218                                 exp = null;
 219                         }
 220
 221                         #endregion
 222
 223                         #region         ---- testing parameterized query with a future date ----
 224                         try
 225                         {
 226                                 BeginCase("testing parameterized query with a future date");
 227
 228                                 rowId = "54416_" + TestCaseNumber.ToString();
 229                                 con.Open();
 230                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId ));
 231                                 cmd.Connection = con;
 232                                 TestedDate1 = new DateTime(2500, 1, 13);
 233                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 234                                 cmd.ExecuteNonQuery();
 235                                 //' checking that value returned correctly
 236                                 cmd.CommandText = string.Format("select {0} from {1}  where ID='{2}'", dateColumnName, dateTableName, rowId);
 237                                 cmd.Parameters.Clear();
 238                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 239                                 Compare(TestedDate1, RetDate);
 240                         }
 241                         catch(Exception ex)
 242                         {
 243                                 exp = ex;
 244                         }
 245                         finally
 246                         {
 247                                 if (dr != null)dr.Close();
 248                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 249                                 {
 250                                         CleanTestRow(rowId);
 251                                         con.Close();
 252                                 }
 253
 254                                 EndCase(exp);
 255                                 exp = null;
 256                         }
 257
 258                         #endregion
 259
 260                         #region         ---- testing parameterized query with a time part ----
 261                         try
 262                         {
 263                                 BeginCase("testing parameterized query with a time part");
 264
 265                                 rowId = "54416_" + TestCaseNumber.ToString();
 266                                 con.Open();
 267                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId ));
 268                                 cmd.Connection = con;
 269                                 TestedDate1 = new DateTime(2500, 1, 13, 12, 13, 14);
 270                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 271                                 cmd.ExecuteNonQuery();
 272                                 //' checking that value returned correctly
 273                                 cmd.CommandText = string.Format("select {0} from {1}  where ID='{2}'", dateColumnName, dateTableName, rowId);
 274                                 cmd.Parameters.Clear();
 275                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 276                                 Compare(TestedDate1, RetDate);
 277                         }
 278                         catch(Exception ex)
 279                         {
 280                                 exp = ex;
 281                         }
 282                         finally
 283                         {
 284                                 if (dr != null)dr.Close();
 285                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 286                                 {
 287                                         CleanTestRow(rowId);
 288                                         con.Close();
 289                                 }
 290
 291                                 EndCase(exp);
 292                                 exp = null;
 293                         }
 294
 295                         #endregion
 296
 297                         #region         ---- testing parameterized query with a time part of 00:00 ----
 298                         try
 299                         {
 300                                 BeginCase("testing parameterized query with a time part of 00:00");
 301
 302                                 rowId = "54416_" + TestCaseNumber.ToString();
 303                                 con.Open();
 304                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId ));
 305                                 cmd.Connection = con;
 306                                 TestedDate1 = new DateTime(2500, 1, 13, 0, 0, 0);
 307                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 308                                 cmd.ExecuteNonQuery();
 309                                 //' checking that value returned correctly
 310                                 cmd.CommandText =string.Format("select {0} from {1}  where ID='{2}'", dateColumnName, dateTableName, rowId);
 311                                 cmd.Parameters.Clear();
 312                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 313                                 Compare(TestedDate1, RetDate);
 314                         }
 315                         catch(Exception ex)
 316                         {
 317                                 exp = ex;
 318                         }
 319                         finally
 320                         {
 321                                 if (dr != null)dr.Close();
 322                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 323                                 {
 324                                         CleanTestRow(rowId);
 325                                         con.Close();
 326                                 }
 327
 328                                 EndCase(exp);
 329                                 exp = null;
 330                         }
 331
 332                         #endregion
 333
 334                         #region         ---- testing parameterized query with a time part of 23:59:59 ----
 335                         try
 336                         {
 337                                 BeginCase("testing parameterized query with a time part of 23:59:59");
 338
 339                                 rowId = "54416_" + TestCaseNumber.ToString();
 340                                 con.Open();
 341                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId ));
 342                                 cmd.Connection = con;
 343                                 TestedDate1 = new DateTime(2500, 1, 13, 23, 59, 59);
 344                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 345                                 cmd.ExecuteNonQuery();
 346                                 //' checking that value returned correctly
 347                                 cmd.CommandText =string.Format("select {0} from {1}  where ID='{2}'", dateColumnName, dateTableName, rowId);
 348                                 cmd.Parameters.Clear();
 349                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 350                                 Compare(TestedDate1, RetDate);
 351                         }
 352                         catch(Exception ex)
 353                         {
 354                                 exp = ex;
 355                         }
 356                         finally
 357                         {
 358                                 if (dr != null)dr.Close();
 359                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 360                                 {
 361                                         CleanTestRow(rowId);
 362                                         con.Close();
 363                                 }
 364                                 EndCase(exp);
 365                                 exp = null;
 366                         }
 367
 368                         #endregion
 369
 370                         #region         ---- testing parameterized query with a time part of AM ----
 371                         try
 372                         {
 373                                 BeginCase("testing parameterized query with a time part of AM");
 374                                 string str = string.Empty; //This is an addional test ,passing GH mechnizim
 375                                 rowId = "54416_" + TestCaseNumber.ToString();
 376                                 con.Open();
 377                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId ));
 378                                 cmd.Connection = con;
 379                                 //TestedDate1 = new DateTime(2500, 1, 13, 11, 0, 0);
 380                                 TestedDate1 = new DateTime(1988,5,31,15,33,44,00);
 381                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 382                                 cmd.ExecuteNonQuery();
 383                                 cmd.Parameters.Clear();
 384
 385                                 //TODO:add also treat for other db
 386                                 if (ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.SQLServer || ConnectedDataProvider.GetDbType(con) == MonoTests.System.Data.Utils.DataBaseServer.Sybase)
 387                                 {
 388                                         cmd.CommandText = string.Format("select CONVERT(varchar,{0},120) from {1}  where ID='{2}'", dateColumnName, dateTableName, rowId);
 389                                         str = cmd.ExecuteScalar().ToString();
 390                                         Compare(TestedDate1, Convert.ToDateTime(str));
 391                                 }
 392                                 //' checking that value returned correctly
 393                                 cmd.CommandText =string.Format("select {0} from {1}  where ID='{2}'", dateColumnName, dateTableName, rowId);
 394                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar().ToString());
 395                                 Compare(TestedDate1, RetDate);
 396                         }
 397                         catch(Exception ex)
 398                         {
 399                                 exp = ex;
 400                         }
 401                         finally
 402                         {
 403                                 if (dr != null)dr.Close();
 404                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 405                                 {
 406                                         CleanTestRow(rowId);
 407                                         con.Close();
 408                                 }
 409
 410                                 EndCase(exp);
 411                                 exp = null;
 412                         }
 413
 414                         #endregion
 415
 416                         #region         ---- testing a where clause using a date ----
 417                         try
 418                         {
 419                                 BeginCase("testing a where clause using a date");
 420
 421                                 rowId = "54416_" + TestCaseNumber.ToString();
 422                                 con.Open();
 423                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId ));
 424                                 cmd.Connection = con;
 425                                 TestedDate1 = new DateTime(2500, 1, 13, 11, 0, 0);
 426                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 427                                 cmd.ExecuteNonQuery();
 428                                 //' checking that value returned correctly
 429                                 cmd = new OracleCommand(string.Format("select {0} from {1} where ID='{2}' and {0}= :date1", dateColumnName, dateTableName, rowId));
 430                                 cmd.Connection = con;
 431                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 432                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 433                                 Compare(TestedDate1, RetDate);
 434                         }
 435                         catch(Exception ex)
 436                         {
 437                                 exp = ex;
 438                         }
 439                         finally
 440                         {
 441                                 if (dr != null)dr.Close();
 442                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 443                                 {
 444                                         CleanTestRow(rowId);
 445                                         con.Close();
 446                                 }
 447
 448                                 EndCase(exp);
 449                                 exp = null;
 450                         }
 451
 452                         #endregion
 453
 454                         #region         ---- testing a where clause using a rage of dates ----
 455                         try
 456                         {
 457                                 BeginCase("testing a where clause using a rage of dates");
 458
 459                                 rowId = "54416_" + TestCaseNumber.ToString();
 460                                 con.Open();
 461                                 cmd = new OracleCommand(string.Format("insert into {0} (ID, {1}) values('{2}', :date1)", dateTableName, dateColumnName, rowId ));
 462                                 cmd.Connection = con;
 463                                 TestedDate1 = new DateTime(2007, 12, 31, 11, 59, 59);
 464                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 465                                 cmd.ExecuteNonQuery();
 466                                 //' checking that value returned correctly
 467                                 cmd = new OracleCommand(string.Format("select {0} from {1} where ID='{2}' and {0} >:date1 and {0} <:date2", dateColumnName, dateTableName, rowId));
 468                                 cmd.Connection = con;
 469                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1.AddSeconds(-1);
 470                                 cmd.Parameters.Add(new OracleParameter("date2", OracleType.DateTime)).Value = TestedDate1.AddSeconds(+1);
 471                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 472                                 Compare(TestedDate1, RetDate);
 473                         }
 474                         catch(Exception ex)
 475                         {
 476                                 exp = ex;
 477                         }
 478                         finally
 479                         {
 480                                 if (dr != null)dr.Close();
 481                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 482                                 {
 483                                         CleanTestRow(rowId);
 484                                         con.Close();
 485                                 }
 486                                 EndCase(exp);
 487                                 exp = null;
 488                         }
 489
 490                         #endregion
 491
 492                         #region         ---- testing a set statement ----
 493                         try
 494                         {
 495                                 BeginCase("testing a set statement");
 496
 497                                 rowId = "54416_" + TestCaseNumber.ToString();
 498                                 con.Open();
 499                                 cmd = new OracleCommand(string.Format("insert into {0} (ID) values('{1}')", dateTableName, rowId));
 500                                 cmd.Connection = con;
 501                                 cmd.ExecuteNonQuery();
 502                                 cmd = new OracleCommand(string.Format("update {0} set {1} = :date1 where ID='{2}'", dateTableName, dateColumnName, rowId));
 503                                 cmd.Connection = con;
 504                                 TestedDate1 = new DateTime(2500, 1, 13, 1, 2, 3);
 505                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 506                                 cmd.ExecuteNonQuery();
 507
 508                                 //' checking that value returned correctly
 509                                 cmd = new OracleCommand(string.Format("select {0} from {1} where ID='{2}' and {0}= :date1", dateColumnName, dateTableName, rowId));
 510                                 cmd.Connection = con;
 511                                 cmd.Parameters.Add(new OracleParameter("date1", OracleType.DateTime)).Value = TestedDate1;
 512                                 RetDate = Convert.ToDateTime(cmd.ExecuteScalar());
 513                                 Compare(TestedDate1, RetDate);
 514                         }
 515                         catch(Exception ex)
 516                         {
 517                                 exp = ex;
 518                         }
 519                         finally
 520                         {
 521                                 if (dr != null)dr.Close();
 522                                 if ( (con != null) && (con.State == ConnectionState.Open) )
 523                                 {
 524                                         CleanTestRow(rowId);
 525                                         con.Close();
 526                                 }
 527
 528                                 EndCase(exp);
 529                                 exp = null;
 530                         }
 531
 532                         #endregion
 533                 }
 534
 535                 /// <summary>
 536                 /// Deletes a row from the date table, according to its ID.
 537                 /// </summary>
 538                 /// <param name="rowId">Id of the row to delete.</param>
 539                 private void CleanTestRow(string rowId)
 540                 {
 541                         OracleCommand deleteCmd = new OracleCommand(string.Format("DELETE FROM {0} WHERE ID = '{1}'", dateTableName, rowId), con);
 542                         deleteCmd.ExecuteNonQuery();
 543                 }
 544         }
 545 }