Powershell: Unable to insert into MySQL date, int values with null

Question

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"

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) — user1986815
– user1986815, Commented May 19, 2020 at 1:00
no INSERT INTO table_foo (col1,col2) VALUES(null, null) is valid mysql — user1986815
– user1986815, Commented May 19, 2020 at 1:42

mklement0 · Accepted Answer · 2020-05-21 01:38:02Z

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)

sql_dummy · Accepted Answer · 2020-05-21 00:58:13Z

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()

answered May 21, 2020 at 0:58

sql_dummy

78310 silver badges26 bronze badges

Comments

LucasBF · Accepted Answer · 2020-05-19 18:57:19Z

-1

try typing NULL, dont use variable:

VALUES('NULL','NULL')

something like this:

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

answered May 19, 2020 at 18:57

LucasBF

1245 bronze badges

2 Comments

sql_dummy Over a year ago

thanks @LucasBF but it still throws error : "Incorrect datetime value: 'NULL' for column 'col1' at row 1"

mklement0 Over a year ago

From SQL's perspective, 'NULL' is a string, not null - remove the surrounding single quotes.

Collectives™ on Stack Overflow

Powershell: Unable to insert into MySQL date, int values with null

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related