PowerApps: Storing Photos in SQL - Use varchar(max)
HTML-код
- Опубликовано: 1 фев 2025
- Want a MASSIVE improvement in performance when working with photos (from the camera control) in PowerApps and SQL Server? Store the photo data in a varchar(max) rather than an image field.
Hat tip to • PowerApps : Saving Ima... - although that video is looking at SharePoint lists it gave me the idea of trying varchar(max).
My original video on using the Camera control with SQL in PowerApps see • Using Camera Control I...
Note: This works with images taken by the Camera control - not images taken/added using the Add Picture control. For a solution for working with Add Picture and Pen Input see my new video here: • PowerApps: Save images...
Thanks in a million. Great content. Awesome. Very well explained. I couldn't find this explanation--simply put anywhere else. Great teachers are hard to find. Grade: A++💥
Again, a very valuable share.thanks paul
Very good job and thank you for sharing your acknowledgment.
Great post Paul, thank you very much!
Paul, great video. One point on the video, this will only work if you use the Camera control, if a user is using the Add Media and/or Pen control the data pushed to the varchar(max) field will not work.
Thanks very much Evan for pointing that out. Will make it clearer in the description.
Hi Evan - I took a look at the Add Picture and Pen controls and got a similar method to work: ruclips.net/video/qX3i8456YwU/видео.html
I'm giving away the ending, but if you save the images from Add Picture/Pen to an Image data type in SQL, you can use the For Json command in a query to convert the data to a base64 URI type string in a View which you can use as the source for the Gallery. Again, this gives big performance gains over using the Image datatype to populate the gallery.
Still Helpful thank you!
Thank you for your great work and great help. Marek
Hi Paul, this is really great! I had problems uploading images to CDS, since the quality was so poor. So I am going to try to upload them as text. Have you tried with CDS btw? Do you know if the image quality is good using text, and is it also faster?
Paul, great job. I was able to store photos from my phone via PowerApps to a SQL Server database table as varchar(max). What I'm trying to do now is display those images on an access 2016 form. I haven't figured that out yet. Any ideas? Thx!
Hi Paul
Sorry for delayed response...
In case you are still having difficulty, here is an approach that may help.
To give extra flexibility, I'd Patch the image in Binary format to SQL (so it is stored in binary format) and use a View (with FOR JSON AUTO as described in this video: ruclips.net/video/qX3i8456YwU/видео.html) so that the data can be returned to PowerApps in the Base64 text format that renders so much more quickly in the PowerApps image control.
With the data in SQL stored in Binary format, you could use that column to populate an image in an Access 2016 form as described here: stackoverflow.com/questions/7769908/how-do-i-display-an-image-from-sql-server-with-microsoft-access
Hi Paul, your videos are very brilliant and everything I try is working well or give me a good direction to create my power apps but i still have a small problem with the Patch command for my SQL images there is the code " Patch('[dbo].[Images]';Defaults('[dbo].[Images]');{PhotoAsImage:Camera1.Photo;PhotoAsText:Camera1.Photo}) " but it says that Patch need a collection to work so at this point my [dbo].[images] doesn't recognize as a collection. In my exemple a using semi-columns because i work most of the time in french but for the collection the way i write it is the way the system suggest
thank's
Happy new year
Hi Vincent. Happy New Year to you too.
Do you have a primary key defined on your SQL table? Without a primary key, PowerApps does not consider your table a valid collection.
Paul O'Flaherty thank you i forgot to create one i will come back to let you know
@@pauloflaherty6757 It's much more easier with a primary key it work's fine now thank's again
can we store and retrieve documents like pdf etc from powerapps to sql server ?
Yes. You can use the add media control to select any file type (though it defaults to only showing image types) and save that to SQL. That said, SQL Server is not the best place to store files (storage is more expensive than other options and is not as fast as Blob Storage) - when this video was made it was free to use SQL with Powerapps which made it a reasonable (but not a technically ideal) option, however you now need additional licences which also allow you to connect to Azure Blob Storage, so I would recommend using SQL for your tabular data and Azure Blob Storage for your file (binary) data, whether you are using PowerApps or some other front end technology.
Hey Paul, What MIME type do these output as? I am trying to display these in an SSRS report with no luck. Sean
Thanks
Paul hi - awesome idea! Have you succeeded doing this with the regular image control? (i.e.g take the .image from an image control to a label.text to SQL varchar(max)?) When I try this the intermediate field (the label field) captures the url for the image and not the image binary in text. Same on the SQL server. any idea how to make it work this way?
Hi Tiran
I haven't tried that scenario. I think rather than trying to get the image data from the intermediary image control, you would need to get the image data directly from the source that the image control is based upon.
What is the source for your image control? Have you looked at perhaps using Flow to get the data from the source and append it to SQL Server?
Hi Paolo, I have a similar need. On Powerapps, I would like to create a text input and I would like to convert it to pdf and save it in a sql table. Is there any way? Thanks in advance
Hello Dear , have you achieve this ? I have similar requirement ?