1

I have a MySQL table table_foo with columns col1 of type DATETIME ,col2 of type int, both accepts NULL values but when inserted from Powershell throws error. 

$oMYSQLCommand.CommandText='INSERT into `table_foo` (`col1`,`col2`) VALUES("' + $null + '", "' + $null + '")'
 $iRowsAffected=$oMYSQLCommand.ExecuteNonQuery()

also tried using [DBNull]::Value as ref in

$oMYSQLCommand.CommandText='INSERT into `table_foo` (`col1`,`col2`) VALUES("' + [DBNull]::Value + '", "' + $null + '")'

Error

Error: Exception calling "ExecuteNonQuery" with "0" argument(s): "Incorrect datetime value: '' for column 'col1' at row 1"
sql_dummy
  • 715
  • 8
  • 23
  • 1
    concaternating this way you add a string that is the word "null". if you want to insert null you must go like this `INSERT into table_foo (col1,col2) VALUES(null, null)` –  May 19 '20 at 01:00
  • @MaxMuster `null` is not a valid token in powershell – sql_dummy May 19 '20 at 01:35
  • 1
    no `INSERT INTO table_foo (col1,col2) VALUES(null, null)` is valid mysql –  May 19 '20 at 01:42

3 Answers3

2

Since you're constructing a string into which the null values are to be embedded, you must represent the null values using SQL syntax, which means: verbatim NULL (without surrounding single quotes):

$oMYSQLCommand.CommandText =
  'INSERT into `table_foo` (`col1`,`col2`) VALUES (NULL, NULL)'

If the values come from PowerShell variables that are situationally $null and must therefore conditionally be translated to verbatim NULL, the best approach is to use a helper function, as demonstrated in this answer.

In the simplest case, with all variables containing strings, if you want to treat an empty string or a $null value as a SQL NULL, you could define a function tf (short for: transform) as follows:

function tf ($val) { 
  if ([string]::IsNullOrEmpty($val)) { 'NULL' } else { "'{0}'" -f $val } 
}

You could then use the function as follows, with $var1 and $var2 containing the values to embed:

$oMYSQLCommand.CommandText =
  'INSERT into `table_foo` (`col1`,`col2`) VALUES ({0}, {1})' -f
     (tf $var1), (tf $var2)
mklement0
  • 382,024
  • 64
  • 607
  • 775
0

One more simple way of @mklement0's answer is. So the issue here is MySQL accepts variable values with quotes and NULL without quotes. If NULL in enclosed in quotes it is treated as string value. So while using variable concatenate quotes " if the variable value is not NULL

 if($var_x -eq ""){$var_x ="NULL"}else{'var_x = '"'+ $var_x +'"'}
 if($var_y -eq ""){$var_y ="NULL"}else{'var_y = '"'+ $var_y +'"'}
$oMYSQLCommand.CommandText='INSERT into `table_foo` (`col1`,`col2`) VALUES(' + $var_x + ',' + $var_y  + ')'
 $iRowsAffected=$oMYSQLCommand.ExecuteNonQuery()
sql_dummy
  • 715
  • 8
  • 23
-1

try typing NULL, dont use variable:

VALUES('NULL','NULL')

something like this:

$oMYSQLCommand.CommandText="INSERT into `table_foo' (`col1`,`col2`) VALUES('NULL','NULL')"
LucasBF
  • 124
  • 5