how to get picture from mysql database

Creating desktop or client-server database solutions?

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
robman
Posts: 4
Joined: Thu Jun 08, 2006 3:41 pm

how to get picture from mysql database

Post by robman » Tue May 08, 2007 10:13 pm

have a table with flds id and picture which is a blob
trying to get the picture from database and display it in a image fld tried this but does not work

put "SELECT * FROM pictures WHERE pictures.ID = 3" into theSQL
put revQueryDatabaseBLOB(dbID, theSQL) into curID
put revDatabaseColumnNamed(curID,"picture") into tdata
put tdata into image "ctg1"

it just returns the type of picture like this

% PNG

any ideas what I am doing wrong?

Thanks
Rob

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5132
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Post by Mark » Wed May 09, 2007 10:36 am

Rob,

This problem pops up every now and then. I always give the advise to encode the data when storing it on the server, e.g. using base64encode. This means you have to decode it after retreiving it from the data. Do you know at which character (ASCII value) exactly the PNG is cut off?

Best,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

robman
Posts: 4
Joined: Thu Jun 08, 2006 3:41 pm

Post by robman » Wed May 09, 2007 11:25 am

I don't know, how do I find it out?

Mark
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 5132
Joined: Thu Feb 23, 2006 9:24 pm
Contact:

Post by Mark » Wed May 09, 2007 12:53 pm

Dear Rob,

I assume you know what has been stored on the server and you know the length of tData. If the length of tData is 5, I would like to know the ascii value of the last char of tData and the ascii value of the 6th character of your image file. I suspect one of them is NULL or a character that needs to be escaped.

You can avoid all this by using the base64encode function.

Best,

Mark
The biggest LiveCode group on Facebook: https://www.facebook.com/groups/livecode.developers
The book "Programming LiveCode for the Real Beginner"! Get it here! http://tinyurl.com/book-livecode

robman
Posts: 4
Joined: Thu Jun 08, 2006 3:41 pm

Post by robman » Wed May 09, 2007 1:10 pm

this is the mysql picture table
ID int
name varchar 20
picture longblob

I see another post here were you insert picture using base64encode, put I did not use rev to insert the picture, can use the base64decode to retrieve the picture still?

robman
Posts: 4
Joined: Thu Jun 08, 2006 3:41 pm

Post by robman » Wed May 09, 2007 4:22 pm

in the card I have image "ctgfork01"

I then tried to update the mysql database using

put base64Encode(image "ctgfork01") into tBase64ImgData

put 123456 into tImgID

put "UPDATE pictures SET picture = :1 WHERE id = :2" into tQuery

recExecuteSQL dbID, tQuery, "tBase64ImgData", "tImgID"


in the variable watcher I do not see where anything is being put into tBase64ImgData

nothing seems to be changing in the database

Do I need to do something else to change the current image in the database to base64encode?

Post Reply

Return to “Databases”