WebDev doPut issue with casting/encoding of (JPEG) byte arrays

Ignition
,
1

Hi all,
I'm uploading JPEG files to a DB using system.db.runPrepUpdate queries from an onFileReceived action on an upload component.

If I just run the query straight from the upload component, it will insert into the DB perfectly and I'm able to retrieve the data, encode it to base 64 then display it on an image component. On the DB the VARBINARY(MAX) looks like this: 0xFFD8FFE000144A46...

However the image data is send through the websocket, so any image larger than the default 2mb will crash the session. I'm not a fan of increasing the websocket limit either, I'd rather use WebDev to send the larger files to the DB.

Using WebDev, my workflow for this problem is as follows:

  1. (onFileReceived) Using the upload file component, get the filedata of the JPEG image as bytes
  2. (onFileReceived) Execute an INSERT query to the images table and retrieve the primary key (id)
  3. (WebDev) Use HTTP doPut with an internal UPDATE query to add the image bytes into the DB.

This is the onFileRecieved action for the upload component:

	#get the params from the file component
	filedata = event.file.getBytes()

	#run the query to insert the image into the DB and return the new ID use run prep update
	query = "INSERT INTO [dbo].[images] (id) OUTPUT INSERTED.id VALUES (NEWID());"
	db = "SEQIGN01_sw_leviathanCoasterInspection"
	imageId = system.db.runQuery(query, db).getValueAt(0,0)
		
	#Set the text field to the ID of the image
	self.getSibling("LabelImageId").props.text = "Database image ID:\n%s" % str(imageId) 
		
	#get the image file data back from the put
	putReturn = system.net.httpPut(url="http://ignition.vrl.com.au:8088/system/webdev/sw-leviathanCoasterInspection/image",contentType='image/jpg',headerValues={'imageId':imageId,'Accept-Ranges':'bytes'},putData=filedata)
		
	#print the output status
	self.getSibling("LabelError").props.text = putReturn

This is the HTTP doPut action:

def doPut(request, session):
	#get the incoming parameters putData
	imageId = request["headers"]["imageId"]
	filedata = request["data"]
	
	try:
		#Update the image in the database
		query = "UPDATE [dbo].[images] SET image = ? WHERE id = ?"
		args = [filedata, imageId] 
		system.db.runPrepUpdate(query, args, "SEQIGN01_sw_leviathanCoasterInspection")
		
	except Exception as e:
		return {'response': "doPost exception error: " + str(e)}
	
	#Check the filedata in logger (will remove later)
	logger = system.util.getLogger("doPutLogger")
	logger.infof(str(filedata[:100]))
	
	#Check the headers in response (will remove later)
	return {'response':request["headers"]}

For some reason (?) the VARBINARY(MAX) data on the DB looks like this: 0x2F396A2F344141555...

I can see in the gateway logs that the request["data"] is actually a byte array:
array('b', [47, 57, 106, 47, 52, 81, 68...
so the data is getting to the doPut as the raw bytes, but it's not getting to the DB as raw byes?

This is what I've tried to resolve the issue:

  1. Changed the MIME type in the HTTP header to 'application/octet-stream'
  2. Cast the request["data"] in the doPut to bytes(request["data"])

Any help with this would be appreciated! Thanks!

2
  1. The file bytes pass through the websocket on their way to the upload event handler.

The rest foolishness. Just insert to the DB with a parameterized ("Prep") query or a named query. The byte array is the proper type for a proper parameter to insert into a BLOB (varbinary) column. No need to have a separate query to generate the empty row. Just have system.db.runPrepUpdate() return the new ID automatically.

  1. Converting to base64 expands your image size, and is likely why your websocket blows up (on display, not upload).

Since you have WebDev, there's no reason you can't set up a doGet() endpoint that accepts an ID and returns the image content for image components (without base64).

If you wish to avoid WebDev, you might want to look at my Blob Server module for the display operation.

1 Like
3

Oh yes, I've made a real blunder of this.. Thanks for your input - I corrected the workflow to the following:

  1. (onFileReceived) execute an INSERT query returning the key (id) to the image record
  2. (onFileReceived) Set a label text to the returned id from the previous query
  3. (WebDev) Set a binding on the image component source to a doGet where I pass the id and get the base64 string back.

This is the onFileReceived script:

def runAction(self, event):
	self.getSibling("Label").props.text = ""
	
	#setup variables
	filename = event.file.name
	filedata = event.file.getBytes()
	
	try:
		query = "INSERT INTO images (id, image) OUTPUT INSERTED.id VALUES (NEWID(), ?);"
		args = [filedata]
		db = "SEQIGN01_sw_leviathanCoasterInspection"
		imageId = system.db.runPrepQuery(query, args, db).getValueAt(0,0)
		
		#Set the text field to the primary key ID 
		self.getSibling("Label").props.text = imageId
		
	except Exception as e:
		self.getSibling("Label").props.text = str(e)

This is the doGet in WebDev:

def doGet(request, session):
	from java.util import Base64

	imageId = request["headers"]["imageId"]
	
	query = "SELECT [image] FROM [dbo].[images] WHERE id = ?"
	args = [imageId]
	imageBytes = system.db.runScalarPrepQuery(query, args, "SEQIGN01_sw_leviathanCoasterInspection")
	
	image = "data:image/jpeg;base64," + Base64.getEncoder().encodeToString(imageBytes)

	return {"contentType": "image/jpeg","response": image}

This is the binding on the image source:

image
image903×758 45.6 KB

If there's a way to display the image without using Base64 encoding please let me know I've tried changing the "response" to "bytes" in the doGet and written that directly to the image source without any success

Thanks again!

4

Yes, don't write a data url to the image source. Supply a URL to the image component that points at the doGet() endpoint, supplying the ID as a query parameter in that URL. The doGet() should return the byte array as its data, after setting the appropriate content type.

See my blob server module for inspiration. (Or just use it, since your blobs are in the database.) You do not need base64 anywhere in the chain.

1 Like