In one of the projects I’m currently working on, I needed to insert a PNG file into a database column of type varbinary.
I failed with my first attempts, but thanks to some blog posts and some threads on StackOverflow, I was able to come up with the following solution by assembling some of the information I found on the Internet.
# to create a hex-string from a PNG file execute the following commands in PowerShell
# replace C:\PATH\TO\FILE.png with the path to the file you want to convert
[Byte[]]$bytes = Get-Content -Encoding Byte C:\PATH\TO\FILE.png
$hexString = ($bytes | ForEach-Object ToString X2) -join ''
$valueToBeInserted = '0x' + $hexString
$valueToBeInserted | clip
After the execution of the above commands, the value to be inserted into the database column of type varbinary is on the clipboard. To add the value to the database column either directly insert it using Microsoft SQL Server Management Studio or by executing the following insert into statement:
-- replace TABLE_NAME, COLUMN_NAME and PASTE_VALUE_TO_BE_INSERTED_HERE
INSERT INTO [dbo].[TABLE_NAME] ([COLUMN_NAME])
VALUES (PASTE_VALUE_TO_BE_INSERTED_HERE);
Works out of the box, Thanks Marc for sharing! To validate the hex output I used a random online tool, maybe this could be done in PowerShell as well?
LikeLike
Hi Michael. There is for sure a way to do so in PowerShell. However it was not necessary in my case as I could test it with an application that consumed the values from the database.
LikeLiked by 1 person