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 using System.Threading;
  30
  31 namespace Test.OracleClient
  32 {
  33         public class OracleTest
  34         {
  35                 private static Thread t = null;\r
  36                 private static string conStr;\r
  37                 public static readonly int MAX_CONNECTIONS = 30; // max connections default to 100, but I will set to 30.\r
  38
  39                 public OracleTest()
  40                 {
  41
  42                 }
  43
  44                 static void MonoTest(OracleConnection con)
  45                 {
  46                         Console.WriteLine ("  Drop table MONO_ORACLE_TEST ...");
  47                         try {
  48                                 OracleCommand cmd2 = con.CreateCommand ();
  49                                 cmd2.CommandText = "DROP TABLE MONO_ORACLE_TEST";
  50                                 cmd2.ExecuteNonQuery ();
  51                         }
  52                         catch (OracleException oe1) {
  53                                 // ignore if table already exists
  54                         }
  55
  56                         OracleCommand cmd = null;
  57
  58                         Console.WriteLine("  Creating table MONO_ORACLE_TEST...");
  59                         cmd = new OracleCommand();
  60                         cmd.Connection = con;
  61                         cmd.CommandText = "CREATE TABLE MONO_ORACLE_TEST ( " +
  62                                 " varchar2_value VarChar2(32),  " +
  63                                 " long_value long, " +
  64                                 " number_whole_value Number(18), " +
  65                                 " number_scaled_value Number(18,2), " +
  66                                 " number_integer_value Integer, " +
  67                                 " float_value Float, " +
  68                                 " date_value Date, " +
  69                                 " char_value Char(32), " +
  70                                 " clob_value Clob, " +
  71                                 " blob_value Blob, " +
  72                                 " clob_empty_value Clob, " +
  73                                 " blob_empty_value Blob, " +
  74                                 " varchar2_null_value VarChar2(32),  " +
  75                                 " number_whole_null_value Number(18), " +
  76                                 " number_scaled_null_value Number(18,2), " +
  77                                 " number_integer_null_value Integer, " +
  78                                 " float_null_value Float, " +
  79                                 " date_null_value Date, " +
  80                                 " char_null_value Char(32), " +
  81                                 " clob_null_value Clob, " +
  82                                 " blob_null_value Blob " +
  83                                 ")";
  84
  85                         cmd.ExecuteNonQuery();
  86
  87                         Console.WriteLine("  Begin Trans for table MONO_ORACLE_TEST...");
  88                         OracleTransaction trans = con.BeginTransaction ();
  89
  90                         Console.WriteLine("  Inserting value into MONO_ORACLE_TEST...");
  91                         cmd = new OracleCommand();
  92                         cmd.Connection = con;
  93                         cmd.Transaction = trans;
  94                         cmd.CommandText = "INSERT INTO mono_oracle_test " +
  95                                 " ( varchar2_value,  " +
  96                                 "  long_value, " +
  97                                 "  number_whole_value, " +
  98                                 "  number_scaled_value, " +
  99                                 "  number_integer_value, " +
 100                                 "  float_value, " +
 101                                 "  date_value, " +
 102                                 "  char_value, " +
 103                                 "  clob_value, " +
 104                                 "  blob_value, " +
 105                                 "  clob_empty_value, " +
 106                                 "  blob_empty_value " +
 107                                 ") " +
 108                                 " VALUES( " +
 109                                 "  'Mono', " +
 110                                 "  'This is a LONG column', " +
 111                                 "  123, " +
 112                                 "  456.78, " +
 113                                 "  8765, " +
 114                                 "  235.2, " +
 115                                 "  TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
 116                                 "  'US', " +
 117                                 "  EMPTY_CLOB(), " +
 118                                 "  EMPTY_BLOB()," +
 119                                 "  EMPTY_CLOB(), " +
 120                                 "  EMPTY_BLOB()" +
 121                                 ")";
 122
 123                         cmd.ExecuteNonQuery();
 124
 125                         Console.WriteLine("  Select/Update CLOB columns on table MONO_ORACLE_TEST...");
 126
 127                         // update BLOB and CLOB columns
 128                         OracleCommand select = con.CreateCommand ();
 129                         select.Transaction = trans;
 130                         select.CommandText = "SELECT CLOB_VALUE, BLOB_VALUE FROM MONO_ORACLE_TEST FOR UPDATE";
 131                         OracleDataReader reader = select.ExecuteReader ();
 132                         if (!reader.Read ())
 133                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 134                         // update clob_value
 135                         Console.WriteLine("     Update CLOB column on table MONO_ORACLE_TEST...");
 136                         OracleLob clob = reader.GetOracleLob (0);
 137                         byte[] bytes = null;
 138                         UnicodeEncoding encoding = new UnicodeEncoding ();
 139                         bytes = encoding.GetBytes ("Mono is fun!");
 140                         clob.Write (bytes, 0, bytes.Length);
 141                         clob.Close ();
 142                         // update blob_value
 143                         Console.WriteLine("     Update BLOB column on table MONO_ORACLE_TEST...");
 144                         OracleLob blob = reader.GetOracleLob (1);
 145                         bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
 146                         blob.Write (bytes, 0, bytes.Length);
 147                         blob.Close ();
 148
 149                         Console.WriteLine("  Commit trans for table MONO_ORACLE_TEST...");
 150                         trans.Commit ();
 151
 152                         // OracleCommand.ExecuteReader of MONO_ORACLE_TEST table
 153                         Console.WriteLine("  Read simple test for table MONO_ORACLE_TEST...");
 154                         ReadSimpleTest(con, "SELECT * FROM MONO_ORACLE_TEST");
 155
 156                         // OracleCommand.ExecuteScalar
 157                         Console.WriteLine(" -ExecuteScalar tests...");
 158                         string varchar2_value = (string) ReadScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
 159                         Console.WriteLine("     String Value: " + varchar2_value);
 160
 161                         Console.WriteLine("  Read Scalar: number_whole_value");
 162                         decimal number_whole_value = (decimal)
 163                         ReadScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
 164                         Console.WriteLine("     Int32 Value: " + number_whole_value.ToString());
 165
 166                         Console.WriteLine("  Read Scalar: number_scaled_value");
 167                         decimal number_scaled_value = (decimal)
 168                         ReadScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
 169                         Console.WriteLine("     Decimal Value: " + number_scaled_value.ToString());
 170
 171                         Console.WriteLine("  Read Scalar: date_value");
 172                         DateTime date_value = (DateTime)
 173                         ReadScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
 174                         Console.WriteLine("     DateTime Value: " + date_value.ToString());
 175
 176                         Console.WriteLine("  Read Scalar: clob_value");
 177                         string clob_value = (string)
 178                         ReadScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
 179                         Console.WriteLine("     CLOB Value: " + clob_value);
 180
 181                         Console.WriteLine("  Read Scalar: blob_value");
 182                         byte[] blob_value = (byte[])
 183                         ReadScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
 184                         string sblob_value = GetHexString (blob_value);
 185                         Console.WriteLine("     BLOB Value: " + sblob_value);
 186
 187                         // OracleCommand.ExecuteOracleScalar
 188                         Console.WriteLine(" -ExecuteOracleScalar tests...");
 189                         Console.WriteLine("  Read Oracle Scalar: varchar2_value");
 190                         ReadOracleScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
 191
 192                         Console.WriteLine("  Read Oracle Scalar: number_whole_value");
 193                         ReadOracleScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
 194
 195                         Console.WriteLine("  Read Oracle Scalar: number_scaled_value");
 196                         ReadOracleScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
 197
 198                         Console.WriteLine("  Read Oracle Scalar: date_value");
 199                         ReadOracleScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
 200
 201                         Console.WriteLine("  Read Oracle Scalar: clob_value");
 202                         ReadOracleScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
 203
 204                         Console.WriteLine("  Read Oracle Scalar: blob_value");
 205                         ReadOracleScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
 206                 }
 207
 208                 static object ReadScalar (OracleConnection con, string selectSql)
 209                 {
 210                         OracleCommand cmd = null;
 211                         cmd = con.CreateCommand();
 212                         cmd.CommandText = selectSql;
 213
 214                         object o = cmd.ExecuteScalar ();
 215
 216                         string dataType = o.GetType ().ToString ();
 217                         Console.WriteLine ("       DataType: " + dataType);
 218                         return o;
 219                 }
 220
 221                 static void ReadOracleScalar (OracleConnection con, string selectSql)
 222                 {
 223                         OracleCommand cmd = null;
 224                         cmd = con.CreateCommand();
 225                         cmd.CommandText = selectSql;
 226
 227                         object o = cmd.ExecuteOracleScalar ();
 228
 229                         string dataType = o.GetType ().ToString ();
 230                         Console.WriteLine ("       DataType: " + dataType);
 231                         if (dataType.Equals("System.Data.OracleClient.OracleLob"))
 232                                 o = ((OracleLob) o).Value;
 233                         if (o.GetType ().ToString ().Equals ("System.Byte[]"))
 234                                 o = GetHexString ((byte[])o);
 235
 236                         Console.WriteLine ("          Value: " + o.ToString ());
 237                 }
 238
 239                 static void ReadSimpleTest(OracleConnection con, string selectSql)
 240                 {
 241                         OracleCommand cmd = null;
 242                         OracleDataReader reader = null;
 243
 244                         cmd = con.CreateCommand();
 245                         cmd.CommandText = selectSql;
 246                         reader = cmd.ExecuteReader();
 247
 248                         Console.WriteLine("  Results...");
 249                         Console.WriteLine("    Schema");
 250                         DataTable table;
 251                         table = reader.GetSchemaTable();
 252                         for(int c = 0; c < reader.FieldCount; c++) {
 253                                 Console.WriteLine("  Column " + c.ToString());
 254                                 DataRow row = table.Rows[c];
 255
 256                                 string strColumnName = row["ColumnName"].ToString();
 257                                 string strBaseColumnName = row["BaseColumnName"].ToString();
 258                                 string strColumnSize = row["ColumnSize"].ToString();
 259                                 string strNumericScale = row["NumericScale"].ToString();
 260                                 string strNumericPrecision = row["NumericPrecision"].ToString();
 261                                 string strDataType = row["DataType"].ToString();
 262
 263                                 Console.WriteLine("      ColumnName: " + strColumnName);
 264                                 Console.WriteLine("      BaseColumnName: " + strBaseColumnName);
 265                                 Console.WriteLine("      ColumnSize: " + strColumnSize);
 266                                 Console.WriteLine("      NumericScale: " + strNumericScale);
 267                                 Console.WriteLine("      NumericPrecision: " + strNumericPrecision);
 268                                 Console.WriteLine("      DataType: " + strDataType);
 269                         }
 270
 271                         int r = 0;
 272                         Console.WriteLine ("    Data");
 273                         while (reader.Read ()) {
 274                                 r++;
 275                                 Console.WriteLine ("       Row: " + r.ToString ());
 276                                 for (int f = 0; f < reader.FieldCount; f++) {
 277                                         string sname = "";
 278                                         object ovalue = "";
 279                                         string svalue = "";
 280                                         string sDataType = "";
 281                                         string sFieldType = "";
 282                                         string sDataTypeName = "";
 283                                         string sOraDataType = "";
 284
 285                                         sname = reader.GetName (f);
 286
 287                                         if (reader.IsDBNull (f)) {
 288                                                 ovalue = DBNull.Value;
 289                                                 svalue = "";
 290                                                 sDataType = "DBNull.Value";
 291                                                 sOraDataType = "DBNull.Value";
 292                                         }
 293                                         else {
 294                                                 //ovalue = reader.GetValue (f);
 295                                                 ovalue = reader.GetOracleValue (f);
 296                                                 object oravalue = null;
 297
 298                                                 sDataType = ovalue.GetType ().ToString ();
 299                                                 switch (sDataType) {
 300                                                 case "System.Data.OracleClient.OracleString":
 301                                                         oravalue = ((OracleString) ovalue).Value;
 302                                                         break;
 303                                                 case "System.Data.OracleClient.OracleNumber":
 304                                                         oravalue = ((OracleNumber) ovalue).Value;
 305                                                         break;
 306                                                 case "System.Data.OracleClient.OracleLob":
 307                                                         OracleLob lob = (OracleLob) ovalue;
 308                                                         oravalue = lob.Value;
 309                                                         lob.Close ();
 310                                                         break;
 311                                                 case "System.Data.OracleClient.OracleDateTime":
 312                                                         oravalue = ((OracleDateTime) ovalue).Value;
 313                                                         break;
 314                                                 case "System.Byte[]":
 315                                                         oravalue = GetHexString((byte[])ovalue);
 316                                                         break;
 317                                                 case "System.Decimal":
 318                                                         //Console.WriteLine("           *** Get Decimal, Int16, Int32, Int64, Float, Double, ...");
 319                                                         decimal dec = reader.GetDecimal (f);
 320                                                         //Console.WriteLine("             GetDecimal: " + dec.ToString ());
 321
 322                                                         oravalue = (object) dec;
 323 /*
 324                                                         try {
 325                                                                 reader.GetInt16 (f);
 326                                                         } catch (NotSupportedException e) {
 327                                                                 Console.WriteLine ("            ** Expected exception caught for GetInt16: NotSupportedException: " + e.Message);
 328                                                         }
 329
 330                                                         try {
 331                                                                 long lng = reader.GetInt64 (f);
 332                                                                 Console.WriteLine("           GetInt64: " + lng.ToString ());
 333                                                                 int n = reader.GetInt32 (f);
 334                                                                 Console.WriteLine("           GetInt32: " + n.ToString ());
 335                                                                 float flt = reader.GetFloat (f);
 336                                                                 Console.WriteLine("           GetFloat: " + flt.ToString ());
 337                                                                 double dbl = reader.GetDouble (f);
 338                                                                 Console.WriteLine("           GetDouble: " + dbl.ToString ());
 339                                                         } catch (OverflowException oe1) {
 340                                                                 Console.WriteLine ("            ** Overflow exception for numbers to big or too small: " + oe1.Message);
 341                                                                 }
 342                                                         */
 343                                                         break;
 344                                                 default:
 345                                                         oravalue = ovalue.ToString ();
 346
 347                                                         break;
 348                                                 }
 349
 350                                                 sOraDataType = oravalue.GetType ().ToString ();
 351                                                 if (sOraDataType.Equals ("System.Byte[]"))
 352                                                         svalue = GetHexString ((byte[]) oravalue);
 353                                                 else
 354                                                         svalue = oravalue.ToString();
 355
 356                                         }
 357                                         sFieldType = reader.GetFieldType(f).ToString();
 358                                         sDataTypeName = reader.GetDataTypeName(f);
 359
 360                                         Console.WriteLine("           Field: " + f.ToString());
 361                                         Console.WriteLine("               Name: " + sname);
 362                                         Console.WriteLine("               Value: " + svalue);
 363                                         Console.WriteLine("               Oracle Data Type: " + sOraDataType);
 364                                         Console.WriteLine("               Data Type: " + sDataType);
 365                                         Console.WriteLine("               Field Type: " + sFieldType);
 366                                         Console.WriteLine("               Data Type Name: " + sDataTypeName);
 367                                 }
 368                         }
 369                         if(r == 0)
 370                                 Console.WriteLine("  No data returned.");
 371                 }
 372
 373                 static void DataAdapterTest (OracleConnection connection)
 374                 {
 375                         Console.WriteLine("  Create select command...");
 376                         OracleCommand command = connection.CreateCommand ();
 377                         command.CommandText = "SELECT * FROM EMP";
 378
 379                         Console.WriteLine("  Create data adapter...");
 380                         OracleDataAdapter adapter = new OracleDataAdapter (command);
 381
 382                         Console.WriteLine("  Create DataSet...");
 383                         DataSet dataSet = new DataSet ("EMP");
 384
 385                         Console.WriteLine("  Fill DataSet via data adapter...");
 386                         adapter.Fill (dataSet);
 387
 388                         Console.WriteLine("  Get DataTable...");
 389                         DataTable table = dataSet.Tables [0];
 390
 391                         Console.WriteLine("  Display each row...");
 392                         int rowCount = 0;
 393                         foreach (DataRow row in table.Rows) {
 394                                 Console.WriteLine ("    row {0}", rowCount + 1);
 395                                 for (int i = 0; i < table.Columns.Count; i += 1) {
 396                                         Console.WriteLine ("      {0}: {1}", table.Columns [i].ColumnName, row [i]);
 397                                 }
 398                                 Console.WriteLine ();
 399                                 rowCount += 1;
 400                         }
 401                 }
 402
 403                 public static void DataAdapterTest2 (OracleConnection con) \r
 404                 {\r
 405                         DataAdapterTest2_Setup (con);\r
 406                         ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
 407                 \r
 408                         GetMetaData (con, "SELECT * FROM mono_adapter_test");\r
 409 \r
 410                         DataAdapterTest2_Insert (con);\r
 411                         ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
 412                 \r
 413                         DataAdapterTest2_Update (con);\r
 414                         ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
 415 \r
 416                         DataAdapterTest2_Delete (con);\r
 417                         ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
 418                 }\r
 419 \r
 420                 public static void GetMetaData (OracleConnection con, string sql) \r
 421                 {\r
 422                         OracleCommand cmd = null;
 423                         OracleDataReader rdr = null;
 424
 425                         cmd = con.CreateCommand();
 426                         cmd.CommandText = sql;
 427
 428                         Console.WriteLine("Read Schema With KeyInfo");
 429                         rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
 430                 \r
 431                         DataTable dt;\r
 432                         dt = rdr.GetSchemaTable();\r
 433                         foreach (DataRow schemaRow in dt.Rows) {\r
 434                                 foreach (DataColumn schemaCol in dt.Columns) {\r
 435                                         Console.WriteLine(schemaCol.ColumnName + \r
 436                                                 " = " + \r
 437                                                 schemaRow[schemaCol]);\r
 438                                         Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString());\r
 439                                 }\r
 440                                 Console.WriteLine("");\r
 441                         }\r
 442 \r
 443                         Console.WriteLine("Read Schema with No KeyInfo");\r
 444 \r
 445                         rdr = cmd.ExecuteReader();
 446 \r
 447                         dt = rdr.GetSchemaTable();\r
 448                         foreach (DataRow schemaRow in dt.Rows) {\r
 449                                 foreach (DataColumn schemaCol in dt.Columns) {\r
 450                                         Console.WriteLine(schemaCol.ColumnName + \r
 451                                                 " = " + \r
 452                                                 schemaRow[schemaCol]);\r
 453                                         Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString());\r
 454                                         Console.WriteLine();\r
 455                                 }\r
 456                         }\r
 457 \r
 458                 }\r
 459 \r
 460                 public static void DataAdapterTest2_Setup (OracleConnection con) \r
 461                 {\r
 462                         Console.WriteLine ("  Drop table mono_adapter_test ...");
 463                         try {
 464                                 OracleCommand cmd2 = con.CreateCommand ();
 465                                 cmd2.CommandText = "DROP TABLE mono_adapter_test";
 466                                 cmd2.ExecuteNonQuery ();
 467                         }
 468                         catch (OracleException oe1) {
 469                                 // ignore if table already exists
 470                         }
 471
 472                         OracleCommand cmd = null;
 473                         int rowsAffected = 0;
 474
 475                         Console.WriteLine("  Creating table mono_adapter_test...");
 476                         cmd = new OracleCommand ();
 477                         cmd.Connection = con;
 478                         cmd.CommandText = "CREATE TABLE mono_adapter_test ( " +
 479                                 " varchar2_value VarChar2(32),  " +
 480                                 " number_whole_value Number(18) PRIMARY KEY, " +
 481                                 " number_scaled_value Number(18,2), " +
 482                                 " number_integer_value Integer, " +
 483                                 " float_value Float, " +
 484                                 " date_value Date, " +
 485                                 " clob_value Clob, " +
 486                                 " blob_value Blob ) ";
 487
 488                         // FIXME: char_value does not work
 489                         /*
 490                         cmd.CommandText = "CREATE TABLE mono_adapter_test ( " +
 491                                 " varchar2_value VarChar2(32),  " +
 492                                 " number_whole_value Number(18) PRIMARY KEY, " +
 493                                 " number_scaled_value Number(18,2), " +
 494                                 " number_integer_value Integer, " +
 495                                 " float_value Float, " +
 496                                 " date_value Date, " +
 497                                 " char_value Char(32), " +
 498                                 " clob_value Clob, " +
 499                                 " blob_value Blob ) ";
 500                         */
 501
 502                         rowsAffected = cmd.ExecuteNonQuery();
 503
 504                         Console.WriteLine("  Begin Trans for table mono_adapter_test...");
 505                         OracleTransaction trans = con.BeginTransaction ();
 506
 507                         Console.WriteLine("  Inserting value into mono_adapter_test...");
 508                         cmd = new OracleCommand();
 509                         cmd.Connection = con;
 510                         cmd.Transaction = trans;
 511
 512                         cmd.CommandText = "INSERT INTO mono_adapter_test " +
 513                                 " ( varchar2_value,  " +
 514                                 "  number_whole_value, " +
 515                                 "  number_scaled_value, " +
 516                                 "  number_integer_value, " +
 517                                 "  float_value, " +
 518                                 "  date_value, " +
 519                                 "  clob_value, " +
 520                                 "  blob_value " +
 521                                 ") " +
 522                                 " VALUES( " +
 523                                 "  'Mono', " +
 524                                 "  11, " +
 525                                 "  456.78, " +
 526                                 "  8765, " +
 527                                 "  235.2, " +
 528                                 "  TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
 529                                 "  EMPTY_CLOB(), " +
 530                                 "  EMPTY_BLOB() " +
 531                                 ")";
 532
 533                         /*
 534                                         cmd.CommandText = "INSERT INTO mono_adapter_test " +
 535                                                 " ( varchar2_value,  " +
 536                                                 "  number_whole_value, " +
 537                                                 "  number_scaled_value, " +
 538                                                 "  number_integer_value, " +
 539                                                 "  float_value, " +
 540                                                 "  date_value, " +
 541                                                 "  char_value, " +
 542                                                 "  clob_value, " +
 543                                                 "  blob_value " +
 544                                                 ") " +
 545                                                 " VALUES( " +
 546                                                 "  'Mono', " +
 547                                                 "  11, " +
 548                                                 "  456.78, " +
 549                                                 "  8765, " +
 550                                                 "  235.2, " +
 551                                                 "  TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
 552                                                 "  'US', " +
 553                                                 "  EMPTY_CLOB(), " +
 554                                                 "  EMPTY_BLOB() " +
 555                                                 ")";
 556                         */
 557                         rowsAffected = cmd.ExecuteNonQuery();\r
 558 \r
 559                         Console.WriteLine("  Select/Update CLOB columns on table mono_adapter_test...");
 560
 561                         // update BLOB and CLOB columns
 562                         OracleCommand select = con.CreateCommand ();
 563                         select.Transaction = trans;
 564                         select.CommandText = "SELECT CLOB_VALUE, BLOB_VALUE FROM mono_adapter_test FOR UPDATE";
 565                         OracleDataReader reader = select.ExecuteReader ();
 566                         if (!reader.Read ())
 567                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
 568
 569                         // update clob_value
 570                         Console.WriteLine("     Update CLOB column on table mono_adapter_test...");
 571                         OracleLob clob = reader.GetOracleLob (0);
 572                         byte[] bytes = null;
 573                         UnicodeEncoding encoding = new UnicodeEncoding ();
 574                         bytes = encoding.GetBytes ("Mono is fun!");
 575                         clob.Write (bytes, 0, bytes.Length);
 576                         clob.Close ();
 577
 578                         // update blob_value
 579                         Console.WriteLine("     Update BLOB column on table mono_adapter_test...");
 580                         OracleLob blob = reader.GetOracleLob (1);
 581                         bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
 582                         blob.Write (bytes, 0, bytes.Length);
 583                         blob.Close ();
 584
 585                         Console.WriteLine("  Commit trans for table mono_adapter_test...");
 586                         trans.Commit ();
 587
 588                         CommitCursor (con);\r
 589                 }\r
 590 \r
 591                 public static void DataAdapterTest2_Insert (OracleConnection con)
 592                 {
 593                         Console.WriteLine("================================");\r
 594                         Console.WriteLine("=== Adapter Insert =============");
 595                         Console.WriteLine("================================");\r
 596                         OracleTransaction transaction = con.BeginTransaction ();
 597                 \r
 598                         Console.WriteLine("   Create adapter...");\r
 599                         OracleDataAdapter da = new OracleDataAdapter("select * from mono_adapter_test", con);
 600                         da.SelectCommand.Transaction = transaction;
 601                 \r
 602                         Console.WriteLine("   Create command builder...");\r
 603                         OracleCommandBuilder mycb = new OracleCommandBuilder(da);
 604 \r
 605                         Console.WriteLine("   Create data set ...");\r
 606                         DataSet ds = new DataSet();\r
 607
 608                         Console.WriteLine("   Fill data set via adapter...");\r
 609                         da.Fill(ds, "mono_adapter_test");\r
 610 \r
 611                         Console.WriteLine("   New Row...");
 612                         DataRow myRow;\r
 613                         myRow = ds.Tables["mono_adapter_test"].NewRow();
 614 \r
 615                         byte[] bytes = new byte[] { 0x45,0x46,0x47,0x48,0x49,0x50 };\r
 616 \r
 617                         Console.WriteLine("   Set values in the new DataRow...");\r
 618                         myRow["varchar2_value"] = "OracleClient";\r
 619                         myRow["number_whole_value"] = 22;\r
 620                         myRow["number_scaled_value"] = 12.34;\r
 621                         myRow["number_integer_value"] = 456;\r
 622                         myRow["float_value"] = 98.76;\r
 623                         myRow["date_value"] = new DateTime(2001,07,09);\r
 624                         Console.WriteLine("   *** FIXME; char value not working");\r
 625                         //myRow["char_value"] = "Romeo";\r
 626                         myRow["clob_value"] = "clobtest";\r
 627                         myRow["blob_value"] = bytes;\r
 628                 \r
 629                         Console.WriteLine("    Add DataRow to DataTable...");           \r
 630                         ds.Tables["mono_adapter_test"].Rows.Add(myRow);
 631 \r
 632                         Console.WriteLine("da.Update(ds...");\r
 633                         da.Update(ds, "mono_adapter_test");\r
 634 \r
 635                         transaction.Commit();
 636                 }
 637 \r
 638                 public static void DataAdapterTest2_Update (OracleConnection con)
 639                 {
 640                         Console.WriteLine("================================");\r
 641                         Console.WriteLine("=== Adapter Update =============");
 642                         Console.WriteLine("================================");\r
 643 \r
 644                         OracleTransaction transaction = con.BeginTransaction ();
 645 \r
 646                         Console.WriteLine("   Create adapter...");\r
 647                         OracleCommand selectCmd = con.CreateCommand ();
 648                         selectCmd.Transaction = transaction;\r
 649                         selectCmd.CommandText = "SELECT * FROM mono_adapter_test";\r
 650                         OracleDataAdapter da = new OracleDataAdapter(selectCmd);\r
 651                         Console.WriteLine("   Create command builder...");\r
 652                         OracleCommandBuilder mycb = new OracleCommandBuilder(da);\r
 653                         Console.WriteLine("   Create data set ...");\r
 654                         DataSet ds = new DataSet();\r
 655 \r
 656                         Console.WriteLine("   Set missing schema action...");\r
 657                 \r
 658                         Console.WriteLine("  Fill data set via adapter...");\r
 659                         da.Fill(ds, "mono_adapter_test");\r
 660                         DataRow myRow;\r
 661 \r
 662                         Console.WriteLine("   New Row...");
 663                         myRow = ds.Tables["mono_adapter_test"].Rows[0];
 664
 665                         Console.WriteLine("Tables Count: " + ds.Tables.Count.ToString());
 666
 667                         DataTable table = ds.Tables["mono_adapter_test"];
 668                         DataRowCollection rows;
 669                         rows = table.Rows;
 670                         Console.WriteLine("   Row Count: " + rows.Count.ToString());
 671                         myRow = rows[0];\r
 672 \r
 673                         byte[] bytes = new byte[] { 0x62,0x63,0x64,0x65,0x66,0x67 };\r
 674 \r
 675                         Console.WriteLine("   Set values in the new DataRow...");\r
 676
 677                         myRow["varchar2_value"] = "Super Power!";\r
 678                 \r
 679                         myRow["number_scaled_value"] = 12.35;\r
 680                         myRow["number_integer_value"] = 457;\r
 681                         myRow["float_value"] = 198.76;\r
 682                         myRow["date_value"] = new DateTime(2002,08,09);\r
 683                         //myRow["char_value"] = "Juliet";\r
 684                         myRow["clob_value"] = "this is a clob";\r
 685                         myRow["blob_value"] = bytes;
 686 \r
 687                         Console.WriteLine("da.Update(ds...");\r
 688                         da.Update(ds, "mono_adapter_test");\r
 689 \r
 690                         transaction.Commit();
 691                 }\r
 692 \r
 693                 public static void DataAdapterTest2_Delete (OracleConnection con)
 694                 {
 695                         Console.WriteLine("================================");\r
 696                         Console.WriteLine("=== Adapter Delete =============");
 697                         Console.WriteLine("================================");\r
 698                         OracleTransaction transaction = con.BeginTransaction ();
 699                 \r
 700                         Console.WriteLine("   Create adapter...");\r
 701                         OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM mono_adapter_test", con);
 702                         Console.WriteLine("   Create command builder...");
 703                         OracleCommandBuilder mycb = new OracleCommandBuilder(da);
 704                         Console.WriteLine("   set transr...");
 705                         da.SelectCommand.Transaction = transaction;
 706
 707                         Console.WriteLine("   Create data set ...");\r
 708                         DataSet ds = new DataSet();\r
 709                 \r
 710                         Console.WriteLine("Fill data set via adapter...");\r
 711                         da.Fill(ds, "mono_adapter_test");\r
 712 \r
 713                         Console.WriteLine("delete row...");
 714                         ds.Tables["mono_adapter_test"].Rows[0].Delete();
 715
 716                         Console.WriteLine("da.Update(table...");\r
 717                         da.Update(ds, "mono_adapter_test");\r
 718 \r
 719                         Console.WriteLine("Commit...");\r
 720                         transaction.Commit();\r
 721                 }
 722
 723                 static void TestNonQueryUsingExecuteReader(OracleConnection con) \r
 724                 {\r
 725                         OracleDataReader reader = null;\r
 726                         OracleTransaction trans = null;\r
 727 \r
 728                         Console.WriteLine("   drop table mono_adapter_test...");\r
 729                         OracleCommand cmd = con.CreateCommand();\r
 730 \r
 731                         cmd.CommandText = "DROP TABLE MONO_ADAPTER_TEST";\r
 732                         trans = con.BeginTransaction();\r
 733                         cmd.Transaction = trans;\r
 734                         try {\r
 735                                 reader = cmd.ExecuteReader();\r
 736                                 Console.WriteLine("   RowsAffected before read: " + reader.RecordsAffected.ToString());\r
 737                                 reader.Read();\r
 738                                 Console.WriteLine("   RowsAffected after read: " + reader.RecordsAffected.ToString());\r
 739                                 reader.Close();\r
 740                                 Console.WriteLine("   RowsAffected after close: " + reader.RecordsAffected.ToString());\r
 741                                 trans.Commit();\r
 742                         }\r
 743                         catch(OracleException e) {\r
 744                                 Console.WriteLine("   OracleException caught: " + e.Message);\r
 745                                 trans.Commit();\r
 746                         }\r
 747 \r
 748                         Console.WriteLine("   Create table mono_adapter_test...");\r
 749                         cmd.CommandText = "CREATE TABLE MONO_ADAPTER_TEST ( " +
 750                                 " varchar2_value VarChar2(32),  " +
 751                                 " number_whole_value Number(18,0) PRIMARY KEY ) ";\r
 752                         trans = con.BeginTransaction();\r
 753                         cmd.Transaction = trans;\r
 754                         reader = cmd.ExecuteReader();\r
 755                         Console.WriteLine("   RowsAffected before read: " + reader.RecordsAffected.ToString());\r
 756                         reader.Read();\r
 757                         Console.WriteLine("   RowsAffected after read: " + reader.RecordsAffected.ToString());\r
 758                         reader.Close();\r
 759                         Console.WriteLine("   RowsAffected after close: " + reader.RecordsAffected.ToString());\r
 760                         trans.Commit();\r
 761 \r
 762                         Console.WriteLine("Insert into table mono_adapter_test...");\r
 763                         \r
 764                         string sql =\r
 765                                 "INSERT INTO MONO_ADAPTER_TEST " +\r
 766                                 "(VARCHAR2_VALUE,NUMBER_WHOLE_VALUE) " +\r
 767                                 "VALUES(:p1,:p2)";\r
 768 \r
 769                         OracleCommand cmd2 = con.CreateCommand();\r
 770                         trans = con.BeginTransaction();\r
 771                         cmd2.Transaction = trans;\r
 772                         cmd2.CommandText = sql;\r
 773                         \r
 774                         OracleParameter myParameter1 = new OracleParameter("p1", OracleType.VarChar, 32);
 775                         myParameter1.Direction = ParameterDirection.Input;\r
 776                 \r
 777                         OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Number);\r
 778                         myParameter2.Direction = ParameterDirection.Input;\r
 779 \r
 780                         myParameter2.Value = 182;\r
 781                         myParameter1.Value = "Mono";\r
 782 \r
 783                         cmd2.Parameters.Add (myParameter1);\r
 784                         cmd2.Parameters.Add (myParameter2);\r
 785                         \r
 786                         // insert 1 record\r
 787                         reader = cmd2.ExecuteReader();\r
 788                         Console.WriteLine("   RowsAffected before read: " + reader.RecordsAffected.ToString());\r
 789                         reader.Read();\r
 790                         Console.WriteLine("   RowsAffected after read: " + reader.RecordsAffected.ToString());\r
 791                         reader.Close();\r
 792                         Console.WriteLine("   RowsAffected after close: " + reader.RecordsAffected.ToString());\r
 793 \r
 794                         // insert another record\r
 795                         Console.WriteLine("   Insert another record...");\r
 796                         myParameter2.Value = 183;\r
 797                         myParameter1.Value = "Oracle";\r
 798                         reader = cmd2.ExecuteReader();\r
 799                         Console.WriteLine("   RowsAffected before read: " + reader.RecordsAffected.ToString());\r
 800                         reader.Read();\r
 801                         Console.WriteLine("   RowsAffected after read: " + reader.RecordsAffected.ToString());\r
 802                         reader.Close();\r
 803                         Console.WriteLine("   RowsAffected after close: " + reader.RecordsAffected.ToString());\r
 804 \r
 805                         trans.Commit();\r
 806                         trans = null;\r
 807                         \r
 808                         ReadSimpleTest(con, "SELECT * FROM MONO_ADAPTER_TEST");\r
 809                 }\r
 810
 811                 static void CommitCursor (OracleConnection con)
 812                 {
 813                         OracleCommand cmd = con.CreateCommand ();
 814                         cmd.CommandText = "COMMIT";
 815                         cmd.ExecuteNonQuery ();
 816                         cmd.Dispose ();
 817                         cmd = null;
 818                 }\r
 819
 820                 static void RollbackTest (OracleConnection connection)
 821                 {
 822                         OracleTransaction transaction = connection.BeginTransaction ();
 823
 824                         OracleCommand insert = connection.CreateCommand ();
 825                         insert.Transaction = transaction;
 826                         insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
 827
 828                         Console.WriteLine ("  Inserting record ...");
 829
 830                         insert.ExecuteNonQuery ();
 831
 832                         OracleCommand select = connection.CreateCommand ();
 833                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 834                         select.Transaction = transaction;
 835                         OracleDataReader reader = select.ExecuteReader ();
 836                         reader.Read ();
 837
 838                         Console.WriteLine ("  Row count SHOULD BE 1, VALUE IS {0}", reader.GetValue (0));
 839                         reader.Close ();
 840
 841                         Console.WriteLine ("  Rolling back transaction ...");
 842
 843                         transaction.Rollback ();
 844
 845                         select = connection.CreateCommand ();
 846                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 847
 848                         reader = select.ExecuteReader ();
 849                         reader.Read ();
 850                         Console.WriteLine ("  Row count SHOULD BE 0, VALUE IS {0}", reader.GetValue (0));
 851                         reader.Close ();
 852                 }
 853
 854                 static void CommitTest (OracleConnection connection)
 855                 {
 856                         OracleTransaction transaction = connection.BeginTransaction ();
 857
 858                         OracleCommand insert = connection.CreateCommand ();
 859                         insert.Transaction = transaction;
 860                         insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
 861
 862                         Console.WriteLine ("  Inserting record ...");
 863
 864                         insert.ExecuteNonQuery ();
 865
 866                         OracleCommand select = connection.CreateCommand ();
 867                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 868                         select.Transaction = transaction;
 869
 870                         Console.WriteLine ("  Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
 871
 872                         Console.WriteLine ("  Committing transaction ...");
 873
 874                         transaction.Commit ();
 875
 876                         select = connection.CreateCommand ();
 877                         select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
 878
 879                         Console.WriteLine ("Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
 880                         transaction = connection.BeginTransaction ();
 881                         OracleCommand delete = connection.CreateCommand ();
 882                         delete.Transaction = transaction;
 883                         delete.CommandText = "DELETE FROM EMP WHERE EMPNO = 8787";
 884                         delete.ExecuteNonQuery ();
 885                         transaction.Commit ();
 886                 }
 887
 888                 public static void ParameterTest2 (OracleConnection connection)
 889                 {
 890                         Console.WriteLine("  Setting NLS_DATE_FORMAT...");\r
 891 \r
 892                         OracleCommand cmd2 = connection.CreateCommand();\r
 893                         cmd2.CommandText = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";\r
 894                 \r
 895                         cmd2.ExecuteNonQuery ();\r
 896 \r
 897                         Console.WriteLine("  Drop table MONO_TEST_TABLE2...");
 898                         try {
 899                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE7";
 900                                 cmd2.ExecuteNonQuery ();
 901                         }
 902                         catch(OracleException oe1) {
 903                                 // ignore if table already exists
 904                         }
 905
 906                         Console.WriteLine("  Create table MONO_TEST_TABLE7...");
 907
 908                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE7(" +
 909                                 " COL1 VARCHAR2(8) NOT NULL, " +
 910                                 " COL2 VARCHAR2(32), " +
 911                                 " COL3 NUMBER(18,2), " +
 912                                 " COL4 NUMBER(18,2), " +
 913                                 " COL5 DATE NOT NULL, " +
 914                                 " COL6 DATE, " +
 915                                 " COL7 BLOB NOT NULL, " +
 916                                 " COL8 BLOB, " +
 917                                 " COL9 CLOB NOT NULL, " +
 918                                 " COL10 CLOB " +
 919                                 ")";
 920                         cmd2.ExecuteNonQuery ();
 921
 922                         Console.WriteLine("  COMMIT...");
 923                         cmd2.CommandText = "COMMIT";
 924                         cmd2.ExecuteNonQuery ();
 925
 926                         Console.WriteLine("  create insert command...");
 927 \r
 928                         OracleTransaction trans = connection.BeginTransaction ();\r
 929                         OracleCommand cmd = connection.CreateCommand ();\r
 930                         cmd.Transaction = trans;\r
 931
 932                         cmd.CommandText = "INSERT INTO MONO_TEST_TABLE7 " + \r
 933                                 "(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10) " + \r
 934                                 "VALUES(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10)";\r
 935                 \r
 936                         Console.WriteLine("  Add parameters...");\r
 937 \r
 938                         OracleParameter parm1 = cmd.Parameters.Add (":P1", OracleType.VarChar, 8);\r
 939                         OracleParameter parm2 = cmd.Parameters.Add (":P2", OracleType.VarChar, 32);\r
 940                 \r
 941                         OracleParameter parm3 = cmd.Parameters.Add (":P3", OracleType.Number);\r
 942                         OracleParameter parm4 = cmd.Parameters.Add (":P4", OracleType.Number);\r
 943                 \r
 944                         OracleParameter parm5 = cmd.Parameters.Add (":P5", OracleType.DateTime);\r
 945                         OracleParameter parm6 = cmd.Parameters.Add (":P6", OracleType.DateTime);\r
 946 \r
 947                         // FIXME: fix BLOBs and CLOBs in OracleParameter\r
 948 \r
 949                         OracleParameter parm7 = cmd.Parameters.Add (":P7", OracleType.Blob);\r
 950                         OracleParameter parm8 = cmd.Parameters.Add (":P8", OracleType.Blob);\r
 951 \r
 952                         OracleParameter parm9 = cmd.Parameters.Add (":P9", OracleType.Clob);\r
 953                         OracleParameter parm10 = cmd.Parameters.Add (":P10", OracleType.Clob);\r
 954 \r
 955                         // TODO: implement out, return, and ref parameters\r
 956 \r
 957                         string s = "Mono";\r
 958                         decimal d = 123456789012345.678M;\r
 959                         DateTime dt = DateTime.Now;\r
 960 \r
 961                         string clob = "Clob";\r
 962                         byte[] blob = new byte[] { 0x31, 0x32, 0x33, 0x34, 0x35 };\r
 963                 \r
 964                         Console.WriteLine("  Set Values...");\r
 965 \r
 966                         parm1.Value = s;\r
 967                         parm2.Value = DBNull.Value;\r
 968                 \r
 969                         parm3.Value = d;\r
 970                         parm4.Value = DBNull.Value;\r
 971                 \r
 972                         parm5.Value = dt;\r
 973                         parm6.Value = DBNull.Value;\r
 974                 \r
 975                         parm7.Value = blob;\r
 976                         parm8.Value = DBNull.Value;\r
 977 \r
 978                         parm9.Value = clob;\r
 979                         parm10.Value = DBNull.Value;\r
 980                 \r
 981                         Console.WriteLine("  ExecuteNonQuery...");\r
 982 \r
 983                         cmd.ExecuteNonQuery ();\r
 984                         trans.Commit();
 985                 }
 986
 987                 public static void ParameterTest (OracleConnection connection)
 988                 {
 989                         Console.WriteLine("  Setting NLS_DATE_FORMAT...");\r
 990 \r
 991                         OracleCommand cmd2 = connection.CreateCommand();\r
 992                         cmd2.CommandText = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";\r
 993                 \r
 994                         cmd2.ExecuteNonQuery ();\r
 995 \r
 996                         Console.WriteLine("  Drop table MONO_TEST_TABLE2...");
 997                         try {
 998                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE7";
 999                                 cmd2.ExecuteNonQuery ();
1000                         }
1001                         catch(OracleException oe1) {
1002                                 // ignore if table already exists
1003                         }
1004
1005                         Console.WriteLine("  Create table MONO_TEST_TABLE7...");
1006
1007                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE7(" +
1008                                 " COL1 VARCHAR2(8) NOT NULL, " +
1009                                 " COL2 VARCHAR2(32), " +
1010                                 " COL3 NUMBER(18,2) NOT NULL, " +
1011                                 " COL4 NUMBER(18,2), " +
1012                                 " COL5 DATE NOT NULL, " +
1013                                 " COL6 DATE, " +
1014                                 " COL7 BLOB NOT NULL, " +
1015                                 " COL8 BLOB, " +
1016                                 " COL9 CLOB NOT NULL, " +
1017                                 " COL10 CLOB " +
1018                                 ")";
1019                         cmd2.ExecuteNonQuery ();
1020
1021                         Console.WriteLine("  COMMIT...");
1022                         cmd2.CommandText = "COMMIT";
1023                         cmd2.ExecuteNonQuery ();
1024
1025                         Console.WriteLine("  create insert command...");
1026 \r
1027                         OracleTransaction trans = connection.BeginTransaction ();\r
1028                         OracleCommand cmd = connection.CreateCommand ();\r
1029                         cmd.Transaction = trans;\r
1030
1031                         cmd.CommandText = "INSERT INTO MONO_TEST_TABLE7 " + \r
1032                                 "(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10) " + \r
1033                                 "VALUES(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10)";\r
1034                 \r
1035                         Console.WriteLine("  Add parameters...");\r
1036 \r
1037                         OracleParameter parm1 = cmd.Parameters.Add (":P1", OracleType.VarChar, 8);\r
1038                         OracleParameter parm2 = cmd.Parameters.Add (":P2", OracleType.VarChar, 32);\r
1039                 \r
1040                         OracleParameter parm3 = cmd.Parameters.Add (":P3", OracleType.Number);\r
1041                         OracleParameter parm4 = cmd.Parameters.Add (":P4", OracleType.Number);\r
1042                 \r
1043                         OracleParameter parm5 = cmd.Parameters.Add (":P5", OracleType.DateTime);\r
1044                         OracleParameter parm6 = cmd.Parameters.Add (":P6", OracleType.DateTime);\r
1045 \r
1046                         // FIXME: fix BLOBs and CLOBs in OracleParameter\r
1047 \r
1048                         OracleParameter parm7 = cmd.Parameters.Add (":P7", OracleType.Blob);\r
1049                         OracleParameter parm8 = cmd.Parameters.Add (":P8", OracleType.Blob);\r
1050 \r
1051                         OracleParameter parm9 = cmd.Parameters.Add (":P9", OracleType.Clob);\r
1052                         OracleParameter parm10 = cmd.Parameters.Add (":P10", OracleType.Clob);\r
1053 \r
1054                         // TODO: implement out, return, and ref parameters\r
1055 \r
1056                         string s = "Mono";\r
1057                         decimal d = 123456789012345.678M;\r
1058                         DateTime dt = DateTime.Now;\r
1059 \r
1060                         string clob = "Clob";\r
1061                         byte[] blob = new byte[] { 0x31, 0x32, 0x33, 0x34, 0x35 };\r
1062                 \r
1063                         Console.WriteLine("  Set Values...");\r
1064 \r
1065                         parm1.Value = s;\r
1066                         parm2.Value = DBNull.Value;\r
1067                 \r
1068                         parm3.Value = d;\r
1069                         parm4.Value = DBNull.Value;\r
1070                 \r
1071                         parm5.Value = dt;\r
1072                         parm6.Value = DBNull.Value;\r
1073                 \r
1074                         parm7.Value = blob;\r
1075                         parm8.Value = DBNull.Value;\r
1076 \r
1077                         parm9.Value = clob;\r
1078                         parm10.Value = DBNull.Value;\r
1079                 \r
1080                         Console.WriteLine("  ExecuteNonQuery...");\r
1081 \r
1082                         cmd.ExecuteNonQuery ();\r
1083                         trans.Commit();
1084                 }
1085
1086                 public static void CLOBTest (OracleConnection connection)
1087                 {
1088                         Console.WriteLine ("  BEGIN TRANSACTION ...");
1089
1090                         OracleTransaction transaction = connection.BeginTransaction ();
1091
1092                         Console.WriteLine ("  Drop table CLOBTEST ...");
1093                         try {
1094                                 OracleCommand cmd2 = connection.CreateCommand ();
1095                                 cmd2.Transaction = transaction;
1096                                 cmd2.CommandText = "DROP TABLE CLOBTEST";
1097                                 cmd2.ExecuteNonQuery ();
1098                         }
1099                         catch (OracleException oe1) {
1100                                 // ignore if table already exists
1101                         }
1102
1103                         Console.WriteLine ("  CREATE TABLE ...");
1104
1105                         OracleCommand create = connection.CreateCommand ();
1106                         create.Transaction = transaction;
1107                         create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
1108                         create.ExecuteNonQuery ();
1109
1110                         Console.WriteLine ("  INSERT RECORD ...");
1111
1112                         OracleCommand insert = connection.CreateCommand ();
1113                         insert.Transaction = transaction;
1114                         insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
1115                         insert.ExecuteNonQuery ();
1116
1117                         OracleCommand select = connection.CreateCommand ();
1118                         select.Transaction = transaction;
1119                         select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
1120                         Console.WriteLine ("  SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
1121
1122                         OracleDataReader reader = select.ExecuteReader ();
1123                         if (!reader.Read ())
1124                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
1125
1126                         Console.WriteLine ("  TESTING OracleLob OBJECT ...");
1127                         OracleLob lob = reader.GetOracleLob (0);
1128                         Console.WriteLine ("  LENGTH: {0}", lob.Length);
1129                         Console.WriteLine ("  CHUNK SIZE: {0}", lob.ChunkSize);
1130
1131                         UnicodeEncoding encoding = new UnicodeEncoding ();
1132
1133                         byte[] value = new byte [lob.Length * 2];
1134
1135                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1136                         Console.WriteLine ("  UPDATING VALUE TO 'TEST ME!'");
1137                         value = encoding.GetBytes ("TEST ME!");
1138                         lob.Write (value, 0, value.Length);
1139
1140                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1141                         Console.WriteLine ("  RE-READ VALUE...");
1142                         lob.Seek (1, SeekOrigin.Begin);
1143
1144                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1145                         value = new byte [lob.Length * 2];
1146                         lob.Read (value, 0, value.Length);
1147                         Console.WriteLine ("  VALUE: {0}", encoding.GetString (value));
1148                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1149
1150                         Console.WriteLine ("  CLOSE OracleLob...");
1151                         lob.Close ();
1152
1153                         Console.WriteLine ("  CLOSING READER...");
1154
1155                         reader.Close ();
1156                         transaction.Commit ();
1157                 }
1158
1159                 public static void BLOBTest (OracleConnection connection)
1160                 {
1161                         Console.WriteLine ("  BEGIN TRANSACTION ...");
1162
1163                         OracleTransaction transaction = connection.BeginTransaction ();
1164
1165                         Console.WriteLine ("  Drop table BLOBTEST ...");
1166                         try {
1167                                 OracleCommand cmd2 = connection.CreateCommand ();
1168                                 cmd2.Transaction = transaction;
1169                                 cmd2.CommandText = "DROP TABLE BLOBTEST";
1170                                 cmd2.ExecuteNonQuery ();
1171                         }
1172                         catch (OracleException oe1) {
1173                                 // ignore if table already exists
1174                         }
1175
1176                         Console.WriteLine ("  CREATE TABLE ...");
1177
1178                         OracleCommand create = connection.CreateCommand ();
1179                         create.Transaction = transaction;
1180                         create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
1181                         create.ExecuteNonQuery ();
1182
1183                         Console.WriteLine ("  INSERT RECORD ...");
1184
1185                         OracleCommand insert = connection.CreateCommand ();
1186                         insert.Transaction = transaction;
1187                         insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
1188                         insert.ExecuteNonQuery ();
1189
1190                         OracleCommand select = connection.CreateCommand ();
1191                         select.Transaction = transaction;
1192                         select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
1193                         Console.WriteLine ("  SELECTING A BLOB (Binary) VALUE FROM BLOBTEST");
1194
1195                         OracleDataReader reader = select.ExecuteReader ();
1196                         if (!reader.Read ())
1197                                 Console.WriteLine ("ERROR: RECORD NOT FOUND");
1198
1199                         Console.WriteLine ("  TESTING OracleLob OBJECT ...");
1200                         OracleLob lob = reader.GetOracleLob (0);
1201
1202                         byte[] value = null;
1203                         string bvalue = "";
1204
1205                         Console.WriteLine ("  UPDATING VALUE");
1206
1207                         byte[] bytes = new byte[6];
1208                         bytes[0] = 0x31;
1209                         bytes[1] = 0x32;
1210                         bytes[2] = 0x33;
1211                         bytes[3] = 0x34;
1212                         bytes[4] = 0x35;
1213                         bytes[5] = 0x36;
1214
1215                         lob.Write (bytes, 0, bytes.Length);
1216
1217                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1218                         Console.WriteLine ("  RE-READ VALUE...");
1219                         lob.Seek (1, SeekOrigin.Begin);
1220
1221                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1222                         value = new byte [lob.Length];
1223                         lob.Read (value, 0, value.Length);
1224
1225                         bvalue = "";
1226                         if (value.GetType ().ToString ().Equals ("System.Byte[]"))
1227                                 bvalue = GetHexString (value);
1228                         Console.WriteLine ("  Bytes: " + bvalue);
1229
1230                         Console.WriteLine ("  CURRENT POSITION: {0}", lob.Position);
1231
1232                         Console.WriteLine ("  CLOSE OracleLob...");
1233                         lob.Close ();
1234
1235                         Console.WriteLine ("  CLOSING READER...");
1236
1237                         reader.Close ();
1238                         transaction.Commit ();
1239                 }
1240
1241                 static void Wait(string msg)
1242                 {
1243                         Console.WriteLine(msg);
1244                         if (msg.Equals(""))
1245                                 Console.WriteLine("Waiting...  Press Enter to continue...");
1246                         Console.ReadLine();
1247                 }
1248
1249                 // use this function to read a byte array into a string
1250                 // for easy display of binary data, such as, a BLOB value
1251                 public static string GetHexString (byte[] bytes)
1252                 {
1253                         string bvalue = "";
1254
1255                         StringBuilder sb2 = new StringBuilder();
1256                         for (int z = 0; z < bytes.Length; z++) {
1257                                 byte byt = bytes[z];
1258                                 sb2.Append (byt.ToString("x"));
1259                         }
1260                         if (sb2.Length > 0)
1261                                 bvalue = "0x" + sb2.ToString ();
1262
1263                         return bvalue;
1264                 }
1265
1266                 static void StoredProcedureTest1 (OracleConnection con)
1267                 {
1268                         // test stored procedure with no parameters
1269
1270
1271                         OracleCommand cmd2 = con.CreateCommand ();
1272
1273                         Console.WriteLine("  Drop table MONO_TEST_TABLE1...");
1274                         try {
1275                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE1";
1276                                 cmd2.ExecuteNonQuery ();
1277                         }
1278                         catch(OracleException oe1) {
1279                                 // ignore if table did not exist
1280                         }
1281
1282                         Console.WriteLine("  Drop procedure SP_TEST1...");
1283                         try {
1284                                 cmd2.CommandText = "DROP PROCEDURE SP_TEST1";
1285                                 cmd2.ExecuteNonQuery ();
1286                         }
1287                         catch(OracleException oe1) {
1288                                 // ignore if procedure did not exist
1289                         }
1290
1291                         Console.WriteLine("  Create table MONO_TEST_TABLE1...");
1292                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE1 (" +
1293                                         " COL1 VARCHAR2(8), "+
1294                                         " COL2 VARCHAR2(32))";
1295                         cmd2.ExecuteNonQuery ();
1296
1297                         Console.WriteLine("  Create stored procedure SP_TEST1...");
1298                         cmd2.CommandText = "CREATE PROCEDURE SP_TEST1 " +
1299                                 " IS " +
1300                                 " BEGIN " +
1301                                 "       INSERT INTO MONO_TEST_TABLE1 (COL1,COL2) VALUES ('aaa','bbbb');" +
1302                                 "       COMMIT;" +
1303                                 " END;";
1304                         cmd2.ExecuteNonQuery ();
1305
1306                         Console.WriteLine("COMMIT...");
1307                         cmd2.CommandText = "COMMIT";
1308                         cmd2.ExecuteNonQuery ();
1309
1310                         Console.WriteLine("  Call stored procedure sp_test1...");
1311                         OracleCommand cmd3 = con.CreateCommand ();\r
1312                         cmd3.CommandType = CommandType.StoredProcedure;\r
1313                         cmd3.CommandText = "sp_test1";\r
1314                         cmd3.ExecuteNonQuery ();
1315                 }
1316
1317                 static void StoredProcedureTest2 (OracleConnection con)
1318                 {
1319                         // test stored procedure with 2 parameters
1320
1321                         Console.WriteLine("  Drop table MONO_TEST_TABLE2...");
1322                         OracleCommand cmd2 = con.CreateCommand ();
1323
1324                         try {
1325                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE2";
1326                                 cmd2.ExecuteNonQuery ();
1327                         }
1328                         catch(OracleException oe1) {
1329                                 // ignore if table already exists
1330                         }
1331
1332                         Console.WriteLine("  Drop procedure SP_TEST2...");
1333                         try {
1334                                 cmd2.CommandText = "DROP PROCEDURE SP_TEST2";
1335                                 cmd2.ExecuteNonQuery ();
1336                         }
1337                         catch(OracleException oe1) {
1338                                 // ignore if table already exists
1339                         }
1340
1341                         Console.WriteLine("  Create table MONO_TEST_TABLE2...");
1342
1343                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE2 (" +
1344                                 " COL1 VARCHAR2(8), "+
1345                                 " COL2 VARCHAR2(32))";
1346                         cmd2.ExecuteNonQuery ();
1347
1348                         Console.WriteLine("  Create stored procedure SP_TEST2...");
1349                         cmd2.CommandText = "CREATE PROCEDURE SP_TEST2(parm1 VARCHAR2,parm2 VARCHAR2) " +
1350                                 " IS " +
1351                                 " BEGIN " +
1352                                 "       INSERT INTO MONO_TEST_TABLE2 (COL1,COL2) VALUES (parm1,parm2);" +
1353                                 "       COMMIT;" +
1354                                 " END;";
1355                         cmd2.ExecuteNonQuery ();
1356
1357                         Console.WriteLine("  COMMIT...");
1358                         cmd2.CommandText = "COMMIT";
1359                         cmd2.ExecuteNonQuery ();
1360
1361                         Console.WriteLine("  Call stored procedure SP_TEST2 with two parameters...");
1362                         OracleCommand cmd3 = con.CreateCommand ();\r
1363                         cmd3.CommandType = CommandType.StoredProcedure;\r
1364                         cmd3.CommandText = "sp_test2";\r
1365 \r
1366                         OracleParameter myParameter1 = new OracleParameter("parm1", OracleType.VarChar);\r
1367                         myParameter1.Value = "yyy13";\r
1368                         myParameter1.Size = 8;\r
1369                         myParameter1.Direction = ParameterDirection.Input;\r
1370                 \r
1371                         OracleParameter myParameter2 = new OracleParameter("parm2", OracleType.VarChar);\r
1372                         myParameter2.Value = "iii13";\r
1373                         myParameter2.Size = 32;\r
1374                         myParameter2.Direction = ParameterDirection.Input;\r
1375 \r
1376                         cmd3.Parameters.Add (myParameter1);\r
1377                         cmd3.Parameters.Add (myParameter2);\r
1378
1379                         cmd3.ExecuteNonQuery ();
1380                 }
1381
1382                 static void OutParmTest1 (OracleConnection con)
1383                 {
1384                         // test stored procedure with 2 parameters
1385                         // 1. input varchar2
1386                         // 2. output varchar
1387
1388                         OracleCommand cmd2 = null;
1389                         Console.WriteLine("  Drop procedure SP_OUTPUTPARMTEST1...");
1390                         try {
1391                                 cmd2 = con.CreateCommand ();
1392                                 cmd2.CommandText = "DROP PROCEDURE SP_OUTPUTPARMTEST1";
1393                                 cmd2.ExecuteNonQuery ();
1394                         }
1395                         catch(OracleException oe1) {
1396                                 // ignore if table already exists
1397                         }
1398
1399                         Console.WriteLine("  Create stored procedure SP_OUTPUTPARMTEST1...");
1400                         // stored procedure concatenates strings
1401                         cmd2.CommandText =
1402                                 "CREATE OR REPLACE PROCEDURE SP_TESTOUTPARM1(parm1 IN VARCHAR2,parm2 OUT VARCHAR2) " +
1403                                 "IS " +
1404                                 "BEGIN " +
1405                                 "       parm2 := 'one' || parm1 || 'three';" +
1406                                 "END;";
1407
1408                         cmd2.ExecuteNonQuery ();
1409
1410                         Console.WriteLine("  COMMIT...");
1411                         cmd2.CommandText = "COMMIT";
1412                         cmd2.ExecuteNonQuery ();
1413
1414                         Console.WriteLine("  Call stored procedure SP_TESTOUTPARM1 with two parameters...");
1415                         OracleCommand cmd3 = con.CreateCommand ();\r
1416                         cmd3.CommandType = CommandType.Text;\r
1417                         cmd3.CommandText =
1418                                 "BEGIN " +
1419                                 "       SP_TESTOUTPARM1(:p1, :p2);" +\r
1420                                 "END;";\r
1421                         OracleParameter myParameter1 = new OracleParameter("p1", OracleType.VarChar);
1422                         myParameter1.Value = "two";\r
1423                         myParameter1.Size = 4;\r
1424                         myParameter1.Direction = ParameterDirection.Input;\r
1425                 \r
1426                         OracleParameter myParameter2 = new OracleParameter("p2", OracleType.VarChar);\r
1427                         myParameter2.Size = 12;\r
1428                         myParameter2.Direction = ParameterDirection.Output;\r
1429 \r
1430                         cmd3.Parameters.Add (myParameter1);\r
1431                         cmd3.Parameters.Add (myParameter2);\r
1432
1433                         cmd3.ExecuteNonQuery ();
1434                         string outValue = (string) myParameter2.Value;
1435                         Console.WriteLine ("    Out Value should be: onetwothree");
1436                         Console.WriteLine ("    Out Value: " + outValue);
1437                 }
1438
1439                 static void OutParmTest2 (OracleConnection con)
1440                 {
1441                         // test stored procedure with 2 parameters
1442                         // 1. input number(18,2)
1443                         // 2. output number(18,2)
1444
1445                         OracleCommand cmd2 = null;
1446                         Console.WriteLine("  Drop procedure SP_OUTPUTPARMTEST2...");
1447                         try {
1448                                 cmd2 = con.CreateCommand ();
1449                                 cmd2.CommandText = "DROP PROCEDURE SP_OUTPUTPARMTEST2";
1450                                 cmd2.ExecuteNonQuery ();
1451                         }
1452                         catch(OracleException oe1) {
1453                                 // ignore if table already exists
1454                         }
1455
1456                         Console.WriteLine("  Create stored procedure SP_OUTPUTPARMTEST2...");
1457
1458                         // stored procedure addes two numbers
1459                         cmd2.CommandText =
1460                                 "CREATE OR REPLACE PROCEDURE SP_TESTOUTPARM2(parm1 IN NUMBER,parm2 OUT NUMBER) " +
1461                                 "IS " +
1462                                 "BEGIN " +
1463                                 "       parm2 := parm1 + 3; " +
1464                                 "END;";
1465
1466                         cmd2.ExecuteNonQuery ();
1467
1468                         Console.WriteLine("  COMMIT...");
1469                         cmd2.CommandText = "COMMIT";
1470                         cmd2.ExecuteNonQuery ();
1471
1472                         Console.WriteLine("  Call stored procedure SP_TESTOUTPARM2 with two parameters...");
1473                         OracleCommand cmd3 = con.CreateCommand ();\r
1474                         cmd3.CommandType = CommandType.Text;\r
1475                         cmd3.CommandText =
1476                                 "BEGIN " +
1477                                 "       SP_TESTOUTPARM2(:p1, :p2);" +\r
1478                                 "END;";\r
1479                         OracleParameter myParameter1 = new OracleParameter("p1", OracleType.Number);
1480                         myParameter1.Value = 2;\r
1481                         myParameter1.Direction = ParameterDirection.Input;\r
1482                 \r
1483                         OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Number);\r
1484                         myParameter2.Direction = ParameterDirection.Output;\r
1485 \r
1486                         cmd3.Parameters.Add (myParameter1);\r
1487                         cmd3.Parameters.Add (myParameter2);\r
1488
1489                         cmd3.ExecuteNonQuery ();
1490                         decimal outValue = (decimal) myParameter2.Value;
1491                         Console.WriteLine ("    Out Value should be: 5");
1492                         Console.WriteLine ("    Out Value: {0}", outValue);
1493                 }
1494
1495                 static void OutParmTest3 (OracleConnection con)
1496                 {
1497                         // test stored procedure with 2 parameters
1498                         // 1. input date
1499                         // 2. output date
1500
1501                         OracleCommand cmd2 = null;
1502                         Console.WriteLine("  Drop procedure SP_OUTPUTPARMTEST3...");
1503                         try {
1504                                 cmd2 = con.CreateCommand ();
1505                                 cmd2.CommandText = "DROP PROCEDURE SP_OUTPUTPARMTEST3";
1506                                 cmd2.ExecuteNonQuery ();
1507                         }
1508                         catch(OracleException oe1) {
1509                                 // ignore if table already exists
1510                         }
1511
1512                         Console.WriteLine("  Create stored procedure SP_OUTPUTPARMTEST3...");
1513
1514                         // stored procedure adds 3 days to date
1515                         cmd2.CommandText =
1516                                 "CREATE OR REPLACE PROCEDURE SP_TESTOUTPARM3(parm1 IN DATE,parm2 OUT DATE) " +
1517                                 "IS " +
1518                                 "BEGIN " +
1519                                 "       parm2 := parm1 + 3; " +
1520                                 "END;";
1521
1522                         cmd2.ExecuteNonQuery ();
1523
1524                         Console.WriteLine("  COMMIT...");
1525                         cmd2.CommandText = "COMMIT";
1526                         cmd2.ExecuteNonQuery ();
1527
1528                         Console.WriteLine("  Call stored procedure SP_TESTOUTPARM3 with two parameters...");
1529                         OracleCommand cmd3 = con.CreateCommand ();\r
1530                         cmd3.CommandType = CommandType.Text;\r
1531                         cmd3.CommandText =
1532                                 "BEGIN " +
1533                                 "       SP_TESTOUTPARM3(:p1, :p2);" +\r
1534                                 "END;";\r
1535                         OracleParameter myParameter1 = new OracleParameter("p1", OracleType.DateTime);
1536                         myParameter1.Value = new DateTime(2004,12,15);\r
1537                         myParameter1.Direction = ParameterDirection.Input;\r
1538                 \r
1539                         OracleParameter myParameter2 = new OracleParameter("p2", OracleType.DateTime);\r
1540                         myParameter2.Direction = ParameterDirection.Output;\r
1541 \r
1542                         cmd3.Parameters.Add (myParameter1);\r
1543                         cmd3.Parameters.Add (myParameter2);\r
1544
1545                         cmd3.ExecuteNonQuery ();
1546                         DateTime outValue = (DateTime) myParameter2.Value;
1547                         Console.WriteLine ("    Out Value should be: 2004-12-18");
1548                         Console.WriteLine ("    Out Value: {0}", outValue.ToString ("yyyy-mm-dd"));
1549                 }
1550
1551                 static void ShowConnectionProperties (OracleConnection con)
1552                 {
1553                         try {
1554                                 Console.WriteLine ("ServerVersion: " + con.ServerVersion);
1555                         } catch (System.InvalidOperationException ioe) {\r
1556                                 Console.WriteLine ("InvalidOperationException caught.");\r
1557                                 Console.WriteLine ("Message: " + ioe.Message);\r
1558                         }
1559
1560                         Console.WriteLine ("DataSource: " + con.DataSource);
1561                 }
1562
1563                 static void NullAggregateTest (OracleConnection con)
1564                 {
1565                         Console.WriteLine("  Drop table MONO_TEST_TABLE3...");
1566                         OracleCommand cmd2 = con.CreateCommand ();
1567
1568                         try {
1569                                 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE3";
1570                                 cmd2.ExecuteNonQuery ();
1571                         }
1572                         catch(OracleException oe1) {
1573                                 // ignore if table already exists
1574                         }
1575
1576                         Console.WriteLine("  Create table MONO_TEST_TABLE3...");
1577
1578                         cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE3 (" +
1579                                 " COL1 VARCHAR2(8), "+
1580                                 " COL2 VARCHAR2(32))";
1581
1582                         cmd2.ExecuteNonQuery ();
1583
1584                         Console.WriteLine("  Insert some rows into table MONO_TEST_TABLE3...");
1585                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','one')";
1586                         cmd2.ExecuteNonQuery ();
1587
1588                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','uno')";
1589                         cmd2.ExecuteNonQuery ();
1590
1591                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','three')";
1592                         cmd2.ExecuteNonQuery ();
1593
1594                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3', null)";
1595                         cmd2.ExecuteNonQuery ();
1596
1597                         cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','few')";
1598                         cmd2.ExecuteNonQuery ();
1599
1600                         Console.WriteLine("  ExecuteScalar...");
1601                         cmd2.CommandText = "SELECT COL1, COUNT(COL2) AS MAX_COL1 FROM MONO_TEST_TABLE3 GROUP BY COL1";
1602                         OracleDataReader reader = cmd2.ExecuteReader ();
1603                         Console.WriteLine (" Read...");
1604                         while (reader.Read ()) {
1605
1606                                 object obj0 = reader.GetValue (0);
1607                                 Console.WriteLine("Value 0: " + obj0.ToString ());
1608                                 object obj1 = reader.GetValue (1);
1609                                 Console.WriteLine("Value 1: " + obj1.ToString ());
1610
1611                                 Console.WriteLine (" Read...");
1612                         }
1613
1614                         Console.WriteLine (" No more records.");
1615                 }
1616
1617                 static void OnInfoMessage (object sender, OracleInfoMessageEventArgs e) \r
1618                 {\r
1619                         Console.WriteLine("InfoMessage Message: " + e.Message.ToString());\r
1620                         Console.WriteLine("InfoMessage Code: " + e.Code.ToString());\r
1621                         Console.WriteLine("InfoMessage Source: " + e.Source.ToString());\r
1622                 }
1623
1624                 static void OnStateChange (object sender, StateChangeEventArgs e) \r
1625                 {\r
1626                         Console.WriteLine("StateChange CurrentSate:" + e.CurrentState.ToString ());\r
1627                         Console.WriteLine("StateChange OriginalState:" + e.OriginalState.ToString ());\r
1628                 }
1629
1630                 public static void ConnectionPoolingTest1 () {\r
1631                         Console.WriteLine("Start Connection Pooling Test 1...");\r
1632                         OracleConnection[] connections = null;\r
1633                         int maxCon = MAX_CONNECTIONS + 1; // add 1 more over the max connections to cause it to wait for the next available connection\r
1634                         int i = 0;\r
1635 \r
1636                         try {\r
1637                                 connections = new OracleConnection[maxCon];                     \r
1638                 \r
1639                                 for (i = 0; i < maxCon; i++) {\r
1640                                         Console.WriteLine("   Open connection: {0}", i);\r
1641                                         connections[i] = new OracleConnection(conStr);\r
1642                                         connections[i].Open ();\r
1643                                 }\r
1644                         } catch (InvalidOperationException e) {\r
1645                                 Console.WriteLine("Expected exception InvalidOperationException caught.");\r
1646                                 Console.WriteLine(e);\r
1647                         }\r
1648 \r
1649                         for (i = 0; i < maxCon; i++) {\r
1650                                 if (connections[i] != null) {\r
1651                                         Console.WriteLine("   Close connection: {0}", i);\r
1652                                         if (connections[i].State == ConnectionState.Open)\r
1653                                                 connections[i].Close ();\r
1654                                         connections[i].Dispose ();\r
1655                                         connections[i] = null;\r
1656                                 }\r
1657                         }\r
1658 \r
1659                         connections = null;\r
1660 \r
1661                         Console.WriteLine("Done Connection Pooling Test 1.");\r
1662                 }\r
1663 \r
1664                 public static void ConnectionPoolingTest2 () {\r
1665                         Console.WriteLine("Start Connection Pooling Test 2...");\r
1666                         OracleConnection[] connections = null;\r
1667                         int maxCon = MAX_CONNECTIONS;\r
1668                         int i = 0;\r
1669 \r
1670                         connections = new OracleConnection[maxCon];                     \r
1671                 \r
1672                         for (i = 0; i < maxCon; i++) {\r
1673                                 Console.WriteLine("   Open connection: {0}", i);\r
1674                                 connections[i] = new OracleConnection(conStr);\r
1675                                 connections[i].Open ();\r
1676                         }\r
1677                 \r
1678                         Console.WriteLine("Start another thread...");\r
1679                         t = new Thread(new ThreadStart(AnotherThreadProc));\r
1680                         t.Start ();\r
1681 \r
1682                         Console.WriteLine("Sleep...");\r
1683                         Thread.Sleep(100);\r
1684 \r
1685                         Console.WriteLine("Closing...");\r
1686                         for (i = 0; i < maxCon; i++) {\r
1687                                 if (connections[i] != null) {\r
1688                                         Console.WriteLine("   Close connection: {0}", i);\r
1689                                         if (connections[i].State == ConnectionState.Open)\r
1690                                                 connections[i].Close ();\r
1691                                         connections[i].Dispose ();\r
1692                                         connections[i] = null;\r
1693                                 }\r
1694                         }\r
1695 \r
1696                         connections = null;\r
1697                 }\r
1698 \r
1699                 private static void AnotherThreadProc () {\r
1700                         Console.WriteLine("Open connection via another thread...");\r
1701                         OracleConnection[] connections = null;\r
1702                         int maxCon = MAX_CONNECTIONS; \r
1703                         int i = 0;\r
1704 \r
1705                         connections = new OracleConnection[maxCon];                     \r
1706                 \r
1707                         for (i = 0; i < maxCon; i++) {\r
1708                                 Console.WriteLine("   Open connection: {0}", i);\r
1709                                 connections[i] = new OracleConnection(conStr);\r
1710                                 connections[i].Open ();\r
1711                         }\r
1712 \r
1713                         Console.WriteLine("Done Connection Pooling Test 2.");\r
1714                         System.Environment.Exit (0);\r
1715                 }
1716
1717                 [STAThread]
1718                 static void Main(string[] args)
1719                 {
1720                         if(args.Length != 3) {
1721                                 Console.WriteLine("Usage: mono TestOracleClient database userid password");
1722                                 return;
1723                         }
1724
1725                         string connectionString = String.Format(
1726                                 "Data Source={0};" +
1727                                 "User ID={1};" +
1728                                 "Password={2}",
1729                                 args[0], args[1], args[2]);
1730
1731                         conStr = connectionString;
1732
1733                         OracleConnection con1 = new OracleConnection();
1734
1735                         ShowConnectionProperties (con1);
1736
1737                         con1.ConnectionString = connectionString;
1738
1739                         con1.InfoMessage += new OracleInfoMessageEventHandler (OnInfoMessage);
1740                         con1.StateChange += new StateChangeEventHandler (OnStateChange);
1741                         Console.WriteLine("Opening...");
1742                         con1.Open ();
1743                         Console.WriteLine("Opened.");
1744
1745                         ShowConnectionProperties (con1);
1746
1747                         Console.WriteLine ("Mono Oracle Test BEGIN ...");
1748                         MonoTest (con1);
1749                         Console.WriteLine ("Mono Oracle Test END ...");
1750
1751                         Wait ("");
1752
1753                         Console.WriteLine ("LOB Test BEGIN...");
1754                         CLOBTest (con1);
1755                         BLOBTest (con1);
1756                         Console.WriteLine ("LOB Test END.");
1757                         Wait ("");
1758
1759                         Console.WriteLine ("Read Simple Test BEGIN - scott.emp...");
1760                         ReadSimpleTest(con1, "SELECT e.*, e.rowid FROM scott.emp e");
1761                         Console.WriteLine ("Read Simple Test END - scott.emp");
1762
1763                         Wait ("");
1764
1765                         Console.WriteLine ("DataAdapter Test BEGIN...");
1766                         DataAdapterTest(con1);
1767                         Console.WriteLine ("DataAdapter Test END.");
1768
1769                         Wait ("");
1770
1771                         Console.WriteLine ("DataAdapter Test 2 BEGIN...");
1772                         DataAdapterTest2(con1);
1773                         Console.WriteLine ("DataAdapter Test 2 END.");
1774
1775                         Wait ("");
1776
1777                         Console.WriteLine ("Rollback Test BEGIN...");
1778                         RollbackTest(con1);
1779                         Console.WriteLine ("Rollback Test END.");
1780
1781                         Wait ("");
1782
1783                         Console.WriteLine ("Commit Test BEGIN...");
1784                         CommitTest(con1);
1785                         Console.WriteLine ("Commit Test END.");
1786
1787                         Wait ("");
1788
1789                         Console.WriteLine ("Parameter Test BEGIN...");
1790                         ParameterTest(con1);
1791                         ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE7");
1792                         Console.WriteLine ("Parameter Test END.");
1793
1794                         Wait ("");
1795
1796                         Console.WriteLine ("Stored Proc Test 1 BEGIN...");
1797                         StoredProcedureTest1 (con1);
1798                         ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE1");
1799                         Console.WriteLine ("Stored Proc Test 1 END...");
1800
1801                         Wait ("");
1802
1803                         Console.WriteLine ("Stored Proc Test 2 BEGIN...");
1804                         StoredProcedureTest2 (con1);
1805                         ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE2");
1806                         Console.WriteLine ("Stored Proc Test 2 END...");
1807
1808                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 1 BEGIN...");
1809                         OutParmTest1 (con1);
1810                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 1 END...");
1811
1812                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 2 BEGIN...");
1813                         OutParmTest2 (con1);
1814                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 2 END...");
1815
1816                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 3 BEGIN...");
1817                         OutParmTest3 (con1);
1818                         Console.WriteLine ("Out Parameter and PL/SQL Block Test 3 END...");
1819
1820                         Wait ("");
1821
1822                         Console.WriteLine ("Test a Non Query using Execute Reader BEGIN...");
1823                         TestNonQueryUsingExecuteReader (con1);
1824                         Console.WriteLine ("Test a Non Query using Execute Reader END...");
1825
1826                         Wait ("");
1827
1828                         Console.WriteLine ("Null Aggregate Warning BEGIN test...");
1829                         NullAggregateTest (con1);
1830                         Console.WriteLine ("Null Aggregate Warning END test...");
1831
1832                         Console.WriteLine("Closing...");
1833                         con1.Close ();
1834                         Console.WriteLine("Closed.");
1835
1836                         //conStr = conStr + ";pooling=true;min pool size=4;max pool size=" + MAX_CONNECTIONS.ToString ();\r
1837                         //ConnectionPoolingTest1 ();\r
1838                         //ConnectionPoolingTest2 ();\r
1839
1840                         Console.WriteLine("Done.");
1841                 }
1842         }
1843 }
1844