Insert Null Value in MySQL via Powershell

Question

I receive data from a webservice and want to insert it into a MySQL database with Powershell. Unfortunatly datetime values ($outdat) could get null. But I do not manage to insert a null value in that case. Inserting dates works. Here is the basic code:

I have already tried with $outdat = $null, '' and [DBNull]::Value, none of them works.

$MySQLAdminUserName = 'xxx'
$MySQLAdminPassword = 'xxx'
$MySQLDatabase = 'xxx'
$MySQLHost = 'localhost'
$ConnectionString = "server=" + $MySQLHost + ";port=3306;uid=" + $MySQLAdminUserName + ";pwd=" + $MySQLAdminPassword + ";database="+$MySQLDatabase

    Try {
        [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")
        $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
        $Connection.ConnectionString = $ConnectionString
        $Connection.Open()

        $MySqlCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
        $MySqlCommand.Connection = $Connection

        $inoid = "123457"
        $outdat = [DBNull]::Value
        $insid = "Hello World"


            $MySqlCommand.CommandText = "INSERT INTO order_all_test (order_id, shop_id, hgsp_out_date) VALUES ('$inoid', '$insid', '$outdat');"
            $MySqlCommand.ExecuteNonQuery() | Out-Null
            $lastId = $MySqlCommand.get_LastInsertedId()

        }

        Catch {
            Write-Host "ERROR : Unable to run query : $query `n$Error[0]"
        }

        Finally {
            $Connection.Close()
            }

"INSERT INTO order_all_test (order_id, shop_id) VALUES ('$inoid', '$insid');" (eliminating a column from INSERT INTO should leave that column uninitialized however, at the moment, I can't verify whether it's the same as dbnull ) — JosefZ
– JosefZ, Commented May 14, 2019 at 21:37
The problem is that I can't leave the column out completly, as sometimes I have values. How I set dbnull to outdat is just an example here for testing reasons, in reality the parameter gets filled through the webservice. — McMaster
– McMaster, Commented May 15, 2019 at 21:50

McMaster · Accepted Answer · 2019-05-15 22:21:00Z

2

I found the solution:

$outdat = "NULL"
...
$MySqlCommand.CommandText = "INSERT INTO order_all_test (order_id, shop_id, hgsp_out_date) VALUES ('$inoid', '$insid', $outdat);"

So the trick was to leave out the single quotes in the sql.

answered May 15, 2019 at 22:21

McMaster

216 bronze badges

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

Collectives™ on Stack Overflow

Insert Null Value in MySQL via Powershell

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related