Save Image to mySQL and Retrieve Image?

[Peter Haworth]

Hi John,

Couple of things.

In your call to revExecuteSQL, I think the variable name containing the
image data should be preceded by "*b".  I say I think because the
dictionary says to do that if it holds binary data but since you're base64
encoding it, I believe that results in only ASCII characters. Nevertheless,
you're putting it into a BLOB column ( I think) so maybe LC uses that as
its criteria rather than the actual contents of the variable, so:

revExecuteSQL tConID, tSQL,  "tDate", "tComment", "*btImage"

In your call to revDatabaseColumnNamed, timage needs to be in quotes:

get revDatabaseColumnNamed(tRecordSet, "image", "tImage")

Also, try checking the result after each call to makes sure no errors
occurred.  After the INSERT, it should contain 1 and after the
revDatabaseColumnNamed, it should be empty.

Also curious as to the reason for base64 encoding the data.  If you're
putting it into a BLOB column, it should accept binary data without a
problem.


Pete
lcSQL Software <http://www.lcsql.com>
Home of lcStackBrowser <http://www.lcsql.com/lcstackbrowser.html> and
SQLiteAdmin <http://www.lcsql.com/sqliteadmin.html>

On Mon, Jun 8, 2015 at 3:10 PM, JOHN PATTEN <johnpatten at me.com> wrote:

> Hi All,
>
> I have a a mobile app that takes a photo and POSTs the base64Encoded image
> to an .lc server script that saves it to a mySQL field.
>
> set the vis of templateImage to false
>    mobilePickPhoto "camera"
>    put the last image into img "imageHold"
>    put the text of image "imageHold" into tPhotoData
>    put base64Encode(tPhotoData) into tPhotoData
>    set the cImageData of img "ImageHold" to tPhotoData
>    reset the templateImage
>    put urlEncode(cd fld "comments") into $Comments
>   put the cImageData of img "imageHold" into tImage
>   put "comments=" & $Comments & "&image=" & tImage into tArgList
>   post tArgList to URL "http://servername.on-rev.com/ipad_1/add_image.lc”
>
> The .lc script:
> <?lc
> put revOpenDatabase ("mysql”,"servername.on-rev.com”,"servername_images”,"servername_images”,"password")
> into tConID
> put "images_data" into tTableName
>
> put $_POST["comments"] into tComment
> put the internet date into tDate
> put $_POST["image"] into tImage
>
> put "_date, comments, image" into tFields
>
>
> put "INSERT INTO" && tTableName && "(" & tFields & ") VALUES (:1, :2,
> :3);" into tSQL
>
> revExecuteSQL tConID, tSQL,  "tDate", "tComment", "tImage"
>
> revCloseDatabase tConID
> put empty
>
> ?>
>
> I can see that there is content in the fields in the table and BLOB field
> displays [BLOB - 64KiB]. So I’m assuming the image data is in the field.
>
> On the App I am queuing the db by record ID and trying to display the
> image from the DB.
>
>  put revOpenDatabase ("mysql","servername.on-rev.com","server_images","server_images","password")
> into tConID
>    put revQueryDatabase(tConID, "SELECT * FROM images_data WHERE _id=" &
> field "db_id" & ";") into tRecordSet
>    get revDatabaseColumnNamed(tRecordSet, "image", tImage)
>    put base64Decode(tImage) into tImage
>    set the text of image "db_image" to tImage
>    RevCloseCursor tRecordSet
>    revCloseDatabase tConID
>
>
> The image turns into a gray fill? No image. I seem to remember this
> happening in the past but can’t remember the solution?
>
> Thank you!
>
> John Patten
> SUSD
>
>
>
> _______________________________________________
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode