Save Image to mySQL and Retrieve Image?

[JOHN PATTEN]

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