With nhib.pdf, Oracle Clob/NClob cannot be inserted or garbled

Source: Internet
Author: User
Tags oracleconnection
The Oracle 9i server and the Oracle 10g client.

Question 1:
The Driver configured for NHibernate is nhib.pdf. Driver. OracleClientDriver, which is stored in the Clob and NClob fields. If the value is large, an error is returned. error message
Chinese: ORA-01461: Can only assign values to LONG values inserted into LONG Columns
ORA-01461: can bind a LONG value only for insert into a LONG column.

A lot of people have encountered this problem on Google. Someone guessed it was a Bug in MS System. Data. OracleClient by switching to Oracle. DataAccess provided by Oracle.

Solution:
Download and install ODAC (Oracle Data Access Components), configure the Driver of nhib.pdf as nhib.pdf. Driver. OracleDataClientDriver, and ensure that the Oracle. DataAccess. dll file is in the running directory.

Question 2:
After using Oracle. DataAccess, you can insert long text into an NClob field, but sometimes the text turns into garbled characters. The specific expression is to enter some Chinese words and save them as normal; enter English characters and save them as normal; enter a mix of Chinese and English html, and save them as garbled characters.
Oracle Character Set settings on servers and clients are excluded. Tracking NHibernate, when calling IDbCommand to execute an SQL statement, the value in the parameter is normal, so the encoding and decoding of text in the program is ruled out.

After testing, the value of NClob stored in the following method will not become garbled: Using Oracle. DataAccess. Client;

OracleConnection con = new OracleConnection ("......");
Con. Open ();
OracleTransaction tran = con. BeginTransaction ();
OracleCommand command = con. CreateCommand ();
Command. CommandType = CommandType. Text;
Command. CommandText = "update cms_template set temp_content =: p where temp_id = 4 ";
OracleParameter param = command. CreateParameter ();
Param. ParameterName = ": p ";
Param. Value = this. textBox1.Text;
Param. OracleDbType = OracleDbType. NClob;
Command. Parameters. Add (param );
Command. ExecuteNonQuery ();
Tran. Commit ();
Con. Close ();

The most critical sentence is to set param. OracleDbType to OracleDbType. NClob, so that Oracle. DataAccess knows how to correctly process this parameter.

In order to be compatible with multiple databases, nhibbench uses the IDbParameter interface in a unified manner. This problem cannot be solved internally in nhibbench. My method is to implement a nhibeter for attributes of the CLob and NClob types. userTypes. IUserType: In its NullSafeSet method, modify the OracleDbType value of IDbParameter corresponding to this field in IDbCommand. The specific implementation is as follows: Public abstract class PatchForOracleLobField: IUserType
{
Public PatchForOracleLobField ()
{
}

Public bool IsMutable
{
Get {return true ;}
}

Public Type ReturnedType
{
Get {return typeof (String );}
}

Public SqlType [] SqlTypes
{
Get {return new SqlType [] {new SqlType (DbType. String )};}
}

Public object DeepCopy (object value)
{
Return value;
}

Public new bool Equals (object x, object y)
{
Return x = y;
}

Public int GetHashCode (object x)
{
Return x. GetHashCode ();
}

Public object Assemble (object cached, object owner)
{
Return DeepCopy (cached );
}

Public object Disassemble (object value)
{
Return DeepCopy (value );
}

Public object NullSafeGet (IDataReader rs, string [] names, object owner)
{
Return nhibtil. NHibernateUtil. StringClob. NullSafeGet (rs, names [0]);
}

Public abstract void NullSafeSet (IDbCommand cmd, object value, int index );

Public object Replace (object original, object target, object owner)
{
Return original;
}
}

Public class OracleClobField: PatchForOracleLobField
{
Public override void NullSafeSet (IDbCommand cmd, object value, int index)
{
If (cmd is OracleCommand)
{
// For CLob and NClob fields, the OracleDbType parameter must be set to OracleDbType. Clob
// Otherwise it will become garbled (Oracle 10g client environment)
OracleParameter param = cmd. Parameters [index] as OracleParameter;
If (param! = Null)
{
Param. OracleDbType = OracleDbType. Clob;
Param. IsNullable = true;
}
}
Nhibtil. NHibernateUtil. StringClob. NullSafeSet (cmd, value, index );
}
}

Public class extends lenclobfield: PatchForOracleLobField
{
Public override void NullSafeSet (IDbCommand cmd, object value, int index)
{
If (cmd is OracleCommand)
{
// For CLob and NClob fields, the OracleDbType parameter must be set to OracleDbType. Clob
// Otherwise it will become garbled (Oracle 10g client environment)
OracleParameter param = cmd. Parameters [index] as OracleParameter;
If (param! = Null)
{
Param. OracleDbType = OracleDbType. NClob;
Param. IsNullable = true;
}
}
Nhibtil. NHibernateUtil. StringClob. NullSafeSet (cmd, value, index );
}
}

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.