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
UPDATE or INSERT Pictures in MySQL
Moderators: FourthWorld, heatherlaine, Klaus, kevinmiller, robinmiller
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:
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
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"
Please let me know if this works for you.
Regards
Oliver
Oliver Kenyon
Software Developer
Runtime Revolution
Software Developer
Runtime Revolution
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
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
-
- VIP Livecode Opensource Backer
- Posts: 977
- Joined: Sat Apr 08, 2006 7:47 am
- Location: Aalst, Belgium
- Contact:
Try base64 encoding
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:
And when you get the data back, you'll do something like:
Hope this helped,
Jan Schenkel.
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
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
Jan Schenkel.
Quartam Reports & PDF Library for LiveCode
www.quartam.com
www.quartam.com