WelsfordAlan
asked on
MySql blob to File - vb.net
How do I retrieve a blob from mysql and save it to a file using vb.net
Blob can either be a jpg or pdf.
Blob can either be a jpg or pdf.
ASKER
Hi Saige
Thank you for the reply, below is the code to test if it works, I am getting an error on the line: Dim data As Byte() = DirectCast(myReader.GetVal ue("DocBin "), Byte())
. The error is
Conversion from string "DocBin" to type 'Integer' is not valid.
I would be most grateful if you could help me solve this problem
Thanks in advance
Alan Welsford
Private Sub ExpToolStripMenuItem_Click (sender As Object, e As EventArgs) Handles ExpToolStripMenuItem.Click
Dim cmd As New MySqlCommand
Dim myConnection As MySqlConnection
Dim pathx = "c:\temp\test.jpg"
Dim DocName As String
Dim sql As String
DocName = MDIParent1.Organisation + "-CompanyLogo"
Sql = "Select docbin from DocEmbed where DocName='" + DocName + "'"
myConnection = New MySqlConnection(MDIParent1 .MyDbConn)
Dim myReader As MySqlDataReader
Try
If myConnection.State <> ConnectionState.Open Then myConnection.Open()
cmd.Connection = myConnection
cmd.CommandText = sql
myReader = cmd.ExecuteReader
While myReader.Read()
Dim data As Byte() = DirectCast(myReader.GetVal ue("DocBin "), Byte())
Using stream As New FileStream(pathx, FileMode.Create, FileAccess.ReadWrite)
Using writer As New BinaryWriter(stream)
writer.Write(data)
writer.Close()
End Using
End Using
End While
myReader.Close()
Catch ex As Exception
' Log your exceptions here
MessageBox.Show("There was an error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
If myConnection IsNot Nothing AndAlso myConnection.State = ConnectionState.Open Then myConnection.Close()
If cmd IsNot Nothing Then
cmd.Dispose()
cmd = Nothing
End If
End Try
End Sub
Thank you for the reply, below is the code to test if it works, I am getting an error on the line: Dim data As Byte() = DirectCast(myReader.GetVal
I would be most grateful if you could help me solve this problem
Thanks in advance
Alan Welsford
Private Sub ExpToolStripMenuItem_Click
Dim cmd As New MySqlCommand
Dim myConnection As MySqlConnection
Dim pathx = "c:\temp\test.jpg"
Dim DocName As String
Dim sql As String
DocName = MDIParent1.Organisation + "-CompanyLogo"
Sql = "Select docbin from DocEmbed where DocName='" + DocName + "'"
myConnection = New MySqlConnection(MDIParent1
Dim myReader As MySqlDataReader
Try
If myConnection.State <> ConnectionState.Open Then myConnection.Open()
cmd.Connection = myConnection
cmd.CommandText = sql
myReader = cmd.ExecuteReader
While myReader.Read()
Dim data As Byte() = DirectCast(myReader.GetVal
Using stream As New FileStream(pathx, FileMode.Create, FileAccess.ReadWrite)
Using writer As New BinaryWriter(stream)
writer.Write(data)
writer.Close()
End Using
End Using
End While
myReader.Close()
Catch ex As Exception
' Log your exceptions here
MessageBox.Show("There was an error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
Finally
If myConnection IsNot Nothing AndAlso myConnection.State = ConnectionState.Open Then myConnection.Close()
If cmd IsNot Nothing Then
cmd.Dispose()
cmd = Nothing
End If
End Try
End Sub
How are you saving to DocBin and what is the Field type for DocBin in the database?
-saige-
-saige-
ASKER
Hi Saige
Below is my upload code....
The field is a blob.
Private Sub CompanyLogoToolStripMenuIt em_Click(s ender As Object, e As EventArgs) Handles CompanyLogoToolStripMenuIt em.Click
Dim DialogResult As Integer
Dim ImportFile As String
'Dim q As Int16 = 1
Dim i As Integer
Dim ExNonQuery As String = "Err"
Dim myConnection As MySqlConnection
Dim myCommand As MySqlCommand
'Dim record As Integer = 0
Dim DocName As String
Dim sql As String
Dim sqlreturn
Dim FileSize As UInt32
Dim rawData() As Byte
Dim fs As FileStream
DocName = MDIParent1.Organisation + "-CompanyLogo"
DialogResult = MsgBox("Bitmap file 102 x 366 to be used as Company logo", MsgBoxStyle.OkCancel)
If DialogResult <> 1 Then Exit Sub
DialogResult = OpenFileDialog1.ShowDialog (Me)
If DialogResult = 1 Then
ImportFile = OpenFileDialog1.FileName
sql = "select ts from DocEmbed where organisation='" + MDIParent1.Organisation + "' and DocName='" + DocName + "'"
sqlreturn = ExQuery(sql, "S")
Try
fs = New FileStream(ImportFile, FileMode.Open, FileAccess.Read)
FileSize = fs.Length
rawData = New Byte(FileSize) {}
fs.Read(rawData, 0, FileSize)
fs.Close()
myConnection = New MySqlConnection(MDIParent1 .MyDbConn)
If sqlreturn = "" Then
sql = "Insert DocEmbed set doctype='bmp',docsize='" + Format(FileSize) + "',organisation='" + MDIParent1.Organisation + "', DocName='" + DocName + "', DocPath='', DocBin=@image_data "
Else
sql = "Update DocEmbed set doctype='bmp',docsize='" + Format(FileSize) + "',DocBin=@image_data , ts=null where organisation='" + MDIParent1.Organisation + "' and DocName='" + DocName + "'"
End If
myCommand = New MySqlCommand(sql, myConnection)
myCommand.Parameters.AddWi thValue("@ image_data ", rawData)
myCommand.Connection.Open( )
i = myCommand.ExecuteNonQuery( )
myCommand.Connection.Close ()
Catch ex As Exception
MessageBox.Show("There was an error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
If i = 1 Then
MessageBox.Show("The image has been uploaded", "Result", MessageBoxButtons.OK, MessageBoxIcon.Information )
Else
MessageBox.Show("The image could not be uploaded", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
End If
End Sub
Below is my upload code....
The field is a blob.
Private Sub CompanyLogoToolStripMenuIt
Dim DialogResult As Integer
Dim ImportFile As String
'Dim q As Int16 = 1
Dim i As Integer
Dim ExNonQuery As String = "Err"
Dim myConnection As MySqlConnection
Dim myCommand As MySqlCommand
'Dim record As Integer = 0
Dim DocName As String
Dim sql As String
Dim sqlreturn
Dim FileSize As UInt32
Dim rawData() As Byte
Dim fs As FileStream
DocName = MDIParent1.Organisation + "-CompanyLogo"
DialogResult = MsgBox("Bitmap file 102 x 366 to be used as Company logo", MsgBoxStyle.OkCancel)
If DialogResult <> 1 Then Exit Sub
DialogResult = OpenFileDialog1.ShowDialog
If DialogResult = 1 Then
ImportFile = OpenFileDialog1.FileName
sql = "select ts from DocEmbed where organisation='" + MDIParent1.Organisation + "' and DocName='" + DocName + "'"
sqlreturn = ExQuery(sql, "S")
Try
fs = New FileStream(ImportFile, FileMode.Open, FileAccess.Read)
FileSize = fs.Length
rawData = New Byte(FileSize) {}
fs.Read(rawData, 0, FileSize)
fs.Close()
myConnection = New MySqlConnection(MDIParent1
If sqlreturn = "" Then
sql = "Insert DocEmbed set doctype='bmp',docsize='" + Format(FileSize) + "',organisation='" + MDIParent1.Organisation + "', DocName='" + DocName + "', DocPath='', DocBin=@image_data "
Else
sql = "Update DocEmbed set doctype='bmp',docsize='" + Format(FileSize) + "',DocBin=@image_data , ts=null where organisation='" + MDIParent1.Organisation + "' and DocName='" + DocName + "'"
End If
myCommand = New MySqlCommand(sql, myConnection)
myCommand.Parameters.AddWi
myCommand.Connection.Open(
i = myCommand.ExecuteNonQuery(
myCommand.Connection.Close
Catch ex As Exception
MessageBox.Show("There was an error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
If i = 1 Then
MessageBox.Show("The image has been uploaded", "Result", MessageBoxButtons.OK, MessageBoxIcon.Information
Else
MessageBox.Show("The image could not be uploaded", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
End If
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Saige
I will only be able to try this out over the weekend... thanks so much for you info. Will respond during the weekend.
I will only be able to try this out over the weekend... thanks so much for you info. Will respond during the weekend.
This comment; https:#a41775343; provides an adequate and testable solution to the authors question.
-saige-
-saige-
Open in new window
-saige-