mcs/class/System.Data/Test/ProviderTests/System.Data.Odbc/OdbcParameterTest.cs

   1 // OdbcCommandTest.cs - NUnit Test Cases for testing the
   2 // OdbcCommand class
   3 //
   4 // Authors:
   5 //      Sureshkumar T (TSureshkumar@novell.com)
   6 //
   7 // Copyright (c) 2005 Novell Inc., and the individuals listed
   8 // on the ChangeLog entries.
   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.Data;
  32 using System.Data.Common;
  33 using System.Data.Odbc;
  34 using System.Globalization;
  35
  36 using Mono.Data;
  37
  38 using NUnit.Framework;
  39
  40 namespace MonoTests.System.Data
  41 {
  42         [TestFixture]
  43         [Category ("odbc")]
  44         public class OdbcParameterTest
  45         {
  46                 [Test]
  47                 public void IntegerParamTest ()
  48                 {
  49                         string query = "select type_int from numeric_family where id = ?";
  50                         IDbConnection conn = ConnectionManager.Singleton.Connection;
  51                         try {
  52                                 ConnectionManager.Singleton.OpenConnection ();
  53                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
  54                                 cmd.CommandText = query;
  55
  56                                 OdbcParameter param = cmd.Parameters.Add ("id", OdbcType.Int);
  57                                 param.Value = 2;
  58                                 OdbcDataReader reader = cmd.ExecuteReader ();
  59                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
  60                                 Assert.AreEqual (-2147483648, (int) reader [0], "#2 value not matching");
  61                         } finally {
  62                                 ConnectionManager.Singleton.CloseConnection ();
  63                         }
  64                 }
  65
  66                 [Test]
  67                 public void BigIntParamTest ()
  68                 {
  69                         string query = "select id, type_bigint from numeric_family where type_bigint = ? and id = 1";
  70                         IDbConnection conn = ConnectionManager.Singleton.Connection;
  71                         try {
  72                                 ConnectionManager.Singleton.OpenConnection ();
  73                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
  74                                 cmd.CommandText = query;
  75
  76                                 OdbcParameter param = cmd.Parameters.Add ("type_bigint", OdbcType.BigInt);
  77                                 param.Value = (long) (9223372036854775807);
  78                                 OdbcDataReader reader = cmd.ExecuteReader ();
  79                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
  80                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
  81                         } finally {
  82                                 ConnectionManager.Singleton.CloseConnection ();
  83                         }
  84                 }
  85
  86                 [Test]
  87                 public void SmallIntParamTest ()
  88                 {
  89                         string query = "select id, type_smallint from numeric_family where type_smallint = ? and id = 1";
  90                         IDbConnection conn = ConnectionManager.Singleton.Connection;
  91                         try {
  92                                 ConnectionManager.Singleton.OpenConnection ();
  93                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
  94                                 cmd.CommandText = query;
  95
  96                                 OdbcParameter param = cmd.Parameters.Add ("type_smallint", OdbcType.BigInt);
  97                                 param.Value = 32767;
  98                                 OdbcDataReader reader = cmd.ExecuteReader ();
  99                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 100                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 101                         } finally {
 102                                 ConnectionManager.Singleton.CloseConnection ();
 103                         }
 104                 }
 105
 106                 [Test]
 107                 public void TinyIntParamTest ()
 108                 {
 109                         string query = "select id, type_tinyint from numeric_family where type_tinyint = ? and id = 1";
 110                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 111                         try {
 112                                 ConnectionManager.Singleton.OpenConnection ();
 113                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 114                                 cmd.CommandText = query;
 115
 116                                 OdbcParameter param = cmd.Parameters.Add ("type_tinyint", OdbcType.BigInt);
 117                                 param.Value = 255;
 118                                 OdbcDataReader reader = cmd.ExecuteReader ();
 119                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 120                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 121                         } finally {
 122                                 ConnectionManager.Singleton.CloseConnection ();
 123                         }
 124                 }
 125
 126
 127                 [Test]
 128                 public void StringParamTest ()
 129                 {
 130                         string query = "select id, fname from employee where fname = ?";
 131                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 132                         try {
 133                                 ConnectionManager.Singleton.OpenConnection ();
 134                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 135                                 cmd.CommandText = query;
 136
 137                                 OdbcParameter param = cmd.Parameters.Add ("fname", OdbcType.VarChar);
 138                                 param.Value = "suresh";
 139                                 OdbcDataReader reader = cmd.ExecuteReader ();
 140                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 141                                 Assert.AreEqual (1, (int) reader [0], "#2 value not matching");
 142                         } finally {
 143                                 ConnectionManager.Singleton.CloseConnection ();
 144                         }
 145                 }
 146
 147
 148                 [Test]
 149                 public void BitParameterTest ()
 150                 {
 151                         string query = "select id, type_bit from numeric_family where type_bit = ? and id = 1";
 152                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 153                         try {
 154                                 ConnectionManager.Singleton.OpenConnection ();
 155                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 156                                 cmd.CommandText = query;
 157
 158                                 OdbcParameter param = cmd.Parameters.Add ("type_bit", OdbcType.Bit);
 159                                 param.Value = true;
 160                                 OdbcDataReader reader = cmd.ExecuteReader ();
 161                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 162                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 163                         } finally {
 164                                 ConnectionManager.Singleton.CloseConnection ();
 165                         }
 166                 }
 167
 168                 [Test]
 169                 public void CharParameterTest ()
 170                 {
 171                         string query = "select id, type_char from string_family where type_char = ? and id = 1";
 172                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 173                         try {
 174                                 ConnectionManager.Singleton.OpenConnection ();
 175                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 176                                 cmd.CommandText = query;
 177
 178                                 OdbcParameter param = cmd.Parameters.Add ("type_char", OdbcType.Char);
 179                                 param.Value = "char";
 180                                 OdbcDataReader reader = cmd.ExecuteReader ();
 181                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 182                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 183                         } finally {
 184                                 ConnectionManager.Singleton.CloseConnection ();
 185                         }
 186                 }
 187
 188                 [Test]
 189                 public void DecimalParameterTest ()
 190                 {
 191                         string query = "select id, type_decimal from numeric_family where type_decimal = ? and id = 1";
 192                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 193                         try {
 194                                 ConnectionManager.Singleton.OpenConnection ();
 195                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 196                                 cmd.CommandText = query;
 197
 198                                 OdbcParameter param = cmd.Parameters.Add ("type_decimal", OdbcType.Decimal);
 199                                 param.Value = 1000.00m;
 200                                 OdbcDataReader reader = cmd.ExecuteReader ();
 201                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 202                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 203                         } finally {
 204                                 ConnectionManager.Singleton.CloseConnection ();
 205                         }
 206                 }
 207
 208                 [Test]
 209                 public void DoubleParameterTest ()
 210                 {
 211                         string query = "select id, type_double from numeric_family where type_double = ? and id = 1";
 212                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 213                         try {
 214                                 ConnectionManager.Singleton.OpenConnection ();
 215                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 216                                 cmd.CommandText = query;
 217
 218                                 OdbcParameter param = cmd.Parameters.Add ("type_double", OdbcType.Double);
 219                                 param.Value = 1.79E+308;
 220                                 OdbcDataReader reader = cmd.ExecuteReader ();
 221                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 222                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 223                         } finally {
 224                                 ConnectionManager.Singleton.CloseConnection ();
 225                         }
 226                 }
 227
 228                 [Test]
 229                 public void ImageParameterTest ()
 230                 {
 231                         string query = "insert into binary_family (id, type_blob) values (6000,?)";
 232                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 233                         try {
 234                                 ConnectionManager.Singleton.OpenConnection ();
 235                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 236                                 cmd.CommandText = query;
 237
 238                                 OdbcParameter param = cmd.Parameters.Add ("type_blob", OdbcType.Image);
 239                                 param.Value = new byte [] { 6, 6, 6, 6, 6, 6, 6, 6 };
 240                                 cmd.ExecuteNonQuery ();
 241                                 cmd.CommandText = "select count (*) from binary_family where id = 6000";
 242                                 int count = (int) cmd.ExecuteScalar ();
 243                                 Assert.AreEqual (1, count, "#1 value not matching");
 244                         } finally {
 245                                 DBHelper.ExecuteNonQuery (conn, "delete from binary_family where id = 6000");
 246                                 ConnectionManager.Singleton.CloseConnection ();
 247                         }
 248                 }
 249
 250                 [Test]
 251                 public void NCharParameterTest ()
 252                 {
 253                         string query = "select id, type_char from string_family where type_char = ? and id = 1";
 254                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 255                         try {
 256                                 ConnectionManager.Singleton.OpenConnection ();
 257                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 258                                 cmd.CommandText = query;
 259
 260                                 OdbcParameter param = cmd.Parameters.Add ("type_char", OdbcType.NChar);
 261                                 param.Value = "char";
 262                                 OdbcDataReader reader = cmd.ExecuteReader ();
 263                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 264                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 265                         } finally {
 266                                 ConnectionManager.Singleton.CloseConnection ();
 267                         }
 268                 }
 269
 270                 [Test]
 271                 public void NTextParameterTest ()
 272                 {
 273                         string query = "insert into string_family (id, type_ntext) values (6000, ?)";
 274                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 275                         try {
 276                                 ConnectionManager.Singleton.OpenConnection ();
 277                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 278                                 cmd.CommandText = query;
 279
 280                                 OdbcParameter param = cmd.Parameters.Add ("type_ntext", OdbcType.NText);
 281                                 param.Value = "ntext";
 282                                 cmd.ExecuteNonQuery ();
 283                                 cmd.CommandText = "select count (*) from string_family where id = 6000";
 284                                 Assert.AreEqual (1,(int) cmd.ExecuteScalar (), "#1 value not matching");
 285                         } finally {
 286                                 DBHelper.ExecuteNonQuery (conn, "delete from string_family where id = 6000");
 287                                 ConnectionManager.Singleton.CloseConnection ();
 288                         }
 289                 }
 290
 291                 [Test]
 292                 public void TextParameterTest ()
 293                 {
 294                         string query = "insert into string_family (id, type_text) values (6000, ?)";
 295                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 296                         try {
 297                                 ConnectionManager.Singleton.OpenConnection ();
 298                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 299                                 cmd.CommandText = query;
 300
 301                                 OdbcParameter param = cmd.Parameters.Add ("type_text", OdbcType.Text);
 302                                 param.Value = "text";
 303                                 cmd.ExecuteNonQuery ();
 304                                 cmd.CommandText = "select count (*) from string_family where id = 6000";
 305                                 Assert.AreEqual (1,(int) cmd.ExecuteScalar (), "#1 value not matching");
 306                         } finally {
 307                                 DBHelper.ExecuteNonQuery (conn, "delete from string_family where id = 6000");
 308                                 ConnectionManager.Singleton.CloseConnection ();
 309                         }
 310                 }
 311
 312                 [Test]
 313                 public void NumericParameterTest ()
 314                 {
 315                         string query = "select id, type_numeric from numeric_family where type_numeric = ? and id = 1";
 316                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 317                         try {
 318                                 ConnectionManager.Singleton.OpenConnection ();
 319                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 320                                 cmd.CommandText = query;
 321
 322                                 OdbcParameter param = cmd.Parameters.Add ("type_numeric", OdbcType.Numeric);
 323                                 param.Precision = 0;
 324                                 param.Scale = 0;
 325                                 param.Value = 1000.00m;
 326                                 OdbcDataReader reader = cmd.ExecuteReader ();
 327                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 328                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 329                         } finally {
 330                                 ConnectionManager.Singleton.CloseConnection ();
 331                         }
 332                 }
 333
 334                 [Test]
 335                 public void NVarCharParameterTest ()
 336                 {
 337                         string query = "select id, type_varchar from string_family where type_varchar = ? and id = 1";
 338                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 339                         try {
 340                                 ConnectionManager.Singleton.OpenConnection ();
 341                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 342                                 cmd.CommandText = query;
 343
 344                                 OdbcParameter param = cmd.Parameters.Add ("type_varchar", OdbcType.NVarChar);
 345                                 param.Value = "varchar";
 346                                 OdbcDataReader reader = cmd.ExecuteReader ();
 347                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 348                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 349                         } finally {
 350                                 ConnectionManager.Singleton.CloseConnection ();
 351                         }
 352                 }
 353
 354                 [Test]
 355
 356                 public void VarCharParameterTest ()
 357                 {
 358                         string query = "select id, type_varchar from string_family where type_varchar = ? and id = 1";
 359                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 360                         try {
 361                                 ConnectionManager.Singleton.OpenConnection ();
 362                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 363                                 cmd.CommandText = query;
 364
 365                                 OdbcParameter param = cmd.Parameters.Add ("type_varchar", OdbcType.VarChar);
 366                                 param.Value = "varchar";
 367                                 OdbcDataReader reader = cmd.ExecuteReader ();
 368                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 369                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 370                         } finally {
 371                                 ConnectionManager.Singleton.CloseConnection ();
 372                         }
 373                 }
 374
 375                 [Test]
 376                 public void RealParameterTest ()
 377                 {
 378                         string query = "select id, type_float from numeric_family where type_float = ? and id = 1";
 379                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 380                         try {
 381                                 ConnectionManager.Singleton.OpenConnection ();
 382                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 383                                 cmd.CommandText = query;
 384
 385                                 OdbcParameter param = cmd.Parameters.Add ("type_float", OdbcType.Real);
 386                                 param.Value = 3.40E+38;
 387                                 OdbcDataReader reader = cmd.ExecuteReader ();
 388                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 389                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 390                         } finally {
 391                                 ConnectionManager.Singleton.CloseConnection ();
 392                         }
 393                 }
 394
 395                 [Test]
 396                 public void SmallDateTimeParameterTest ()
 397                 {
 398                         string query = "select id, type_smalldatetime from datetime_family where type_smalldatetime = ? and id = 1";
 399                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 400                         try {
 401                                 ConnectionManager.Singleton.OpenConnection ();
 402                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 403                                 cmd.CommandText = query;
 404
 405                                 OdbcParameter param = cmd.Parameters.Add ("type_smalldatetime", OdbcType.SmallDateTime);
 406                                 param.Value = DateTime.Parse ("2079-06-06 23:59:00");
 407                                 OdbcDataReader reader = cmd.ExecuteReader ();
 408                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 409                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 410                         } finally {
 411                                 ConnectionManager.Singleton.CloseConnection ();
 412                         }
 413                 }
 414
 415                 [Test]
 416                 public void DateTimeParameterTest ()
 417                 {
 418                         string query = "select id, type_datetime from datetime_family where type_datetime = ? and id = 1";
 419                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 420                         try {
 421                                 ConnectionManager.Singleton.OpenConnection ();
 422                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 423                                 cmd.CommandText = query;
 424
 425                                 OdbcParameter param = cmd.Parameters.Add ("type_datetime", OdbcType.DateTime);
 426                                 param.Value = DateTime.ParseExact ("9999-12-31 23:59:59.997", "yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture);
 427                                 OdbcDataReader reader = cmd.ExecuteReader ();
 428                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 429                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 430                         } finally {
 431                                 ConnectionManager.Singleton.CloseConnection ();
 432                         }
 433                 }
 434
 435                 [Test]
 436                 [Ignore ("Not running on ms.net")]
 437                 public void DateParameterTest ()
 438                 {
 439                         string query = "select id, type_datetime from datetime_family where type_datetime = ? and id = 1";
 440                         IDbConnection conn = ConnectionManager.Singleton.Connection;
 441                         try {
 442                                 ConnectionManager.Singleton.OpenConnection ();
 443                                 OdbcCommand cmd = (OdbcCommand) conn.CreateCommand ();
 444                                 cmd.CommandText = query;
 445
 446                                 OdbcParameter param = cmd.Parameters.Add ("type_datetime", OdbcType.Date);
 447                                 param.Value = DateTime.ParseExact ("9999-12-31 23:59:59.997", "yyyy-MM-dd HH:mm:ss.fff", CultureInfo.InvariantCulture);
 448                                 OdbcDataReader reader = cmd.ExecuteReader ();
 449                                 Assert.IsTrue (reader.Read (), "#1 no data to test");
 450                                 Assert.AreEqual (1, Convert.ToInt32 (reader [0]), "#2 value not matching");
 451                         } finally {
 452                                 ConnectionManager.Singleton.CloseConnection ();
 453                         }
 454                 }
 455         }
 456 }