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

[Daniel Morgan]

How big were your text files were you trying to copy?

Here is a test that works with latest svn for me.
It basically reads a text file named "cs-parser.cs" into an oracle clob.
Then it takes an oracle clob and writes it to a file named "cs-parser2.cs".
This file has a size of 279KB.

// testclob.cs
using System;
using System.Data;
using System.Data.OracleClient;
using System.Text;
using System.IO;

class TestClob 
{
	public static void Main (string[] args) 
	{
		OracleConnection con = new OracleConnection("data
source=palis;user id=scott;password=tiger");
		con.Open();
		
		CLOBTest (con);
		ReadClob (con);
		
		con.Close();
		con = null;
	}

	// read the CLOB into file "cs-parser2.cs"
	public static void ReadClob (OracleConnection connection) 
	{
		OracleCommand rcmd = connection.CreateCommand ();
		rcmd.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST";
		OracleDataReader reader2 = rcmd.ExecuteReader ();
		if (!reader2.Read ())
			Console.WriteLine ("ERROR: RECORD NOT FOUND");

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

		string lobvalue = (string) lob2.Value;
		
		using (StreamWriter sw = new StreamWriter("cs-parser2.cs"))
{
			sw.Write(lobvalue);
		}

		lob2.Close ();
		reader2.Close ();

	}

	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 ();

		try {
			// read file "cs-parser.cs" into the oracle clob
			using (StreamReader sr = new
StreamReader("cs-parser.cs")) {
				string sbuff = sr.ReadToEnd ();
				byte[] evalue = encoding.GetBytes (sbuff);
				lob.Write (evalue, 0, evalue.Length);
			}
		}
		catch (Exception e) {
			Console.WriteLine("The file could not be read:");
			Console.WriteLine(e.Message);
		}
		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?

regards,
Joost Evertse
 
2u2urrz