错误“必须为此操作打开连接”从oracle数据库读取BLOB时 [英] Error "Connection must be open for this operation" while reading a BLOB from oracle database

问题描述

您好，



我正在尝试使用带有输出参数（parm）的存储过程从oracle数据库中检索BLOB值。以下是我执行此操作的代码段。

Hello,

I am trying to retrieve a BLOB value from a oracle database using a stored procedure with output parameters(parm). Here is the piece of code where I do this.

using (DbCommand cmd = db.GetStoredProcCommand(sStoredProcName))
{
  cmd.Connection = conn;
  AttachParameters(db, cmd, lParamValueList);

  // Open connection
  if (idbTransaction != null)
  {
    db.ExecuteNonQuery(cmd, idbTransaction);
  }
  else
  {
    db.ExecuteNonQuery(cmd);
  }
  // Retrieve any return values
  GetReturnValues(db, conn, cmd, ref lParamValueList);
}

然后在GetReturnValues下有另一个调用函数，这段麻烦的代码是

And then under GetReturnValues there is another calling function, the piece of troubled code is

cmd.Connection = conn;
if (db != null && cmd != null && (Out || IsReturnValue))
{
  int iIndex = ((Oracle.DataAccess.Client.OracleCommand)cmd).Parameters.IndexOf(ParamName);
  if (iIndex >= 0)
  {
    Oracle.DataAccess.Client.OracleParameter parm = (((Oracle.DataAccess.Client.OracleCommand)cmd).Parameters[iIndex]);

   if (conn.State == ConnectionState.Closed)
     conn.Open();

     Oracle.DataAccess.Types.OracleBlob blob = new Oracle.DataAccess.Types.OracleBlob((Oracle.DataAccess.Client.OracleConnection)conn);
     blob = (Oracle.DataAccess.Types.OracleBlob)parm.Value;  //<============== Troubled code

     if (blob.Length > 0)
     {
       byte[] bytes = new byte[blob.Length];
       blob.Read(bytes, 0, (int)blob.Length);
       ReturnValueBytes = bytes;
      }
...

一切似乎工作正常，除非它到达代码行我将输出参数值设置为ORACLEBLOB变量。我知道ORACLE BLOB要求显式打开连接。直到麻烦的行BLOB连接状态打开并且一旦通过

Everything seems to working fine unless the it reaches the line of the code where I am setting the output parameter value to a ORACLEBLOB variable. I understand that the ORACLE BLOB requires the connection to be opened explicitly. Till the troubled line the BLOB connection state is open and as soon as it passes

blob = (Oracle.DataAccess.Types.OracleBlob)parm.Value

连接的状态BLOB变量内部已关闭。



我无法理解为什么会发生这种情况。任何提示或帮助将不胜感激。



谢谢，

迪帕克

the state of the connection inside the BLOB variable is closed.

I am not able to understand why is this happening. Any hints or help would be much appreciated.

Thanks,
Deepak

推荐答案

你应该首先打开连接，做所有事情。试试下面的内容。

You should open the connection first, the do everything. Try the below.

using (DbCommand cmd = db.GetStoredProcCommand(sStoredProcName))
{
    cmd.Connection = conn;
    conn.Open();

    // Other codes...
}

大多数情况下（和我一起），这是一个愚蠢的错误。我更专注于BLOB变量，几乎忽略了其余的东西。



我正在做

As it happens most of the time (with me), it was a silly mistake. I was concentrating more on the BLOB variable and almost ignored the rest of the stuff.

I was doing

db.ExecuteNonQuery(cmd);

并且此后连接正在关闭。我将我的代码更改为

and the connection was getting closed just after this. I changed my code to

cmd.ExecuteNonQuery();

，现在工作正常。



希望这会帮助别人并节省一些时间。



干杯，

迪帕克

and its working fine now.

Hope this will help someone else and will save some time.

Cheers,
Deepak

这篇关于错误“必须为此操作打开连接”从oracle数据库读取BLOB时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！