CLOBs

flutos · Post by **flutos** » Tue 06 Oct 2009 21:58

I was trying to use the dotconnect drivers and I noticed that inserting into clobs I would only get the first 4000 characters inserted into the clob column. When I switched back to the oracle drivers I get all the characters inserted. Is there a problem with large clobs? ALso Im using nhibernate so thats actually doing the low level ado.net stuff.

thanks

scott

Shalex · Post by **Shalex** » Wed 07 Oct 2009 12:19

I have checked the latest 5.25.44 version of dotConnect for Oracle using the following code:

Code: Select all

  string connStrDirect = "server=xxx;SID=xxx;uid=xxx;pwd=xxx;port=xxx;Direct=true;Unicode=true;";
  using (OracleConnection conn = new OracleConnection(connStrDirect)) {
    conn.Open();
    OracleCommand command = conn.CreateCommand();
    command.CommandText = "insert into CLOB_TEST(id,clob) values(1,:p1)";
    
    OracleParameter param1 = new OracleParameter();
    param1.ParameterName = "p1";
    param1.OracleDbType = OracleDbType.Clob;
    //textBefore.txt contains 28800 characters
    StreamReader re = File.OpenText(@"D:\tempClob\textBefore.txt");
    param1.Value = re.ReadToEnd();
    re.Close();

    command.Parameters.Add(param1);
    command.ExecuteNonQuery();

    command.CommandText = "select * from CLOB_TEST where CLOB_TEST.ID=1";
    command.ParameterCheck = true;
    OracleDataReader reader = command.ExecuteReader();
    reader.Read();
    int i = reader.GetOrdinal("clob");
    FileInfo t = new FileInfo(@"D:\tempClob\textAfter.txt");
    StreamWriter Tex = t.CreateText();
    Tex.Write(reader.GetString(i));
    Tex.Close(); //textAfter.txt contains 28800 characters as well
    reader.Close();
  }

I cannot reproduce the problem.
Please tell us how we should modify this sample to reproduce the error or send us ( http://www.devart.com/company/contact.html ) a small test project to reproduce the issue.

flutos · Post by **flutos** » Wed 07 Oct 2009 12:53

The low level coding you are doing is being done by nhibenate for me so I looked at what nhibernate is doing at its using DbType.AnsiString for the dbtype rather that ORacleDbType.Clob . This is because of the abstracted nature of nhibernate but regardless when I use microsofts oracle client and oracles oracle client , they both work fine when nhibernate uses AnsiString, how does the dotCOnnect driver interpret DbType.AsnsiString?

thanks

scott

Shalex · Post by **Shalex** » Thu 08 Oct 2009 11:45

DbType.AnsiString is interpreted to OracleDbType.VarChar by dotConnect for Oracle. It corresponds to Oracle VARCHAR2, which can store only up to 4000 bytes of characters. That's why character set is truncated after 4000 bytes. It is necessary to use OracleDbType.Clob.

flutos · Post by **flutos** » Thu 08 Oct 2009 12:22

ok,Im not sure why the microsoft and oracle drivers work but I guess I would have to do some changes to the nhibernate driver for oracle to get that working with dotconnect, do you know if anyone is using dotconnect with nhibernate.

thanks

scott

Shalex · Post by **Shalex** » Fri 09 Oct 2009 09:56

Yes, we have customers who use NHibernate with dotConnect for Oracle. According to their requests, we have implemented the Devart.Data.Oracle.NHibernate.NHibernateOracleConnection and Devart.Data.Oracle.NHibernate.NHibernateOracleCommand classes. For example, please refer to http://www.devart.com/forums/viewtopic.php?t=15685 .

flutos · Post by **flutos** » Fri 09 Oct 2009 13:56

ya, I got that one and since you are using the oracle dialect , it will use ansi strings for clobs so you if you tried it with clobs you would see the inserted text getting cut off. The driver would need to be updated to interpret clobs using Oracle clob type but its a little tricky given the abstract nature of nhibernate but I think its doable. I might try and do that if we decide to go further with your driver but it something you might want to consider adding to the driver.

thanks

scott

pankajnagarkoti86 · Post by **pankajnagarkoti86** » Mon 12 Oct 2009 22:22

The low level coding you are doing is being done by nhibenate for me so I looked at what nhibernate is doing at its using DbType.AnsiString for the dbtype rather that ORacleDbType.Clob .

flutos · Post by **flutos** » Tue 13 Oct 2009 00:18

ya, I think the Nhibernate OracleDataClientDriver.InitializeParameter methods needs to be changed to have it return the correct Clob type I suspect . If I have a chance I might give this a try but we arnt sure yet if we will pursue trying this driver for now anyhow.

scott