I tried to update one CLOB column in Oracle 9i, but the saved data in database is gibberish. It only happend when I use Transaction in .cs file. But if I remove the transaction related codes, it work.

Environment:
Client: Windows XP, Visual Studio 2005, System.Data.OracleClient 2.0
Server: HP-UX II for OS, Oracle 9i with American set as the Language and a character set of English US ASCII

Here are the code using transaction (not working version):

string selectSQL = "select configvalue from appconfig where configid = 1";
string newvalue = txtNewValue.Text.Trim(); // use "test value" at web form
lock (this)
{
DataTable dt = new DataTable();
OracleConnection conn = new OracleConnection(ConfigurationManager.ConnectionStrings["dbconn"].ConnectionString);
OracleCommand cmd = new OracleCommand(selectSQL, conn);
OracleDataAdapter da = new OracleDataAdapter(cmd);
OracleCommandBuilder ocb = new OracleCommandBuilder(da);
cmd.UpdatedRowSource = UpdateRowSource.None;
try
{
conn.Open();
da.Fill(dt);
if (dt != null && dt.Rows.Count > 0)
{
OracleTransaction trans = conn.BeginTransaction();
cmd.Transaction = trans;
try
{
dt.Rows[0][configfield] = newvalue;
da.Update(dt);
trans.Commit();
}
catch (Exception ee)
{
trans.Rollback();
}
finally
{
if (conn != null)
{
if (conn.State != ConnectionState.Closed)
conn.Close();
conn = null;
}
trans = null;
}
}
}
catch (Exception ex)
{}
finally
{
if (conn != null)
{
if (conn.State != ConnectionState.Closed)
conn.Close();
conn = null;
}
}
}

Anyone has any idea what's wrong with the code. It only happened with Transaction in .cs file. It's very weird.

Thanks.

AX

Hi AX,

I'm trying to reproduce this so I can investigate it, but I'm running into some problems. Can you provide the following information that might help me?

(1) SQL to create the table. Here is what I used:

create table appconfig (configid NUMBER CONSTRAINT configid_pk PRIMARY KEY, configvalue CLOB);

With the table like this, my OracleCommandBuilder fails to generate the UpdateCommand for the query you provided, because the query doesn't contain the primary key. Since your query only contains one column, and your problem description indicates it's a CLOB, I'm not sure how your OracleCommandBuilder is working, since you can't make the CLOB the primary key.

(2) How would you know if any errors are occurring? Your catch blocks don't output any information when errors occur. I would suggest adding some way to output the error message and call stack (e.g. ex.ToString()). I assume you stepped through this in a debugger, or somehow concluded no errors were occurring, but normally you wouldn't want to just eat the errors in this scenario.

(3) Are you sure the update is actually going through to the database? If so, how did you verify that? Is it because you have an initial value in the row, and it's actually changed after the code runs? Or have you run client or server-side tracing to verify what is being sent? System.Data.OracleClient is built on top of Oracle's OCI API, so you can use their OCI tracing to see what's happening at that level. For reference on how to do that, see: http://download-west.oracle.com/docs/cd/B19306_01/network.102/b14213/sqlnet.htm. That doc is for 10g, but at least the basic settings should work on 9i as well. For example, the following is what I use in my sqlnet.orato turn on OCI tracing:

Thanks,
Sarah

Hello, Sarah,
Thanks for your help.

1. The configid is the primary key.

2. The value is saved to database, but the value is gibberish. No error occured. The app never go to catch block. I have the screen snapshot and I can send a copy to you.

3. Thanks for the trace info. I will do it later.

AX

If the configid is the primary key and you are using the SQL included in your code snippet, I still don't understand how your OracleCommandBuilder is able to generate the UPDATE statement.

Do you know what UPDATE statement is being used? You can get it using GetUpdateCommand on the CommandBuilder object. You can also get it out of the OCI trace, but GetUpdateCommand will show you what it looks like originally in .NET, as opposed to how it might look at the OCI level.

I'm not sure what you have a screen shot of, but you can e-mail it to me at the address in my forum profile, but REMOVE the "online" from the address. You can also send me your OCI trace if you capture one. I am not an expert reading those traces, but I can at least look for the significant operations relevant to this scenario.

Thanks,
Sarah