mcs/class/System.Data.OracleClient/Test/System.Data.OracleClient.jvm/OracleDataReader/OracleDataReader_NextResult.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.Text;
  26 using System.Data;
  27 using System.Data.OracleClient ;
  28
  29 using MonoTests.System.Data.Utils;
  30
  31
  32 using NUnit.Framework;
  33
  34 namespace MonoTests.System.Data.OracleClient
  35 {
  36         [TestFixture]
  37         public class OracleDataReader_NextResult : ADONetTesterClass
  38         {
  39                 OracleConnection con;
  40                 Exception exp = null;
  41
  42                 [SetUp]
  43                 public void SetUp() {
  44                         base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
  45                         con = new OracleConnection (MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
  46                         con.Open();
  47                 }
  48
  49                 [TearDown]
  50                 public void TearDown() {
  51                         if (con.State == ConnectionState.Open) con.Close();
  52                 }
  53
  54                 public static void Main()
  55                 {
  56                         OracleDataReader_NextResult tc = new OracleDataReader_NextResult();
  57                         Exception exp = null;
  58                         try
  59                         {
  60                                 tc.BeginTest("OracleDataReader_NextResult");
  61                                 tc.run();
  62                         }
  63                         catch(Exception ex){exp = ex;}
  64                         finally {tc.EndTest(exp);}
  65                 }
  66
  67                 public void run()
  68                 {
  69
  70
  71                         base.PrepareDataForTesting(MonoTests.System.Data.Utils.ConnectedDataProvider.ConnectionString);
  72                         con.Open();
  73
  74                         TestMultipleResultSetsWithSP();
  75                         TestMultipleResultSetsWithSQLText();
  76
  77                         if (con.State == ConnectionState.Open) con.Close();
  78
  79                 }
  80
  81                 [Test]
  82                 [Ignore ("JVM test")]
  83                 public void TestMultipleResultSetsWithSQLText()
  84                 {
  85
  86                         if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle)
  87                         {
  88                                 this.Log("Multiple result sets by sql text is not tested in oracle.");
  89                                 return;
  90                         }
  91
  92                         if (ConnectedDataProvider.GetDbType() == DataBaseServer.DB2)
  93                         {
  94                                 this.Log("Multiple result sets using compound statement not supported at DB2.");
  95                                 return;
  96                         }
  97
  98                         bool NextResultExists = false;
  99                         OracleDataReader rdr = null;
 100                         OracleCommand cmd;
 101                         int TblResult0=-1;
 102                         int TblResult1=-1;
 103                         int TblResult2=-1;
 104                         try
 105                         {
 106                                 BeginCase("Setup: Get expected results.");
 107
 108                                 //get excpected results
 109                                 GetExcpectedResults(ref TblResult0, ref TblResult1, ref TblResult2);
 110                                 this.Pass("Setup: Get expected results ended.");
 111                         }
 112                         catch (Exception ex)
 113                         {
 114                                 exp = ex;
 115                         }
 116                         finally
 117                         {
 118                                 EndCase(exp);
 119                         }
 120
 121
 122                         string cmdTxt = BuildCommandText();
 123                         cmd = new OracleCommand(cmdTxt, con);
 124                         cmd.CommandType = CommandType.Text;
 125                         rdr = cmd.ExecuteReader();
 126                         // -------------- ResultSet  1 ------------
 127                         try
 128                         {
 129                                 exp = null;
 130                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 1 exists");
 131                                 Compare(rdr != null, true);
 132                         }
 133                         catch(Exception ex)
 134                         {
 135                                 exp = ex;
 136                         }
 137                         finally
 138                         {
 139                                 EndCase(exp);
 140                         }
 141                         try
 142                         {
 143                                 exp = null;
 144                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 1 contains data");
 145                                 NextResultExists = rdr.Read();
 146                                 Compare(NextResultExists, true);
 147                         }
 148                         catch(Exception ex)
 149                         {
 150                                 exp = ex;
 151                         }
 152                         finally
 153                         {
 154                                 EndCase(exp);
 155                         }
 156                         int i = 1;
 157                         try
 158                         {
 159                                 exp = null;
 160                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 1 Data");
 161                                 while (rdr.Read())
 162                                 {
 163                                         i++;
 164                                 }
 165                                 Compare(i, TblResult0);
 166                         }
 167                         catch(Exception ex)
 168                         {
 169                                 exp = ex;
 170                         }
 171                         finally
 172                         {
 173                                 EndCase(exp);
 174                         }
 175                         try
 176                         {
 177                                 exp = null;
 178                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 1 Schema");
 179                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CUSTOMERID");
 180                         }
 181                         catch(Exception ex)
 182                         {
 183                                 exp = ex;
 184                         }
 185                         finally
 186                         {
 187                                 EndCase(exp);
 188                         }
 189                         // -------------- ResultSet  2 ------------
 190                         try
 191                         {
 192                                 exp = null;
 193                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 2 exists");
 194                                 NextResultExists = rdr.NextResult();
 195                                 Compare(NextResultExists, true);
 196                         }
 197                         catch(Exception ex)
 198                         {
 199                                 exp = ex;
 200                         }
 201                         finally
 202                         {
 203                                 EndCase(exp);
 204                         }
 205                         try
 206                         {
 207                                 exp = null;
 208                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 2 contains data");
 209                                 NextResultExists = rdr.Read();
 210                                 Compare(NextResultExists, true);
 211                         }
 212                         catch(Exception ex)
 213                         {
 214                                 exp = ex;
 215                         }
 216                         finally
 217                         {
 218                                 EndCase(exp);
 219                         }
 220                         try
 221                         {
 222                                 exp = null;
 223                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 2 Data");
 224                                 i = 1;
 225                                 while (rdr.Read())
 226                                 {
 227                                         i++;
 228                                 }
 229                                 Compare(i, TblResult1);
 230                         }
 231                         catch(Exception ex)
 232                         {
 233                                 exp = ex;
 234                         }
 235                         finally
 236                         {
 237                                 EndCase(exp);
 238                         }
 239                         try
 240                         {
 241                                 exp = null;
 242                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 2 Schema");
 243                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CATEGORYID");
 244                         }
 245                         catch(Exception ex)
 246                         {
 247                                 exp = ex;
 248                         }
 249                         // -------------- ResultSet  3 ------------
 250                         finally
 251                         {
 252                                 EndCase(exp);
 253                         }
 254                         try
 255                         {
 256                                 exp = null;
 257                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 3 exists");
 258                                 NextResultExists = rdr.NextResult();
 259                                 Compare(NextResultExists, true);
 260                         }
 261                         catch(Exception ex)
 262                         {
 263                                 exp = ex;
 264                         }
 265                         finally
 266                         {
 267                                 EndCase(exp);
 268                         }
 269                         try
 270                         {
 271                                 exp = null;
 272                                 BeginCase("(Multiple Resultsets sql text) - Check if ResultSet 3 contains data");
 273                                 NextResultExists = rdr.Read();
 274                                 Compare(NextResultExists, true);
 275                         }
 276                         catch(Exception ex)
 277                         {
 278                                 exp = ex;
 279                         }
 280                         finally
 281                         {
 282                                 EndCase(exp);
 283                         }
 284                         try
 285                         {
 286                                 exp = null;
 287                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 3 Data");
 288                                 i = 1;
 289                                 while (rdr.Read())
 290                                 {
 291                                         i++;
 292                                 }
 293                                 Compare(i, TblResult2);
 294                         }
 295                         catch(Exception ex)
 296                         {
 297                                 exp = ex;
 298                         }
 299                         finally
 300                         {
 301                                 EndCase(exp);
 302                         }
 303                         try
 304                         {
 305                                 exp = null;
 306                                 BeginCase("(Multiple Resultsets sql text) - Check ResultSet 3 Schema");
 307                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "REGIONID");
 308                         }
 309                         catch(Exception ex)
 310                         {
 311                                 exp = ex;
 312                         }
 313                         finally
 314                         {
 315                                 EndCase(exp);
 316                         }
 317                         try
 318                         {
 319                                 exp = null;
 320                                 BeginCase("(Multiple Resultsets sql text) - Check that resultset 4 does not exist.");
 321                                 NextResultExists = rdr.NextResult();
 322                                 Compare(NextResultExists, false);
 323                         }
 324                         catch(Exception ex)
 325                         {
 326                                 exp = ex;
 327                         }
 328                         finally
 329                         {
 330                                 EndCase(exp);
 331                         }
 332                         try
 333                         {
 334                                 exp = null;
 335                                 BeginCase("(Multiple Resultsets sql text) - Check that resultset 4 does not contain data.");
 336                                 NextResultExists = rdr.Read();
 337                                 Compare(NextResultExists, false);
 338                         }
 339                         catch(Exception ex)
 340                         {
 341                                 exp = ex;
 342                         }
 343                         finally
 344                         {
 345                                 EndCase(exp);
 346                         }
 347                         if (!rdr.IsClosed)
 348                                 rdr.Close();
 349                 }
 350
 351                 [Test]
 352                 [Ignore ("JVM test")]
 353                 public void TestMultipleResultSetsWithSP()
 354                 {
 355 #if !JAVA
 356                         if (ConnectedDataProvider.GetDbType() == DataBaseServer.Oracle)
 357                         {
 358                                 this.Log("Not testing Stored procedures with multiple ref-cursors on Oracle with .NET due to bug in .NET (only the first ref-cursor is retrived).");
 359                                 return;
 360                         }
 361
 362                         if (ConnectedDataProvider.GetDbType(con) == DataBaseServer.PostgreSQL)
 363                         {
 364                                 // fail to work on .NET OLEDB
 365                                 this.Log("Not testing PostgreSQL CommandType.StoredProcedure which return SETOF");
 366                                 return;
 367                         }
 368 #endif
 369
 370                         bool NextResultExists = false;
 371                         // transaction use was add for PostgreSQL
 372                         OracleTransaction tr = con.BeginTransaction();
 373                         OracleCommand cmd = new OracleCommand("GH_MULTIRECORDSETS", con, tr);
 374                         cmd.Parameters.Add(new OracleParameter("RCT_Employees", OracleType.Cursor)).Direction = ParameterDirection.Output;
 375                         cmd.Parameters.Add(new OracleParameter("RCT_Customers", OracleType.Cursor)).Direction = ParameterDirection.Output;
 376                         cmd.Parameters.Add(new OracleParameter("RCT_Orders", OracleType.Cursor)).Direction = ParameterDirection.Output;
 377                         cmd.CommandType = CommandType.StoredProcedure;
 378                         OracleDataReader rdr = cmd.ExecuteReader();
 379
 380                         try
 381                         {
 382                                 exp = null;
 383                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 exists");
 384                                 Compare(rdr != null, true);
 385                         }
 386                         catch(Exception ex)
 387                         {
 388                                 exp = ex;
 389                         }
 390                         finally
 391                         {
 392                                 EndCase(exp);
 393                         }
 394                         try
 395                         {
 396                                 exp = null;
 397                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 1 contains data");
 398                                 NextResultExists = rdr.Read();
 399                                 Compare(NextResultExists, true);
 400                         }
 401                         catch(Exception ex)
 402                         {
 403                                 exp = ex;
 404                         }
 405                         finally
 406                         {
 407                                 EndCase(exp);
 408                         }
 409                         try
 410                         {
 411                                 exp = null;
 412                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Data");
 413                                 Compare(rdr.GetValue(1).ToString(), "Yavine");
 414                         }
 415                         catch(Exception ex)
 416                         {
 417                                 exp = ex;
 418                         }
 419                         finally
 420                         {
 421                                 EndCase(exp);
 422                         }
 423                         try
 424                         {
 425                                 exp = null;
 426                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 1 Schema");
 427                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "EMPLOYEEID");
 428                         }
 429                         catch(Exception ex)
 430                         {
 431                                 exp = ex;
 432                         }
 433                         finally
 434                         {
 435                                 EndCase(exp);
 436                         }
 437
 438
 439                         // -------------- ResultSet  2 ------------
 440                         try
 441                         {
 442                                 exp = null;
 443                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 exists");
 444                                 NextResultExists = rdr.NextResult();
 445                                 Compare(NextResultExists, true);
 446                         }
 447                         catch(Exception ex)
 448                         {
 449                                 exp = ex;
 450                         }
 451                         finally
 452                         {
 453                                 EndCase(exp);
 454                         }
 455                         try
 456                         {
 457                                 exp = null;
 458                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 2 contains data");
 459                                 NextResultExists = rdr.Read();
 460                                 Compare(NextResultExists, true);
 461                         }
 462                         catch(Exception ex)
 463                         {
 464                                 exp = ex;
 465                         }
 466                         finally
 467                         {
 468                                 EndCase(exp);
 469                         }
 470                         try
 471                         {
 472                                 exp = null;
 473                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Data");
 474                                 Compare(rdr.GetValue(1).ToString(), "Morgenstern Gesundkost");
 475                         }
 476                         catch(Exception ex)
 477                         {
 478                                 exp = ex;
 479                         }
 480                         finally
 481                         {
 482                                 EndCase(exp);
 483                         }
 484                         try
 485                         {
 486                                 exp = null;
 487                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 2 Schema");
 488                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "CUSTOMERID");
 489                         }
 490                         catch(Exception ex)
 491                         {
 492                                 exp = ex;
 493                         }
 494                         finally
 495                         {
 496                                 EndCase(exp);
 497                         }
 498
 499                         // -------------- ResultSet  3 ------------
 500                         try
 501                         {
 502                                 exp = null;
 503                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 exists");
 504                                 NextResultExists = rdr.NextResult();
 505                                 Compare(NextResultExists, true);
 506                         }
 507                         catch(Exception ex)
 508                         {
 509                                 exp = ex;
 510                         }
 511                         finally
 512                         {
 513                                 EndCase(exp);
 514                         }
 515                         try
 516                         {
 517                                 exp = null;
 518                                 BeginCase("(Multiple Resultsets stored proc.) - Check if ResultSet 3 contains data");
 519                                 NextResultExists = rdr.Read();
 520                                 Compare(NextResultExists, false);
 521                         }
 522                         catch(Exception ex)
 523                         {
 524                                 exp = ex;
 525                         }
 526                         finally
 527                         {
 528                                 EndCase(exp);
 529                         }
 530                         try
 531                         {
 532                                 exp = null;
 533                                 BeginCase("(Multiple Resultsets stored proc.) - Check ResultSet 3 Schema");
 534                                 Compare(rdr.GetSchemaTable().Rows[0].ItemArray.GetValue(0).ToString().ToUpper(), "ORDERID");
 535                         }
 536                         catch(Exception ex)
 537                         {
 538                                 exp = ex;
 539                         }
 540                         finally
 541                         {
 542                                 EndCase(exp);
 543                         }
 544                         try
 545                         {
 546                                 exp = null;
 547                                 BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not exist.");
 548                                 NextResultExists = rdr.NextResult();
 549                                 Compare(NextResultExists, false);
 550                         }
 551                         catch(Exception ex)
 552                         {
 553                                 exp = ex;
 554                         }
 555                         finally
 556                         {
 557                                 EndCase(exp);
 558                         }
 559                         try
 560                         {
 561                                 exp = null;
 562                                 BeginCase("(Multiple Resultsets stored proc.) - Check that resultset 4 does not contain data.");
 563                                 NextResultExists = rdr.Read();
 564                                 Compare(NextResultExists, false);
 565                         }
 566                         catch(Exception ex)
 567                         {
 568                                 exp = ex;
 569                         }
 570                         finally
 571                         {
 572                                 EndCase(exp);
 573                         }
 574
 575                         //Cleanup:
 576                         if (!rdr.IsClosed)
 577                         {
 578                                 rdr.Close();
 579                         }
 580
 581                         // transaction use was add for PostgreSQL
 582                         tr.Commit();
 583
 584                 }
 585
 586
 587                 #region "Private Utilities"
 588                 private string BuildCommandText()
 589                 {
 590                         string beginStatement;
 591                         string endStatement;
 592                         string commandDelimiter;
 593                         string[] commands = new string[] {"select * from Customers", "select * from Categories", "select * from Region"};
 594
 595                         GetDBSpecificSyntax(ConnectedDataProvider.GetDbType(), out beginStatement, out endStatement, out commandDelimiter);
 596
 597                         StringBuilder cmdBuilder = new StringBuilder();
 598                         cmdBuilder.Append(beginStatement);
 599                         cmdBuilder.Append(" ");
 600                         foreach (string statement in commands)
 601                         {
 602                                 cmdBuilder.Append(statement);
 603                                 cmdBuilder.Append(commandDelimiter);
 604                                 cmdBuilder.Append(" ");
 605                         }
 606                         cmdBuilder.Append(endStatement);
 607
 608                         return cmdBuilder.ToString();
 609                 }
 610                 private void GetDBSpecificSyntax(DataBaseServer dbServer, out string beginStatement, out string endStatement, out string commandDelimiter)
 611                 {
 612                         switch (dbServer)
 613                         {
 614                                 case DataBaseServer.SQLServer:
 615                                         beginStatement = "BEGIN";
 616                                         endStatement = "END";
 617                                         commandDelimiter = ";";
 618                                         break;
 619                                 case DataBaseServer.Sybase:
 620                                         beginStatement = "BEGIN";
 621                                         endStatement = "END";
 622                                         commandDelimiter = "\r\n";
 623                                         break;
 624                                 case DataBaseServer.Oracle:
 625                                         beginStatement = "BEGIN";
 626                                         endStatement = "END;";
 627                                         commandDelimiter = ";";
 628                                         break;
 629
 630                                 case DataBaseServer.DB2:
 631                                         {
 632                                                 beginStatement = "";
 633                                                 endStatement = "";
 634                                         }
 635                                         commandDelimiter = ";";
 636                                         break;
 637
 638                                 case DataBaseServer.PostgreSQL:
 639                                         beginStatement = "";
 640                                         endStatement = "";
 641                                         commandDelimiter = ";";
 642                                         break;
 643
 644                                 default:
 645                                         this.Fail("Unknown DataBaseServer type");
 646                                         throw new ApplicationException("Unknown DataBaseServer type");
 647                         }
 648                 }
 649                 private void GetExcpectedResults(ref int TblResult0, ref int TblResult1, ref int TblResult2)
 650                 {
 651                         // get excpected results
 652
 653                         // transaction use was add for PostgreSQL
 654                         OracleTransaction tr = con.BeginTransaction();
 655                         OracleCommand cmd = new OracleCommand("", con,tr);
 656                         cmd.CommandText = "Select count(*) from Customers";
 657                         TblResult0 = Int32.Parse(cmd.ExecuteScalar().ToString());
 658                         cmd.CommandText = "Select count(*) from Categories";
 659                         TblResult1 = Int32.Parse(cmd.ExecuteScalar().ToString());
 660                         cmd.CommandText = "Select count(*) from Region";
 661                         TblResult2 = Int32.Parse(cmd.ExecuteScalar().ToString());
 662                         tr.Commit();
 663                 }
 664
 665                 #endregion
 666         }
 667 }