Create File From Image Stored in SQL Server Table

  • 16 March 2017
  • 4 replies
  • 314 views
S
Badge +4

Hi All. I am new to K2 so still in learning mode, so bear with me. I am building an application that stores an Image File to a SQL Server table. It works fine in terms of viewing in K2 and storing in the table. I am trying to write a process on the database side that creates an image file to the O/S. I have used both BCP method as well as sp_OACreate 'ADODB.Stream' to generate the file, which it does but the image is not readable. When I look at the data, I notice what appears to be xml tags at the beginning of the record so I am figuring that is part of my problem. I tried removing the tags but to no avail. Has anyone dealt with this before and can provide me with some guidance?

 

Thanks,

Steve

 

 


14354i18967993BFD514FF.png

4 replies

K
Badge +9

 

 

Hi Steven_rossi,

 

Your file data is in xml format, first use xml node method (in sql server) to fetch filename and file contents as column values while fetching convert file content as Text () datatype as this can hold your large binary data.

 

 

 

Example:

Declare @xml xml= '
<File>
<name>1</name>
<content>43423492394923492323fsd</content>
</File>
<File>
<name>2</name>
<content>43423492394923492323fsd7753r7ye7ryf78ye3734234234234</content>
</File>
<File>
<name>3</name>
<content>4</content>
</File>'

 

SELECT @xml.value('(/File//name/node())[1]','varchar(50)') AS FileName,
cast ( @xml.value('(/File//content/node())[1]','varchar(max)') as Text )as FileContent

 

Then try to use DataLabel with Expression having html Image tag to show it in your smartform

 

 Example:

  Add column with datalabel in ListView and check literal option and add expression as shown in below image

 

 

 

 

S
Badge +4
Hi Kran. This is awesome. Thank you so much for your response. I can definitely see how the use of the expression builder will come in handy now. Where I am stuck at this point though is how to take that File contents and actually make a file on the operating system. We are wanting to build a process (TSQL procedure I was hoping) that takes that file content out of the table record and build the file image from it not to re display again back in K2. Is there some guidance you can point me in the right direction for? Thanks again! Steve
K
Badge +9

Hi  Steven rossi,

 

there are two ways to store imge in localpath  from k2 smartforms

 

1. using k2 service broker ( you can create  custom broker service to store document in local path or can download from k2 market )

2. Sql command (you can use below sample as you have decided to proceed with this)

from the image, you have added in first post it appears your content is in base64 format, so I have added code to separate content first from xml format and then converted it to binary hexastring to store  as image in local path.  

 

There are two ways to store image in localpath

 

 

Declare @xml xml

Declare @FileContent nvarchar(max)

DECLARE @ObjectToken INT

Declare  @ImagePath nvarchar(max)

Declare  @BinaryContent varbinary(max)

 

/* store your data to xml variable first*/

set @xml=(Select  ([imagebinarydata])  from [userimages] where IMAGEID='09AB2B83-8E52-4D49-8B65-9DC515852512')

/* From xml variable  get file content */

set @FileContent= (SELECT cast ( @xml.value('(/content/node())[1]','varchar(max)') as Text )as FileContent)

/* convert file content to HexString */

set @BinaryContent=( select cast(N'' as xml).value('xs:base64Binary(sql:variable("@FileContent"))', 'varbinary(MAX)'))

 

/*now pass hex value to genrate Image in local path*/

 

set @ImagePath='e:Imagetest.jpg'

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT

EXEC sp_OASetProperty @ObjectToken, 'Type', 1

EXEC sp_OAMethod @ObjectToken, 'Open'

EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @BinaryContent

EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @ImagePath , 2

EXEC sp_OAMethod @ObjectToken, 'Close'

EXEC sp_OADestroy @ObjectToken

 

 

let me know if it helps.

Cheers !

 

 

 

S
Badge +4

This is amazing Kran. Thank you so much. I dug into it a bit more myself after I posted and once I relized it was base64 and needed to be converted to binary to create the file, I found the following site helpful which utilized tables and bcp to do it, however, your method is more elegant. Thank you so much for taking the time to help me. Learning something new everyday with K2 and SQL Server!

 

http://stackoverflow.com/questions/35647967/how-do-i-save-base64-encoded-image-data-to-a-file-in-a-dynamically-named-subfold

 

Thanks,

Steve

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings