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