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 OracleConnectio n = New OracleConnectio n("data source=XXXX;use r
id=XXXXX;passwo rd=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(bufferS ize - 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 OracleDataReade r =
CMD.ExecuteRead er(CommandBehav ior.SequentialA ccess)

Do While myReader.Read()

' Get the BlobID.

OutPutFile = myReader.GetVal ue(0)

' Create a file to hold the output.

fs = New FileStream("Ora Blob" & OutPutFile & ".AU", FileMode.OpenOr Create,
FileAccess.Writ e)

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.GetByt es(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(outbyt e)

bw.Flush()

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

startIndex += bufferSize

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

Loop

' Write the remaining buffer.

bw.Write(outbyt e, 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=sqlo ledb;server=loc alhost;user
id=myuser;passw ord=mypassword; initial catalog=NorthWi nd")
Dim cmd As New OleDbCommand("S ELECT Picture FROM
Categories WHERE CategoryName='T est'", cn)
cn.Open()
Dim dr As OleDbDataReader = cmd.ExecuteRead er()
dr.Read()
Dim b(dr.GetBytes(P ictureCol, 0, Nothing, 0,
Integer.MaxValu e) - 1) As Byte
dr.GetBytes(Pic tureCol, 0, b, 0, b.Length)
dr.Close()
cn.Close()
Dim fs As New System.IO.FileS tream(DestFileP ath,
IO.FileMode.Cre ate, IO.FileAccess.W rite)
fs.Write(b, 0, b.Length)
fs.Close()

Regards
Mahesh ChandraMouli
Microsoft .NET MVP|MCAD

-----Original Message-----
Hello,

I'm trying to read a Blob from Oracle and then write it to an audiofile(.AU). I'm using Visual Studio.Net 2003 (VB). I can't seem to get mycode to work. Will someone take a look at it and tell me what am I doingwrong. Any help would be appreciated. Thanks
Dim Conn As OracleConnectio n = New OracleConnectio n ("data source=XXXX;use rid=XXXXX;passw ord=XXXXX")

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

Dim fs As FileStream

Dim bw As BinaryWriter

Dim bufferSize As integer= 100

Dim outbyte(bufferS ize - 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 OracleDataReade r =
CMD.ExecuteRea der(CommandBeha vior.Sequential Access)

Do While myReader.Read()

' Get the BlobID.

OutPutFile = myReader.GetVal ue(0)

' Create a file to hold the output.

fs = New FileStream("Ora Blob" & OutPutFile & ".AU", FileMode.OpenOr Create,FileAccess.Wri te)

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.GetByt es(1, startIndex, outbyte, 0, bufferSize)
' Continue reading and writing while there are bytes beyond the size of thebuffer.

Do While retval = bufferSize

bw.Write(outby te)

bw.Flush()

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

startIndex += bufferSize

retval = myReader.GetByt es(1, startIndex, outbyte, 0, bufferSize)
Loop

' Write the remaining buffer.

bw.Write(outby te, 0, retval - 1)

bw.Flush()

' Close the output file.

bw.Close()

fs.Close()

Loop

' Close the reader and the connection.

myReader.Close ()

Conn.Close()

.