3

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()
Improve this question
4
  • Why are you building this big ugly ad hoc INSERT statement in PowerShell? Can't PowerShell call a stored procedure, where you can properly deal with nullable parameters (hint: stuffing NULL inside '' is not the way to do this). Commented Feb 29, 2012 at 22:36
  • Because I am still learning, and seeking advice such as yours :-) Commented Feb 29, 2012 at 22:38
  • Well my first piece of advice is to use a stored procedure, strong typing, not treating NULL as strings, but I'm not the first person about how to achieve all of those things using PowerShell. Can you explain why you're using PowerShell for this task in the first place? Commented Feb 29, 2012 at 22:41
  • Certainly. I'm using Powershell because our dogfood is Win32 and uses a COM API; Powershell plays nicely with it. The report that generates the data (my example just imports an XML file) doesn't actually use that API, but it was a good opportunity to learn the scripting language. The suits want the data loaded into a database for long term reporting, so that's what I'm working on now. Commented Feb 29, 2012 at 22:51

3 Answers 3

Reset to default
12 
$DBNull = [System.DBNull]::Value

Also see related question here: Powershell and SQL parameters. If empty string, pass DBNull

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thanks, David. I'm going to try making a couple of functions as described in your link, one for booleans and one for nulls, and use that to replace the values at the time of the insert. So far, I can successfully convert PS $null to [System.DBNull]::Value, but when PS goes to insert it, it still gets cast as a string with value "". This was apparently a bug in PS1: link. Based on your link I will probably try something more robust than just an insert statement.
[DBNull]::Value was an extremely valuable breadcrumb into properly entering my data into the table. Thanks!
Just to clarify: [System.DBNull]::Value applies if you have a parameterized query, whereas the question uses string interpolation to embed variable elements directly into the command string; while parameterized queries are definitely preferable, the answer to the question as asked is to transform a $null value to verbatim string NULL during the string interpolation, without enclosing it in embedded single quotes.
Thank you for answering the original question.
My pleasure, @MarkWorrall; specifically, the (suboptimal) string-interpolation answer is here.
0

I've changed all blank values with PS $null and Write-SQLTableData ran successfully.

Foreach ($incident in $incidents)
    {
    if ($incident.closed_at -eq '')
        {
        $incident.closed_at = $null
        }
    }

$incident | Select "number","opened_at","short_description","closed_at","incident_state","state","u_operation_mode"|`
Write-SqlTableData -ServerInstance $Instance -DatabaseName $Database -SchemaName dbo -TableName $Table -ConnectionTimeout 600 –Force
Improve this answer

Comments

0

First things first: Rather than "baking" variable elements of a SQL command into the command string using string interpolation, it is more robust and secure to use a parameterized query instead, or, as Aaron Bertrand suggests, a stored procedure; that way, the dynamic elements of the query 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 verbatim NULL.

You can define a helper function:

function Expand-SqlQueryTemplate {
  param([string] $template, [array] $values)
  # Transform the values to SQL syntax.
  $transformedValues = $values | ForEach-Object {
    if ($null -eq $_) {
      'NULL'
    } elseif ($_ -is [string] -or $_ -is [datetime]) { # string or date
      # Enclose in '...', escaping embedded ' as ''
      "'{0}'" -f ($_ -replace "'", "''")
    } else { # other types, such as numbers
      # Use them as-is, though you may need to tweak this,
      # depending on what types you pass.
      $_
    }
  }
  # Expand the template string with the transformed values
  # and output the result.
  $template -f $transformedValues
}

You can then invoke it as follows (I'm using a simplified example):

# Define the command-string template.
$template = 'INSERT INTO SomeTable (Name, Age, Other) VALUES ({0}, {1}, {2})'

# Invoke the function, passing the values to substitute as an array.
Expand-SqlQueryTemplate $template  'J. Doe', 42, $null

The above yields the following string:

INSERT INTO SomeTable (Name, Age, Other) VALUES ('J. Doe', 42, NULL)

[1] The same applies to PowerShell's expandable (interpolating) strings ("..."). E.g., "[$null]" yields verbatim [].

Improve this answer

Comments

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.