[Mono-devel-list] System.Data.OracleClient and CLOB's

[Daniel Morgan]

With the OracleLob, use the Value property.

Value retrieves the data for you and returns the result.
If a CLOB, it returns a String.  If a BLOB, it returns a byte[].

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

See functions CLOBTest and BLOBTest.  Note, these tests do not use the Value
property; they use Read.

In CLOBTest that I pasted below, you use UnicodeEncoding to encode the byte
array to a String.  Also, notice the byte array is twice the size so the
byte array can handle unicode characters.  The OracleLob has its own Read
and Write functions.  So, there is no need for a StreamReader.

I pasted CLOBTest below.

		public static void CLOBTest (OracleConnection connection)
		{		
			Console.WriteLine ("  BEGIN TRANSACTION ...");

			OracleTransaction transaction =
connection.BeginTransaction ();

			Console.WriteLine ("  Drop table CLOBTEST ...");
			try {
				OracleCommand cmd2 =
connection.CreateCommand ();
				cmd2.Transaction = transaction;
				cmd2.CommandText = "DROP TABLE CLOBTEST";
				cmd2.ExecuteNonQuery ();
			}
			catch (OracleException oe1) {
				// ignore if table already exists
			}

			Console.WriteLine ("  CREATE TABLE ...");

			OracleCommand create = connection.CreateCommand ();
			create.Transaction = transaction;
			create.CommandText = "CREATE TABLE CLOBTEST
(CLOB_COLUMN CLOB)";
			create.ExecuteNonQuery ();

			Console.WriteLine ("  INSERT RECORD ...");

			OracleCommand insert = connection.CreateCommand ();
			insert.Transaction = transaction;
			insert.CommandText = "INSERT INTO CLOBTEST VALUES
(EMPTY_CLOB())";
			insert.ExecuteNonQuery ();

			OracleCommand select = connection.CreateCommand ();
			select.Transaction = transaction;
			select.CommandText = "SELECT CLOB_COLUMN FROM
CLOBTEST FOR UPDATE";
			Console.WriteLine ("  SELECTING A CLOB (CHARACTER)
VALUE FROM CLOBTEST");

			OracleDataReader reader = select.ExecuteReader ();
			if (!reader.Read ())
				Console.WriteLine ("ERROR: RECORD NOT
FOUND");

			Console.WriteLine ("  TESTING OracleLob OBJECT
...");
			OracleLob lob = reader.GetOracleLob (0);
			Console.WriteLine ("  LENGTH: {0}", lob.Length);
			Console.WriteLine ("  CHUNK SIZE: {0}",
lob.ChunkSize);

			UnicodeEncoding encoding = new UnicodeEncoding ();

			byte[] value = new byte [lob.Length * 2];

			Console.WriteLine ("  CURRENT POSITION: {0}",
lob.Position);
			Console.WriteLine ("  UPDATING VALUE TO 'TEST
ME!'");
			value = encoding.GetBytes ("TEST ME!");
			lob.Write (value, 0, value.Length);

			Console.WriteLine ("  CURRENT POSITION: {0}",
lob.Position);
			Console.WriteLine ("  RE-READ VALUE...");
			lob.Seek (1, SeekOrigin.Begin);

			Console.WriteLine ("  CURRENT POSITION: {0}",
lob.Position);
			value = new byte [lob.Length * 2];
			lob.Read (value, 0, value.Length);
			Console.WriteLine ("  VALUE: {0}",
encoding.GetString (value));
			Console.WriteLine ("  CURRENT POSITION: {0}",
lob.Position);

			Console.WriteLine ("  CLOSE OracleLob...");
			lob.Close ();

			Console.WriteLine ("  CLOSING READER...");
			
			reader.Close ();
			transaction.Commit ();
		}

-----Original Message-----
From: mono-devel-list-admin at lists.ximian.com
[mailto:mono-devel-list-admin at lists.ximian.com] On Behalf Of Joost Evertse
Sent: Thursday, January 06, 2005 6:54 PM
To: mono-devel-list at lists.ximian.com
Subject: [Mono-devel-list] System.Data.OracleClient and CLOB's


I've been trying to get data from an CLOB column in oracle in to a string to
display on my ASP.NET webpage (mod_mono) I already tried to let the
clob.value.getstring() method do the conversion for me but that gives me an
exception about arguments not being even. I tried another way, by connecting
a stream reader to read bytes into chars en put it in a string. But the
funny thing is that it doesn't return enough data from the record. I also
tried to alter the buffersizes, but to no avail. It always cuts data at some
point. No errors, but not enough data. Below is a piece of code that
represents what i'm doing.

----
string post="";
			
OracleLob CLOB= dr.GetOracleLob(1);
				
StreamReader streamreader = new
StreamReader(CLOB,System.Text.Encoding.Default);
				
post = streamreader.ReadToEnd();
Response.Write("adding : " + post);
-----

Does anyone have a clue?