Retrieving Blob Image MySql

Question

User-1402564948 posted

Hello,

I've mostly been working with Sql Server, but this solution has a MySql database.  

I've been able to insert a record with an image, but I'm having trouble getting the image on the page.

The record shows that there is a blob and has the size.

Edit Edit
 Copy Copy
 Delete Delete
30
497e80e1-772f-4ac4-9964-4eecedf4b898
2016-04-20 08:48:35
[BLOB - 34.4 KiB]
NULL
Upload Photo
NULL

Also, I'm using a Odbc connector which I guess is required to connect to MySql.

Here is my image handler and code.

Imports System.Data.SqlClient
Imports System.Data.Odbc

Public Class getWallImage
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load

        Dim wId As Integer = Convert.ToInt32(Request.QueryString("wId"))
        Dim conn As New OdbcConnection

        'Connect to the database and bring back the image contents & MIME type for the specified picture
        conn.ConnectionString = "Driver=MySQL ODBC 5.1 Driver;server=server;User=User;password=Password;port=Port;database=DB"
        Const SQL As String = "SELECT [ALL] FROM [Walls] WHERE [wId = @wId]"
        Dim myCommand As New OdbcCommand(SQL, conn)
        myCommand.Parameters.AddWithValue("@wId", wId)

        conn.Open()
        Dim myReader As OdbcDataReader = myCommand.ExecuteReader

        If myReader.Read Then
            Response.BufferOutput = True
            Response.BinaryWrite(myReader("wImage"))
            Response.ContentType = "Image/JPG"
            Response.ContentType = "Image/gif"
            Response.ContentType = "Image/png"
            Response.End()
        End If 'nothing in the URL as HTTP GET

        myReader.Close()
        conn.Close()
    End Sub

End Class

If FileUpload1 IsNot Nothing Then
                Dim strExtension As String = System.IO.Path.GetExtension(FileUpload1.FileName)
                If (strExtension.ToUpper() = ".JPG") Or (strExtension.ToUpper() = ".GIF") Or (strExtension.ToUpper() = ".PNG") Or (strExtension.ToUpper() = ".PNG") Or (strExtension.ToUpper() = ".JPEG") Then

                    ' Resize Image Before Uploading to DataBase
                    Dim imageToBeResized As System.Drawing.Image = System.Drawing.Image.FromStream(FileUpload1.PostedFile.InputStream)
                    Dim imageHeight As Integer = imageToBeResized.Height
                    Dim imageWidth As Integer = imageToBeResized.Width
                    Dim maxHeight As Integer = 640
                    Dim maxWidth As Integer = 480
                    imageHeight = (imageHeight * maxWidth) / imageWidth
                    imageWidth = maxWidth

                    If imageHeight > maxHeight Then
                        imageWidth = (imageWidth * maxHeight) / imageHeight
                        imageHeight = maxHeight
                    End If

                    Dim bitmap As New Bitmap(imageToBeResized, imageWidth, imageHeight)
                    Dim stream As System.IO.MemoryStream = New MemoryStream()
                    bitmap.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg)
                    stream.Position = 0
                    Dim image As Byte() = New Byte(stream.Length) {}
                    stream.Read(image, 0, image.Length)

                    Dim connstring As String = "string"
                    Dim sqlConn As New OdbcConnection(connstring)
                    'Dim Label126 As DateTime
                    ' Build your query '
                    Dim query = "INSERT INTO Walls (Id, Body, wImage, wDate) VALUES (" + "?, ?, ?, ?)"
                    ' Build your command '
                    Dim cmd As New OdbcCommand(query, sqlConn)
                    sqlConn.Open()
                    
                    ' Add your parameters and execute your query '

                    'cmd.Parameters.Add("ID", OdbcType.UniqueIdentifier).Value = ID()

                    cmd.Parameters.AddWithValue("@Id", User.Identity.GetUserId().ToString)
                    cmd.Parameters.Add("Body", OdbcType.VarChar).Value = Body.Text()
                    Dim UploadedImage As New OdbcParameter("@wImage", OdbcType.Image, image.Length)
                    UploadedImage.Value = image
                    cmd.Parameters.Add(UploadedImage)
                   cmd.Parameters.Add("wDate", OdbcType.DateTime).Value = DateTime.Now
                    cmd.ExecuteNonQuery()

                    Body.Text = String.Empty
                    FileUpload1.Dispose()
                    SettingsUpdatedMessage.Text = "The value was inserted into your database"
                    GridView1.DataBind()
                    sqlConn.Close()
                End If

Thanks for any help!

Answer 1

User-1716253493 posted

try like this

        If reader.HasRows Then
            Do While reader.Read()
               'do something
            Loop
        Else
            Console.WriteLine("No rows found.")
        End If