MySQL Using UPDATE for saving a picture

[Jan Schenkel]

--- baleareninsel at gmx.net wrote:
> Hi everybody,
> 
> Trying to save a picture (.jpg or .bmp) to mySQL
> Database.
> 
> I Use 
>   put the imagedata of image "Foto_Image" into temp3
> ## works
>   put "UPDATE Customers SET Fomage = '"& temp3 & "'
> where kndnr =" & kundennummer into SQL_Befehl
> to_i
> 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
> ok
> 
> 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
> 
> Horst
> 

Hi Horst,

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:
##
on mouseUp
  global gConnectionID
  put base64encode(image "foobar") into tBase64ImgData
  put 123456 into tImgID
  put "UPDATE img_table SET img_data = :1 WHERE img_id
= :2" \
      into tQuery
  revExecuteSQL gConnectionID, tQuery,
"tBase64ImgData","tImgID"
end mouseUp
##

And when you get the data back, you'll do something
like:
##
on mouseUp
  global gConnectionID
  put 123456 into tID
  put "SELECT * FROM img_table WHERE img_id = :1" into
tQuery
  put
revQueryDatabaseBlob(gConnectionID,tQuery,"tImgID") \
      into tCursorID
  put revDatabaseColumnNamed(tCursorID,"img_data")
into tData
  put base64decode(tData) into image "foobar"
  revCloseCursor tCursorID
end mouseUp

Hope this helped,

Jan Schenkel.

Quartam Reports for Revolution
<http://www.quartam.com>

=====
"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 
http://mail.yahoo.com