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

   1 // testclob.cs - tests loading a text file into an oracle clob and vice-versa
   2 using System;
   3 using System.Data;
   4 using System.Data.OracleClient;
   5 using System.Text;
   6 using System.IO;
   7
   8 class TestClob
   9 {
  10         static string infilename = @"cs-parser.cs";
  11         static string outfilename = @"cs-parser2.cs";
  12         static string connectionString = "data source=palis;user id=scott;password=tiger"
  13
  14         public static void Main (string[] args)
  15         {
  16                 OracleConnection con = new OracleConnection();
  17                 con.ConnectionString = connectionString;
  18                 con.Open();
  19
  20                 CLOBTest (con);
  21                 ReadClob (con);
  22
  23                 con.Close();
  24                 con = null;
  25         }
  26
  27         // read the CLOB into file "cs-parser2.cs"
  28         public static void ReadClob (OracleConnection connection)
  29         {
  30                 OracleCommand rcmd = connection.CreateCommand ();
  31                 rcmd.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST";
  32                 OracleDataReader reader2 = rcmd.ExecuteReader ();
  33                 if (!reader2.Read ())
  34                         Console.WriteLine ("ERROR: RECORD NOT FOUND");
  35
  36                 Console.WriteLine ("  TESTING OracleLob OBJECT 2...");
  37                 OracleLob lob2 = reader2.GetOracleLob (0);
  38                 Console.WriteLine ("  LENGTH: {0}", lob2.Length);
  39                 Console.WriteLine ("  CHUNK SIZE: {0}", lob2.ChunkSize);
  40
  41                 string lobvalue = (string) lob2.Value;
  42
  43                 using (StreamWriter sw = new StreamWriter(outfilename)) {
  44                         sw.Write(lobvalue);
  45                 }
  46
  47                 lob2.Close ();
  48                 reader2.Close ();
  49
  50         }
  51
  52         public static void CLOBTest (OracleConnection connection)
  53         {
  54                 Console.WriteLine ("  BEGIN TRANSACTION ...");
  55
  56                 OracleTransaction transaction = connection.BeginTransaction ();
  57
  58                 Console.WriteLine ("  Drop table CLOBTEST ...");
  59                 try {
  60                         OracleCommand cmd2 = connection.CreateCommand ();
  61                         cmd2.Transaction = transaction;
  62                         cmd2.CommandText = "DROP TABLE CLOBTEST";
  63                         cmd2.ExecuteNonQuery ();
  64                 }
  65                 catch (OracleException oe1) {
  66                         // ignore if table already exists
  67                 }
  68
  69                 Console.WriteLine ("  CREATE TABLE ...");
  70
  71                 OracleCommand create = connection.CreateCommand ();
  72                 create.Transaction = transaction;
  73                 create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
  74                 create.ExecuteNonQuery ();
  75
  76                 Console.WriteLine ("  INSERT RECORD ...");
  77
  78                 OracleCommand insert = connection.CreateCommand ();
  79                 insert.Transaction = transaction;
  80                 insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
  81                 insert.ExecuteNonQuery ();
  82
  83                 OracleCommand select = connection.CreateCommand ();
  84                 select.Transaction = transaction;
  85                 select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
  86                 Console.WriteLine ("  SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
  87
  88                 OracleDataReader reader = select.ExecuteReader ();
  89                 if (!reader.Read ())
  90                         Console.WriteLine ("ERROR: RECORD NOT FOUND");
  91
  92                 Console.WriteLine ("  TESTING OracleLob OBJECT ...");
  93                 OracleLob lob = reader.GetOracleLob (0);
  94                 Console.WriteLine ("  LENGTH: {0}", lob.Length);
  95                 Console.WriteLine ("  CHUNK SIZE: {0}", lob.ChunkSize);
  96
  97                 UnicodeEncoding encoding = new UnicodeEncoding ();
  98
  99                 try {
 100                         // read file "cs-parser.cs" into the oracle clob
 101                         using (StreamReader sr = new StreamReader(infilename)) {
 102                                 string sbuff = sr.ReadToEnd ();
 103                                 byte[] evalue = encoding.GetBytes (sbuff);
 104                                 lob.Write (evalue, 0, evalue.Length);
 105                         }
 106                 }
 107                 catch (Exception e) {
 108                         Console.WriteLine("The file could not be read:");
 109                         Console.WriteLine(e.Message);
 110                 }
 111                 lob.Close ();
 112
 113                 Console.WriteLine ("  CLOSING READER...");
 114
 115                 reader.Close ();
 116                 transaction.Commit ();
 117         }
 118 }