Solved

Migration of files and datatypes

  • 8 October 2020
  • 3 replies
  • 74 views

Badge +1

Hello,

Our business has an old database table which stores/contains files using VARBINARY(MAX) as the datatype.  It is my understanding that this datatype is not supported in K2 Five and thus I cannot create a SmartObject on this table and display these files using a SmartForm.  

The goal is to somehow migrate these files out of the current database table, and upload them into a different table in the format that K2 recognizes.  Which, from a DB perspective using VARCHAR(MAX) as the datatype, looks something like this - 

<file><name>NameOfFile.pdf</name><content>iVBORKh.….</content></file>

My question is….what is the best approach to achieve this objective?  Can .NET code we written to somehow cycle through these old DB records/files and upload them into our existing table via a K2 API? Or even call the existing table’s own SmartObject from .NET code in order to upload the files?

Thanks,

Kyle

icon

Best answer by boringNerd 9 October 2020, 09:32

Hi,

 

As you have pointed out, K2 stores the files into the database in the following format:

 

<file><name>NameOfFile.pdf</name><content>iVBORKh.….</content></file>

 

So you provide the name of the file in <name></name>, but the value inside <content></content> is going to be a little tricky. This value is actually a Base64-encoded value of your entire file. 

 

Just to show you how this work, you can paste this Base64 values into this tool and retrieve the actual file:

aGVsbG8gd29ybGQ=

 

I got this Base64 value by encoding a simple file using this converter.

 

You can definitely write a simple .NET or C# application to do this. You just need to loop through your database, download the file, grab the name of the file, Base64-encode the file, piece together the different parts into a string that resembles the above, and store it into the new database.

View original

3 replies

Badge +13

Hi,

 

As you have pointed out, K2 stores the files into the database in the following format:

 

<file><name>NameOfFile.pdf</name><content>iVBORKh.….</content></file>

 

So you provide the name of the file in <name></name>, but the value inside <content></content> is going to be a little tricky. This value is actually a Base64-encoded value of your entire file. 

 

Just to show you how this work, you can paste this Base64 values into this tool and retrieve the actual file:

aGVsbG8gd29ybGQ=

 

I got this Base64 value by encoding a simple file using this converter.

 

You can definitely write a simple .NET or C# application to do this. You just need to loop through your database, download the file, grab the name of the file, Base64-encode the file, piece together the different parts into a string that resembles the above, and store it into the new database.

Userlevel 1
Badge +4
is your old database sql server?

 

 

Badge +1

Thank you! This info was helpful as we were able to achieve our objective by converting the files into a Base64-encoded value.

Reply