sysconf 表的 tag2 字段类型为blob类型
当插入一个大于4000字节的数据时报错,错误信息:
ORA-01461: 仅可以为插入 LONG 列的 LONG 值赋值环境:
ROACLE:8.0.5.1.0
VS2005 int nSize = 4000; // size<=4000正常, size>4000时报错
byte[] bArray = new byte[nSize];
for (int i = 0; i < nSize; i++)
{
bArray[i] = 66;
} string sConn = "Provider=MSDAORA.1;User ID=hptest;Data Source=test;Password=hptest;server=dbserver";
OleDbConnection conn = new OleDbConnection(sConn);
OleDbTransaction tra = null;
try
{
conn.Open();
Console.WriteLine("打开成功!");
OleDbCommand com = conn.CreateCommand();
tra = conn.BeginTransaction();
com.Transaction = tra; string SQL = "update sysconf set tag2=? where name='HotelName'";
Console.WriteLine(SQL);
com.CommandText = SQL;
com.Parameters.Add("tag2", OleDbType.Binary).Value = bArray; int n = com.ExecuteNonQuery(); // bArray的长度大于4000时在这里出现异常
Console.WriteLine("执行完毕,影响的行数:" + n);
tra.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
tra.Rollback();
} if (conn.State == ConnectionState.Open)
conn.Close();
Console.Read();
当插入一个大于4000字节的数据时报错,错误信息:
ORA-01461: 仅可以为插入 LONG 列的 LONG 值赋值环境:
ROACLE:8.0.5.1.0
VS2005 int nSize = 4000; // size<=4000正常, size>4000时报错
byte[] bArray = new byte[nSize];
for (int i = 0; i < nSize; i++)
{
bArray[i] = 66;
} string sConn = "Provider=MSDAORA.1;User ID=hptest;Data Source=test;Password=hptest;server=dbserver";
OleDbConnection conn = new OleDbConnection(sConn);
OleDbTransaction tra = null;
try
{
conn.Open();
Console.WriteLine("打开成功!");
OleDbCommand com = conn.CreateCommand();
tra = conn.BeginTransaction();
com.Transaction = tra; string SQL = "update sysconf set tag2=? where name='HotelName'";
Console.WriteLine(SQL);
com.CommandText = SQL;
com.Parameters.Add("tag2", OleDbType.Binary).Value = bArray; int n = com.ExecuteNonQuery(); // bArray的长度大于4000时在这里出现异常
Console.WriteLine("执行完毕,影响的行数:" + n);
tra.Commit();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
tra.Rollback();
} if (conn.State == ConnectionState.Open)
conn.Close();
Console.Read();
可以参考FortuneBase中的代码
参考地址
www.cnblogs.com/mail-ricklee
改为:
com.Parameters.Add("tag2", OleDbType.Blob).Value = bArray;
不知为什么网上的一些例子在我这里都会报错,包括MSDN的。但总算是解决了!
结贴!static void Main(string[] args)
{
int nSize = 50000;
byte[] bArray = new byte[nSize];
for (int i = 0; i < nSize; i++)
{
bArray[i] = 68;
} /*
* 须使用OracleClient命名空间
* 须安装支持OLEDB 的ORACLE 客户端
*/
string sConn = "Data Source=192.168.0.5;User ID=hpbjjy;Unicode=True;Password=hpbjjy";
OracleConnection conn = new OracleConnection(sConn);
OracleTransaction tra = null;
try
{
conn.Open();
Console.WriteLine("打开成功!");
OracleCommand com = conn.CreateCommand();
tra = conn.BeginTransaction();
com.Transaction = tra; /*
* 先使用查询获得该BLOB字段的对象
* 同时使用for update 锁定该字段,否则无法修改
*/
string SQL = "select tag2 from sysconf where name='HotelName' for update"; // tag2字段类型为BLOB
com.CommandText = SQL;
OracleDataReader reader = com.ExecuteReader();
OracleLob lob = OracleLob.Null;
reader.Read();
lob = reader.GetOracleLob(0); // 获取BLOB对象 lob.Write(bArray, 0, bArray.Length); // 将数据写入该对象
SQL = "update sysconf set tag2=:t where name='HotelName'";
com.CommandText = SQL;
com.Parameters.Add("t", OracleType.Blob).Value = lob;
com.ExecuteNonQuery(); tra.Commit();
Console.WriteLine("成功完成!");
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
if (tra != null)
tra.Rollback();
} if (conn.State == ConnectionState.Open)
conn.Close();
Console.Read();
}