Error "Connection must be open for this operation" while reading a BLOB from oracle database

Question

0.00/5 (No votes)

See more:

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.

C#

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);
}

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

C#

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;
      }
...

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

C#

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

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

Posted 9-Jun-14 5:44am

catchbobbie

Updated 9-Jun-14 5:49am

v2

Add a Solution

2 solutions

Solution 1

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

C#

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

    // Other codes...
}

Posted 9-Jun-14 7:38am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Comments

catchbobbie 10-Jun-14 4:34am

Thanks Dash,

Have already tried that, If you see I am opening the connection just before declaring the BLOB variable. The problem is the connection gets closed when I assign the out param value to it.

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 10-Jun-14 13:58pm

Ok, cool. :)

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

catchbobbie · Accepted Answer · 2014-06-10T04:01:00

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