Inserting and Selecting .wav files from and to MySQL table
-
I am a VB.Net developer and I am trying to use VB.Net, a web service and a MySQL table on a Linux server to save and retrieve .wav files. I have been able to insert and retrieve .wav files to a MS SQL table by using the image datatype in the MSSQL table. I have converted a .wav file to both a byte array and a base64 character type and saved it (inserted) to a MSSQL table with no trouble. And I have used the select statment to retrieve that same .wav file and my code works. However, I really need to create a webservice to store .wav files into a MySql table on a Linux server. We currently have numerous webservices that work well with MySql but inserting a .wav file has proven to be a real challenge. I am not trying to use a webservice during the testing, I make a direct connection to the MySql db and use ODBC SQL statements for my Inserts and Selects. I have used numerous reader and writer routines that I have found on the Internet but none of the have worked. There is not much on the internet about how to save .wav files in a database, but I am making an assumption I could use some of the same code that is used to save and retrieve image files. This assumption has worked when I am using MSSQL tables but it doesn't seem to work with MySql tables. Does anyone have any idea as to how to do this?
-
I am a VB.Net developer and I am trying to use VB.Net, a web service and a MySQL table on a Linux server to save and retrieve .wav files. I have been able to insert and retrieve .wav files to a MS SQL table by using the image datatype in the MSSQL table. I have converted a .wav file to both a byte array and a base64 character type and saved it (inserted) to a MSSQL table with no trouble. And I have used the select statment to retrieve that same .wav file and my code works. However, I really need to create a webservice to store .wav files into a MySql table on a Linux server. We currently have numerous webservices that work well with MySql but inserting a .wav file has proven to be a real challenge. I am not trying to use a webservice during the testing, I make a direct connection to the MySql db and use ODBC SQL statements for my Inserts and Selects. I have used numerous reader and writer routines that I have found on the Internet but none of the have worked. There is not much on the internet about how to save .wav files in a database, but I am making an assumption I could use some of the same code that is used to save and retrieve image files. This assumption has worked when I am using MSSQL tables but it doesn't seem to work with MySql tables. Does anyone have any idea as to how to do this?
I figured it out myself and I thought I would put the answer onto this forum in case anyone else might need to do the same thing. 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 Stri