mcs/class/System.Data/Test/System.Data.Odbc/OdbcDataReaderTest.cs

   1 //
   2 // OdbcDataReaderTest.cs - NUnit Test Cases for testing the
   3 // OdbcDataReader class
   4 //
   5 // Author:
   6 //      Sureshkumar T (TSureshkumar@novell.com)
   7 //
   8 // Copyright (c) 2004 Novell Inc., and the individuals listed
   9 // on the ChangeLog entries.
  10 //
  11 // Permission is hereby granted, free of charge, to any person obtaining
  12 // a copy of this software and associated documentation files (the
  13 // "Software"), to deal in the Software without restriction, including
  14 // without limitation the rights to use, copy, modify, merge, publish,
  15 // distribute, sublicense, and/or sell copies of the Software, and to
  16 // permit persons to whom the Software is furnished to do so, subject to
  17 // the following conditions:
  18 //
  19 // The above copyright notice and this permission notice shall be
  20 // included in all copies or substantial portions of the Software.
  21 //
  22 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  23 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  24 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  25 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  26 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  27 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  28 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  29 //
  30
  31
  32 using System;
  33 using System.Data;
  34 using System.Data.Odbc;
  35
  36 using NUnit.Framework;
  37
  38 namespace MonoTests.System.Data.Odbc
  39 {
  40
  41   [TestFixture]
  42   public class OdbcDataReaderTest : MySqlOdbcBaseClient
  43   {
  44
  45           [SetUp]
  46           public void GetReady () {
  47                 OpenConnection ();
  48                 CreateTestSetup (); // create database & test tables
  49           }
  50
  51           [TearDown]
  52           public void Clean () {
  53                 CleanTestSetup (); // clean test database;
  54                 CloseConnection ();
  55           }
  56
  57         /// <summary>
  58         /// Tests the return value of GetByte method of OdbcDataReader
  59         /// </summary>
  60         [Test]
  61         public void GetByteTest ()
  62         {
  63                 OdbcDataReader reader = null;
  64                 try {
  65                         // For this Test, you must create sample table
  66                         // called test, with a column of name 'col_int'.
  67                         // and the table with atleast a row with a minimum value for col_int as 0xff
  68                         // This tries to read a int column using GetByte method
  69                         OdbcCommand cmd = conn.CreateCommand ();
  70                         string query = "select col_int from test order by col_int;";
  71                         cmd.CommandText = query;
  72                         reader = cmd.ExecuteReader ();
  73                         if (reader.Read ()) {
  74                                 byte b = reader.GetByte (0);
  75                                 Assertion.AssertEquals ("GetByte returns wrong result!", 0xff, b);
  76                         } else // This should not happen while testing
  77                                 Assertion.AssertEquals ("test table doens not have a test data!", true, true);
  78                     } finally { // try/catch is necessary to gracefully close connections
  79                         if (reader != null && reader.IsClosed)
  80                                 reader.Close ();
  81                         CleanTestSetup ();
  82                         CloseConnection ();
  83                   }
  84         }
  85
  86         /// <summary>
  87         /// Tests the return column type of data reader
  88         /// To test the bugzilla id 49340
  89         /// </summary>
  90         [Test]
  91         public void ColumnDataTypeTest ()
  92         {
  93                 OdbcCommand dbcmd = conn.CreateCommand ();
  94                 string sql = "SELECT  * from test";
  95                 dbcmd.CommandText = sql;
  96                 IDataReader reader = dbcmd.ExecuteReader ();
  97                 try {
  98                         Assertion.AssertEquals ("GetDataTypeName returns invalid Type for column #1",
  99                                         "TinyInt", reader.GetDataTypeName (0));
 100                         Assertion.AssertEquals ("GetDataTypeName returns invalid Type for column #2",
 101                                         "VarChar", reader.GetDataTypeName (1));
 102                         // Test via method GetFieldType.ToString
 103                         Assertion.AssertEquals ("GetFieldType returns invalid Type for column #1",
 104                                         "System.Byte", reader.GetFieldType (0).ToString ());
 105                         Assertion.AssertEquals ("GetFieldType returns invalid Type for column #2",
 106                                         "System.String", reader.GetFieldType (1).ToString ());
 107
 108                         // Test via method GetSchemaTable
 109                         reader = dbcmd.ExecuteReader ();
 110                         DataTable schemaTable = reader.GetSchemaTable ();
 111                         Assertion.AssertEquals ("GetSchemaTable.ColumnDataType failes for column #1",
 112                                         typeof (System.Byte), schemaTable.Rows [0]["DataType"]);
 113                         Assertion.AssertEquals ("GetSchemaTable.ColumnDataType failes for column #1",
 114                                         typeof (System.String), schemaTable.Rows [1]["DataType"]);
 115               } finally {
 116                         // clean up
 117                         if (reader != null && !reader.IsClosed)
 118                                 reader.Close ();
 119                         reader = null;
 120                         CleanTestSetup ();
 121                         CloseConnection ();
 122               }
 123         }
 124
 125         [Test]
 126         public void GetNameTest ()
 127         {
 128               OdbcCommand dbcmd = conn.CreateCommand ();
 129               string sql = "SELECT  * from test";
 130               dbcmd.CommandText = sql;
 131               OdbcDataReader reader = dbcmd.ExecuteReader ();
 132               try {
 133                       Assertion.AssertEquals ("GetName failes ", "pk_tint", reader.GetName (0));
 134               } finally {
 135                       // clean up
 136                       if (reader != null && !reader.IsClosed)
 137                               reader.Close ();
 138                       reader = null;
 139                       CleanTestSetup ();
 140                       CloseConnection ();
 141               }
 142       }
 143
 144       [Test]
 145       public void GetBytesTest ()
 146       {
 147                 OdbcCommand cmd = conn.CreateCommand ();
 148                 string sql = "SELECT * FROM test";
 149                 cmd.CommandText = sql;
 150                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);
 151                 try {
 152                         if (reader.Read ()) {
 153                                 // Get By Parts for the column blob
 154                                 int totalsize = 100;
 155                                 int buffsize = 5;
 156                                 int buffstart = 0;
 157                                 long retval = 0;
 158                                 long start = 0;
 159                                 byte [] val = new byte [totalsize];
 160                                 retval = reader.GetBytes (3, start, val, buffstart, buffsize);
 161                                 while (retval == buffsize) {
 162                                         start += buffsize;
 163                                         buffstart += buffsize;
 164                                         retval = reader.GetBytes (3, start, val, buffstart, buffsize);
 165                                 }
 166                                 buffstart += (int) retval;
 167
 168                                 // assemble here.
 169                                 string col = System.Text.Encoding.Default.GetString (val, 0, buffstart);
 170
 171                                 Assertion.AssertEquals ("The assembled value length does not match",
 172                                               39, col.Length);
 173                         }
 174                 } finally {
 175                         // clean up
 176                         if (reader != null && !reader.IsClosed)
 177                                 reader.Close ();
 178                         reader = null;
 179                         CleanTestSetup ();
 180                         CloseConnection ();
 181                 }
 182       }
 183
 184       [Test]
 185       public void GetBytesNullBufferTest ()
 186       {
 187                 OdbcCommand cmd = conn.CreateCommand ();
 188                 string sql = "SELECT * FROM test";
 189                 cmd.CommandText = sql;
 190                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);
 191                 try {
 192                         if (reader.Read ()) {
 193                                 Assertion.AssertEquals ("GetBytes on a fixed length column does not work!",
 194                                                   11, reader.GetBytes (1,0,null,0,0));
 195                                 Assertion.AssertEquals ("GetBytes with non null column does not work!",
 196                                                   39, reader.GetBytes (3,0,null,0,0));
 197                         }
 198                         // for null value, length in bytes should return 0
 199                         if (reader.Read ())
 200                                 Assertion.AssertEquals ("GetBytes with null column does not return -1" ,
 201                                                   -1, reader.GetBytes (3,0,null,0,0));
 202                 } finally {
 203                         // clean up
 204                         if (reader != null && !reader.IsClosed )
 205                                 reader.Close ();
 206                         reader = null;
 207                         CleanTestSetup ();
 208                         CloseConnection ();
 209                 }
 210       }
 211
 212       [Test]
 213       public void GetValueBinaryTest ()
 214       {
 215                 OdbcCommand cmd = conn.CreateCommand ();
 216                 string sql = "SELECT * FROM test";
 217                 cmd.CommandText = sql;
 218                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);
 219                 try {
 220                         if (reader.Read ()) {
 221                                 object ob = reader.GetValue (3);
 222                                 Assertion.AssertEquals ("Type of binary column is wrong!",
 223                                                 "System.Byte[]", ob.GetType ().ToString () );
 224                         }
 225                 } finally {
 226                         // clean up
 227                         if (reader != null && !reader.IsClosed )
 228                                 reader.Close ();
 229                         reader = null;
 230                         CleanTestSetup ();
 231                         CloseConnection ();
 232                 }
 233       }
 234
 235       [Test]
 236       public void GetDateTimeTest ()
 237       {
 238                 OdbcCommand cmd = conn.CreateCommand ();
 239                 string sql = "SELECT * FROM test";
 240                 cmd.CommandText = sql;
 241                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.Default);
 242                 try {
 243                         if (reader.Read ()) {
 244                                 object ob = reader["col_datetime"];
 245                                 Assertion.AssertEquals ("Type of datetime column is wrong!",
 246                                                 "System.DateTime", ob.GetType ().ToString () );
 247                                 ob = reader["col_date"];
 248                                 Assertion.AssertEquals ("Type of date column is wrong!",
 249                                                 "System.DateTime", ob.GetType ().ToString () );
 250                                 // FIXME : Once TIME data type is fixed, enable this check
 251                                 //ob = reader["col_time"];
 252                                 //Assertion.AssertEquals ("Type of time column is wrong!",
 253                                                 //"System.DateTime", ob.GetType ().ToString () );
 254
 255                                 DateTime dt = reader.GetDateTime (4);
 256                                 Assertion.AssertEquals ("DateValue (SQL_TIMESTAMP) is wrong", new DateTime (2004, 8, 22, 0, 0, 0), dt);
 257                                 dt = reader.GetDateTime (5);
 258                                 Assertion.AssertEquals ("DateValue (SQL_DATE) is wrong", new DateTime (2004, 8, 22, 0, 0, 0), dt);
 259                                 // FIXME : Once TIME data type is fixed, enable this check
 260                                 //dt = reader.GetDateTime (7);
 261                                 //Assertion.AssertEquals ("DateValue is wrong", "2004-08-22", dt.ToString ());
 262                         }
 263                 } finally {
 264                         // clean up
 265                         if (reader != null && !reader.IsClosed )
 266                                 reader.Close ();
 267                         reader = null;
 268                         CleanTestSetup ();
 269                         CloseConnection ();
 270                 }
 271       }
 272
 273
 274
 275       [Test]
 276       public void NumericTest()
 277      {
 278                 using(IDbConnection dbConnection = new OdbcConnection
 279                 (connectionString))
 280                 {
 281                         dbConnection.Open();
 282                         IDbCommand dbCommand = dbConnection.CreateCommand();
 283                         //note this will fail if the table already exists, ie if the test has failed.
 284                         dbCommand.CommandText = "CREATE TABLE NumericTable (NumericField NUMERIC(10) NOT NULL)";
 285                         dbCommand.ExecuteNonQuery();
 286                         dbCommand.CommandText = "INSERT INTO NumericTable (NumericField) VALUES (125)";
 287                         dbCommand.ExecuteNonQuery();
 288                         dbCommand.CommandText = "SELECT * FROM NumericTable";
 289                         using(IDataReader reader = dbCommand.ExecuteReader())
 290                         {
 291                                 while(reader.Read())
 292                                 {
 293                                         for(int index = 0; index < reader.FieldCount; index++)
 294                                         {
 295                                                 Object dataValue = reader.GetValue(index);
 296                                                 Assert.AreEqual("System.Decimal",dataValue.GetType().ToString());
 297                                                 Assert.AreEqual("125", dataValue.ToString());
 298                                 }
 299                         }
 300                 }
 301
 302                 dbCommand.CommandText = "DROP TABLE NumericTable";
 303                 dbCommand.ExecuteNonQuery();
 304            }
 305      }
 306
 307
 308       /// <summary>
 309       /// This test for the return type &amp; value for GetValue
 310       /// in case of Odbc Data type TINYINT
 311       /// </summary>
 312       [Test]
 313       public void TinyIntTest ()
 314       {
 315                 OdbcCommand cmd = conn.CreateCommand ();
 316                 string sql = "SELECT * FROM test";
 317                 cmd.CommandText = sql;
 318                 OdbcDataReader reader = cmd.ExecuteReader (CommandBehavior.SequentialAccess);
 319                 try {
 320                         if (reader.Read ()) {
 321                                 object ob = reader.GetValue (0);
 322                                 Assertion.AssertEquals ("Type of tinyInt column is wrong!",
 323                                                 "System.Byte", ob.GetType ().ToString () );
 324                                 Assertion.AssertEquals ("Value of tinyInt column is wrong!",
 325                                                 1, System.Convert.ToInt16(ob) );
 326                         }
 327                 } finally {
 328                         // clean up
 329                         if (reader != null && !reader.IsClosed )
 330                                 reader.Close ();
 331                         reader = null;
 332                         CleanTestSetup ();
 333                         CloseConnection ();
 334                 }
 335       }
 336
 337
 338   }
 339 }