Inserting image into SQL Server column of type VARBINARY(MAX) using Powershell

Question

I'm stuck. I'm trying to insert a picture from my hard drive to a SQL Server column of type VARBINARY(MAX). I have it converting to something, but I'm not even sure what it is. What comes out looks like "81 69 20 0 81 69 20 0 81 69 20 0 81 69 20 0 81 69 20 0 81 69 20 0 81" but much longer. In my update command if I replace $file with 01234 that updates without any problem so I'm almost sure it's a matter of converting it into the proper format whatever that may be.

$i = 1
$shape|foreach{
if ($shape.Item($i).name.Substring(0, 7) -eq 'Picture')
    {
        #write-host $shape.Item($i).name
        $shape.Item($i).copy()

        #write-host $firstChart.name
        $firstChart.paste()
        $firstChart.Export("c:\temp\pictures\image1.jpg", "JPG")
        #$firstChart.Delete

        [Byte[]]$file = get-content -Encoding Byte C:\TEMP\pictures\image1.jpg

        #$file = [convert]::ToBase64String((get-content C:\TEMP\pictures\image1.jpg -encoding byte))

        $cmd.CommandText ="UPDATE QuoteData SET PII_Image1 = $file Where QuoteNumber =  '"+$WorkSheet.Range('G7').Text.replace("'","''")+"'"
        $cmd.ExecuteNonQuery()
    }
    $i++
}

VARBINARY stores binary bytes. Why are you encoding it as base64 string? The proper encoding would be hexadecimal like 0x234fd12349767b or something — UnhandledExcepSean
– UnhandledExcepSean, Commented Oct 24, 2018 at 17:06
That was just a test. I have it comment out. The format you listed is what I'm looking for. — Zach
– Zach, Commented Oct 24, 2018 at 18:09
This has me in hex format, but I can't seem to get rid of the spaces with replace(" ",""). $file = gc -encoding byte "c:\temp\pictures\image1.jpg" |% {("{0:x}" -f $_)}; — Zach
– Zach, Commented Oct 25, 2018 at 13:27
Can you post the value of $file? Or atleast the first dozen characters or so? — UnhandledExcepSean
– UnhandledExcepSean, Commented Oct 25, 2018 at 13:59
Sure it's "ff d8 ff e0 0 10 4a 46 49 46 0 1 1 1 0 c0". My thoughts are if I could get the spaces out and add 0x to the front it might take it. — Zach
– Zach, Commented Oct 25, 2018 at 14:11

UnhandledExcepSean · Accepted Answer · 2018-10-25 17:48:40Z

3

Your byte array needs converted into a hexadecimal representation. Please note the $hexString line was added and the $cmd.CommandText was changed.

    [Byte[]]$file = get-content -Encoding Byte C:\TEMP\pictures\image1.jpg
    $hexString = ($file|ForEach-Object ToString X2) -join ''
    $hexString = '0x'+$hexString 

    $cmd.CommandText ="UPDATE QuoteData SET PII_Image1 = $hexString Where QuoteNumber =  '"+$WorkSheet.Range('G7').Text.replace("'","''")+"'"

edited Oct 25, 2018 at 17:48

answered Oct 25, 2018 at 14:38

UnhandledExcepSean

12.9k4 gold badges38 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Zach Over a year ago

Thank you. That was a massive leap forward. It's complaining about "The identifier that starts with is too long. Maximum length is 128." which I'm working on now.

Zach Over a year ago

Ok I figured out the identifier issue. I had to add the 0x to hexString before putting it in the SQL statement. It works great now!

Zach Over a year ago

I don't want to admit how many hours I spent trying to figure this out so thank you :). Also do you know why adding the 0x in the sql command would have made it think $hexString was a column name?

UnhandledExcepSean Over a year ago

@Zach it was most likely malformed sql. Probably the apostrophes inside the quoted string

Collectives™ on Stack Overflow

Inserting image into SQL Server column of type VARBINARY(MAX) using Powershell

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related