MySQL Using UPDATE for saving a picture
janschenkel at yahoo.com
Mon Dec 18 15:52:21 EST 2006
--- baleareninsel at gmx.net wrote:
> Hi everybody,
> Trying to save a picture (.jpg or .bmp) to mySQL
> I Use
> put the imagedata of image "Foto_Image" into temp3
> ## works
> put "UPDATE Customers SET Fomage = '"& temp3 & "'
> where kndnr =" & kundennummer into SQL_Befehl
> In the Variable Watcher I can see the SQL_Befehl as:
> UPDATE knddaten SET Foto_image = ' --- here are
> crazy signs -- ' where kndnr = 3120 ## which looks
> The Error Message shown by the Message box is:
> You have an error in your SQL Syntax; check the
> manual that corresponds to your mySQL server version
> for the right syntax to use near ' ## the crazy
> signs, but not so much as there are in the picture
> The problem seems to be, that the image itself will
> not be transfered.
> The type of Table Customers and Field Foto_image is
> set to BLOB
> Again, I need a helpfull hand
> best regards and thank you
I'm not sure if this has been mentioned yet, but one
way to ensure nothing messes up the query is by
encoding the image data in a safe format on the way to
the database, and decoding it when ypu get it back out
of the database.
The easiest solution for that is undoubtedly 'base64'
encoding. See the 'base64encode' and 'base64decode'
functions. The result of encoding binary data using
base64 is a longer string, but it doesn't contain any
of the unsafe characters that will mess up the query.
As an example:
put base64encode(image "foobar") into tBase64ImgData
put 123456 into tImgID
put "UPDATE img_table SET img_data = :1 WHERE img_id
= :2" \
revExecuteSQL gConnectionID, tQuery,
And when you get the data back, you'll do something
put 123456 into tID
put "SELECT * FROM img_table WHERE img_id = :1" into
put base64decode(tData) into image "foobar"
Hope this helped,
Quartam Reports for Revolution
"As we grow older, we grow both wiser and more foolish at the same time." (La Rochefoucauld)
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
More information about the Use-livecode