Retrieving BLOB image from MySQL

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
sujithsamuel
Posts: 2
Joined: Fri Nov 29, 2013 8:04 am

Retrieving BLOB image from MySQL

Post by sujithsamuel » Fri Nov 29, 2013 8:32 am

Hi

In a MYSQL DB table I have a description and an image. The image is a BLOB. I directly put the image in the DB, the image is a JPG file. In Livecode I have to retrieve the image from the DB and display it in an image Area on the stack.

There are no errors in the code and while debugging I can see that the binary data is being retrieved, but it does not display in the image area ("imgBox").

Code: Select all

on mouseUp
    
   ## Connect to the database
   put "mysql" into theDBType
   put "127.0.0.1:3306" into theDBHost
   put "traffic" into theDBName
   put "root" into theDBUser
   put "root" into theDBPassword
   put revOpenDatabase( theDBType, theDBHost, theDBName, theDBUser, theDBPassword ) into theConnectionID
   
   if theConnectionID is an integer then
      ## Query the database for data
      put "SELECT * FROM signs where idsigns ='1'" into tQuery
      put revQueryDatabase( theConnectionID, tQuery, "tIndex") into theCursor
      if theCursor is not empty then
         //take the desc from the Query result
         put revDatabaseColumnNamed(theCursor, "desc") into tDesc
         //Show the dec in the TextBox
         put tDesc into field "imgDesc"
         //take the image from the Query result
         put revDatabaseColumnNamed(theCursor, "signimg", "tImageData") into tErrorMsg
         //Show the image in the imageBox
         put base64decode(tImageData) into theImage
         set the text of image "imgBox" to theImage
         show image imgBox
      else
         answer "Error Cursor: Reading from DB"
      end if
      //Close the Cursor with Query Result
      revCloseCursor theCursor 
   else
      answer "Error connecting to the database:" && theConnectionID & "."
   end if
   //Close the DB Connection ID
   revCloseDatabase theConnectionID
end mouseUp
Please let me know if there is an error in the flow.

Thank you for the help.
Last edited by sujithsamuel on Fri Nov 29, 2013 11:21 am, edited 1 time in total.

SparkOut
Posts: 1983
Joined: Sun Sep 23, 2007 4:58 pm

Re: Retrieving BLOB image from MySQL

Post by SparkOut » Fri Nov 29, 2013 10:00 am

Without looking very closely, the first thing that strikes me is the line:

Code: Select all

show image imgBox
Try putting the image control name in quotes:

Code: Select all

show image "imgBox"
Hope it's as simple as that

sujithsamuel
Posts: 2
Joined: Fri Nov 29, 2013 8:04 am

Re: Retrieving BLOB image from MySQL

Post by sujithsamuel » Fri Nov 29, 2013 11:19 am

Nope, fixed that but still dont see the image in the image Area (imgBox).
Please let me know if you think of anything else.

Thanks

bangkok
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 829
Joined: Fri Aug 15, 2008 7:15 am

Re: Retrieving BLOB image from MySQL

Post by bangkok » Fri Nov 29, 2013 2:21 pm

Why do you use the holdervariable for the second revDatabaseColumnNamed and not for the first one ?

Have you tried :

Code: Select all

put revDatabaseColumnNamed(theCursor, "signimg") into tImageData
put base64decode(tImageData) into theImage
set the text of image "imgBox" to theImage

or:
put theImage into image "imgBox"
Further question :
-your query result is ok ? Do you get text in the field "imgDesc" ?

-are you sure of the encoding/format of your image stored in the blob ?

-have you tried with another image format, like PNG ?

Post Reply

Return to “Databases”