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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.