Introduction
I needed to be able to FTP wav files to a server but using a normal FTP connection and transfer took too long, 30 to 40 seconds. So I decided a web service was what I needed.
But my first problem was figuring out how to save the wav files to a database. It wasn't too hard to figure out if I was using a MSSQL database. There were many articles about saving images and retrieving images from a MSSQL database and I was able to modify the code from those articles so that I could save and retrieve my .wav files. But the majority of those examples used the image datatype for MSSQL.
However, we were using a MySQL database and the webservice I was using was written in PHP, so that gave me some addition problems to overcome. In my tests I had to use a base64 string because that was the only data type I could send to my web service. My PHP web service could not handle a byte array being passed to it.
In addition, I could not find many articles about saving .wav files, so finding reference material turned out to be a considerable task.
The final result was fairly simple as you will see, but getting to that result was rather time consuming and involved a lot of trial and error.
This first section shows the code for using MSSQL as the database
Private Sub PrepareWavFile()
Dim strInFile As String
Dim strOutFile As String
strInFile = Application.StartupPath() & "\" & Me.TextBox1.Text
strOutFile = Application.StartupPath() & "\" & Me.TextBox2.Text
If File.Exists(strInFile) = False Then Exit Sub
Dim fs As FileStream
Dim br As BinaryReader
Dim info As New FileInfo(strInFile)
Dim WavFile(CInt(info.Length)) As Byte
Try
fs = New FileStream(strInFile, FileMode.Open, FileAccess.Read)
br = New BinaryReader(fs)
WavFile = br.ReadBytes(CInt(info.Length))
intWavLength = Int(CType(WavFile.Length, Integer))
Finally
If Not (br Is Nothing) Then br.Close()
If Not (fs Is Nothing) Then fs.Close()
End Try
SaveWavToSQLTable(WavFile)
End Sub
Public Sub SaveWavToSQLTable(ByVal bwavfile() As Byte)
Dim DBInsertCmd As SqlClient.SqlCommand
Dim sSQL As String
Dim strConn As String
strConn = "data source=Local;initial catalog=TestDB;" &_
"integrated security=SSPI;" &_
"persist security info=False;packet size=4096"
DBInsertCmd = New SqlClient.SqlCommand
DBInsertCmd.Connection = New SqlClient.SqlConnection(strConn)
Dim strBase64WavFile As String = Convert.ToBase64String(bwavfile)
sSQL = "INSERT INTO wavfiles(filename, soundfile) values (@filename, " &_
"@bwavfile)"
DBInsertCmd.CommandText = sSQL
With DBInsertCmd.Parameters
.Clear()
.Add("@filename", Me.TextBox2.Text)
.Add("@bwavfile", strBase64WavFile)
End With
DBInsertCmd.Connection.Open()
Try
DBInsertCmd.ExecuteNonQuery()
MessageBox.Show("Wav File Saved")
Catch oExcept As Exception
MessageBox.Show(oExcept.Message)
End Try
DBInsertCmd.Connection.Close()
DBInsertCmd = Nothing
End Sub
Private Sub retrieveSQLwavFile()
Dim DBSelectCmd As SqlClient.SqlCommand
Dim sSQL As String
Dim strConn As String
Dim strOutFile As String
strOutFile = Application.StartupPath() & "\" & Me.TextBox4.Text
strConn = "data source=Local;initial catalog=TestDB;integrated " &_
"security=SSPI;persist security info=False;packet size=4096"
DBSelectCmd = New SqlClient.SqlCommand
DBSelectCmd.Connection = New SqlClient.SqlConnection(strConn)
sSQL = "SELECT Photo FROM Image WHERE FileName=(@filename)"
DBSelectCmd.CommandText = sSQL
With DBSelectCmd.Parameters
.Clear()
.Add("@filename", Me.TextBox3.Text)
End With
DBSelectCmd.Connection.Open()
Dim b() As Byte
Try
b = DBSelectCmd.ExecuteScalar()
Dim K As Long
K = UBound(b)
Dim WriteFs As New FileStream(strOutFile, FileMode.Create, _
FileAccess.Write)
WriteFs.Write(b, 0, K)
WriteFs.Close()
MessageBox.Show("Wav file has been retrieved and written to " &_
"application folder")
Catch oExcept As Exception
MessageBox.Show(oExcept.Message)
End Try
DBSelectCmd.Connection.Close()
DBSelectCmd = Nothing
End Sub
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.