Saving a File to Oracle: Byte[ ] not the right data type for a Blob?

[jfrost10]

I'm trying to save an uploaded file from a C# asp.net page into a Blob field in an Oracle table.

I'm putting the file into a byte array and passing that as the parameter value, but when I execute the Oracle command object (using the System.Data.OracleClient namespace), it throws an error saying

"Cannot bind type System.Byte[] as Blob"

So what should the datatype be when putting data into a blob field then?

Thanks,

J

 

[ThatRickGuy]

Personally opinion... Oracle is a royal PITA.

You can't put binary data directly into Blob fields with Oracle. You have to do some funky append work. Let me see if I can find my code. (Another plug for a data abstraction layer, you only have to write this stuff once!)

If .OracleDbType <> OracleDbType.Blob Then
  .Value = piFields(i).GetValue(DataObject, Nothing)
Else
  Dim ms As New IO.MemoryStream()
  If Not piFields(i).GetValue(DataObject, Nothing) Is Nothing Then
    'ToDo: we need to set something up here to handle audio objects differently

    'this is for images
    CType(piFields(i).GetValue(DataObject, Nothing), System.Drawing.Image).Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg)
  End If

  Dim blob As Byte() = ms.ToArray

  'Create an oracle parameter to hold the blob
  Dim pm() As OracleParameter = {New OracleParameter("tempblob", OracleDbType.Blob, ParameterDirection.Output)}

  [b]ExecuteNonScalar("declare xx blob; begin dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;", pm)[/b]

  Dim tempLob As Oracle.DataAccess.Types.OracleBlob
  tempLob = pm(0).Value

  tempLob.Append(blob, 0, blob.Length)

  .Value = tempLob
End If

This is the code I am using in part of our DAL. The key thing to understand in what this is doing is that we are creating a temporary object in Oracle, we get a reference to that temporary object and return it as a blob parameter. Then we can append the binary data to that parameter. And finally we can set the value of the parameter for our actual insert/update statement to the value of the return parameter with the binary data appended.

I'm not sure who at Oracle thought this would be a good idea. But I have a few choice words I'd share with them over a couple of beers.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[jfrost10]

Thanks for the reply Rick. We've got an added headache in that we're using DAAB (Microsoft Data Access Application Block), which by default doesn't utilize the Oracle data types...which means I don't have direct access to the Blob datatable when creating the parameter (we've been trying to pass it in as an object).

I'm wondering if this isn't part of the issue...

D

 

[jfrost10]

Ah, I got it working...the issue was that we were trying to push the data in as Object, but that wasn't working. However, switching it to dbtype.binary did the trick. The file is now being saved to the db.

w00t!

D