0

In the following powershell script I am attempting to call a Rest API, that returns a binary encoding of a file, and update a particular VARBINARY cell in my SQL server table with the content of the response.

$headers=@{}
$headers.Add("accept", "text/plain")
$headers.Add("authorization", "Bearer xyz")
$response = Invoke-WebRequest -Uri 'https://www.formstack.com/api/v2/download...' -Method GET -Headers $headers

$value = $response.Content
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=myServer; Database =myDB; User ID=uid; Password=pwd;"
$sqlConnection.Open()
$sqlCommand=$sqlConnection.CreateCommand()
$sqlCommand.CommandText = "UPDATE dbo.Table SET Document = $value WHERE Unique_ID ='123';"
$sqlCommand.ExecuteNonQuery()

I run into the following error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect syntax near '80'."

The "80" appears to be the second element of the byte array in $response.Content.

Is there a way to resolve this? This code otherwise works for inserting random values into the table, but I can't figure out how to insert the binary content.

UPDATE :

I got it to work by converting the byte array to a hexdecimal string with the following code:

$hexString = ($value | ForEach-Object ToString X2) -join ''
$valuehex = '0x' + $hexString
7
  • Are you sure it's binary when requesting text/plain? As with C#, have you tried to use an SqlParameter specifying the varbinary dbtype and length -1? Commented Nov 7, 2022 at 23:29
  • Does $value start with 0x? Commented Nov 8, 2022 at 2:32
  • Single quotes around text indicates the values are varchar not integers. If database expects integers than remove the single quotes. Commented Nov 8, 2022 at 4:19
  • @Nick.McDermaid no, the output of the $value variable is something like 37 80 68 etc. If I execute $value.GetType() I get the following: Name : Byte[] BaseType: System.Array Commented Nov 8, 2022 at 20:57
  • See the answer here for the format it has to be in in order to update/insert into SQL stackoverflow.com/questions/1120689/… it needs to look something like this: 0x378068...., It must start with 0x and must have no spaces Commented Nov 9, 2022 at 1:54

0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.