UPDATE or INSERT Pictures in MySQL

[inselfan]

Holá everybody,

Trying to save a picture (.jpg or .bmp) to mySQL Database.

After understanding INSERT, DELETE and, with a lot of help (thanks Mark) also UPDATE, I want to UPDATE also my Pictures. Thinking, it's the same way than a text, but I'm wrong

Reading everything I found in the www, I found out, that I have to sent a "*b" in front of the image. Well but this also does not work. Here is the stack:


I Use
put the imagedata of image "Foto_Image" into temp3
put "UPDATE knddaten SET Foto_image =*b" & temp3 & " WHERE kndnr =" & kundennummer into SQL_Befehl

revexecuteSQL rec_ID, 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 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

[oliverk]

Hi Horst,

I think that what you are doing wrong is passing the actual image data as a parameter to revExecuteSQL.

What you need to do is pass the name of the variable that contains the data, rather than the data itself.

For example:

Code: Select all

local tImageData
put the imageData of image "Foto_Image" into tImageData
put "UPDATE knddaten SET Foto_image=:1 WHERE kndnr=:2" into SQL_Befehl
revExecuteSQL rec_ID, SQL_Befehl, "tImageData", "kundennumber"

Notice here the use of :1 and :2, these are placeholders and they tell Revooution to insert the first and second variables passed to revExecuteSQL into the query in place of :1 and :2 respectively.

Please let me know if this works for you.

Regards

Oliver

[inselfan]

Holá Oliver,

Thanks for your helpfull hand, BUT, I still stay at the same point than before. I get the message:

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 'ÿT'$ÿR&#ÿ?ÿDÿM"ÿW+"ÿ]1$ÿ[0 ÿa1%ÿf6*ÿa2(ÿa2(ÿ_3*ÿ^3*ÿ]5)ÿY8)ÿX;-ÿT=-ÿpYKÿnXK' at line 1

I used your syntax word by word.

Any other idea?

best regards

Horst

[Janschenkel]

Hi Horst,

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:

Code: Select all

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:

Code: Select all

on mouseUp
  global gConnectionID
  put 123456 into tImgID
  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.