Save Image to mySQL and Retrieve Image?

[Peter Haworth]

Hi John,
It's sounds like it might be a problem with either the .lc script or what
you do with the data after you get it back from the database and put it
into the image, neither of which is an area I'm familiar with.

One thing you might try is writing the data to a file with the appropriate
extension for the type of image data and then see if you can open it with
whatever program on your computer displays that type of data.  If that is
successful, then sounds like it's something to do with the way you load the
data into the Livecode image control.

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 4:29 PM, JOHN PATTEN <johnpatten at me.com> wrote:

> Thanks Peter,
>
> I made those changes, quotes around get revDatabaseColumnNamed(tRecordSet,
> "image", "tImage”) and *b for the BLOB. I also checked the result and it
> reported a “1” on the INSERT command and empty on the
> RevDatabaseColumnNamed call.
>
> But it is still filling the image object with a gray fill and no image?  I
> even eliminated the base64 routines with no luck. This changed the data
> reported in the BLOB field to: [BLOB- 611 B] from [BLOB - 64 KiB].  But
> still resulted in  a gray fill in the image object when querying for the
> image.
>
> Is there a way to extract the BLOB data manually using phpMyAdmin
> (download) and manipulate the data directly in LiveCode?
>
> Thanks for the advice!
>
> John Patten
> SUSD
>
>
>
>
> > On Jun 8, 2015, at 3:44 PM, Peter Haworth <pete at lcsql.com> wrote:
> >
> > 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
> > _______________________________________________
> > 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
>
>
> _______________________________________________
> 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
>