[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Image BLOB, Read, MySQL

Posted on 2008-06-19
4
Medium Priority
?
463 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month20 days, 6 hours left to enroll

873 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