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

   1 //
   2 // TestOracleClient.cs - Tests Sytem.Data.OracleClient
   3 //                       data provider in Mono.
   4 //
   5 // Part of managed C#/.NET library System.Data.OracleClient.dll
   6 //
   7 // Part of the Mono class libraries at
   8 // mcs/class/System.Data.OracleClient/System.Data.OracleClient.OCI
   9 //
  10 // Tests:
  11 //     Assembly: System.Data.OracleClient.dll
  12 //     Namespace: System.Data.OracleClient
  13 //
  14 // Author:
  15 //     Daniel Morgan <danmorg@sc.rr.com>
  16 //
  17 // Copyright (C) Daniel Morgan, 2002
  18 //
  19
  20 // Expected Results:
  21 //    3 new rows where ENAME being: 'conn3', 'conn9', and 'conn1'
  22
  23 using System;
  24 using System.IO;
  25 using System.Runtime.InteropServices;
  26 using System.Data;
  27 using System.Data.OracleClient;
  28 using System.Text;
  29
  30 namespace Test.OracleClient
  31 {
  32         public class OracleTest
  33         {
  34                 public OracleTest()
  35                 {
  36
  37                 }
  38
  39                 static void DoTest1(OracleConnection con, int conn)
  40                 {
  41                         string inst = conn.ToString();
  42
  43                         string insertSql =
  44                                 "insert into scott.emp " +
  45                                 "(empno, ename, job, sal, deptno) " +
  46                                 "values(123" + inst + "," +
  47                                 "'conn" + inst + "'," +
  48                                 "'homy" + inst + "'," +
  49                                 "321" + inst + ",20)";
  50
  51                         Console.WriteLine("insertSql: " + insertSql);
  52                         OracleCommand cmd = new OracleCommand();
  53                         cmd.Connection = con;
  54
  55                         cmd.CommandText = insertSql;
  56                         cmd.ExecuteNonQuery();
  57
  58                         if(conn == 2)
  59                                 cmd.CommandText = "rollback";
  60                         else
  61                                 cmd.CommandText = "commit";
  62                         cmd.ExecuteNonQuery();
  63                 }
  64
  65                 static void DoTest9(OracleConnection con) {
  66                         string inst = "9";
  67
  68                         string insertSql =
  69                                 "insert into scott.emp " +
  70                                 "(empno, ename, job, sal, deptno) " +
  71                                 "values(123" + inst + "," +
  72                                 "'conn" + inst + "'," +
  73                                 "'homy" + inst + "'," +
  74                                 "321" + inst + ",20)";
  75
  76                         Console.WriteLine("insertSql: " + insertSql);
  77                         OracleCommand cmd = new OracleCommand();
  78                         cmd.Connection = con;
  79
  80                         cmd.CommandText = insertSql;
  81                         cmd.ExecuteNonQuery();
  82
  83                         cmd.CommandText = "commit";
  84                         cmd.ExecuteNonQuery();
  85                 }
  86
  87                 static void ReadSimpleTest(OracleConnection con)
  88                 {
  89                         string selectSql =
  90                                 "SELECT ename, job FROM scott.emp";
  91                         OracleCommand cmd = new OracleCommand();
  92                         cmd.Connection = con;
  93                         cmd.CommandText = selectSql;
  94                         OracleDataReader reader = cmd.ExecuteReader();
  95                         Console.WriteLine("Results...");
  96                         Console.WriteLine("Schema");
  97                         DataTable table;
  98                         table = reader.GetSchemaTable();
  99                         for(int c = 0; c < reader.FieldCount; c++) {
 100                                 Console.WriteLine("  Column " + c.ToString());
 101                                 DataRow row = table.Rows[c];
 102
 103                                 string ColumnName = (string) row["ColumnName"];
 104                                 string BaseColumnName = (string) row["BaseColumnName"];
 105                                 int ColumnSize = (int) row["ColumnSize"];
 106                                 int NumericScale = Convert.ToInt32( row["NumericScale"]);
 107                                 int NumericPrecision = Convert.ToInt32(row["NumericPrecision"]);
 108                                 Type DataType = (Type) row["DataType"];
 109
 110                                 Console.WriteLine("    ColumnName: " + ColumnName);
 111                                 Console.WriteLine("    BaseColumnName: " + BaseColumnName);
 112                                 Console.WriteLine("    ColumnSize: " + ColumnSize.ToString());
 113                                 Console.WriteLine("    NumericScale: " + NumericScale.ToString());
 114                                 Console.WriteLine("    NumericPrecision: " + NumericPrecision.ToString());
 115                                 Console.WriteLine("    DataType: " + DataType.ToString());
 116                         }
 117
 118                         int row = 0;
 119                         Console.WriteLine("Data");
 120                         while(reader.Read()) {
 121                                 row++;
 122                                 Console.WriteLine("  Row: " + row.ToString());
 123                                 for(int f = 0; f < reader.FieldCount; f++) {
 124                                         object ovalue;
 125                                         string svalue;
 126                                         ovalue = reader.GetValue(0);
 127                                         svalue = ovalue.ToString();
 128                                         Console.WriteLine("     Field: " + f.ToString());
 129                                         Console.WriteLine("         Value: " + svalue);
 130                                 }
 131                         }
 132                         if(row == 0)
 133                                 Console.WriteLine("No data returned.");
 134                 }
 135
 136                 static void DataAdapterTest (OracleConnection connection)
 137                 {
 138                         OracleCommand command = connection.CreateCommand ();
 139                         command.CommandText = "SELECT * FROM EMP";
 140                         OracleDataAdapter adapter = new OracleDataAdapter (command);
 141
 142                         DataSet dataSet = new DataSet ("EMP");
 143
 144                         adapter.Fill (dataSet);
 145
 146                         DataTable table = dataSet.Tables [0];
 147                         int rowCount = 0;
 148                         foreach (DataRow row in table.Rows) {
 149                                 Console.WriteLine ("row {0}", rowCount + 1);
 150                                 for (int i = 0; i < table.Columns.Count; i += 1) {
 151                                         Console.WriteLine ("{0}:{1}", table.Columns [i].ColumnName, row [i]);
 152                                 }
 153                                 Console.WriteLine ();
 154                                 rowCount += 1;
 155                         }
 156                 }
 157
 158                 static void RollbackTest (OracleConnection connection)
 159                 {
 160                         OracleTransaction transaction = connection.BeginTransaction ();
 161
 162                         OracleCommand insert = connection.CreateCommand ();
 163                         insert.Transaction = transaction;
 164                         insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
 165
 166                         Console.WriteLine ("Inserting record ...");
 167
 168                         insert.ExecuteNonQuery ();
 169
 170                         OracleCommand select = connection.CreateCommand ();
 171                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 172                         select.Transaction = transaction;
 173                         OracleDataReader reader = select.ExecuteReader ();
 174                         reader.Read ();
 175
 176                         Console.WriteLine ("Row count SHOULD BE 1, VALUE IS {0}", reader.GetValue (0));
 177                         reader.Close ();
 178
 179                         Console.WriteLine ("Rolling back transaction ...");
 180
 181                         transaction.Rollback ();
 182
 183                         select = connection.CreateCommand ();
 184                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 185
 186                         reader = select.ExecuteReader ();
 187                         reader.Read ();
 188                         Console.WriteLine ("Row count SHOULD BE 0, VALUE IS {0}", reader.GetValue (0));
 189                         reader.Close ();
 190                 }
 191
 192                 static void CommitTest (OracleConnection connection)
 193                 {
 194                         OracleTransaction transaction = connection.BeginTransaction ();
 195
 196                         OracleCommand insert = connection.CreateCommand ();
 197                         insert.Transaction = transaction;
 198                         insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
 199
 200                         Console.WriteLine ("Inserting record ...");
 201
 202                         insert.ExecuteNonQuery ();
 203
 204                         OracleCommand select = connection.CreateCommand ();
 205                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 206                         select.Transaction = transaction;
 207
 208                         Console.WriteLine ("Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
 209
 210                         Console.WriteLine ("Committing transaction ...");
 211
 212                         transaction.Commit ();
 213
 214                         select = connection.CreateCommand ();
 215                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 216
 217                         Console.WriteLine ("Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
 218                         transaction = connection.BeginTransaction ();
 219                         OracleCommand delete = connection.CreateCommand ();
 220                         delete.Transaction = transaction;
 221                         delete.CommandText = "DELETE FROM EMP WHERE EMPNO = 8787";
 222                         delete.ExecuteNonQuery ();
 223                         transaction.Commit ();
 224                 }
 225
 226                 public static void ParameterTest (OracleConnection connection)
 227                 {
 228                         OracleTransaction transaction = connection.BeginTransaction ();
 229                         OracleCommand insert = connection.CreateCommand ();
 230                         insert.Transaction = transaction;
 231
 232                         insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (:P1, :P2, :P3)";
 233                         insert.Parameters.Add (":P1", 8888);
 234                         insert.Parameters.Add (":P2", "danmorg");
 235                         insert.Parameters.Add (":P3", "Monoist");
 236
 237                         Console.WriteLine ("INSERTING DATA WITH PARAMETERS...");
 238                         Console.WriteLine (insert.CommandText);
 239                         insert.Prepare ();
 240                         insert.ExecuteNonQuery ();
 241
 242                         OracleCommand select = connection.CreateCommand ();
 243                         select.Transaction = transaction;
 244
 245                         select.CommandText = "SELECT ENAME, JOB FROM EMP WHERE EMPNO=:P1";
 246                         select.Parameters.Add (":P1", 8888);
 247
 248                         Console.WriteLine ("VERIFYING RESULTS ...");
 249
 250                         OracleDataReader reader = select.ExecuteReader ();
 251                         if (!reader.Read ())
 252                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 253
 254                         Console.WriteLine ("ENAME - SHOULD BE danmorg, is {0}", reader.GetValue (0));
 255                         Console.WriteLine ("JOB - SHOULD BE Monoist, is {0}", reader.GetValue (1));
 256
 257                         reader.Close ();
 258
 259                         Console.WriteLine ("ROLLBACK TRANSACTION...");
 260
 261                         transaction.Rollback ();
 262                 }
 263
 264                 public static void LOBTest (OracleConnection connection)
 265                 {
 266                         Console.WriteLine ("BEGIN TRANSACTION ...");
 267
 268                         OracleTransaction transaction = connection.BeginTransaction ();
 269
 270                         Console.WriteLine ("CREATE TABLE ...");
 271
 272                         OracleCommand create = connection.CreateCommand ();
 273                         create.Transaction = transaction;
 274                         create.CommandText = "CREATE TABLE LOBTEST (CLOB_COLUMN CLOB)";
 275                         create.ExecuteNonQuery ();
 276
 277                         Console.WriteLine ("INSERT RECORD ...");
 278
 279                         OracleCommand insert = connection.CreateCommand ();
 280                         insert.Transaction = transaction;
 281                         insert.CommandText = "INSERT INTO LOBTEST VALUES (EMPTY_CLOB())";
 282                         insert.ExecuteNonQuery ();
 283
 284                         OracleCommand select = connection.CreateCommand ();
 285                         select.Transaction = transaction;
 286                         select.CommandText = "SELECT CLOB_COLUMN FROM LOBTEST FOR UPDATE";
 287                         Console.WriteLine ("SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
 288
 289                         OracleDataReader reader = select.ExecuteReader ();
 290                         if (!reader.Read ())
 291                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 292
 293                         Console.WriteLine ("TESTING OracleLob OBJECT ...");
 294                         OracleLob lob = reader.GetOracleLob (0);
 295                         Console.WriteLine ("LENGTH: {0}", lob.Length);
 296                         Console.WriteLine ("CHUNK SIZE: {0}", lob.ChunkSize);
 297                         //Console.WriteLine ("ABOUT TO READ VALUE ... SHOULD BE ''");
 298
 299                         UnicodeEncoding encoding = new UnicodeEncoding ();
 300
 301                         byte[] value = new byte [lob.Length * 2];
 302                         //lob.Read (value, 0, (int) lob.Length * 2);
 303                         //Console.WriteLine ("VALUE: {0}", encoding.GetString (value));
 304
 305                         Console.WriteLine ("CURRENT POSITION: {0}", lob.Position);
 306                         Console.WriteLine ("UPDATING VALUE TO 'TEST ME!'");
 307                         value = encoding.GetBytes ("TEST ME!");
 308                         lob.Write (value, 0, value.Length);
 309
 310                         Console.WriteLine ("CURRENT POSITION: {0}", lob.Position);
 311                         Console.WriteLine ("RE-READ VALUE...");
 312                         lob.Seek (1, SeekOrigin.Begin);
 313
 314                         Console.WriteLine ("CURRENT POSITION: {0}", lob.Position);
 315                         value = new byte [lob.Length * 2];
 316                         lob.Read (value, 0, value.Length);
 317                         Console.WriteLine ("VALUE: {0}", encoding.GetString (value));
 318                         Console.WriteLine ("CURRENT POSITION: {0}", lob.Position);
 319
 320                         Console.WriteLine ("CLOSE OracleLob...");
 321                         lob.Close ();
 322
 323                         Console.WriteLine ("CLOSING READER...");
 324                         reader.Close ();
 325                         transaction.Commit ();
 326
 327                         Console.WriteLine ("DROP TABLE...");
 328
 329                         OracleCommand command = connection.CreateCommand ();
 330                         command.CommandText = "DROP TABLE LOBTEST";
 331                         command.ExecuteNonQuery ();
 332
 333                 }
 334
 335                 static void Wait(string msg)
 336                 {
 337                         Console.WriteLine(msg);
 338                         Console.WriteLine("Waiting...  Press Enter to continue...");
 339                         string nothing = Console.ReadLine();
 340                 }
 341
 342                 [STAThread]
 343                 static void Main(string[] args)
 344                 {
 345                         if(args.Length != 3) {
 346                                 Console.WriteLine("Usage: mono TestOracleClient database userid password");
 347                                 return;
 348                         }
 349
 350                         string connectionString = String.Format(
 351                                 "Data Source={0};" +
 352                                 "User ID={1};" +
 353                                 "Password={2}",
 354                                 args[0], args[1], args[2]);
 355
 356                         Wait("Verify database.");
 357
 358                         OracleConnection con1 = new OracleConnection();
 359                         con1.ConnectionString = connectionString;
 360                         con1.Open();
 361
 362                         Wait("Verify 1 connection.");
 363
 364                         OracleConnection con2 = new OracleConnection();
 365                         con2.ConnectionString = connectionString;
 366                         con2.Open();
 367
 368                         Wait("Verify 2 connections.");
 369
 370                         OracleConnection con3 = new OracleConnection();
 371                         con3.ConnectionString = connectionString;
 372                         con3.Open();
 373
 374                         Wait("Verify 3 connections.");
 375
 376                         //DoTest1(con1, 1);
 377                         //DoTest1(con2, 2);
 378                         //DoTest1(con3, 3);
 379
 380                         //DoTest9(con1);
 381
 382                         Console.WriteLine ("LOB Test BEGIN...");
 383                         LOBTest (con1);
 384                         Console.WriteLine ("LOB Test END.");
 385                         Wait ("Press enter to continue ...");
 386
 387                         Console.WriteLine ("Read Simple Test BEGIN...");
 388                         ReadSimpleTest(con1);
 389                         Console.WriteLine ("Read Simple Test END.");
 390
 391                         Wait ("Press enter to continue ...");
 392
 393                         Console.WriteLine ("DataAdapter Test BEGIN...");
 394                         DataAdapterTest(con1);
 395                         Console.WriteLine ("DataAdapter Test END.");
 396
 397                         Console.WriteLine ("Rollback Test BEGIN...");
 398                         RollbackTest(con1);
 399                         Console.WriteLine ("Rollback Test END.");
 400
 401                         Console.WriteLine ("Commit Test BEGIN...");
 402                         CommitTest(con1);
 403                         Console.WriteLine ("Commit Test END.");
 404
 405                         Console.WriteLine ("Parameter Test BEGIN...");
 406                         ParameterTest(con1);
 407                         Console.WriteLine ("Parameter Test END.");
 408
 409
 410
 411                         Wait("Verify Proper Results.");
 412
 413                         con1.Close();
 414
 415                         Wait("Verify 2 connections left.");
 416
 417                         con2.Close();
 418
 419                         Wait("Verify 1 connection left.");
 420
 421                         con3.Close();
 422
 423                         Wait("Verify all disconnected.");
 424                 }
 425         }
 426 }
 427