DB Image Retrieval Problem

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: Klaus, FourthWorld, heatherlaine, kevinmiller, robinmiller

Post Reply
Posts: 16
Joined: Mon Oct 06, 2008 5:45 pm

DB Image Retrieval Problem

Post by jkrische » Sat Aug 08, 2009 9:59 pm

Hello all!

Hoping someone can tell me what I'm doing wrong here.

My stack is a GUI for an SQLlite DB. I have a db table, then in runrev I have a "get picture" button, an image object, and a button set for db record navigation.

The get picture button works correctly, or at least I assume so, at getting an image from an open/save type dialog, b64 encoding it, and storing that in the db table, in an appropriate blob field. My problem is in retrieving that image data from the table and putting it into the image object when navigating records.

my command for retrieving the pict is invoked by:
get_pict "my_target_image_name"

the corresponding function is as follows:

Code: Select all

command get_pict fldname
   global gConID
   global workingrecID
   put "SELECT A.pictdata FROM mytable AS A WHERE A.recID=" & workingrecID & ";" into tSQL
   put revQueryDatabase(gConID,tSQL) into tResultID
   put revDatabaseColumnNamed(tResultID,"pictdata") into  tPictdata
   //set the imageData of image fldname to base64decode(tPictdata)
   put base64decode(tPictdata) into image fldname 
end get_pict
As you can see, I have tried 2 strategies and neither works, and I'm not sure why. The results of using the "set imageData" method are either a black box or a noise image being put into the image object, while the results of the "put b64dec into image" method are apparently null, ie, the image object appears empty.

So, what am I doing wrong?

FYI, I tried and rejected the "set imageSource" method (which DID work in both directions), as it relies on the picture still being at that specific location on the filesystem for all eternity to remain visible to runrev. If that file moves, is deleted, etc, runrev won't be able to show the image. Therefore, I thought it best to import the image into a DB table, where I'm storing everything else in my app anyway.

Posts: 16
Joined: Mon Oct 06, 2008 5:45 pm

Solved my own issue, here's how

Post by jkrische » Sun Aug 09, 2009 7:43 am

Hello again everyone!

I solved my own problem and thought it would be good to post both what the solution was and what the diagnostic process was which revealed the solution so that others might benefit. I'm not claiming any special prowess here, just trying to "give back" by helping to save other developers a headache or two.

The underlying issue was 2-fold. First up was a database problem. The correct SQLite3 field type for storing a base64-encoded image from runrev is NOT "blob" as I had assumed from other non-runrev DB work, but is in fact just plain-jane TEXT. I'm not sure what is going on, maybe SQLite is *also* b64 encoding (or some other enc method) data when storing in a BLOB field or something along those lines. You'll see why I think that in a moment.

The second part of the solution was to use the "put b64dec into image" method for putting the decoded image data into the runrev image object.

The diagnostic process was actually quite simple. The fact that my original output was either a black square or a partially black, partially noise pattern block told me that the binary data being passed to the image object was not correct. Therefore, the next step for me was to examine the underlying b64-encoded string, since I can't look at the binary data directly.

To do this, I temporarily created 2 scrolling text fields on the card. One was named "pict_hexdata" and would get set to the b64encoded text that is being passed to the db for storage; this happens when the user clicks the "get pict" button and picks an image from somewhere on the filesystem. The second field was "pict_hexdata_r", which is used to contain the data that is retrieved from the DB *before b64decoding*. I set them both to a monspace font and left them at the default width & height. By performing each action (set and get), each field should be populated with what should be identical data, that being b64-encoded text. What goes in and what comes out should be the same, right?

But it wasn't.

The db-retrieved version was not the expected string of hex data, which in my case was ~1600 lines of hex characters with each line being 72 characters long. Instead, I got a single, long line of what appears to be some flavor of UTF weirdness, mostly characters outside the normal ASCII set. This told me that what the DB was actually storing was NOT the plain text I was passing to it, nor expecting to retrieve from it, but was somehow further encoded.

