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
glop11294484
  • 19
  • 3
  • 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? – AlwaysLearning Nov 07 '22 at 23:29
  • Does `$value` start with `0x`? – Nick.Mc Nov 08 '22 at 02:32
  • Single quotes around text indicates the values are varchar not integers. If database expects integers than remove the single quotes. – jdweng Nov 08 '22 at 04: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 – glop11294484 Nov 08 '22 at 20:57
  • See the answer here for the format it has to be in in order to update/insert into SQL https://stackoverflow.com/questions/1120689/how-can-i-insert-binary-file-data-into-a-binary-sql-field-using-a-simple-insert it needs to look something like this: `0x378068....`, It must start with `0x` and must have no spaces – Nick.Mc Nov 09 '22 at 01:54
  • Avoid injection issues by parameterising your query: `$sqlCommand.CommandText = "UPDATE dbo.Table SET Document = @value WHERE Unique_ID ='123'"`; then insert the value like so: `$sqlCommand.Parameters.Add("@value", SqlDbType.VarBinary).Value = $value` – JohnLBevan Nov 09 '22 at 16:10
  • You should be able to add your solution as an answer and accept it... noting of course it was my suggestion ;) – Nick.Mc Nov 10 '22 at 11:30

0 Answers0