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 // To Compile:
  15 // mcs TestOracleClient.cs /r:System.Data.dll /r:System.Data.OracleClient.dll /nowarn:0168
  16 //
  17 // Author:
  18 //     Daniel Morgan <danielmorgan@verizon.net>
  19 //
  20 // Copyright (C) Daniel Morgan, 2002, 2004-2005
  21 //
  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 MonoTest(OracleConnection con)
  40                 {
  41                         Console.WriteLine ("  Drop table MONO_ORACLE_TEST ...");
  42                         try {
  43                                 OracleCommand cmd2 = con.CreateCommand ();
  44                                 cmd2.CommandText = "DROP TABLE MONO_ORACLE_TEST";
  45                                 cmd2.ExecuteNonQuery ();
  46                         }
  47                         catch (OracleException oe1) {
  48                                 // ignore if table already exists
  49                         }
  50
  51                         OracleCommand cmd = null;
  52                         int rowsAffected = 0;
  53
  54                         Console.WriteLine("  Creating table MONO_ORACLE_TEST...");
  55                         cmd = new OracleCommand();
  56                         cmd.Connection = con;
  57                         cmd.CommandText = "CREATE TABLE MONO_ORACLE_TEST ( " +
  58                                 " varchar2_value VarChar2(32),  " +
  59                                 " number_whole_value Number(18), " +
  60                                 " number_scaled_value Number(18,2), " +
  61                                 " number_integer_value Integer, " +
  62                                 " float_value Float, " +
  63                                 " date_value Date, " +
  64                                 " char_value Char(32), " +
  65                                 " clob_value Clob, " +
  66                                 " blob_value Blob, " +
  67                                 " clob_empty_value Clob, " +
  68                                 " blob_empty_value Blob, " +
  69                                 " varchar2_null_value VarChar2(32),  " +
  70                                 " number_whole_null_value Number(18), " +
  71                                 " number_scaled_null_value Number(18,2), " +
  72                                 " number_integer_null_value Integer, " +
  73                                 " float_null_value Float, " +
  74                                 " date_null_value Date, " +
  75                                 " char_null_value Char(32), " +
  76                                 " clob_null_value Clob, " +
  77                                 " blob_null_value Blob " +
  78                                 ")";
  79
  80                         rowsAffected = cmd.ExecuteNonQuery();
  81
  82                         Console.WriteLine("  Begin Trans for table MONO_ORACLE_TEST...");
  83                         OracleTransaction trans = con.BeginTransaction ();
  84
  85                         Console.WriteLine("  Inserting value into MONO_ORACLE_TEST...");
  86                         cmd = new OracleCommand();
  87                         cmd.Connection = con;
  88                         cmd.Transaction = trans;
  89                         cmd.CommandText = "INSERT INTO mono_oracle_test " +
  90                                 " ( varchar2_value,  " +
  91                                 "  number_whole_value, " +
  92                                 "  number_scaled_value, " +
  93                                 "  number_integer_value, " +
  94                                 "  float_value, " +
  95                                 "  date_value, " +
  96                                 "  char_value, " +
  97                                 "  clob_value, " +
  98                                 "  blob_value, " +
  99                                 "  clob_empty_value, " +
 100                                 "  blob_empty_value " +
 101                                 ") " +
 102                                 " VALUES( " +
 103                                 "  'Mono', " +
 104                                 "  123, " +
 105                                 "  456.78, " +
 106                                 "  8765, " +
 107                                 "  235.2, " +
 108                                 "  TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
 109                                 "  'US', " +
 110                                 "  EMPTY_CLOB(), " +
 111                                 "  EMPTY_BLOB()," +
 112                                 "  EMPTY_CLOB(), " +
 113                                 "  EMPTY_BLOB()" +
 114                                 ")";
 115
 116                         rowsAffected = cmd.ExecuteNonQuery();
 117
 118                         Console.WriteLine("  Select/Update CLOB columns on table MONO_ORACLE_TEST...");
 119
 120                         // update BLOB and CLOB columns
 121                         OracleCommand select = con.CreateCommand ();
 122                         select.Transaction = trans;
 123                         select.CommandText = "SELECT CLOB_VALUE, BLOB_VALUE FROM MONO_ORACLE_TEST FOR UPDATE";
 124                         OracleDataReader reader = select.ExecuteReader ();
 125                         if (!reader.Read ())
 126                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 127                         // update clob_value
 128                         Console.WriteLine("     Update CLOB column on table MONO_ORACLE_TEST...");
 129                         OracleLob clob = reader.GetOracleLob (0);
 130                         byte[] bytes = null;
 131                         UnicodeEncoding encoding = new UnicodeEncoding ();
 132                         bytes = encoding.GetBytes ("Mono is fun!");
 133                         clob.Write (bytes, 0, bytes.Length);
 134                         clob.Close ();
 135                         // update blob_value
 136                         Console.WriteLine("     Update BLOB column on table MONO_ORACLE_TEST...");
 137                         OracleLob blob = reader.GetOracleLob (1);
 138                         bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
 139                         blob.Write (bytes, 0, bytes.Length);
 140                         blob.Close ();
 141
 142                         Console.WriteLine("  Commit trans for table MONO_ORACLE_TEST...");
 143                         trans.Commit ();
 144
 145                         // OracleCommand.ExecuteReader of MONO_ORACLE_TEST table
 146                         Console.WriteLine("  Read simple test for table MONO_ORACLE_TEST...");
 147                         ReadSimpleTest(con, "SELECT * FROM MONO_ORACLE_TEST");
 148
 149                         // OracleCommand.ExecuteScalar
 150                         Console.WriteLine(" -ExecuteScalar tests...");
 151                         string varchar2_value = (string) ReadScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
 152                         Console.WriteLine("     String Value: " + varchar2_value);
 153
 154                         Console.WriteLine("  Read Scalar: number_whole_value");
 155                         decimal number_whole_value = (decimal)
 156                         ReadScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
 157                         Console.WriteLine("     Int32 Value: " + number_whole_value.ToString());
 158
 159                         Console.WriteLine("  Read Scalar: number_scaled_value");
 160                         decimal number_scaled_value = (decimal)
 161                         ReadScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
 162                         Console.WriteLine("     Decimal Value: " + number_scaled_value.ToString());
 163
 164                         Console.WriteLine("  Read Scalar: date_value");
 165                         DateTime date_value = (DateTime)
 166                         ReadScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
 167                         Console.WriteLine("     DateTime Value: " + date_value.ToString());
 168
 169                         Console.WriteLine("  Read Scalar: clob_value");
 170                         string clob_value = (string)
 171                         ReadScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
 172                         Console.WriteLine("     CLOB Value: " + clob_value);
 173
 174                         Console.WriteLine("  Read Scalar: blob_value");
 175                         byte[] blob_value = (byte[])
 176                         ReadScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
 177                         string sblob_value = GetHexString (blob_value);
 178                         Console.WriteLine("     BLOB Value: " + sblob_value);
 179
 180                         // OracleCommand.ExecuteOracleScalar
 181                         Console.WriteLine(" -ExecuteOracleScalar tests...");
 182                         Console.WriteLine("  Read Oracle Scalar: varchar2_value");
 183                         ReadOracleScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
 184
 185                         Console.WriteLine("  Read Oracle Scalar: number_whole_value");
 186                         ReadOracleScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
 187
 188                         Console.WriteLine("  Read Oracle Scalar: number_scaled_value");
 189                         ReadOracleScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
 190
 191                         Console.WriteLine("  Read Oracle Scalar: date_value");
 192                         ReadOracleScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
 193
 194                         Console.WriteLine("  Read Oracle Scalar: clob_value");
 195                         ReadOracleScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
 196
 197                         Console.WriteLine("  Read Oracle Scalar: blob_value");
 198                         ReadOracleScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
 199                 }
 200
 201                 static object ReadScalar (OracleConnection con, string selectSql)
 202                 {
 203                         OracleCommand cmd = null;
 204                         cmd = con.CreateCommand();
 205                         cmd.CommandText = selectSql;
 206
 207                         object o = cmd.ExecuteScalar ();
 208
 209                         string dataType = o.GetType ().ToString ();
 210                         Console.WriteLine ("       DataType: " + dataType);
 211                         return o;
 212                 }
 213
 214                 static void ReadOracleScalar (OracleConnection con, string selectSql)
 215                 {
 216                         OracleCommand cmd = null;
 217                         cmd = con.CreateCommand();
 218                         cmd.CommandText = selectSql;
 219
 220                         object o = cmd.ExecuteOracleScalar ();
 221
 222                         string dataType = o.GetType ().ToString ();
 223                         Console.WriteLine ("       DataType: " + dataType);
 224                         if (dataType.Equals("System.Data.OracleClient.OracleLob"))
 225                                 o = ((OracleLob) o).Value;
 226                         if (o.GetType ().ToString ().Equals ("System.Byte[]"))
 227                                 o = GetHexString ((byte[])o);
 228
 229                         Console.WriteLine ("          Value: " + o.ToString ());
 230                 }
 231
 232                 static void ReadSimpleTest(OracleConnection con, string selectSql)
 233                 {
 234                         OracleCommand cmd = null;
 235                         OracleDataReader reader = null;
 236
 237                         cmd = con.CreateCommand();
 238                         cmd.CommandText = selectSql;
 239                         reader = cmd.ExecuteReader();
 240
 241                         Console.WriteLine("  Results...");
 242                         Console.WriteLine("    Schema");
 243                         DataTable table;
 244                         table = reader.GetSchemaTable();
 245                         for(int c = 0; c < reader.FieldCount; c++) {
 246                                 Console.WriteLine("  Column " + c.ToString());
 247                                 DataRow row = table.Rows[c];
 248
 249                                 string strColumnName = row["ColumnName"].ToString();
 250                                 string strBaseColumnName = row["BaseColumnName"].ToString();
 251                                 string strColumnSize = row["ColumnSize"].ToString();
 252                                 string strNumericScale = row["NumericScale"].ToString();
 253                                 string strNumericPrecision = row["NumericPrecision"].ToString();
 254                                 string strDataType = row["DataType"].ToString();
 255
 256                                 Console.WriteLine("      ColumnName: " + strColumnName);
 257                                 Console.WriteLine("      BaseColumnName: " + strBaseColumnName);
 258                                 Console.WriteLine("      ColumnSize: " + strColumnSize);
 259                                 Console.WriteLine("      NumericScale: " + strNumericScale);
 260                                 Console.WriteLine("      NumericPrecision: " + strNumericPrecision);
 261                                 Console.WriteLine("      DataType: " + strDataType);
 262                         }
 263
 264                         int r = 0;
 265                         Console.WriteLine ("    Data");
 266                         while (reader.Read ()) {
 267                                 r++;
 268                                 Console.WriteLine ("       Row: " + r.ToString ());
 269                                 for (int f = 0; f < reader.FieldCount; f++) {
 270                                         string sname = "";
 271                                         object ovalue = "";
 272                                         string svalue = "";
 273                                         string sDataType = "";
 274                                         string sFieldType = "";
 275                                         string sDataTypeName = "";
 276                                         string sOraDataType = "";
 277
 278                                         sname = reader.GetName (f);
 279
 280                                         if (reader.IsDBNull (f)) {
 281                                                 ovalue = DBNull.Value;
 282                                                 svalue = "";
 283                                                 sDataType = "DBNull.Value";
 284                                                 sOraDataType = "DBNull.Value";
 285                                         }
 286                                         else {
 287                                                 ovalue = reader.GetOracleValue (f);
 288                                                 object oravalue = null;
 289
 290                                                 sDataType = ovalue.GetType ().ToString ();
 291                                                 switch (sDataType) {
 292                                                 case "System.Data.OracleClient.OracleString":
 293                                                         oravalue = ((OracleString) ovalue).Value;
 294                                                         break;
 295                                                 case "System.Data.OracleClient.OracleNumber":
 296                                                         oravalue = ((OracleNumber) ovalue).Value;
 297                                                         break;
 298                                                 case "System.Data.OracleClient.OracleLob":
 299                                                         OracleLob lob = (OracleLob) ovalue;
 300                                                         oravalue = lob.Value;
 301                                                         lob.Close ();
 302                                                         break;
 303                                                 case "System.Data.OracleClient.OracleDateTime":
 304                                                         oravalue = ((OracleDateTime) ovalue).Value;
 305                                                         break;
 306                                                 default:
 307                                                         oravalue = "*** no test available ***";
 308                                                         break;
 309                                                 }
 310
 311                                                 sOraDataType = oravalue.GetType ().ToString ();
 312                                                 if (sOraDataType.Equals ("System.Byte[]"))
 313                                                         svalue = GetHexString ((byte[]) oravalue);
 314                                                 else
 315                                                         svalue = oravalue.ToString();
 316
 317                                         }
 318                                         sFieldType = reader.GetFieldType(f).ToString();
 319                                         sDataTypeName = reader.GetDataTypeName(f);
 320
 321                                         Console.WriteLine("           Field: " + f.ToString());
 322                                         Console.WriteLine("               Name: " + sname);
 323                                         Console.WriteLine("               Value: " + svalue);
 324                                         Console.WriteLine("               Oracle Data Type: " + sOraDataType);
 325                                         Console.WriteLine("               Data Type: " + sDataType);
 326                                         Console.WriteLine("               Field Type: " + sFieldType);
 327                                         Console.WriteLine("               Data Type Name: " + sDataTypeName);
 328                                 }
 329                         }
 330                         if(r == 0)
 331                                 Console.WriteLine("  No data returned.");
 332                 }
 333
 334                 static void DataAdapterTest (OracleConnection connection)
 335                 {
 336                         Console.WriteLine("  Create select command...");
 337                         OracleCommand command = connection.CreateCommand ();
 338                         command.CommandText = "SELECT * FROM EMP";
 339
 340                         Console.WriteLine("  Create data adapter...");
 341                         OracleDataAdapter adapter = new OracleDataAdapter (command);
 342
 343                         Console.WriteLine("  Create DataSet...");
 344                         DataSet dataSet = new DataSet ("EMP");
 345
 346                         Console.WriteLine("  Fill DataSet via data adapter...");
 347                         adapter.Fill (dataSet);
 348
 349                         Console.WriteLine("  Get DataTable...");
 350                         DataTable table = dataSet.Tables [0];
 351
 352                         Console.WriteLine("  Display each row...");
 353                         int rowCount = 0;
 354                         foreach (DataRow row in table.Rows) {
 355                                 Console.WriteLine ("    row {0}", rowCount + 1);
 356                                 for (int i = 0; i < table.Columns.Count; i += 1) {
 357                                         Console.WriteLine ("      {0}: {1}", table.Columns [i].ColumnName, row [i]);
 358                                 }
 359                                 Console.WriteLine ();
 360                                 rowCount += 1;
 361                         }
 362                 }
 363
 364                 static void RollbackTest (OracleConnection connection)
 365                 {
 366                         OracleTransaction transaction = connection.BeginTransaction ();
 367
 368                         OracleCommand insert = connection.CreateCommand ();
 369                         insert.Transaction = transaction;
 370                         insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
 371
 372                         Console.WriteLine ("  Inserting record ...");
 373
 374                         insert.ExecuteNonQuery ();
 375
 376                         OracleCommand select = connection.CreateCommand ();
 377                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 378                         select.Transaction = transaction;
 379                         OracleDataReader reader = select.ExecuteReader ();
 380                         reader.Read ();
 381
 382                         Console.WriteLine ("  Row count SHOULD BE 1, VALUE IS {0}", reader.GetValue (0));
 383                         reader.Close ();
 384
 385                         Console.WriteLine ("  Rolling back transaction ...");
 386
 387                         transaction.Rollback ();
 388
 389                         select = connection.CreateCommand ();
 390                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 391
 392                         reader = select.ExecuteReader ();
 393                         reader.Read ();
 394                         Console.WriteLine ("  Row count SHOULD BE 0, VALUE IS {0}", reader.GetValue (0));
 395                         reader.Close ();
 396                 }
 397
 398                 static void CommitTest (OracleConnection connection)
 399                 {
 400                         OracleTransaction transaction = connection.BeginTransaction ();
 401
 402                         OracleCommand insert = connection.CreateCommand ();
 403                         insert.Transaction = transaction;
 404                         insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
 405
 406                         Console.WriteLine ("  Inserting record ...");
 407
 408                         insert.ExecuteNonQuery ();
 409
 410                         OracleCommand select = connection.CreateCommand ();
 411                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 412                         select.Transaction = transaction;
 413
 414                         Console.WriteLine ("  Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
 415
 416                         Console.WriteLine ("  Committing transaction ...");
 417
 418                         transaction.Commit ();
 419
 420                         select = connection.CreateCommand ();
 421                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 422
 423                         Console.WriteLine ("Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
 424                         transaction = connection.BeginTransaction ();
 425                         OracleCommand delete = connection.CreateCommand ();
 426                         delete.Transaction = transaction;
 427                         delete.CommandText = "DELETE FROM EMP WHERE EMPNO = 8787";
 428                         delete.ExecuteNonQuery ();
 429                         transaction.Commit ();
 430                 }
 431
 432                 public static void ParameterTest (OracleConnection connection)
 433                 {
 434                         OracleTransaction transaction = connection.BeginTransaction ();
 435                         OracleCommand insert = connection.CreateCommand ();
 436                         insert.Transaction = transaction;
 437
 438                         insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (:P1, :P2, :P3)";
 439                         insert.Parameters.Add (":P1", 8888);
 440                         insert.Parameters.Add (":P2", "danmorg");
 441                         insert.Parameters.Add (":P3", "Monoist");
 442
 443                         Console.WriteLine ("  INSERTING DATA WITH PARAMETERS...");
 444                         Console.WriteLine ("  " +  insert.CommandText);
 445                         insert.Prepare ();
 446                         insert.ExecuteNonQuery ();
 447
 448                         OracleCommand select = connection.CreateCommand ();
 449                         select.Transaction = transaction;
 450
 451                         select.CommandText = "SELECT ENAME, JOB FROM EMP WHERE EMPNO=:P1";
 452                         select.Parameters.Add (":P1", 8888);
 453
 454                         Console.WriteLine ("  VERIFYING RESULTS ...");
 455
 456                         OracleDataReader reader = select.ExecuteReader ();
 457                         if (!reader.Read ())
 458                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 459
 460                         Console.WriteLine ("  ENAME - SHOULD BE danmorg, is {0}", reader.GetValue (0));
 461                         Console.WriteLine ("  JOB - SHOULD BE Monoist, is {0}", reader.GetValue (1));
 462
 463                         reader.Close ();
 464
 465                         Console.WriteLine ("  ROLLBACK TRANSACTION...");
 466
 467                         transaction.Rollback ();
 468                 }
 469
 470                 public static void CLOBTest (OracleConnection connection)
 471                 {
 472                         Console.WriteLine ("  BEGIN TRANSACTION ...");
 473
 474                         OracleTransaction transaction = connection.BeginTransaction ();
 475
 476                         Console.WriteLine ("  Drop table CLOBTEST ...");
 477                         try {
 478                                 OracleCommand cmd2 = connection.CreateCommand ();
 479                                 cmd2.Transaction = transaction;
 480                                 cmd2.CommandText = "DROP TABLE CLOBTEST";
 481                                 cmd2.ExecuteNonQuery ();
 482                         }
 483                         catch (OracleException oe1) {
 484                                 // ignore if table already exists
 485                         }
 486
 487                         Console.WriteLine ("  CREATE TABLE ...");
 488
 489                         OracleCommand create = connection.CreateCommand ();
 490                         create.Transaction = transaction;
 491                         create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
 492                         create.ExecuteNonQuery ();
 493
 494                         Console.WriteLine ("  INSERT RECORD ...");
 495
 496                         OracleCommand insert = connection.CreateCommand ();
 497                         insert.Transaction = transaction;
 498                         insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
 499                         insert.ExecuteNonQuery ();
 500
 501                         OracleCommand select = connection.CreateCommand ();
 502                         select.Transaction = transaction;
 503                         select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
 504                         Console.WriteLine ("  SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
 505
 506                         OracleDataReader reader = select.ExecuteReader ();
 507                         if (!reader.Read ())
 508                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 509
 510                         Console.WriteLine ("  TESTING OracleLob OBJECT ...");
 511                         OracleLob lob = reader.GetOracleLob (0);
 512                         Console.WriteLine ("  LENGTH: {0}", lob.Length);
 513                         Console.WriteLine ("  CHUNK SIZE: {0}", lob.ChunkSize);
 514
 515                         UnicodeEncoding encoding = new UnicodeEncoding ();
 516
 517                         byte[] value = new byte [lob.Length * 2];
 518
 519                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
 520                         Console.WriteLine ("  UPDATING VALUE TO 'TEST ME!'");
 521                         value = encoding.GetBytes ("TEST ME!");
 522                         lob.Write (value, 0, value.Length);
 523
 524                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
 525                         Console.WriteLine ("  RE-READ VALUE...");
 526                         lob.Seek (1, SeekOrigin.Begin);
 527
 528                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
 529                         value = new byte [lob.Length * 2];
 530                         lob.Read (value, 0, value.Length);
 531                         Console.WriteLine ("  VALUE: {0}", encoding.GetString (value));
 532                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
 533
 534                         Console.WriteLine ("  CLOSE OracleLob...");
 535                         lob.Close ();
 536
 537                         Console.WriteLine ("  CLOSING READER...");
 538
 539                         reader.Close ();
 540                         transaction.Commit ();
 541                 }
 542
 543                 public static void BLOBTest (OracleConnection connection)
 544                 {
 545                         Console.WriteLine ("  BEGIN TRANSACTION ...");
 546
 547                         OracleTransaction transaction = connection.BeginTransaction ();
 548
 549                         Console.WriteLine ("  Drop table BLOBTEST ...");
 550                         try {
 551                                 OracleCommand cmd2 = connection.CreateCommand ();
 552                                 cmd2.Transaction = transaction;
 553                                 cmd2.CommandText = "DROP TABLE BLOBTEST";
 554                                 cmd2.ExecuteNonQuery ();
 555                         }
 556                         catch (OracleException oe1) {
 557                                 // ignore if table already exists
 558                         }
 559
 560                         Console.WriteLine ("  CREATE TABLE ...");
 561
 562                         OracleCommand create = connection.CreateCommand ();
 563                         create.Transaction = transaction;
 564                         create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
 565                         create.ExecuteNonQuery ();
 566
 567                         Console.WriteLine ("  INSERT RECORD ...");
 568
 569                         OracleCommand insert = connection.CreateCommand ();
 570                         insert.Transaction = transaction;
 571                         insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
 572                         insert.ExecuteNonQuery ();
 573
 574                         OracleCommand select = connection.CreateCommand ();
 575                         select.Transaction = transaction;
 576                         select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
 577                         Console.WriteLine ("  SELECTING A BLOB (Binary) VALUE FROM BLOBTEST");
 578
 579                         OracleDataReader reader = select.ExecuteReader ();
 580                         if (!reader.Read ())
 581                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 582
 583                         Console.WriteLine ("  TESTING OracleLob OBJECT ...");
 584                         OracleLob lob = reader.GetOracleLob (0);
 585
 586                         byte[] value = null;
 587                         string bvalue = "";
 588
 589                         Console.WriteLine ("  UPDATING VALUE");
 590
 591                         byte[] bytes = new byte[6];
 592                         bytes[0] = 0x31;
 593                         bytes[1] = 0x32;
 594                         bytes[2] = 0x33;
 595                         bytes[3] = 0x34;
 596                         bytes[4] = 0x35;
 597                         bytes[5] = 0x36;
 598
 599                         lob.Write (bytes, 0, bytes.Length);
 600
 601                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
 602                         Console.WriteLine ("  RE-READ VALUE...");
 603                         lob.Seek (1, SeekOrigin.Begin);
 604
 605                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
 606                         value = new byte [lob.Length];
 607                         lob.Read (value, 0, value.Length);
 608
 609                         bvalue = "";
 610                         if (value.GetType ().ToString ().Equals ("System.Byte[]"))
 611                                 bvalue = GetHexString (value);
 612                         Console.WriteLine ("  Bytes: " + bvalue);
 613
 614                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
 615
 616                         Console.WriteLine ("  CLOSE OracleLob...");
 617                         lob.Close ();
 618
 619                         Console.WriteLine ("  CLOSING READER...");
 620
 621                         reader.Close ();
 622                         transaction.Commit ();
 623                 }
 624
 625                 static void Wait(string msg)
 626                 {
 627                         Console.WriteLine(msg);
 628                         if (msg.Equals(""))
 629                                 Console.WriteLine("Waiting...  Press Enter to continue...");
 630                         string nothing = Console.ReadLine();
 631                 }
 632
 633                 // use this function to read a byte array into a string
 634                 // for easy display of binary data, such as, a BLOB value
 635                 public static string GetHexString (byte[] bytes)
 636                 {
 637                         string bvalue = "";
 638
 639                         StringBuilder sb2 = new StringBuilder();
 640                         for (int z = 0; z < bytes.Length; z++) {
 641                                 byte byt = bytes[z];
 642                                 sb2.Append (byt.ToString("x"));
 643                         }
 644                         if (sb2.Length > 0)
 645                                 bvalue = "0x" + sb2.ToString ();
 646
 647                         return bvalue;
 648                 }
 649
 650                 static void StoredProcedureTest1 (OracleConnection con)
 651                 {
 652                         // test stored procedure with no parameters
 653
 654
 655                         OracleCommand cmd2 = con.CreateCommand ();
 656
 657                         Console.WriteLine("  Drop table MONO_TEST_TABLE1...");
 658                         try {
 659                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE1";
 660                                 cmd2.ExecuteNonQuery ();
 661                         }
 662                         catch(OracleException oe1) {
 663                                 // ignore if table did not exist
 664                         }
 665
 666                         Console.WriteLine("  Drop procedure SP_TEST1...");
 667                         try {
 668                                 cmd2.CommandText = "DROP PROCEDURE SP_TEST1";
 669                                 cmd2.ExecuteNonQuery ();
 670                         }
 671                         catch(OracleException oe1) {
 672                                 // ignore if procedure did not exist
 673                         }
 674
 675                         Console.WriteLine("  Create table MONO_TEST_TABLE1...");
 676                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE1 (" +
 677                                         " COL1 VARCHAR2(8), "+
 678                                         " COL2 VARCHAR2(32))";
 679                         cmd2.ExecuteNonQuery ();
 680
 681                         Console.WriteLine("  Create stored procedure SP_TEST1...");
 682                         cmd2.CommandText = "CREATE PROCEDURE SP_TEST1 " +
 683                                 " IS " +
 684                                 " BEGIN " +
 685                                 "       INSERT INTO MONO_TEST_TABLE1 (COL1,COL2) VALUES ('aaa','bbbb');" +
 686                                 "       COMMIT;" +
 687                                 " END;";
 688                         cmd2.ExecuteNonQuery ();
 689
 690                         Console.WriteLine("COMMIT...");
 691                         cmd2.CommandText = "COMMIT";
 692                         cmd2.ExecuteNonQuery ();
 693
 694                         Console.WriteLine("  Call stored procedure sp_test1...");
 695                         OracleCommand cmd3 = con.CreateCommand ();\r
 696                         cmd3.CommandType = CommandType.StoredProcedure;\r
 697                         cmd3.CommandText = "sp_test1";\r
 698                         cmd3.ExecuteNonQuery ();
 699                 }
 700
 701                 static void StoredProcedureTest2 (OracleConnection con)
 702                 {
 703                         // test stored procedure with 2 parameters
 704
 705                         Console.WriteLine("  Drop table MONO_TEST_TABLE2...");
 706                         OracleCommand cmd2 = con.CreateCommand ();
 707
 708                         try {
 709                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE2";
 710                                 cmd2.ExecuteNonQuery ();
 711                         }
 712                         catch(OracleException oe1) {
 713                                 // ignore if table already exists
 714                         }
 715
 716                         Console.WriteLine("  Drop procedure SP_TEST2...");
 717                         try {
 718                                 cmd2.CommandText = "DROP PROCEDURE SP_TEST2";
 719                                 cmd2.ExecuteNonQuery ();
 720                         }
 721                         catch(OracleException oe1) {
 722                                 // ignore if table already exists
 723                         }
 724
 725                         Console.WriteLine("  Create table MONO_TEST_TABLE2...");
 726
 727                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE2 (" +
 728                                 " COL1 VARCHAR2(8), "+
 729                                 " COL2 VARCHAR2(32))";
 730                         cmd2.ExecuteNonQuery ();
 731
 732                         Console.WriteLine("  Create stored procedure SP_TEST2...");
 733                         cmd2.CommandText = "CREATE PROCEDURE SP_TEST2(parm1 VARCHAR2,parm2 VARCHAR2) " +
 734                                 " IS " +
 735                                 " BEGIN " +
 736                                 "       INSERT INTO MONO_TEST_TABLE2 (COL1,COL2) VALUES (parm1,parm2);" +
 737                                 "       COMMIT;" +
 738                                 " END;";
 739                         cmd2.ExecuteNonQuery ();
 740
 741                         Console.WriteLine("  COMMIT...");
 742                         cmd2.CommandText = "COMMIT";
 743                         cmd2.ExecuteNonQuery ();
 744
 745                         Console.WriteLine("  Call stored procedure SP_TEST2 with two parameters...");
 746                         OracleCommand cmd3 = con.CreateCommand ();\r
 747                         cmd3.CommandType = CommandType.StoredProcedure;\r
 748                         cmd3.CommandText = "sp_test2";\r
 749 \r
 750                         OracleParameter myParameter1 = new OracleParameter("parm1", OracleType.VarChar);\r
 751                         myParameter1.Value = "yyy13";\r
 752                         myParameter1.Size = 8;\r
 753                         myParameter1.Direction = ParameterDirection.Input;\r
 754                 \r
 755                         OracleParameter myParameter2 = new OracleParameter("parm2", OracleType.VarChar);\r
 756                         myParameter2.Value = "iii13";\r
 757                         myParameter2.Size = 32;\r
 758                         myParameter2.Direction = ParameterDirection.Input;\r
 759 \r
 760                         cmd3.Parameters.Add (myParameter1);\r
 761                         cmd3.Parameters.Add (myParameter2);\r
 762
 763                         cmd3.ExecuteNonQuery ();
 764                 }
 765
 766                 static void ShowConnectionProperties (OracleConnection con)
 767                 {
 768                         IDbConnection dbcon = (IDbConnection) con;
 769
 770                         try {
 771                                 Console.WriteLine ("ServerVersion: " + con.ServerVersion);
 772                         } catch (System.InvalidOperationException ioe) {\r
 773                                 Console.WriteLine ("InvalidOperationException caught.");\r
 774                                 Console.WriteLine ("Message: " + ioe.Message);\r
 775                         }
 776
 777                         Console.WriteLine ("DataSource: " + con.DataSource);
 778                 }
 779
 780                 static void NullAggregateTest (OracleConnection con)
 781                 {
 782                         Console.WriteLine("  Drop table MONO_TEST_TABLE3...");
 783                         OracleCommand cmd2 = con.CreateCommand ();
 784
 785                         try {
 786                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE3";
 787                                 cmd2.ExecuteNonQuery ();
 788                         }
 789                         catch(OracleException oe1) {
 790                                 // ignore if table already exists
 791                         }
 792
 793                         Console.WriteLine("  Create table MONO_TEST_TABLE3...");
 794
 795                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE3 (" +
 796                                 " COL1 VARCHAR2(8), "+
 797                                 " COL2 VARCHAR2(32))";
 798
 799                         cmd2.ExecuteNonQuery ();
 800
 801                         Console.WriteLine("  Insert some rows into table MONO_TEST_TABLE3...");
 802                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','one')";
 803                         cmd2.ExecuteNonQuery ();
 804
 805                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','uno')";
 806                         cmd2.ExecuteNonQuery ();
 807
 808                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','three')";
 809                         cmd2.ExecuteNonQuery ();
 810
 811                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3', null)";
 812                         cmd2.ExecuteNonQuery ();
 813
 814                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','few')";
 815                         cmd2.ExecuteNonQuery ();
 816
 817                         Console.WriteLine("  ExecuteScalar...");
 818                         cmd2.CommandText = "SELECT COL1, COUNT(COL2) AS MAX_COL1 FROM MONO_TEST_TABLE3 GROUP BY COL1";
 819                         OracleDataReader reader = cmd2.ExecuteReader ();
 820                         Console.WriteLine (" Read...");
 821                         while (reader.Read ()) {
 822
 823                                 object obj0 = reader.GetValue (0);
 824                                 Console.WriteLine("Value 0: " + obj0.ToString ());
 825                                 object obj1 = reader.GetValue (1);
 826                                 Console.WriteLine("Value 1: " + obj1.ToString ());
 827
 828                                 Console.WriteLine (" Read...");
 829                         }
 830
 831                         Console.WriteLine (" No more records.");
 832                 }
 833
 834                 static void OnInfoMessage (object sender, OracleInfoMessageEventArgs e) \r
 835                 {\r
 836                         Console.WriteLine("InfoMessage Message: " + e.Message.ToString());\r
 837                         Console.WriteLine("InfoMessage Code: " + e.Code.ToString());\r
 838                         Console.WriteLine("InfoMessage Source: " + e.Source.ToString());\r
 839                 }
 840
 841                 static void OnStateChange (object sender, StateChangeEventArgs e) \r
 842                 {\r
 843                         Console.WriteLine("StateChange CurrentSate:" + e.CurrentState.ToString ());\r
 844                         Console.WriteLine("StateChange OriginalState:" + e.OriginalState.ToString ());\r
 845                 }
 846
 847                 [STAThread]
 848                 static void Main(string[] args)
 849                 {
 850                         args = new string[3] { "palis", "scott", "tiger" };
 851
 852                         if(args.Length != 3) {
 853                                 Console.WriteLine("Usage: mono TestOracleClient database userid password");
 854                                 return;
 855                         }
 856
 857                         string connectionString = String.Format(
 858                                 "Data Source={0};" +
 859                                 "User ID={1};" +
 860                                 "Password={2}",
 861                                 args[0], args[1], args[2]);
 862
 863                         OracleConnection con1 = new OracleConnection();
 864
 865                         ShowConnectionProperties (con1);
 866
 867                         con1.ConnectionString = connectionString;
 868
 869                         con1.InfoMessage += new OracleInfoMessageEventHandler (OnInfoMessage);
 870                         con1.StateChange += new StateChangeEventHandler (OnStateChange);
 871                         Console.WriteLine("Opening...");
 872                         con1.Open ();
 873                         Console.WriteLine("Opened.");
 874
 875                         ShowConnectionProperties (con1);
 876
 877                         Console.WriteLine ("Mono Oracle Test BEGIN ...");
 878                         MonoTest (con1);
 879                         Console.WriteLine ("Mono Oracle Test END ...");
 880
 881                         Wait ("");
 882
 883                         Console.WriteLine ("LOB Test BEGIN...");
 884                         CLOBTest (con1);
 885                         BLOBTest (con1);
 886                         Console.WriteLine ("LOB Test END.");
 887                         Wait ("");
 888
 889                         Console.WriteLine ("Read Simple Test BEGIN - scott.emp...");
 890                         ReadSimpleTest(con1, "SELECT e.*, e.rowid FROM scott.emp e");
 891                         Console.WriteLine ("Read Simple Test END - scott.emp");
 892
 893                         Wait ("");
 894
 895                         Console.WriteLine ("DataAdapter Test BEGIN...");
 896                         DataAdapterTest(con1);
 897                         Console.WriteLine ("DataAdapter Test END.");
 898
 899                         Wait ("");
 900
 901                         Console.WriteLine ("Rollback Test BEGIN...");
 902                         RollbackTest(con1);
 903                         Console.WriteLine ("Rollback Test END.");
 904
 905                         Wait ("");
 906
 907                         Console.WriteLine ("Commit Test BEGIN...");
 908                         CommitTest(con1);
 909                         Console.WriteLine ("Commit Test END.");
 910
 911                         Wait ("");
 912
 913                         Console.WriteLine ("Parameter Test BEGIN...");
 914                         ParameterTest(con1);
 915                         Console.WriteLine ("Parameter Test END.");
 916
 917                         Wait ("");
 918
 919                         Console.WriteLine ("Stored Proc Test 1 BEGIN...");
 920                         StoredProcedureTest1 (con1);
 921                         ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE1");
 922                         Console.WriteLine ("Stored Proc Test 1 END...");
 923
 924                         Wait ("");
 925
 926                         Console.WriteLine ("Stored Proc Test 2 BEGIN...");
 927                         StoredProcedureTest2 (con1);
 928                         ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE2");
 929                         Console.WriteLine ("Stored Proc Test 2 END...");
 930
 931                         Wait ("");
 932
 933                         Console.WriteLine ("Null Aggregate Warning BEGIN test...");
 934                         NullAggregateTest (con1);
 935                         Console.WriteLine ("Null Aggregate Warning END test...");
 936
 937                         Console.WriteLine("Closing...");
 938                         con1.Close ();
 939                         Console.WriteLine("Closed.");
 940
 941                         Console.WriteLine("Done.");
 942                 }
 943         }
 944 }
 945