So, a simple change to the DB field from BLOB to TEXT made my two hexdata testing fields come out identical, and instead of a noise pattern in only part of the image I got a noise pattern over the whole image space. So, a quick change from one method to another for putting that image data into the image object and presto-chango... the image I picked from the filesystem is there in the image object exactly as was originally intended.

So, that's it. I'm now able to get a picture form the filesystem, store it in a DB record and retrieve it later, showing it on the card or whatever else I want to do with it. This may not seem like a big deal to seasoned devels but to us noobs it could be a stumbling block.

Lastly, here are my get & set functions. The get script is attached to a "get picture" button. The set (retrieve) function is a command defined in the main stack, so that it can be invoked from other objects like record navigation buttons/pulldowns. Note that in each, I assume 2 globals that I commonly work with, gConID (db connection ID) and workingrecID, which stores the ID of current record being shown. Your method for handling these 2 values may differ from mine, but you will need them in some fashion regardless. You'll probably want some error trapping, too.

Code: Select all

(button "get_pict")
on mouseUp
   global gConID
   global workingrecID
   answer file "Select a picture to import:"
   put it into tChosenFile
   put url("binfile:" & tChosenFile) into image "my_imageobj_name"
   put base64encode(url("binfile:" & tChosenFile)) into tBase64ImgData

   put "UPDATE myTable SET pictdata = :1 WHERE recID = :2" into tQuery
   revExecuteSQL gConID, tQuery, "tBase64ImgData","workingrecID" 
end mouseUp

Code: Select all

command retrieve_pict fldname
   // fldname is the name of the image object into which you want the retrieved image to be placed

   global gConID
   global workingrecID
   put "SELECT A.pictdata FROM myTable AS A WHERE A.recID=" & workingrecID & ";" into tSQL
   put revQueryDatabase(gConID,tSQL) into tResultID
   put revDatabaseColumnNamed(tResultID,"pictdata") into  tPictdata
   put base64decode(tPictdata) into image fldname 
end retrieve_pict

Posts: 19
Joined: Fri Jul 01, 2011 11:45 pm

Re: DB Image Retrieval Problem

Post by askari » Fri Jul 01, 2011 11:47 pm

Much thanks for this code and digging through to find a solution, I know I spent a whole night trying to figure it out

Posts: 1
Joined: Mon Nov 07, 2011 6:32 pm

Re: DB Image Retrieval Problem

Post by kug-media » Mon Nov 07, 2011 6:35 pm

Great, thanks for sharing - this save some time of looking into a solution ... :D

Posts: 7
Joined: Wed Jan 04, 2012 4:34 pm

Re: DB Image Retrieval Problem

Post by classic12 » Wed Jan 04, 2012 5:10 pm

I have an existing MYSQL database that has blobfield with images in there.

I have used the following SQL:

on displayDetails
## Query all details
## Put the result into the field
put "SELECT * FROM Tickets WHERE Ticket_No =2" into tSQLQuery
put revDataFromQuery(tab, return, sDatabaseID, tSQLQuery) into field "details"
put "SELECT * FROM Tickets WHERE Ticket_No =3" into tSQLQuery2
put revDataFromQuery(tab, return, sDatabaseID, tSQLQuery2) into field "details2"

put "SELECT Image1 FROM Ticket_images WHERE Ticket_No =1" into tSQLQuery3
put revDataFromQuery(tab, return, sDatabaseID, tSQLQuery3) into image "image1"
put revDataFromQuery(tab, return, sDatabaseID, tSQLQuery3) into field "details3"

end displayDetails

Fields detail & detail2 show okay (as these are showing text fields)
In Image1 I get garbage ˇÿˇ‡
I did try :

put revDataFromQuery(tab, return, sDatabaseID, tSQLQuery3) into bresult1
put bresult1 into image "image1"
The images in the existing table are not encoded in any way. What do I need to do to display the image correctly.



Post Reply

Return to “Talking LiveCode”