Reading Blobs From Oracle

[Ken]

Hello,

I'm trying to read a Blob from Oracle and then write it to an audio
file(.AU). I'm using Visual Studio.Net 2003 (VB). I can't seem to get my
code to work. Will someone take a look at it and tell me what am I doing
wrong. Any help would be appreciated. Thanks


Dim Conn As OracleConnection = New OracleConnection("data source=XXXX;user
id=XXXXX;password=XXXXX")

Dim CMD As OracleCommand = New OracleCommand("SELECT BlobID, Audio FROM
TblBLOB", Conn)

Dim fs As FileStream

Dim bw As BinaryWriter

Dim bufferSize As integer= 100

Dim outbyte(bufferSize - 1) As Byte

Dim retval As Long

Dim startIndex As Long = 0

Dim OutPutFile As String = "



' Open the connection and read data into the DataReader.

Conn.Open()

Dim myReader As OracleDataReader =
CMD.ExecuteReader(CommandBehavior.SequentialAccess)

Do While myReader.Read()

' Get the BlobID.

OutPutFile = myReader.GetValue(0)

' Create a file to hold the output.

fs = New FileStream("OraBlob" & OutPutFile & ".AU", FileMode.OpenOrCreate,
FileAccess.Write)

bw = New BinaryWriter(fs)

' Reset the starting byte for a new BLOB.

startIndex = 0

' Read bytes into outbyte() and retain the number of bytes returned.

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)



' Continue reading and writing while there are bytes beyond the size of the
buffer.

Do While retval = bufferSize

bw.Write(outbyte)

bw.Flush()

' Reposition the start index to the end of the last buffer and fill the
buffer.

startIndex += bufferSize

retval = myReader.GetBytes(1, startIndex, outbyte, 0, bufferSize)

Loop

' Write the remaining buffer.

bw.Write(outbyte, 0, retval - 1)

bw.Flush()

' Close the output file.

bw.Close()

fs.Close()

Loop

' Close the reader and the connection.

myReader.Close()

Conn.Close()

 

[Mahesh ChandraMouli[MVP]]

Hi,

Try this code to read/write from a blob column

Dim PictureCol As Integer = 0 ' the column # of the BLOB
field
Dim cn As New OleDbConnection
("provider=sqloledb;server=localhost;user
id=myuser;password=mypassword;initial catalog=NorthWind")
Dim cmd As New OleDbCommand("SELECT Picture FROM
Categories WHERE CategoryName='Test'", cn)
cn.Open()
Dim dr As OleDbDataReader = cmd.ExecuteReader()
dr.Read()
Dim b(dr.GetBytes(PictureCol, 0, Nothing, 0,
Integer.MaxValue) - 1) As Byte
dr.GetBytes(PictureCol, 0, b, 0, b.Length)
dr.Close()
cn.Close()
Dim fs As New System.IO.FileStream(DestFilePath,
IO.FileMode.Create, IO.FileAccess.Write)
fs.Write(b, 0, b.Length)
fs.Close()

Regards
Mahesh ChandraMouli
Microsoft .NET MVP|MCAD