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

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