Display Picture from MySQL into Web Listbox Cell

Targets Web
1

Hello everyone

I am trying to display images stored as blob in MySQL into Web Listbox cells.
The following are my code snippets. I cant display the images. Can anybody point out my mistakes? Thanks in advance. rs is Rowset filled with database rows having
the image at the column SitePicture

If rs <> Nil Then
rs.MoveToFirstRow
dim i as integer
var SitePicture() as WebPicture
var picCell() as webListBoxImageRenderer

SitePicture.ResizeTo(rs.RowCount)
picCell.ResizeTo(rs.RowCount)
for i = 0 to rs.RowCount-1
picCell(i) = new WebListBoxImageRenderer
SitePicture(i) = new WebPicture(rs.Column(“SitePicture”).PictureValue)
picCell(i).URL = SitePicture(i).URL
lbSites.addRow("",rs.Column(“SiteName”))
lbSites.RowTagAt(i) = _
rs.Column(“SiteDatabaseName”).StringValue
lbSites.cellvalueAt(i,0) = picCell(i)
if i < rs.RowCount then rs.MoveToNextRow
next i
end if

2

What exactly happen when you run the code: noting displayed, error message, exception thrown ?

Also please tell what OS version and which Xojo version.

Thanks

3

Have you successfully opened the images using any database tools before trying with Xojo?

I store and open images from a MySQL database, but I convert them to strings before inserting into a Blob or Medium Blob field and when loading from the DB I them convert them from strings back to images. I do this using a MBS plugin and the JPEGStringToPictureMBS method. If you have the MBS plugins this is the easiest and most reliable way to store pictures in a MySQL database. There is an example in the MBS \Examples\Picture folder called Picture To String.xojo_binary_project that will give what you need to convert pictures to a JPEG string and back to pictures. For me this works flawlessly.

One other thing, I store thumbnails in a Medium Blob and the medium or high res images in a Blob. Prior to storing I scale the image to a max width or height so all the images are consistently sized.

Edit: This reply doesn’t address displaying the pictures in a WebListbox , but if you can’t display the images into an ImageViewer from MySQL first you’ll never get them to display in the WebListbox. I’m just wanting to verify you can display them first before tackling the WebListbox. I personally did not have much luck storing images as blobs until I converted them to strings. YMMV

4

you must convert pictures to strings before storing them in a database field
as @Tom_Dixon explained above
if you don’t have the MBS plugins, you can use the xojo encodebase64 (with a linewrap of zero)
and decodebase64
beware that mysql can have some exotic textEncodings for its default storage.

5

Thank you for the response guys. I ll try some of the suggestions and update later.

6

I ve managed to make the images load in the cell
It turns out that the images were not created if it is not forcefully loaded to a visual control on the web page. Perhaps as part of the optimization process. But this behavior should not be happening.

Forcefully loading the images into a web image viewer which are embedded into the web page but hiding it ( make it visible = false) ensures that the images are really forcefully created and the web cell image renderer now has a reference to a proper image and can draw it. The updated codes look like this now

If rs <> Nil Then
  rs.MoveToFirstRow
  dim i as integer
  var SitePicture() as  WebPicture
  var picCell() as  webListBoxImageRenderer 
  var ivBuffer() as WebImageViewer // <--- new addition
  
  SitePicture.ResizeTo(rs.RowCount) 
  picCell.ResizeTo(rs.RowCount)
  ivBuffer.ResizeTo(rs.RowCount) // <--- new addition
  for i = 0 to rs.RowCount-1
    picCell(i) = new WebListBoxImageRenderer
    SitePicture(i) = new WebPicture(rs.Column("SitePicture").PictureValue)
    
    ivBuffer(i) = new WebImageViewer // <--- new addition
    ivBuffer(i).Visible = true       // <--- new addition
    self.AddControl(ivBuffer(i))     // <--- new adiition
    
    picCell(i).URL = SitePicture(i).URL
    lbSites.addRow("",rs.Column("SiteName"))
    lbSites.RowTagAt(i) = _
    rs.Column("SiteDatabaseName").StringValue
    if i < rs.RowCount then rs.MoveToNextRow
  next i
  
  // give time for the image to load properly
  for i = 0 to rs.RowCount-1
    lbSites.cellvalueAt(i,0) = picCell(i)
    //  new addition, ensure image is really loaded
    ivBuffer(i).Picture = SitePicture(i) 
  next i
  
end if

Now the images load properly.
This is a very unexpected behavior. I’m confident and hopeful that Xojo team can be rectify it in the near future.

image
image1920Ă—1080 125 KB

2 Likes