Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 469
  • Last Modified:

Image BLOB, Read, MySQL

Hi

I'm trying to fix my code so I can read a image from  BLOB filed in my MySQL table and then put it in my picturebox.

Here is the code

Public Sub GetBLOBImage(ByVal TableName As String, ByVal FieldName As String, ByVal PictureBox As PictureBox)
    '// Create the SQL query
    Dim QueryString As String = "SELECT Image FROM tbl_employed WHERE EmpNr = 1234"""
    Dim rawData() As Byte
    Dim FileSize As UInt32
    Dim fs As FileStream
 
    '// The "Using" block will automatically dispose of the connection when we're finished
    Using MyConnectionMySQLOpen As New MySqlClient.MySqlConnection(m_strConnectionString)
 
        Try
            '// Open the DB connection
            MyConnectionMySQLOpen.Open()
 
            '// Create a new command object
            Dim cmd As New MySqlClient.MySqlCommand()
 
            '// Set command properties
            With cmd
                .Connection = MyConnectionMySQLOpen
                .CommandType = CommandType.Text
                .CommandText = QueryString
            End With
 
            '// Execute the SQL query with the command object, and get the affected rows in the DB back
            Dim myData As MySqlDataReader = cmd.ExecuteReader
 
            '// Close the connection
            MyConnectionMySQLOpen.Close()
 
            If Not myData.HasRows Then Throw New Exception("There are no BLOBs data")
 
            myData.Read()
 
            '////////////////// HERE is where I need the help to complete the code//////////////////////////
            FileSize = myData.xxxxxx
            rawData = New Byte(CInt(FileSize)) {}
 
            myData.GetBytes(myData.GetOrdinal(FieldName), 0, rawData, 0, CInt(FileSize))
 
            PictureBox.Image = xxxxxxxxxxxxxx
 
        Catch MyException As MySqlException
            Throw
        Catch ex As Exception
            Throw
        Finally
            '// Close connection if an exception was thrown before the connection could close
            If MyConnectionMySQLOpen.State = ConnectionState.Open Then
                MyConnectionMySQLOpen.Close()
            End If
        End Try
    End Using
 
End Sub

Open in new window

0
AWestEng
Asked:
AWestEng
  • 2
  • 2
1 Solution
 
mankowitzCommented:
There is a forum article that gives some sample code.
Check out http://forums.mysql.com/read.php?38,6172,186617#msg-186617

' This assumes that the filesize is stored as a separate column in the table
FileSize = myData.GetUInt32(myData.GetOrdinal("filesize"))
rawData = New Byte(FileSize) {}
'get the bytes and filesize
myData.GetBytes(myData.GetOrdinal("file"), 0, rawData, 0, FileSize)
Dim ad As New MemoryStream(100000)
Dim bm As Bitmap
ad.Write(rawData, 0, FileSize)
bm = New Bitmap(ad)
0
 
AWestEngAuthor Commented:
oki. but must I have the filesize in the table?

is it not possible to solve it otherwise?
0
 
mankowitzCommented:
Yes, you should be able to do it without the filesize, but you would have to do a separate query which returns a scalar, like this:

 MyCon = New MySqlConnection("your connection string here")
            MyCon.Open()
            Dim str As String = "SELECT image_field FROM table_name WHERE
id=1"
            MyCmd = New MySqlCommand(str, MyCon)
            Dim b() As Byte
            b = MyCmd.ExecuteScalar()
            If (b.Length > 0) Then
                Dim stream As New MemoryStream(b, True)
                stream.Write(b, 0, b.Length)
                picturebox1.image= New Bitmap(stream)
                stream.Close()
            End If
            MyCon.Close()
0
 
AWestEngAuthor Commented:
oki, thx I will test it and get back to you
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now