Hi there, hope someone can help with this - After retrieving a file from a oracle blob and write to hdd the file is corrupt . I believe it might be a char set issue: here is my code:
Dim con As OracleConnection = New OracleConnection(sConnString)
con.Open()
' Source and Destination location
Dim SourceLoc As String = Application.StartupPath & "\visioDOC.vsd"
Dim DestinationLoc As String = Application.StartupPath & "\visioDOC_1.vsd"
Dim fs As FileStream
Dim fileData As Byte()
' providing read access to the file
fs = New FileStream(SourceLoc, FileMode.Open, FileAccess.Read)
' Create a byte array of file stream length
ReDim fileData(fs.Length)
'Read block of bytes from stream into the byte array
If direction = "in" Then
fs.Read(fileData, 0, System.Convert.ToInt32(fs.Length))
'Close the File Stream
Dim fs1 As FileStream = New FileStream(DestinationLoc, FileMode.OpenOrCreate, FileAccess.Write)
fs1.Write(fileData, 0, System.Convert.ToInt32(fs.Length))
fs.Close()
fs1.Close()
End If
' Creating Anonymous PL/SQL block string
Dim block As String = " BEGIN " & _
" update gef_moi_attr set attr_val_blob = :1 WHERE object_id = " & IDs & " and attr_id='fileContent' ;" & _
" SELECT attr_val_blob into :2 from gef_moi_attr WHERE object_id = " & IDs & " and attr_id='fileContent';" & _
" end ;"
' Set command to create Anonymous PL/SQL Block
Dim cmd As OracleCommand = New OracleCommand
cmd.CommandText = block
cmd.Connection = con
' setting the command type
' as Text instead of StoredProcedure
cmd.CommandType = CommandType.Text
' Step 4
' Setting Oracle parameters
' Bind the parameter as OracleDbType.Blob to command for inserting file
Dim param As OracleParameter = cmd.Parameters.Add("blobtodb", OracleDbType.Blob)
param.Direction = ParameterDirection.Input
' Assigning Byte Array to Oracle Parameter
param.Value = fileData
' Bind the parameter as OracleDbType.Blob to command for retrieving the FILE
Dim param2 As OracleParameter = cmd.Parameters.Add("blobfromdb", OracleDbType.Blob)
param2.Direction = ParameterDirection.Output
Try
' Execute the Anonymous PL/SQL Block. The anonymous PL/SQL block inserts the
' file to the database and then retrieves the images as an output parameter
cmd.ExecuteNonQuery()
' Save the retrieved file to the DestinationLoc in the file system
' Create a byte array
Dim byteData As Byte()
Dim Paramvalue As OracleBlob
Paramvalue = cmd.Parameters(1).Value
' fetch the value of Oracle parameter into the byte array
byteData = CType((Paramvalue.Value), Byte())
' get the length of the byte array
Dim ArraySize As Integer = New Integer
ArraySize = byteData.GetUpperBound(0)
' Write the Blob data fetched from database to the filesystem at the destination location
If direction = "out" Then
Dim fs1 As FileStream = New FileStream(DestinationLoc, FileMode.OpenOrCreate, FileAccess.Write)
fs1.Write(byteData, 0, ArraySize)
fs1.Close()
End If
Catch ex As Exception
MsgBox(ex.Message)
done = False
Finally
cmd.Dispose()
con.Close()
con.Dispose()
done = True
End Try
Return done
Dim con As OracleConnection = New OracleConnection(sConnString)
con.Open()
' Source and Destination location
Dim SourceLoc As String = Application.StartupPath & "\visioDOC.vsd"
Dim DestinationLoc As String = Application.StartupPath & "\visioDOC_1.vsd"
Dim fs As FileStream
Dim fileData As Byte()
' providing read access to the file
fs = New FileStream(SourceLoc, FileMode.Open, FileAccess.Read)
' Create a byte array of file stream length
ReDim fileData(fs.Length)
'Read block of bytes from stream into the byte array
If direction = "in" Then
fs.Read(fileData, 0, System.Convert.ToInt32(fs.Length))
'Close the File Stream
Dim fs1 As FileStream = New FileStream(DestinationLoc, FileMode.OpenOrCreate, FileAccess.Write)
fs1.Write(fileData, 0, System.Convert.ToInt32(fs.Length))
fs.Close()
fs1.Close()
End If
' Creating Anonymous PL/SQL block string
Dim block As String = " BEGIN " & _
" update gef_moi_attr set attr_val_blob = :1 WHERE object_id = " & IDs & " and attr_id='fileContent' ;" & _
" SELECT attr_val_blob into :2 from gef_moi_attr WHERE object_id = " & IDs & " and attr_id='fileContent';" & _
" end ;"
' Set command to create Anonymous PL/SQL Block
Dim cmd As OracleCommand = New OracleCommand
cmd.CommandText = block
cmd.Connection = con
' setting the command type
' as Text instead of StoredProcedure
cmd.CommandType = CommandType.Text
' Step 4
' Setting Oracle parameters
' Bind the parameter as OracleDbType.Blob to command for inserting file
Dim param As OracleParameter = cmd.Parameters.Add("blobtodb", OracleDbType.Blob)
param.Direction = ParameterDirection.Input
' Assigning Byte Array to Oracle Parameter
param.Value = fileData
' Bind the parameter as OracleDbType.Blob to command for retrieving the FILE
Dim param2 As OracleParameter = cmd.Parameters.Add("blobfromdb", OracleDbType.Blob)
param2.Direction = ParameterDirection.Output
Try
' Execute the Anonymous PL/SQL Block. The anonymous PL/SQL block inserts the
' file to the database and then retrieves the images as an output parameter
cmd.ExecuteNonQuery()
' Save the retrieved file to the DestinationLoc in the file system
' Create a byte array
Dim byteData As Byte()
Dim Paramvalue As OracleBlob
Paramvalue = cmd.Parameters(1).Value
' fetch the value of Oracle parameter into the byte array
byteData = CType((Paramvalue.Value), Byte())
' get the length of the byte array
Dim ArraySize As Integer = New Integer
ArraySize = byteData.GetUpperBound(0)
' Write the Blob data fetched from database to the filesystem at the destination location
If direction = "out" Then
Dim fs1 As FileStream = New FileStream(DestinationLoc, FileMode.OpenOrCreate, FileAccess.Write)
fs1.Write(byteData, 0, ArraySize)
fs1.Close()
End If
Catch ex As Exception
MsgBox(ex.Message)
done = False
Finally
cmd.Dispose()
con.Close()
con.Dispose()
done = True
End Try
Return done