Storing Byte Array (Images) in MSSQL Database issues

Ignition
, , , ,
1

I'm trying to store a screenshot (image) uploaded with the file upload component in perspective into a MSSQL database. The image is retrieved as a byte array, but when I attempt to insert it into the database, I'm encountering speed issues and some errors.

From the file upload component I have an event script on fileUploaded

byte_data = event.file.getBytes()
# old b64 encoding...
self.view.custom.screenshot = byte_data

With this named query

INSERT INTO UserFeedback (userid, FeedbackType, Description, Screenshot)
VALUES (:userid, :FeedbackType, :Description, CONVERT(VARBINARY(MAX), :Screenshot))

the screenshot column is of type varbinary

I got an error

ValueError: byte must be in range(0, 256)

I've also encoded the data in Base64, but that also presented issues (chinese text?). Furthermore, I've been instructed not to store anything in the gateway/server files.

Has anyone faced a similar problem? Any suggestions on how to efficiently store and retrieve images (or large binary data) in a MSSQL database via Ignition without storing on the gateway? Should I look into alternatives like storing the image in the filesystem and keeping only the path in the database?

Any insights or suggestions would be highly appreciated!

2

Do not save the byte data to a Perspective property. That makes it not byte data any more.

Provide byte_data to your named query directly, and do not use CONVERT() in your SQL. (Thinking you need CONVERT is a hint that you were doing it wrong.)

1 Like
3

To give more context, the reason I tried to store the byte_data to a Perspective property is because I use a button to trigger the named query after the user inputs 2 other fields

image
image997Ă—750 19.2 KB

1st. The user uses a file upload component to input an image.
2nd. The user provides feedback type from a dropdown and also a description.
3rd. After the user has inputted all the information, they press a submit button which triggers the named query.

the script of the button looks like this

def runAction(self, event):
	userid = self.session.props.auth.user.id
	
	feedbackType = self.parent.getChild("feedback").getChild("input").getChild("type").props.value
	description = self.parent.getChild("feedback").getChild("input").getChild("descript").props.text
	screenshot = self.view.custom.screenshot
	
	
	if not feedbackType:
	    system.perspective.openPopup('ErrorPopUp', 'SomePath/PopUp', params={'msg': 'Feedback type is missing!'})
	    return
	
	if not description:
	    system.perspective.openPopup('ErrorPopUp', 'SomePath/PopUp', params={'msg': 'Description is missing!'})
	    return
	
	#import base64
		
#	if screenshot is not None:
#	    screenshot = bytearray(base64.b64decode(screenshot))
	
	params = {
	    "userid": userid,
	    "FeedbackType": feedbackType,
	    "Description": description,
	    "Screenshot": screenshot
	}
	system.perspective.print("Type of byte_data: " + str(type(screenshot)))
	system.db.runNamedQuery("Feedback/insertFeedback", params)

how can I hold the byte data from the moment the image is uploaded, to the moment the submit button is pressed? This is why I tried saving the byte array to a custom property temporarily.
Should I save it in a temp file and after the write delete it?

just for reference this was my initial named query

IF :Screenshot IS NULL
   INSERT INTO UserFeedback (userid, FeedbackType, Description)
   VALUES (:userid, :FeedbackType, :Description)
ELSE
   INSERT INTO UserFeedback (userid, FeedbackType, Description, Screenshot)
   VALUES (:userid, :FeedbackType, :Description, :Screenshot)
4

That's an option, but I would just put it in the database unconditionally. If you don't like it after the fact, you can just tell the DB to delete that row.

5

If I undestood correctly, od you mean to first insert the image in the database table and then associate/link the feedback type and description with it afterward? Maybe using the last inserted ID?

then add to the query
something like SELECT SCOPE_IDENTITY() and instead update that row with the 2 other values? or make conditional queries if image is null

6

There are a number of choices. Either is fine. Or consider a separate table, to which they can add however many screenshots they need. Also consider using two separate pages. One to capture the feedback type and description, and the other to review and optionally add a screenshot. That way, the upload handler will already have the type and description available, and could send a message to close that page immediately after upload.

1 Like
7

That’s a good way to go about it. How would I capture several pictures? Would the byte array encapsule them all? And then to display them, would they “count” as 1 image?

8

No, separate events for each upload. Save them in the DB separately, with a reference to the row id holding the type and description.

No, your display UI would need to have a table or something to show however many images are attached to the feedback.

9

Right… just join them on feedbackId.
Thanks!

10

BTW, that isn't reliable in Ignition, due to multi-threading and connection pooling. Use the getKey argument to the relevant scripting functions to retrieve a newly inserted row's ID.

1 Like
11

Good tip. I was using it in another view to show the created id upon write (I am developing user customizable dashboards) , I’ll switch to getKey