# Storing Byte Array (Images) in MSSQL Database issues **URL:** \ **Category:** Ignition\ **Tags:** ignition81, perspective, scripting, database, python\ **Created:** [August 18, 2023, 3:29pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082 "2023-08-18T15:29:01Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Mariano\_Alvarez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mariano_alvarez/32/35283_2.png) [@Mariano\_Alvarez](https://forum.inductiveautomation.com/u/Mariano_Alvarez)\ **Post date:** [August 18, 2023, 3:29pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/1 "2023-08-18T15:29:01Z")
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 ```plaintext byte_data = event.file.getBytes() # old b64 encoding... self.view.custom.screenshot = byte_data ``` With this named query ```plaintext 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! ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 18, 2023, 3:40pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/2 "2023-08-18T15:40:57Z")
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.) ---
**Author:** ![Mariano\_Alvarez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mariano_alvarez/32/35283_2.png) [@Mariano\_Alvarez](https://forum.inductiveautomation.com/u/Mariano_Alvarez)\ **Post date:** [August 18, 2023, 3:55pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/3 "2023-08-18T15:55:41Z")
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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/3/4/34095bb72395c98f098bb1629ae72af74f09ca59.png) 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 ```plaintext 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 ```plaintext 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) ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 18, 2023, 3:56pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/4 "2023-08-18T15:56:56Z")
> [@Mariano\_Alvarez](#): > > Should I save it in a temp file and after the write delete it? 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. ---
**Author:** ![Mariano\_Alvarez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mariano_alvarez/32/35283_2.png) [@Mariano\_Alvarez](https://forum.inductiveautomation.com/u/Mariano_Alvarez)\ **Post date:** [August 18, 2023, 4:08pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/5 "2023-08-18T16:08:59Z")
> [@pturmel](#): > > put it in the database unconditionally 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 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 18, 2023, 4:12pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/6 "2023-08-18T16:12:59Z")
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. ---
**Author:** ![Mariano\_Alvarez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mariano_alvarez/32/35283_2.png) [@Mariano\_Alvarez](https://forum.inductiveautomation.com/u/Mariano_Alvarez)\ **Post date:** [August 18, 2023, 4:18pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/7 "2023-08-18T16:18:49Z")
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? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 18, 2023, 4:23pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/8 "2023-08-18T16:23:26Z")
> [@Mariano\_Alvarez](#): > > Would the byte array encapsule them all? No, separate events for each upload. Save them in the DB separately, with a reference to the row id holding the type and description. > [@Mariano\_Alvarez](#): > > And then to display them, would they “count” as 1 image? No, your display UI would need to have a table or something to show however many images are attached to the feedback. ---
**Author:** ![Mariano\_Alvarez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mariano_alvarez/32/35283_2.png) [@Mariano\_Alvarez](https://forum.inductiveautomation.com/u/Mariano_Alvarez)\ **Post date:** [August 18, 2023, 4:25pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/9 "2023-08-18T16:25:30Z")
Right… just join them on feedbackId. Thanks! ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [August 18, 2023, 4:26pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/10 "2023-08-18T16:26:30Z")
> [@Mariano\_Alvarez](#): > > `SELECT SCOPE_IDENTITY()` 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. ---
**Author:** ![Mariano\_Alvarez](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/mariano_alvarez/32/35283_2.png) [@Mariano\_Alvarez](https://forum.inductiveautomation.com/u/Mariano_Alvarez)\ **Post date:** [August 18, 2023, 4:37pm UTC](https://forum.inductiveautomation.com/t/storing-byte-array-images-in-mssql-database-issues/78082/11 "2023-08-18T16:37:55Z")
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