Solved

Image BLOB, Read, MySQL

Posted on 2008-06-19
4
449 Views
Last Modified: 2013-11-26
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
Comment
Question by:AWestEng
  • 2
  • 2
4 Comments
 
LVL 24

Expert Comment

by:mankowitz
ID: 21826448
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
 
LVL 1

Author Comment

by:AWestEng
ID: 21829343
oki. but must I have the filesize in the table?

is it not possible to solve it otherwise?
0
 
LVL 24

Accepted Solution

by:
mankowitz earned 500 total points
ID: 21834494
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
 
LVL 1

Author Comment

by:AWestEng
ID: 21837863
oki, thx I will test it and get back to you
0

Featured Post

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question