Out of Memory error loading image from Mysql to picture box VB.net

[KenBZim2]

Hi there, using VB2008 and Mysql Database:

The following code is intended to write a jpeg file into the Mysql database as a BLOB, then select the BLOB image from the
database and assign it to a picture box.
The image is sucessfully stored in the database, but an out of memory error occurs when attempting to assign it to the picture box


The Mysql Datatable is constructed as follows:
CREATE TABLE `graphic_images` (
`graphic_image_id` INTEGER(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`image_blob` MEDIUMBLOB,
PRIMARY KEY USING BTREE (`graphic_image_id`),
UNIQUE KEY `graphic_image_id` USING BTREE (`graphic_image_id`)
) ENGINE=InnoDB
AUTO_INCREMENT=1 CHARACTER SET 'latin1' COLLATE 'latin1_swedish_ci'
COMMENT='InnoDB free: 9216 kB';

Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
      'Insert the graphics image from a jpeg file
            Insert_Graphics_Image_Into_Graphic_Images_Table("C:\IMG_2342.jpg")
 End Sub 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
      'Retrieve The Image From the database
        Dim MyPicture As Image = Nothing
         Try
           
                MyPicture = Retrieve_Graphics_Image_From_Table(1)
           PictureBox1.BackgroundImage = MyPicture
        Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try
End Sub



Public Function Insert_Graphics_Image_Into_Graphic_Images_Table(ByVal Path_And_File_Name As String) As Boolean
        Dim connection As New MySqlConnection(Database_Connection_String)
        Dim Command As New MySqlCommand("Insert into graphic_images (image_blob) Values (@image_blob)", connection)

        'Create the image object
        Using Picture As Image = Image.FromFile(Path_And_File_Name)
            'Create an empty stream in memory
            Using Stream As New IO.MemoryStream
                'Fill the stream with Binary Data From the Image
                Picture.Save(Stream, Imaging.ImageFormat.Jpeg)

                'Get an array of bytes from the stream and assign to the parameter
                Command.Parameters.Add("@image_blob", MySqlDbType.VarBinary).Value = Stream.GetBuffer()
            End Using
        End Using
        connection.Open()
        Command.ExecuteNonQuery()
        connection.Close()

    End Function
    Public Function Retrieve_Graphics_Image_From_Table(ByVal Graphic_ID As Integer) As Image
        Try
            Dim connection As New MySqlConnection(Database_Connection_String)
            Dim Command As New MySqlCommand("Select image_blob from graphic_images Where graphic_image_id=" & Str(Graphic_ID).Trim, connection)

            connection.Open()
            Dim PictureData As Byte() = DirectCast(Command.ExecuteScalar(), Byte())
            connection.Close()
            Dim Picture As Image = Nothing

            'Create a stream in memory containing the bytes that make up the image
            Using Stream As New IO.MemoryStream(PictureData)
                'Read The Stream and create an image object from its contents
                Picture = Image.FromStream(Stream)

            End Using
            Return Picture
        Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try


    End Function

[jmcilhinney]

Firstly, if you're loading the image from a file just to save it then there's no point calling Image.FromFile and then creating a MemoryStream to get a Byte array. Just call File.ReadAllBytes to get a Byte array directly from the file.

Assuming that your issue remains after doing that, does it happen the first time you try to load an image or do you have to do it several times?

[jmcilhinney]

Also, it's rather silly to take an Integer parameter in your method and convert that to a String and Trim it in order to insert it into your SQL code. How could a String created from an Integer possibly need trimming for one thing? I'd sugg4est always using parameters with SQL code but, if you are going to stick with string concatenation in this case, where it's sure to be safe, why not just do this:

Dim Command As New MySqlCommand("Select image_blob from graphic_images Where graphic_image_id=" & Graphic_ID, connection)

[KenBZim2]

Hi there, thankyou for you advice. I have made some changes, but am now getting a different error
'Parameter Is Not Valid'
This is the revised code:

I welcome any help or further suggestions

Private Sub ToolStripButton2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ToolStripButton2.Click
       'Upload the image
            Insert_Graphics_Image_Into_Graphic_Images_Table("C:\IMG_2342.jpg")
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim MyPicture As Image
       Try
            MyPicture = Retrieve_Graphics_Image_From_Table(1)
          Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try
End Sub

Public Function Insert_Graphics_Image_Into_Graphic_Images_Table(ByVal Path_And_File_Name As String) As Boolean
        Dim Connection As New MySqlConnection(Database_Connection_String)
        Dim Command As New MySqlCommand("Insert into graphic_images (image_blob) Values (@image_blob)", Connection)
        Dim MyByteArray() As Byte
        Try
            MyByteArray = IO.File.ReadAllBytes(Path_And_File_Name)
            Dim Stream As New MemoryStream(MyByteArray)
            Command.Parameters.Add("@image_blob", MySqlDbType.VarBinary).Value = Stream
            Connection.Open()
            Command.ExecuteNonQuery()
            connection.Close()
        Catch Ex As Exception

Private Sub Retrieve_Graphics_Image_From_Table()
        Try
            Dim connection As New MySqlConnection(Database_Connection_String)
            Dim Command As New MySqlCommand("Select image_blob from graphic_images Where graphic_image_id=2", connection)

            connection.Open()
            Dim BufferData As Byte() = DirectCast(Command.ExecuteScalar(), Byte())
            connection.Close()
            Dim ms As New MemoryStream(BufferData)
            PictureBox1.Image = New Bitmap(ms)
            
          'Dim MyImage As Image = Image.FromStream(ms)
          'PictureBox1.BackgroundImage = MyImage

            ms.Close()


        Catch Ex As Exception
            MsgBox(Ex.Message)
    'Error Returned is 'Parameter Is Not Valid'
        End Try

End Sub

[jmcilhinney]

Get rid of the MemoryStream. Previously you were getting a Byte array from the MemoryStream to insert. I said to get a Byte array directly from File.ReadAllBytes. The MemoryStream has no use there.

[KenBZim2]

I have changed the insert as follows, but now get a different error on the insert:
max_packet_allowed
I have researched this on the internet and found this:
"The largest possible packet that can be transmitted to or from a MySQL 5.7 server or client is 1GB. When a MySQL client or the mysqld server receives a packet bigger than max_allowed_packet bytes, it issues an ER_NET_PACKET_TOO_LARGE error and closes the connection."
This file is nowhere near that size

Public Function Insert_Graphics_Image_Into_Graphic_Images_Table(ByVal Path_And_File_Name As String) As Boolean
        Dim Connection As New MySqlConnection(Database_Connection_String)
        Dim Command As New MySqlCommand("Insert into graphic_images (image_blob) Values (@image_blob)", Connection)
        Dim MyByteArray() As Byte
        Try
            MyByteArray = IO.File.ReadAllBytes(Path_And_File_Name )

            Command.Parameters.AddWithValue("@image_blob", MyByteArray)
            Connection.Open()
            Command.ExecuteNonQuery()
            connection.Close()
        Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try

    End Function

[jmcilhinney]

Maybe try using Add instead of AddWithValue and then setting the size of the parameter to the size of the column in the database. It's a bit of a stab in the dark but I know that AddWithValue can cause issues sometimes. I notice that you were using Add in that location originally and changed it, although you weren't actually setting the parameter size then anyway.