Click here to Skip to main content
12,507,343 members (73,748 online)
Click here to Skip to main content
Add your own
alternative version

Stats

42.2K views
910 downloads
29 bookmarked
Posted

Saving and Retrieving Wav files from MSSQL, MSDE and MySQL databases

, 21 Feb 2005
Rate this:
Please Sign up or sign in to vote.
This code will let you save .WAV files to either a MSSQL, MSDE or MySql database and then retrieve them

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
        ' make sure objects are closed in case the thread
        ' was aborted in the middle of this method
        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)

    'MS SQL/MSDE> wavfiles table; 
    '+-----------+-------------+------+-----+----------+ 
    '| Field | Type | Null | Key | Size | 
    '+-----------+-------------+------+-----+----------+ 
    '| ID | Int | | | 4 | 
    '| filename | Nvarchar(25)| YES | | 50 | 
    '| Soundfile | nText | YES | | 16 | 
    '+-----------+-------------+------+-----+----------+ 
    Dim DBInsertCmd As SqlClient.SqlCommand
    Dim sSQL As String
    Dim strConn As String

    'set these parameters to your own database
    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)
    ' This part is to convert byte array to Base64 string 
    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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

j1webb
Web Developer
United States United States
No Biography provided

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralAnother Error.. Pin
Akaru14-May-09 17:59
memberAkaru14-May-09 17:59 
GeneralTwo Errors Pin
espelly15-Oct-06 23:49
memberespelly15-Oct-06 23:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160927.1 | Last Updated 21 Feb 2005
Article Copyright 2005 by j1webb
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid