Failed to convert parameter value from a PSObject to a Byte[] (PowerShell)

Question

I am attempting to insert some data into a azure sql database using powershell. I am a powershell beginner. The data I am inserting comes from a json file.

The data I am trying to insert is: (1)Property1 of the json which is a string. (2)Property2 of the json which is a string . (3) the entire content of the json file as a string.

However I'm getting this error:

Exception calling "ExecuteNonQuery" with "0" argument(s): "Failed to convert parameter value from a PSObject to a Byte[]."

If I try to use: $Command.Parameters["@JsonContent"].Value = $jsonContent.ToString() - Then I get an empty/blank string inserted into the table

This is my code: Please could you help me see the problem

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "Server=$server;Database=$database;User 
ID=$username;Password=$password;Trusted_Connection=False;"

if($Connection.State -eq 'Closed')
{
  $Connection.Open()
}
else 
{
  $Connection.Open()
}


$insertStatement = "INSERT INTO $table (P1, P2, JsonContent) 
VALUES (@P1, @P2, @JsonContent)"


$Command = New-Object System.Data.SqlClient.SqlCommand($insertStatement, $Connection)
$Command.Parameters.Add("@P1", [System.Data.SqlDbType]::VarChar, 250)
$Command.Parameters.Add("@P2", [System.Data.SqlDbType]::VarChar, 250)
$Command.Parameters.Add("@JsonContent", [System.Data.SqlDbType]::NVarChar, -1)

Get-ChildItem -Path $jsonFilesPath -Filter *.json | ForEach-Object {
$jsonContent = Get-Content $_.FullName | ConvertFrom-Json -Depth 100

# Set the parameter values for the SQL insert statement
$Command.Parameters["@P1"].Value = $jsonContent.P1
$Command.Parameters["@P2"].Value = $jsonContent.P2
$Command.Parameters["@JsonContent"].Value = $jsonContent


# Execute SQL insert statement
$Command.CommandText = $insertStatement
$Command.ExecuteNonQuery()

# Output the filename to the pipeline log
Write-Host "Inserted $($_.FullName) into $table"
}


$Connection.Close()

The table I am trying to insert into is defined as:

CREATE TABLE [dev].[TestTable]
(
[P1] [varchar](250) NOT NULL,
[P2] [varchar](250) NOT NULL,
[JsonContent] [nvarchar](max) NOT NULL,
[JsonHash]  AS (hashbytes('SHA2_256',[JsonContent])) PERSISTED,
CONSTRAINT [pkTestTable] PRIMARY KEY CLUSTERED 
(
[P1] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) 
ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

mklement0 · Accepted Answer · 2023-05-04T02:34:26.213

It may not solve all your problems, but let me address an obvious one:

You're reading *.json files, so it makes no sense to pipe them to ConvertTo-Json; perhaps you meant ConvertFrom-Json [update: you've since changed to ConvertFrom-Json in your question], i.e. perhaps your intent is to parse the JSON text into an object graph - which is the prerequisite for being able to access properties .P1 and .P2

So perhaps this is what you meant:

Get-ChildItem -Path $jsonFilesPath -Filter *.json | ForEach-Object {
  
  # Read the JSON text as a single, multi-line string
  $jsonText = $_ | Get-Content -Raw

  # Parse the JSON text into an object graph, so you can access properties.
  $objectGraphFromJson = $jsonText | ConvertFrom-Json
  
  # Set the parameter values for the SQL insert statement
  $Command.Parameters["@P1"].Value = $objectGraphFromJson.P1
  $Command.Parameters["@P2"].Value = $objectGraphFromJson.P2
  $Command.Parameters["@JsonContent"].Value = $jsonText
  
  # Execute SQL insert statement
  $Command.CommandText = $insertStatement
  $Command.ExecuteNonQuery()
  
  # Output the filename to the pipeline log
  Write-Host "Inserted $($_.FullName) into $table"
}

Note:

It is $jsonText, i.e. the original JSON string that is assigned to the JsonContent SQL parameter.
- Note that with your own code, once you changed to ConvertFrom-Json, you would have assigned a [pscustomobject] a.k.a [psobject] instance instead (the object graph that JSON was parsed into), which would explain the symptom.
As an aside re your use of the -Depth parameter:
- There is usually no need for -Depth when reading JSON (ConvertFrom-Json - except if your JSON has more than 1024(!) nesting levels).
- However, -Depth is important when writing JSON (ConvertTo-Json), because the default depth is then, unfortunately, 2. See this answer for more information.

Failed to convert parameter value from a PSObject to a Byte[] (PowerShell)

1 Answers1