cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mike528
Resolver II
Resolver II

Adding PenInput Image to SQL Server varbinary(max) using Flow calling a Stored Procedure

I am capturing a signature with the peninput. I can write teh signature image directly to a SQL table varbinary(max) column easily with a patch(). This image can then be read back and displayed in powerapps and included in a SSRS report. 

 

However, I have the need to call a stored procedure (i.e must use Flow) where this image could be included all in one step.  I create a JSON string to pass the parameters using the JSON() function and IncludeBinaryData option. But this is just a big encoded 64 string that can't be ready with JSON_VALUE() in SQL Server (apparently there is a limit on the the size with the JSON_VALUE() function). And if I pass as a separate parameter, I can't seem to convert the varchar(max) to a valid varbinary(max). 

 

 Any ideas are welcome, but at this point I have to make multiple calls to the DB (which I hate) for this single step.

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @mike528 ,

Could you please share a bit more about your scenario?

Do you want to pass the JSON String value from your canvas app to your flow, then within your flow, call the storage procedure to add the JSON string value into the varbinary(max) column in your SQL Table?

 

Currently, there is an known limit with JSON_VALUE() in SQL Server -- it could only process 4000 characters at most. If the encoded base64 string is more than 4000, the JSON_VALUE() would return null or error.

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

https://bertwagner.com/2018/09/18/extracting-json-values-longer-than-4000-characters/

 

You could consider take a try with the OPENJSON function in your SQL Server storage procedure. The check if the issue could be fixed.

 

As an alternative solution, I think the varchar(MAX) type column could achieve your needs. The varchar(MAX) type column could store 8000 bytes at most. You could add a varchar(MAX) type column in your SQL Table to store the passed encoded base64 string value directly. You need to modify your storage procedure to add value to the varchar(MAX) type column in your SQL Table.

 

Within your canvas app, you need to convert the PenInput image into a encoded base64 string value using the following formula:

Substitute(JSON(PenInput1.Image, JSONFormat.IncludeBinaryData), """", "") // convert PenInput image into encoded base64 string value

then pass the converted string value back to your flow. Within your flow, call the storage procedure action with the passed encoded base64 string value, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

4 REPLIES 4
v-xida-msft
Community Support
Community Support

Hi @mike528 ,

Could you please share a bit more about your scenario?

Do you want to pass the JSON String value from your canvas app to your flow, then within your flow, call the storage procedure to add the JSON string value into the varbinary(max) column in your SQL Table?

 

Currently, there is an known limit with JSON_VALUE() in SQL Server -- it could only process 4000 characters at most. If the encoded base64 string is more than 4000, the JSON_VALUE() would return null or error.

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

https://bertwagner.com/2018/09/18/extracting-json-values-longer-than-4000-characters/

 

You could consider take a try with the OPENJSON function in your SQL Server storage procedure. The check if the issue could be fixed.

 

As an alternative solution, I think the varchar(MAX) type column could achieve your needs. The varchar(MAX) type column could store 8000 bytes at most. You could add a varchar(MAX) type column in your SQL Table to store the passed encoded base64 string value directly. You need to modify your storage procedure to add value to the varchar(MAX) type column in your SQL Table.

 

Within your canvas app, you need to convert the PenInput image into a encoded base64 string value using the following formula:

Substitute(JSON(PenInput1.Image, JSONFormat.IncludeBinaryData), """", "") // convert PenInput image into encoded base64 string value

then pass the converted string value back to your flow. Within your flow, call the storage procedure action with the passed encoded base64 string value, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the reply, I did more searching last night and came across an article that solves my problem by using XQuery in the stored procedure to convert the base64 image string as a varchar(max) and to a varbinary(max) to save to my table. Tested it and it all works as expected, being able to see the signature in the SSRS report. 

 

CAST('' AS XML).value('xs:base64Binary(sql:column("ImageBase64"))', 'VARBINARY(MAX)')

 

Where ImageBase64 contains the base64 text for the image.

 

Also, you idea of using OPENJSON() to get passed the 4K limit on JSON_VALUE() worked as well!

 

Where would we be without developer blogs and google?

 

Thanks 

stapes
Impactful Individual
Impactful Individual

I am trying to implement this solution. I want to save a signature image to SQL. I first converted the image, as you suggest, saving it to a context variable:

UpdateContext({varImage:Substitute(JSON(PenInput1.Image, JSONFormat.IncludeBinaryData), """", "")})

My value now looks something like this:

data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAjEAAAE8CAYAAAAxGeC3AAAAAXNSR0IArs4c6QAAHIRJREFUeF7t3T/oLeldP/...

Next, I try to save it using a Flow. In Sql I have defined the image field as varchar(MAX).

In the Flow, I am using an Initialize Variable control, calling it a String type, and using an Expression to use it as:

string(triggerBody()['varImage_Value'])

When I try to run this, it fails with 

Invalid binary encoding.
     inner exception: Invalid binary encoding.
clientRequestId: bbf50b4d-83ea-4371-97af-d376b4ed6dd2

I have tried using the Initialize Variable control to save it as JSON type too.

This also failed - different error:

Unable to process template language expressions in action 'varImage' inputs at line '1' and column '12433': 'The template language function 'json' parameter is not valid. The provided value 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAjEAAAE8CAYAAAAxGe...

How do I save this signature image to my database? 

stapes
Impactful Individual
Impactful Individual

Sorry - won't let me delete my question. Solved this problem using Patch.

Patch(PPM_Signatures,Defaults(PPM_Signatures),{Image:PenInput1.Image,Date:Now(),FK_AssetDetailID:Gallery2.Selected.ID})

Helpful resources

Announcements
PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,799)