Hi!
I am trying to upload files (.docx, .pptx, .pdf) to an online MySQL database and later be able to save/download it locally to my pc. I am trying to develop a software that could do this particular task using VB.NET and MySql.
I have a tbl_proposals with fields:
- id
- doc_name
- created_by
- date_uploaded
- client_name
- description
- raw_file (mediumblob)
- file_format
This is my codes for uploading to the database and so far it seems to be working fine:
Dim fs As New System.IO.FileStream(filepath, IO.FileMode.Open, IO.FileAccess.Read)
'filepath contains actual file path e.g. c:\documents\words.docx
Dim doc(fs.Length() - 1) As Byte
fs.Read(doc, 0, doc.Length)
fs.Close()
com.CommandText = "INSERT INTO tbl_proposals (doc_name, created_by, date_uploaded, client_name, description, raw_file, file_format) VALUES(@dname, @cby, @duploaded, @cname, @desc, @rfile, @fformat)"
com.Parameters.AddWithValue("@dname", fname)
com.Parameters.AddWithValue("@cby", uname)
com.Parameters.AddWithValue("@duploaded", Now.Date)
com.Parameters.AddWithValue("@cname", txtClient.Text)
com.Parameters.AddWithValue("@desc", txtDesc.Text)
Dim raw_file_param As New MySqlParameter("@rfile", MySqlDbType.MediumBlob, doc.Length, ParameterDirection.Input, False, 0, 0, Nothing, DataRowVersion.Current, doc)
com.Parameters.Add(raw_file_param)
com.Parameters.AddWithValue("@fformat", ext)
com.ExecuteNonQuery()
MessageBox.Show("Upload complete.", "Upload Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
and this is where i'm having problems - my download codes
ofd.SelectedPath = "C:\"
ofd.Description = "Choose where to save Proposal / Quote"
If ofd.ShowDialog() = Windows.Forms.DialogResult.OK Then
file_path = ofd.SelectedPath
End If
com.CommandText = "SELECT * FROM tbl_proposals WHERE doc_name = '" & selected_file & "'"
reader = com.ExecuteReader()
If reader.Read Then
file_path = file_path & "\" & reader.GetString(1) & reader.GetString(7)
Dim fs As New System.IO.FileStream(file_path, IO.FileMode.Create, FileAccess.ReadWrite)
Dim raw_col As Integer = 7 'column # of raw data
Dim raw_data(reader.GetBytes(raw_col, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte
reader.GetBytes(raw_col, 0, raw_data, 0, raw_data.Length)
fs.Write(raw_data, 0, raw_data.Length)
fs.Close()
MsgBox("Complete")
End If
reader.Close()
the line "Dim raw_data(reader.GetBytes(raw_col, 0, Nothing, 0, Integer.MaxValue) - 1) As Byte" returns an exception: GetBytes can only be called on binary or guid columns
I tried to check what's the cause of this exception and understood that the data type in my database should be in VARBINARY. I tried doing that and gave me the same result. also tried using LONGBLOB and MEDIUMBLOB and still the same.
I hope someone can help me here. Thank you.
- Jun S.