uploading and downloading images from Android to MySQL

[seanmiller]

Hi,

I've got a problem that has me stumped. I've done lots of experiments and researched the issue here on the forums to no avail (as of yet). I'd really appreciate some advice. Here's the issue:

I've set up a signature feature in an app. A user signs a form; the form is uploaded to a MySQL database on the cloud; then the user is able to retrieve the signature image from the cloud at a later date. I can get this to work reliably in development, but when I upload the image from the Android version of the app, it shows up in the database, but I can't download and see it subsequently.

Here are the steps to accomplish all this:

1. export snapshot of graphic as PNG then POST to server script/MySQL:

Code: Select all

export snapshot from graphic "signature" of card "finaldrive" to tSignature as PNG
     
         if the environment is "mobile" then
            put "final_drive_id" & "=" & gFinalDriveID & "&" & "signature_role" & "=" & gSignatureRole & "&" & "final_drive_signature" & "=" & base64encode(tSignature) into tPostString
      else
         put libURLFormData("final_drive_id",gFinalDriveID,"signature_role",gSignatureRole,"final_drive_signature",base64encode(tSignature)) into tPostString
      end if
   
      post tPostString to URL gServerPath & "final_drive_signature.lc"

2. On the server, the script is as follows:

Code: Select all

<?lc

// filename: final_drive_signature.lc

put $_POST["final_drive_id"] into tFinalDriveID
put $_POST["signature_role"] into tSignatureRole
put $_POST["final_drive_signature"] into tFinalDriveSignature

put revOpenDatabase("mysql","localhost","DATABASE","USERNAME","PASSWORD") into tConnectionID

if tConnectionID is a number then
	set itemDel to tab
		
	if tSignatureRole is "student" then
		revExecuteSQL tConnectionID, "UPDATE final_drives SET final_drive_student_signature = (:1) WHERE final_drive_id = '" & tFinalDriveID & "'","tFinalDriveSignature"
		
	else if tSignatureRole is "instructor" then
		revExecuteSQL tConnectionID, "UPDATE final_drives SET final_drive_instructor_signature = (:1) WHERE final_drive_id = '" & tFinalDriveID & "'","tFinalDriveSignature"
	end if
	
	put the result into tResult
	
	revExecuteSQL tConnectionID, "UPDATE final_drives SET last_updated = NOW() WHERE final_drive_id = '" & tFinalDriveID & "'"
	
	put tResult
end if

revCloseDatabase tConnectionID

?>

This updates a TEXT column in a table in a MySQL database with the base64-encoded PNG image. Using a BLOB column or JPEG (comes out black with gray text) doesn't seem to work.

3. To retrieve the saved image:

Code: Select all

<?lc

// filename: get_signature.lc

put $_POST["final_drive_id"] into tFinalDriveID
put $_POST["signature_role"] into tSignatureRole

put revOpenDatabase("mysql","localhost","DATABASE","USERNAME","PASSWORD") into tConnectionID

if tConnectionID is a number then
	set itemDel to tab
	
	if tSignatureRole is "student" then
		
		put revQueryDatabase(tConnectionID,"SELECT * FROM final_drives WHERE final_drive_id = " & tFinalDriveID & ";") into tFinalDriveRecord
		get revDatabaseColumnNamed(tFinalDriveRecord,"final_drive_student_signature",tSignature)
		
	else if tSignatureRole is "instructor" then
		
		put revQueryDatabase(tConnectionID,"SELECT * FROM final_drives WHERE final_drive_id = " & tFinalDriveID & ";") into tFinalDriveRecord
		get revDatabaseColumnNamed(tFinalDriveRecord,"final_drive_instructor_signature",tSignature)
		
	end if
		
	revCloseCursor tFinalDriveRecord
	
	put tSignature
end if

revCloseDatabase tConnectionID

?>

And in the app itself:

Code: Select all

if the environment is "mobile" then
            put "final_drive_id" & "=" & gFinalDriveID & "&" & "signature_role" & "=" & gSignatureRole into tPostString
            else   
               put libURLFormData("final_drive_id",gFinalDriveID,"signature_role",gSignatureRole) into tPostString
            end if
            
            post tPostString to URL gServerPath & "get_signature.lc"
            
            if it is not empty then
               set the text of image "signatureContainer" of card "finaldrive" to base64decode(it)
end if

Why do you think this works in the development environment, but not on Android?

Regards,
Sean

[dave.kilroy]

Hi Sean, I would try urlencoding your base64 data (remember you don't have to urldecode in your .lc script as apache does this for you automatically)...

Dave

[seanmiller]

Great tip, Dave! I really appreciate it. I made this change to the POST code and was able to get the image to upload and download from the cloud on Android:

Code: Select all

export snapshot from graphic "signature" of card "finaldrive" to tSignature as PNG
     
         if the environment is "mobile" then
            put "final_drive_id" & "=" & gFinalDriveID & "&" & "signature_role" & "=" & gSignatureRole & "&" & "final_drive_signature" & "=" & urlEncode(base64encode(tSignature)) into tPostString
      else
         put libURLFormData("final_drive_id",gFinalDriveID,"signature_role",gSignatureRole,"final_drive_signature",base64encode(tSignature)) into tPostString
      end if
   
      post tPostString to URL gServerPath & "final_drive_signature.lc"

Best,
Sean