I am inserting rows into a table from a Powershell script. I loop through a collection of PSObjects ("property bags") and inserts a row for each one. Each noteProperty on the PSObject corresponds to a column in the table. Most of them are strings, a few are dates, and a few are booleans.
Properties that have a $null value in Powershell are being inserted as "blank" strings and not NULL in the table, however. $null date values show up as 1900-01-01. The table does not have any constraints beyond a couple of columns having NOT NULL specified. If I do a manual insert with NULL as a value, it works fine.
I could loop over each object looking for $null properties and then replace it with the string NULL, but that seems like a hack instead of the "right" way. As you can see below, I'm already doing this for the booleans, as their corresponding columns in the table are bits. I erroneously assumed that $null in Powershell would translate to NULL in SQL. Is there another way I can make it correctly insert NULL into the table if the property has a value in Powershell of $null? Or, is converting $null to the string "NULL" before inserting the best way to do it?
Here's the code I'm using:
$listOfLunsReport = Import-Clixml e:\powershell\myObjectCollection.xml
$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Data Source=SQLSERVER\MYDATABASE;Initial Catalog=my_report;Integrated Security=SSPI;"
$conn.open()
$cmd = New-Object System.Data.SqlClient.SqlCommand
$cmd.connection = $conn
foreach ($lunObject in $listOfLunsReport)
{
if ($lunObject.SnapsConfigured -eq $true)
{
$snapBit = 1
}
else
{
$snapBit = 0
}
if ($lunObject.isDatastore -eq $true)
{
$dsBit = 1
}
else
{
$dsBit = 0
}
$cmd.commandtext = "INSERT INTO listOfLuns (Array,lunNumber,LunUID,CapacityInMB,StorageGroup,`
SnapsConfigured,SnapName,SnapUID,NumberOfSnaps,LatestSnap,VM,PhysicalServer,isDatastore,`
DatastoreName,ReportDate,ArrayModel,ArrayLocation) VALUES ('{0}','{1}','{2}','{3}','{4}',`
'{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}')" -f `
$lunObject.Array,$lunObject.lunNumber,$lunObject.LunUID,[int]$lunObject.CapacityInMB,`
$lunObject.StorageGroup,$snapBit,$lunObject.SnapName,$lunObject.SnapUID,`
$lunObject.NumberOfSnaps,$lunObject.LatestSnap,$lunObject.VM,$lunObject.PhysicalServer,`
$dsBit,$lunObject.DatastoreName,$lunObject.ReportDate,$lunObject.ArrayModel,$lunObject.ArrayLocation
$cmd.executenonquery()
}
$conn.close()
3条答案
按热度按时间yjghlzjz1#
Also see related question here: Powershell and SQL parameters. If empty string, pass DBNull
dz6r00yl2#
I've changed all blank values with PS $null and Write-SQLTableData ran successfully.
vtwuwzda3#
First things first: Rather than "baking" variable elements of a SQL command into the command string, it is more robust and secure to use a parameterized query instead, or, as Aaron Bertrand suggests, a stored procedure; that way, the variable elements are passed as parameters.
Your template string assumes that all values to be inserted are strings, given that you've enclosed the placeholders in
'...'
(e.g.,'{0}'
).Given that
$null
passed to-f
, the format operator, is converted to the empty string[1], you'll end up with literal''
parameter values in the command string, whereas what you truly need is verbatimNULL
.You can define a helper function:
You can then invoke it as follows (I'm using a simplified example):
The above yields the following string:
[1] The same applies to PowerShell's expandable strings (
"..."
); e.g.,"[$null]"
yields verbatim[]
.