Re: Storing/Retrieving BLOB Data directly into a PictureBox in VB.NET

[John Webb]

I needed to save .wav files in a MySQL database but was having trouble changing the .wav file to a format that could be saved to a MySql DB and then later retrieved.

This code did the trick but I had to make a couple of changes because I was using an ODBC driver for MySql and not a native driver and I would get an error when I tried to use the FILESIZE argument.

Here is what I finally came up with and it works great.

Private Sub SaveWavFile(ByVal WavFileAndPath As String, ByVal StudyRef As String, ByVal UserName As String, ByVal PW As String)
Dim cmd As New OdbcCommand
Dim SQL As String
Dim rawData() As Byte
Dim fs As FileStream
Dim connectionString As String = " _
& "DRIVER={MySQL ODBC 3.51 Driver}" _
& ";SERVER=XXX.XXX.XX.XXX " _
& ";DATABASE=MyDatabase" _
& ";UID=UserName" _
& ";PASSWORD=PW" _
& ";OPTION=3;"

Dim conn As New OdbcConnection(connectionString)
Try
fs = New FileStream(WavFileAndPath, FileMode.Open, FileAccess.Read)
rawData = New Byte(fs.Length) {}
fs.Read(rawData, 0, fs.Length)
fs.Close()
conn.Open()
SQL = "INSERT INTO dictations VALUES(?, ?)"
cmd.Connection = conn
cmd.CommandText = SQL
cmd.Parameters.Add("@Study_ref", StudyRef)
cmd.Parameters.Add("@bin_data", rawData)
Try
cmd.ExecuteNonQuery()
Catch e As Exception
MessageBox.Show(e.Message)
End Try

MessageBox.Show("File Inserted into database successfully!", _
"Success!", MessageBoxButtons.OK, MessageBoxIcon.Asterisk)

conn.Close()
Catch ex As Exception
MessageBox.Show("There was an error: " & ex.Message, "Error", _
MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try

End Sub

And if you want to retrieve the file from the MySQL table you can use this code:

Sub RetrieveWavFilel(ByVal imavar1 As String)
'mysql> describe dictations;
'+-----------+-------------+------+-----+---------+-------+
'| Field | Type | Null | Key | Default | Extra |
'+-----------+-------------+------+-----+---------+-------+
'| study_ref | varchar(25) | YES | | NULL | |
'| bin_data | longblob | YES | | NULL | |
'+-----------+-------------+------+-----+---------+-------+
Dim fs As FileStream ' Writes the BLOB to a file (*.bmp).
Dim bw As BinaryWriter ' Streams the binary data to the FileStream object.
Dim bufferSize As Integer = 100 ' The size of the BLOB buffer.
Dim outbyte(bufferSize - 1) As Byte ' The BLOB byte() buffer to be filled by
'GetBytes.
Dim retval As Long ' The bytes returned from GetBytes.
Dim startIndex As Long = 0 ' The starting position in the BLOB output.
Dim strTmp As String = ""
Dim bWavFile() As Byte
'Dim connectionString As String = "Driver={MySQL};SERVER=localhost;DATABASE=NorthwindMySQL;" '
Dim connectionString As String = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=205.235.93.124;DATABASE=intelemed;UID=intelemed;PASSWORD=jcr00101;OPTION=3;"
Dim conn As New OdbcConnection(connectionString)
conn = New OdbcConnection(connectionString)
conn.Open()
Dim cmdOrder As New OdbcCommand("SELECT study_ref, bin_data From dictations Where (study_ref = ?) ", conn)
cmdOrder.Parameters.Add("@Study_ref", OdbcType.VarChar, 25).Value = imavar1
Dim fdRead As Odbc.OdbcDataReader = cmdOrder.ExecuteReader(CommandBehavior.CloseConnection)
Do While fdRead.Read()
strTmp = fdRead.GetString(0)
' Create a file to hold the output.
Dim strWavFileOut As String = Me.TextBox2.Text
fs = New FileStream(strWavFileOut, 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 = fdRead.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 = fdRead.GetBytes(1, startIndex, outbyte, 0, bufferSize)
Loop
' Write the remaining buffer.
Try
bw.Write(outbyte, 0, retval - 1)
Catch
End Try
bw.Flush()
' Close the output file.
bw.Close()
fs.Close()
Loop
fdRead.Close()
conn.Close()
End Sub

This took me a lot of work and research because there is not much on .wav files on the internet. I still have to make this work with a web service and I think that may take some doing because I have my argument on the VB.net side defined as a byte array and we are using PHP for the web service and I don't don't if we have a comparable argument type to use on the web service side.

I also got the webservice to work. I had to convert the data the base64 so I could send it as a string. So this turns out to be a successful little endeavor. I hope this code will help others.