C# Oracle DataAccess CLOB insert issue

mservidio
mservidio used Ask the Experts™
on
When I insert a CLOB to a table of some XML, starting with ?<, after the insert occurs and I look in the table data, the initial character (question mark), is flipped, and no longer a regular question mark but an upside down question mark which must be another character. Thus when reading it out, I get errors.

The variable test in this example is a string which holds the xml data.

What am I missing here? I have stepped through the code and ensured that when the parameter is set, that it indeed starts like ?<


OracleCommand command = new OracleCommand("insert into gridview (gridviewid,stream,id,description,collapsed) " +
                " values (gridview_seq.nextval,:stream,:id,:description,:collapsed) returning gridviewid into :gridviewid", conn);
            command.Parameters.Add(":stream", OracleDbType.Clob, test, ParameterDirection.Input);
            command.Parameters.Add(":id", OracleDbType.Int32, id, ParameterDirection.Input);
            command.Parameters.Add(":description", OracleDbType.Varchar2, textEdit1.Text, ParameterDirection.Input);
            command.Parameters.Add(":collapsed", OracleDbType.Int32,BoolToNumber(this.grid.IsCollapsed), ParameterDirection.Input);
            command.Parameters.Add(":gridviewid", OracleDbType.Int32, ParameterDirection.Output);
            
            command.ExecuteNonQuery();

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Here is example of the data. First code is the data as set in the insert command param. Second code is the value after stored in the table.
Here is the value of variable test in the input parameter:

?<XtraSerializer version="1.0" application="View">
  <property name="#LayoutVersion" />
  <property name="PreviewIndent">-1</property>
  <property name="LevelIndent">-1</property>

Here is the value after it's stored in the table:

¿<XtraSerializer version="1.0" application="View">
  <property name="#LayoutVersion" />
  <property name="PreviewIndent">-1</property>
  <property name="LevelIndent">-1</property>


...etc

Open in new window

Resolved. It was a character set issue. I was able to resolve by switching the column datatype to NCLOB instead of CLOB. CLOB is dependent on the system character set setting I think, whereas NCLOB is based on the character set setting in the Oracle db.

http://www.dbazine.com/olc/olc-articles/scardina1
http://developer.mimer.com/documentation/mdp_92/mdpoverview/source/mimerconvertodp.htm

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial