Inserting NULL Into Database from PowerShell

Question

I have data that I grab via an API that gives me a json file. I use PowerShell to convert this to a PowerShell object.

$allusers = Get-Content 'test.txt' | Out-String | ConvertFrom-Json

I take data from this object and insert it into a SQL Server database. The problem I'm having is that for keys that don't exist in the object, I would like to insert a NULL into the database.

I've explored other solutions here, and most suggest using [DBNull]::Value

I have variables set as such if the value doesn't exist for the specific user in the array:

$oa_email_id = [DBNull]::Value
$oa_email = [DBNull]::Value
$oa_sms_id = [DBNull]::Value
$oa_sms_phone = [DBNull]::Value

However for null INT values, I get a 0 inserted and for null VARCHAR(max) values, a blank string gets inserted. Is there something I'm missing or doing incorrectly?

Insert to database:

$sql_insert_cmd = New-Object System.Data.SqlClient.SqlCommand
$sql_insert_cmd.CommandText = "
    INSERT INTO
        TEMP_OMNIALERT_CURRENT_SUBSCRIBERS
            (ID_NUM, OA_ID, OA_VALIDATED, OA_ACCOUNT_EXPIRATION_DATE, OA_OPTOUT_DATE, OA_EMAIL_ID,OA_EMAIL, OA_SMS_ID,OA_SMS_PHONE)
    VALUES
            ('"+    $id_num + "', '" +
                    $oa_id + "', '" +
                    $oa_validated + "', '" +
                    $oa_account_expiration_date + "', '" +
                    $oa_optout_date + "', '" +
                    $oa_email_id + "', '" +
                    $oa_email + "', '" +
                    $oa_sms_id + "', '" +
                    $oa_sms_phone + "')"

$sql_insert_cmd.Connection = $connection
$execute_insert = $sql_insert_cmd.ExecuteNonQuery()

Table create:

$sql_insert_cmd.CommandText = "
USE TmsEPly;
DROP TABLE IF EXISTS TEMP_OMNIALERT_CURRENT_SUBSCRIBERS;
CREATE TABLE
    TEMP_OMNIALERT_CURRENT_SUBSCRIBERS
    (
        SUBMISSION_ID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
        ID_NUM VARCHAR(MAX) NOT NULL,
        OA_ID INT NOT NULL,
        OA_VALIDATED INT NOT NULL,
        OA_ACCOUNT_EXPIRATION_DATE VARCHAR(MAX) NOT NULL,
        OA_OPTOUT_DATE VARCHAR(MAX) NOT NULL,
        OA_EMAIL_ID INT NULL,
        OA_EMAIL VARCHAR(MAX) NULL,
        OA_SMS_ID INT NULL,
        OA_SMS_PHONE VARCHAR(MAX) NULL
    )"

You haven't shown the code where you're inserting. My guess is the problem lies in where you're creating parameters. — Mike Shepard
– Mike Shepard, Commented Sep 20, 2018 at 15:03
Have you tried removing the single quotes around those fields? — PhilDulac
– PhilDulac, Commented Sep 20, 2018 at 15:40
You need to use a parameterized statement to use null this way (with [DBNull]::Value). If you want to build a single-string insert statement with nulls, you will have to use the string "Null" without any quotes for the value. Highly recommend using parameters, though. — Mike Shepard
– Mike Shepard, Commented Sep 20, 2018 at 16:30

GreyOrGray · Accepted Answer · 2018-09-20 17:09:29Z

Rather than building a string, would you consider loading a datatable and then writing that to your database?

Using your table creation, I put together the following test, which successfully loads data into TEMP_OMNIALERT_CURRENT_SUBSCRIBERS on my local test database. The code to define and build the datatable is large but only needs to happen once and then rows can be added into it as needed as you iterate through the json data. One thing to note is that I create a column in the datatable for SUBMISSION_ID and never populate it - this is so that it skips that first identity column rather than trying to insert into it and failing.

###import the sqlserver module
ipmo sqlserver 

###your values
$oa_email_id = [DBNull]::Value
$oa_email = [DBNull]::Value
$oa_sms_id = [DBNull]::Value
$oa_sms_phone = [DBNull]::Value

###my temp values for the other columns
$ID_NUM = "a"
$OA_ID = 1 
$OA_VALIDATED = 1
$OA_ACCOUNT_EXPIRATION_DATE = get-date
$OA_OPTOUT_DATE = get-date

###define the datatable structure
$testTable = New-Object system.data.datatable "FileTable"
$col0 = New-Object system.Data.DataColumn SUBMISSION_ID,([int])
$col1 = New-Object system.Data.DataColumn ID_NUM,([string])
$col2 = New-Object system.Data.DataColumn OA_ID,([int])
$col3 = New-Object system.Data.DataColumn OA_VALIDATED,([int])
$col4 = New-Object system.Data.DataColumn OA_ACCOUNT_EXPIRATION_DATE,([string])
$col5 = New-Object system.Data.DataColumn OA_OPTOUT_DATE,([string])
$col6 = New-Object system.Data.DataColumn OA_EMAIL_ID,([int])
$col7 = New-Object system.Data.DataColumn OA_EMAIL,([string])
$col8 = New-Object system.Data.DataColumn OA_SMS_ID,([int])
$col9 = New-Object system.Data.DataColumn OA_SMS_PHONE,([string])

$testTable.columns.add($col0)
$testTable.columns.add($col1)
$testTable.columns.add($col2)
$testTable.columns.add($col3)
$testTable.columns.add($col4)
$testTable.columns.add($col5)
$testTable.columns.add($col6)
$testTable.columns.add($col7)
$testTable.columns.add($col8)
$testTable.columns.add($col9)

###add a new row and populate it
$row = $testTable.NewRow()
$row.ID_NUM = $ID_NUM
$row.OA_ID = $OA_ID
$row.OA_VALIDATED = $OA_VALIDATED
$row.OA_ACCOUNT_EXPIRATION_DATE = $OA_ACCOUNT_EXPIRATION_DATE
$row.OA_OPTOUT_DATE = $OA_OPTOUT_DATE
$row.OA_EMAIL_ID = $oa_email_id
$row.OA_EMAIL = $oa_email
$row.OA_SMS_ID = $oa_sms_id
$row.OA_SMS_PHONE = $oa_sms_phone
$testTable.Rows.Add($row)

###write the datatable to our target table
$testTable | write-sqltabledata -ServerInstance "localhost" -database "test" -schemaname "dbo" -tablename "TEMP_OMNIALERT_CURRENT_SUBSCRIBERS"

applekwisp · Accepted Answer · 2018-09-20 18:01:16Z

I think I did what Mike Shepard described above and my NULL values now get inserted.

I changed my query and insert statements to parameters instead:

$sql_insert_cmd.CommandText = "
    INSERT INTO
        TEMP_OMNIALERT_CURRENT_SUBSCRIBERS
            (ID_NUM, OA_ID, OA_VALIDATED, OA_ACCOUNT_EXPIRATION_DATE, OA_OPTOUT_DATE, OA_EMAIL_ID, OA_EMAIL, OA_SMS_ID, OA_SMS_PHONE) 
    VALUES
            (@id_num, @oa_id, @oa_validated, @oa_account_expiration_date, @oa_optout_date, @oa_email_id, @oa_email, @oa_sms_id, @oa_sms_phone)"


$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@id_num",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_id",[Data.SQLDBType]::Int))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_validated",[Data.SQLDBType]::Int))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_account_expiration_date",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_optout_date",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_email_id",[Data.SQLDBType]::Int))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_email",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_sms_id",[Data.SQLDBType]::Int))) | OUT-NULL
$sql_insert_cmd.Parameters.Add((New-Object DATA.SQLClient.SQLParameter("@oa_sms_phone",[Data.SQLDBType]::VarChar, -1))) | OUT-NULL

I insert the data using :

$sql_insert_cmd.Parameters[0].Value = $user.username
$sql_insert_cmd.Parameters[1].Value = $user.id
$sql_insert_cmd.Parameters[2].Value = $user.validated
$sql_insert_cmd.Parameters[3].Value = $user.account_expiration_date
$sql_insert_cmd.Parameters[4].Value = $user.optout_date
$sql_insert_cmd.Parameters[5].Value = $oa_email_id
$sql_insert_cmd.Parameters[6].Value = $oa_email
$sql_insert_cmd.Parameters[7].Value = $oa_sms_id
$sql_insert_cmd.Parameters[8].Value = $oa_sms_phone

$sql_insert_cmd.ExecuteScalar()

Collectives™ on Stack Overflow

Inserting NULL Into Database from PowerShell

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related