how to get picture from mysql database

[robman]

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]

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

[robman]

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

[Mark]

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

[robman]

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]

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?