mcs/class/System.Data.OracleClient/Test/System.Data.OracleClient/OracleParameterTest.cs

   1 //
   2 // OracleParameterTest.cs -
   3 //      NUnit Test Cases for OracleParameter
   4 //
   5 // Author:
   6 //      Leszek Ciesielski  <skolima@gmail.com>
   7 //
   8 // Copyright (C) 2006 Forcom (http://www.forcom.com.pl/)
   9 //
  10 // Permission is hereby granted, free of charge, to any person obtaining
  11 // a copy of this software and associated documentation files (the
  12 // "Software"), to deal in the Software without restriction, including
  13 // without limitation the rights to use, copy, modify, merge, publish,
  14 // distribute, sublicense, and/or sell copies of the Software, and to
  15 // permit persons to whom the Software is furnished to do so, subject to
  16 // the following conditions:
  17 //
  18 // The above copyright notice and this permission notice shall be
  19 // included in all copies or substantial portions of the Software.
  20 //
  21 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  22 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  23 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  24 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  25 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  26 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  27 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  28 //
  29
  30 using System;
  31 using System.Configuration;
  32 using System.Data;
  33 using System.Data.OracleClient;
  34 using System.Globalization;
  35 using System.Threading;
  36
  37 using NUnit.Framework;
  38
  39 namespace MonoTests.System.Data.OracleClient
  40 {
  41         [TestFixture]
  42         public class OracleParameterTest
  43         {
  44                 String connection_string;
  45                 OracleConnection connection;
  46                 OracleCommand command;
  47
  48                 // test string
  49                 string test_value = "  simply trim test      ";
  50                 string test_value2 = "  simply trim test in query      ";
  51
  52                 [TestFixtureSetUp]
  53                 public void FixtureSetUp ()
  54                 {
  55                         connection_string = Environment.GetEnvironmentVariable ("MONO_TESTS_ORACLE_CONNECTION_STRING");
  56                 }
  57
  58                 [SetUp]
  59                 public void SetUp ()
  60                 {
  61                         if (connection_string == null)
  62                                 return;
  63
  64                         connection = new OracleConnection (connection_string);
  65                         connection.Open ();
  66                         using (command = connection.CreateCommand ()) {
  67                                 // create the tables
  68                                 command.CommandText =
  69                                         "create table oratest (id number(10), text varchar2(64),"
  70                                         + " text2 varchar2(64) )";
  71                                 command.ExecuteNonQuery ();
  72
  73                                 command.CommandText =
  74                                         "create table culture_test (id number(10), value1 float,"
  75                                         + " value2 number(20,10), value3 number (20,10))";
  76                                 command.ExecuteNonQuery ();
  77
  78                                 command.CommandText =
  79                                         "create table oratypes_test (id NUMBER(10), value1 VARCHAR2(100),"
  80                                         + " value2 DATE)";
  81                                 command.ExecuteNonQuery ();
  82
  83                                 command.CommandText =
  84                                         "create or replace procedure params_pos_test (param1 in number,"
  85                                         + "param2 in number,param3 in number,result out number) as"
  86                                         + " begin result:=param3; end;";
  87                                 command.ExecuteNonQuery ();
  88                         }
  89                 }
  90
  91                 [TearDown]
  92                 public void TearDown ()
  93                 {
  94                         if (connection_string == null)
  95                                 return;
  96
  97                         using (command = connection.CreateCommand ()) {
  98                                 command.CommandText = "drop table oratest";
  99                                 command.ExecuteNonQuery ();
 100                                 command.CommandText = "drop table culture_test";
 101                                 command.ExecuteNonQuery ();
 102                                 command.CommandText = "drop table oratypes_test";
 103                                 command.ExecuteNonQuery ();
 104                         }
 105
 106                         connection.Close ();
 107                         connection.Dispose ();
 108                 }
 109
 110                 [Test] // ctor ()
 111                 public void Constructor1 ()
 112                 {
 113                         OracleParameter param = new OracleParameter ();
 114                         Assert.AreEqual (DbType.AnsiString, param.DbType, "#1");
 115                         Assert.AreEqual (ParameterDirection.Input, param.Direction, "#2");
 116                         Assert.IsFalse (param.IsNullable, "#3");
 117                         Assert.AreEqual (OracleType.VarChar, param.OracleType, "#4");
 118                         Assert.AreEqual (string.Empty, param.ParameterName, "#5");
 119                         Assert.AreEqual ((byte) 0, param.Precision, "#6");
 120                         Assert.AreEqual ((byte) 0, param.Scale, "#7");
 121                         Assert.AreEqual (0, param.Size, "#8");
 122                         Assert.AreEqual (string.Empty, param.SourceColumn, "#9");
 123                         Assert.IsFalse (param.SourceColumnNullMapping, "#10");
 124                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, "#11");
 125                         Assert.IsNull (param.Value, "#12");
 126                 }
 127
 128                 [Test] // ctor ()
 129                 public void Constructor2 ()
 130                 {
 131                         OracleParameter param;
 132
 133                         param = new OracleParameter ("firstName", "Miguel");
 134                         Assert.AreEqual (DbType.AnsiString, param.DbType, "#A1");
 135                         Assert.AreEqual (ParameterDirection.Input, param.Direction, "#A2");
 136                         Assert.IsFalse (param.IsNullable, "#A3");
 137                         Assert.AreEqual (OracleType.VarChar, param.OracleType, "#A4");
 138                         Assert.AreEqual ("firstName", param.ParameterName, "#A5");
 139                         Assert.AreEqual ((byte) 0, param.Precision, "#A6");
 140                         Assert.AreEqual ((byte) 0, param.Scale, "#A7");
 141                         Assert.AreEqual (6, param.Size, "#A8");
 142                         Assert.AreEqual (string.Empty, param.SourceColumn, "#A9");
 143                         Assert.IsFalse (param.SourceColumnNullMapping, "#A10");
 144                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, "#A11");
 145                         Assert.AreEqual ("Miguel", param.Value, "#A12");
 146
 147                         param = new OracleParameter ((string) null, new DateTime (2006, 1, 5));
 148                         Assert.AreEqual (DbType.DateTime, param.DbType, "#B1");
 149                         Assert.AreEqual (ParameterDirection.Input, param.Direction, "#B2");
 150                         Assert.IsFalse (param.IsNullable, "#B3");
 151                         Assert.AreEqual (OracleType.DateTime, param.OracleType, "#B4");
 152                         Assert.AreEqual (string.Empty, param.ParameterName, "#B5");
 153                         Assert.AreEqual ((byte) 0, param.Precision, "#B6");
 154                         Assert.AreEqual ((byte) 0, param.Scale, "#B7");
 155                         Assert.AreEqual (7, param.Size, "#B8");
 156                         Assert.AreEqual (string.Empty, param.SourceColumn, "#B9");
 157                         Assert.IsFalse (param.SourceColumnNullMapping, "#B10");
 158                         Assert.AreEqual (DataRowVersion.Current, param.SourceVersion, "#B11");
 159                         Assert.AreEqual (new DateTime (2006, 1, 5), param.Value, "#B12");
 160                 }
 161
 162                 [Test]
 163                 public void ParameterName ()
 164                 {
 165                         OracleParameter param = new OracleParameter ("A", "B");
 166                         param.ParameterName = null;
 167                         Assert.AreEqual (string.Empty, param.ParameterName, "#1");
 168                         param.ParameterName = "B";
 169                         Assert.AreEqual ("B", param.ParameterName, "#2");
 170                         param.ParameterName = string.Empty;
 171                         Assert.AreEqual (string.Empty, param.ParameterName, "#3");
 172                 }
 173
 174                 [Test] // bug #78509
 175                 public void TrimsTrailingSpacesTest ()
 176                 {
 177                         if (connection_string == null)
 178                                 Assert.Ignore ("Please consult README.tests.");
 179
 180                         using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
 181                                 // insert test values
 182                                 command.CommandText =
 183                                         "insert into oratest (id,text,text2) values (:id,:txt,'"
 184                                         + test_value2 + "')";
 185                                 command.Parameters.Add (new OracleParameter ("ID", OracleType.Int32));
 186                                 command.Parameters.Add( new OracleParameter ("TXT", OracleType.VarChar));
 187                                 command.Parameters ["ID"].Value = 100;
 188                                 command.Parameters ["TXT"].Value = test_value;
 189                                 command.ExecuteNonQuery ();
 190
 191                                 // read test values
 192                                 command.CommandText =
 193                                         "select text,text2 from oratest where id = 100";
 194                                 command.Parameters.Clear ();
 195                                 using (OracleDataReader reader = command.ExecuteReader ()) {
 196                                         if (reader.Read ()) {
 197                                                 Assert.AreEqual (test_value2, reader.GetString (1), "Directly passed value mismatched");
 198                                                 Assert.AreEqual (test_value, reader.GetString (0), "Passed through bind value mismatched");
 199                                         } else {
 200                                                 Assert.Fail ("Expected records not found.");
 201                                         }
 202                                 }
 203                         }
 204                 }
 205
 206                 [Test] // bug #79284
 207                 public void CultureSensitiveNumbersTest ()
 208                 {
 209                         if (connection_string == null)
 210                                 Assert.Ignore ("Please consult README.tests.");
 211
 212                         CultureInfo currentCulture = Thread.CurrentThread.CurrentCulture;
 213
 214                         Thread.CurrentThread.CurrentCulture = new CultureInfo ("en-GB", false);
 215                         CultureSensitiveNumbersInsertTest (1);
 216                         CultureSensitiveNumbersSelectTest (1);
 217
 218                         Thread.CurrentThread.CurrentCulture = new CultureInfo ("pl-PL", false);
 219                         CultureSensitiveNumbersInsertTest (2);
 220                         CultureSensitiveNumbersSelectTest (2);
 221
 222                         Thread.CurrentThread.CurrentCulture = new CultureInfo ("ja-JP", false);
 223                         CultureSensitiveNumbersInsertTest (3);
 224                         CultureSensitiveNumbersSelectTest (3);
 225
 226                         Thread.CurrentThread.CurrentCulture = currentCulture;
 227                 }
 228
 229                 // regression for bug #79284
 230                 protected void CultureSensitiveNumbersInsertTest (int id)
 231                 {
 232                         using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
 233                                 // insert test values
 234                                 command.CommandText =
 235                                         "insert into culture_test (id,value1,value2,value3) values (:id,:value1,:value2,:value3)";
 236                                 command.Parameters.Add (new OracleParameter ("ID", OracleType.Int32));
 237                                 command.Parameters.Add( new OracleParameter ("VALUE1", OracleType.Float));
 238                                 command.Parameters.Add( new OracleParameter ("VALUE2", OracleType.Double));
 239                                 command.Parameters.Add( new OracleParameter ("VALUE3", OracleType.Number));
 240                                 command.Parameters ["ID"].Value = id;
 241                                 command.Parameters ["VALUE1"].Value = 2346.2342f;
 242                                 command.Parameters ["VALUE2"].Value = 4567456.23412m;
 243                                 command.Parameters ["VALUE3"].Value = new OracleNumber(4567456.23412m);
 244
 245                                 try {
 246                                         command.ExecuteNonQuery ();
 247                                 } catch (OracleException e) {
 248                                         if (e.Code == 1722)
 249                                                 Assert.Fail("Culture incompatibility error while inserting [" + id + ']');
 250                                         else throw;
 251                                 }
 252                         }
 253                 }
 254
 255                 // regression for bug #79284
 256                 protected void CultureSensitiveNumbersSelectTest (int id)
 257                 {
 258                         using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
 259                                 // read test values
 260                                 command.CommandText =
 261                                         "select value1,value2,value3 from culture_test where id = " + id;
 262                                 command.Parameters.Clear ();
 263                                 try {
 264                                         using (OracleDataReader reader = command.ExecuteReader ()) {
 265                                                 if (reader.Read ()) {
 266                                                         Assert.AreEqual (2346.2342f,reader.GetFloat(0),
 267                                                                 "Float value improperly stored [" + id + ']');
 268                                                         Assert.AreEqual (4567456.23412m, reader.GetDecimal (1),
 269                                                                 "Decimal value improperly stored [" + id + ']');
 270                                                         Assert.AreEqual (4567456.23412m, reader.GetOracleNumber(2).Value,
 271                                                                 "OracleNumber value improperly stored [" + id + ']');
 272                                                 } else {
 273                                                         Assert.Fail ("Expected records not found [" + id + ']');
 274                                                 }
 275                                         }
 276                                 } catch (FormatException) {
 277                                         Assert.Fail("Culture incompatibility error while reading [" + id + ']');
 278                                 }
 279                         }
 280                 }
 281
 282                 // added support for OracleString, OracleNumber and OracleDateTime in OracleParameter
 283                 [Test]
 284                 public void OracleTypesInValueTest ()
 285                 {
 286                         if (connection_string == null)
 287                                 Assert.Ignore ("Please consult README.tests.");
 288
 289                         try {
 290                                 int test_int = 10;
 291                                 string test_string = "koza";
 292                                 DateTime test_dateTime = DateTime.MinValue;
 293                                 using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
 294                                         // insert test values
 295                                         command.CommandText =
 296                                                 "insert into oratypes_test (id,value1,value2)"
 297                                                 +" values (:idx,:txtx,:datex)";
 298                                         command.Parameters.Add(
 299                                                 new OracleParameter("IDX", OracleType.Number))
 300                                                 .Direction = ParameterDirection.Input;
 301                                         command.Parameters.Add(
 302                                                 new OracleParameter("TXTX", OracleType.VarChar))
 303                                                 .Direction = ParameterDirection.Input;
 304                                         command.Parameters.Add(
 305                                                 new OracleParameter("DATEX", OracleType.DateTime))
 306                                                 .Direction = ParameterDirection.Input;
 307
 308                                         command.Parameters ["IDX"].Value = new OracleNumber(test_int);
 309                                         command.Parameters ["TXTX"].Value = new OracleString(test_string);
 310                                         command.Parameters ["DATEX"].Value = new OracleDateTime(test_dateTime);
 311
 312                                         command.ExecuteNonQuery ();
 313
 314                                         // read test values
 315                                         command.CommandText =
 316                                                 "select value1,value2 from oratypes_test where id = "
 317                                                 + test_int;
 318                                         command.Parameters.Clear ();
 319                                         using (OracleDataReader reader = command.ExecuteReader ()) {
 320                                                 if (reader.Read ()) {
 321                                                         Assert.AreEqual (test_string, reader.GetString (0), "OracleString mismatched");
 322                                                         Assert.AreEqual (test_dateTime, reader.GetDateTime(1), "OracleDateTime mismatched");
 323                                                 } else {
 324                                                         Assert.Fail ("Expected records not found.");
 325                                                 }
 326                                         }
 327                                 }
 328                         } catch (ArgumentException e) {
 329                                 Assert.Fail("OracleType not handled: " + e.Message);
 330                         }
 331                 }
 332
 333                 [Test] // verify that parameters are bound by name
 334                 public void ProcedureParametersByNameTest ()
 335                 {
 336                         if (connection_string == null)
 337                                 Assert.Ignore ("Please consult README.tests.");
 338
 339                         using (command = connection.CreateCommand ()) { // reusing command from SetUp causes parameter names mismatch
 340                                 command.CommandText = "params_pos_test";
 341                                 command.CommandType = CommandType.StoredProcedure;
 342
 343                                 command.Parameters.Add (new OracleParameter ("PARAM3", OracleType.Int32));
 344                                 command.Parameters.Add (new OracleParameter ("PARAM1", OracleType.Int32));
 345                                 command.Parameters.Add (new OracleParameter ("PARAM2", OracleType.Int32));
 346                                 command.Parameters.Add (new OracleParameter ("RESULT", OracleType.Int32))
 347                                         .Direction = ParameterDirection.Output;
 348
 349                                 command.Parameters ["PARAM1"].Value = 1;
 350                                 command.Parameters ["PARAM2"].Value = 2;
 351                                 command.Parameters ["PARAM3"].Value = 3;
 352
 353                                 command.ExecuteNonQuery ();
 354
 355                                 Assert.AreEqual (3, command.Parameters ["RESULT"].Value,
 356                                         "Unexpected result value.");
 357                         }
 358                 }
 359
 360                 private void ParamSize_SPCreation_ValueInsertion (OracleConnection conn)
 361                 {
 362                     string createSP =
 363                         "CREATE OR REPLACE PROCEDURE GetTextValue \n" +
 364                         "( \n" +
 365                         "idParam IN Number(10),\n" +
 366                         "text OUT varchar2(64) \n" +
 367                         ")\n" +
 368                         "AS\n" +
 369                         "BEGIN\n" +
 370                         "SELECT oratest.text INTO text \n" +
 371                         "  FROM oratest\n" +
 372                         "  WHERE oratest.id = idParam; \n" +
 373                         "END;\n";
 374
 375                     string insertValue = "INSERT INTO oratest VALUES " +
 376                         "(424908, \"This is a test for 424908 parameter size bug\", NULL);";
 377
 378                     using (command = conn.CreateCommand ()) {
 379                         command.CommandText = createSP;
 380                         command.CommandType = CommandType.Text;
 381                         command.ExecuteNonQuery ();
 382
 383                         command.CommandText = insertValue;
 384                         command.ExecuteNonQuery ();
 385
 386                         command.CommandText = "commit";
 387                         command.ExecuteNonQuery ();
 388                     }
 389                 }
 390
 391                 [Test]
 392                 [Category("NotWorking")]
 393                 public void ParamSize_424908_ValueError ()
 394                 {
 395                     //OracleConnection conn = new OracleConnection (connection_string);
 396                     //conn.Open ();
 397
 398                     ParamSize_SPCreation_ValueInsertion (connection);
 399
 400                     using (command = connection.CreateCommand ()) {
 401
 402                         OracleParameter id = new OracleParameter ();
 403                         id.ParameterName = "idParam";
 404                         id.OracleType = OracleType.Number;
 405                         id.Direction = ParameterDirection.Input;
 406                         id.Value = 424908;
 407                         command.Parameters.Add (id);
 408
 409                         OracleParameter text = new OracleParameter ();
 410                         text.ParameterName = "text";
 411                         text.OracleType = OracleType.NVarChar;
 412                         text.Direction = ParameterDirection.Output;
 413                         text.Value = string.Empty;
 414                         text.Size = 64;
 415                         command.Parameters.Add (text);
 416
 417                         try {
 418                             command.CommandType = CommandType.StoredProcedure;
 419                             command.CommandText = "GetTextValue";
 420                             command.ExecuteNonQuery ();
 421                             Assert.Fail ("Expected OracleException not occurred!");
 422                         } catch (OracleException ex) {
 423                             Assert.AreEqual ("6502", ex.Code, "Error code mismatch");
 424                             connection.Close ();
 425                         }
 426                     }
 427                 }
 428
 429                 [Test]
 430                 [Category("NotWorking")]
 431                 public void ParamSize_424908_ConstructorSizeSetTest ()
 432                 {
 433                     //OracleConnection conn = new OracleConnection (connection_string);
 434                     //conn.Open ();
 435
 436                     ParamSize_SPCreation_ValueInsertion (connection);
 437
 438                     using (command = connection.CreateCommand ()) {
 439                         OracleParameter id = new OracleParameter ();
 440                         id.ParameterName = "idParam";
 441                         id.OracleType = OracleType.Number;
 442                         id.Direction = ParameterDirection.Input;
 443                         id.Value = 424908;
 444                         command.Parameters.Add (id);
 445
 446                         OracleParameter text = new OracleParameter ("text", OracleType.NVarChar, 64);
 447                         text.Direction = ParameterDirection.Output;
 448                         text.Value = string.Empty;
 449                         text.Size = 64;
 450                         command.Parameters.Add (text);
 451
 452                         command.CommandType = CommandType.StoredProcedure;
 453                         command.CommandText = "GetTextValue";
 454                         command.ExecuteNonQuery ();
 455
 456                         Assert.AreEqual ("This is a test for 424908 parameter size bug", text.Value, "OracleParameter value mismatch");
 457                     }
 458                 }
 459
 460                 [Test]
 461                 [Category("NotWorking")]
 462                 public void ParamSize_424908_SizeNotSetError ()
 463                 {
 464
 465                     ParamSize_SPCreation_ValueInsertion (connection);
 466
 467                     using (command = connection.CreateCommand ()) {
 468                         OracleParameter id = new OracleParameter ();
 469                         id.ParameterName = "idParam";
 470                         id.OracleType = OracleType.Number;
 471                         id.Direction = ParameterDirection.Input;
 472                         id.Value = 424908;
 473                         command.Parameters.Add (id);
 474
 475                         OracleParameter text = new OracleParameter ();
 476                         text.ParameterName = "text";
 477                         text.OracleType = OracleType.NVarChar;
 478                         text.Direction = ParameterDirection.Output;
 479                         text.Value = DBNull.Value;
 480                         command.Parameters.Add (text);
 481
 482                         try {
 483                             command.CommandType = CommandType.StoredProcedure;
 484                             command.CommandText = "GetTextValue";
 485                             command.ExecuteNonQuery ();
 486                             Assert.Fail ("Expected System.Exception not occurred!");
 487                         } catch (Exception ex) {
 488                             Assert.AreEqual ("Size must be set.", ex.Message, "Exception mismatch");
 489                         }
 490                     }
 491                 }
 492         }
 493 }