Link to home
Start Free TrialLog in
Avatar of WelsfordAlan
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.
Avatar of it_saige
it_saige
Flag of United States of America image

Assuming that you have at least the filename and file (as a binary blob), it really is just as simple as:
Imports System.IO

Sub SaveFiles(Optional ByVal path As String = "")
	Dim cmd As New MySqlCommand
	Dim reader As New MySqlDataReader
	If String.IsNullOrEmpty(path) Then path = Path.GetDirectoryName(Application.ExecutablePath)
	Try
		' Making the assumption that you already have your connection object configured
		If conn.State <> ConnectionState.Open Then conn.Open()
		cmd.Connection = conn
		' Name is filename and data is filedata in binary/blob form.
		cmd.CommandText = "SELECT name, data from files"
		reader = cmd.ExecuteReader
		While reader.Read()
			Dim data As Byte() = DirectCast(reader.GetValue("data"), Byte())
			Using stream As New FileStream(Path.Combine(path, reader.GetString("name")), FileMode.Create, FileAccess.ReadWrite)
				Using writer As New BinaryWriter(stream)
					writer.Write(data)
					writer.Close()
				End Using
			End Using
		End While
		reader.Close()
	Catch ex As Exception
		' Log your exceptions here
	Finally
		If conn IsNot Nothing AndAlso conn.State = ConnectionState.Open Then conn.Close()
		If cmd IsNot Nothing Then 
			cmd.Dispose()
			cmd = Nothing
		End If
	End Try
End Sub

Open in new window


-saige-
Avatar of WelsfordAlan
WelsfordAlan

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.GetValue("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.GetValue("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
How are you saving to DocBin and what is the Field type for DocBin in the database?

-saige-
Hi Saige

Below is my upload code....
The field is a blob.

User generated image
 Private Sub CompanyLogoToolStripMenuItem_Click(sender As Object, e As EventArgs) Handles CompanyLogoToolStripMenuItem.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.AddWithValue("@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
ASKER CERTIFIED SOLUTION
Avatar of it_saige
it_saige
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
This comment; https:#a41775343; provides an adequate and testable solution to the authors question.

-saige-