Save Image to mySQL and Retrieve Image?
johnpatten at me.com
Tue Jun 9 01:29:54 CEST 2015
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!
> 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
> 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:
>> 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
>> use-livecode mailing list
>> use-livecode at lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your
>> subscription preferences:
> use-livecode mailing list
> use-livecode at lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription preferences:
More information about the use-livecode