Upload image to OnPrem SQL Server

[charlied_mc]

I'm trying to upload an image file to my on-premise SQL Server instance. Table schema is as follows:

CREATE TABLE [Attachment]
(
ID INT IDENTITY(1,1)
, ParentType NVARCHAR(20)
, ParentID INT
, Content VARBINARY
, CONSTRAINT PK_Attachment PRIMARY KEY (ID)
)

Using VARBINARY since the IMAGE data type will be deprecated. The tutorial I'm following is here:

https://powerapps.microsoft.com/en-us/blog/upload-files-from-powerapps-to-sql-server/

I think the "Add Picture" control has changed, because the ".Image" property is no longer available. I'm using ".Media" instead but still getting an error. Here's my OnSelect formula for the submit button:

Patch('[dbo].[Attachment]', Defaults('[dbo].[Attachment]'), {ParentType:"Label", ParentID:1, Content:AddMediaButton1.Media})'

Error Message:

The requested operation is invalid. Server Response: Microsoft SQL: String or binary data would be truncated.

What am I missing?

[v-xida-msft · Accepted solution]

Hi @charlied_mc ,

Could you please share a bit more about the error message within your app?

Based on the SQL syntax that you mentioned, I think there is something wrong with the data type you set (VARBINARY) for the Content column in your SQL table.

If you want to store image file within your SQL table, you must create Image type column, or varchar(max) type column or varbinary(max) type column to store the captured image data.

In addition, you could still use the UploadedImage1.Image formula to get captured picture from the Add Picture control.

Please check my response within the following thread for more details:

https://powerusers.microsoft.com/t5/General-Discussion/images-from-azure-sql/m-p/236994#M69602

I have made a test on my side, please take a try with the following workaround:

PowerApps's configuration as below:

Set the OnSelect property of the "Upload Pic" button to following formula:

Patch(
      '[dbo].[Images]', 
        Defaults('[dbo].[Images]'), 
       {
          ImageName: "Beauty,jpg", 
          ImageData: UploadedImage1.Image        /* <-- ImageData column is a varbinary(max) type column */
       }
)

Please take a try to re-add a varbinary(max) type column or varchar(max) type column in your SQL table to store the captured  image data. Or you could consider take a try to re-create a new SQL table on your side, then take a try with above solution I provided, check if the issue is solved.

Best regards,

[charlied_mc]

Thank you Kris! Adding the (MAX) to the VARBINARY column allowed the upload to complete - we are looking good. Now to figure out how to display that image from the table back to PowerApps - any recommendations on that?

Thanks!

Charlie

[v-xida-msft]

Hi @charlied_mc ,

Do you want to display your image data from your SQL table within your PowerApps app?

Based on the needs that you mentioned, I think the Gallery control with Image layout could achieve your needs.

You could consider take a try to add a Gallery control within your app, set the Items property to your SQL Table data source ('[dbo].[Attachment]'). Then within the Gallery control, add a Image control, set the Image property of the Image control to following:

ThisItem.ImageField

On your side, it is:

ThisItem.Content

Please also check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/upload-files-from-powerapps-to-sql-server/

If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

Best regards,

[Phritz20]

I am attempting to utilize this solution on a Canvas app I'm developing and it is working fine, except that when using the app on the mobile platform, it is not sending over the full quality version of the photograph or at least what the phone is capable of.

When using the app in the development environment, it's obviously grabbing whatever image file I browse to upload and it is a duplicate copy bit for bit. So works there.

But on mobile devices, it's only uploading about a 250kb file when I'm sure that the phone would normally be generating at least a 1.5MB or more file. I can't figure out how to fix or change this.

The test Canvas app I created as optimized for Tablet just for testing, but I don't see why that would change this.

[Phritz20]

Nevermind, I'm stupid. I found that the iOS app has a default setting turned on to "Optimize images for upload" My bad.