ExecuteNonQuery: The CommandText property has not been properly initialized

Question

I am trying to insert data into a SQL database with Powershell. This is my code:

$date = Get-Date -Format 'dd/MM/yyyy'
$time = Get-Date -Format 'HH:mm:ff'

Add-Type -AssemblyName System.Data.OracleClient
$OracleConnectionString = "SERVER=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)
HOST=***)(PORT=***))(CONNECT_DATA=(SERVICE_NAME=***)));uid=***;pwd=***;"
$OracleConnection = New-Object System.Data.OracleClient.OracleConnection($OracleConnectionString);
$OracleConnection.Open()

$command = New-Object System.Data.OracleClient.OracleCommand
$Command.Connection = $OracleConnection
$Command.CommandText = "INSERT INTO DATETEST (DATETEST, ZAHL) VALUES (TO_DATE('".$date.", ".$time."', 'DD/MM/YYYY, HH:MI:SS'), 21)"

$Command.ExecuteNonQuery()
$OracleConnection.Close()

The error is in line $command.ExecuteNonQuery() where it says:

Exception calling "ExecuteNonQuery" with "0" argument(s):"The CommandText property has not been properly initialized"

When I replace the TO_DATE function with the $date variable in the Command.CommandTextline, it works and the values are getting inserted. But I need the TO_DATE function in order to insert the datetime to the table.

A colleague of mine wrote this PHP file and used the same function, and it works:

        "insert into datetest(datetest)
        VALUES (TO_DATE('".$date.", ".$time."', 'DD/MM/YYYY, HH24:MI'))";

What am I doing wrong?

Answer 1

The problem is caused because Powershell's string concatenation operator is not the same as PHP's.

Let's parse the code to see what's happening. The PHP-stydel statement uses periods . for catenating. That is,

 "insert into datetest(datetest) VALUES (TO_DATE('"
.$date.
", "
.$time."
', 'DD/MM/YYYY, HH24:MI'))";

will add $date's value to the string, as well as $time's. Since Powershell's string concatenation operator is +, the output is not what you'd expect. The result is, surprisingly enough, an empty string. This happens as per default Powershell doensn't complain about unassigned properties and variables. Set strict mode set-strictmode -version latest and see what's the output:

Set-StrictMode -Version latest
"INSERT INTO DATETEST (DATETEST, ZAHL) VALUES `
  (TO_DATE('".$date.", ".$time."', 'DD/MM/YYYY, HH:MI:SS'), 21)"
       
PropertyNotFoundException: The property '24.10.2022' cannot be found on this object.
Verify that the property exists.

As per the error message says, Powershell thinks that one tires to access string object's property '24.10.2022' which doens't exist.

Resolution: use the right concatenation operator like so,

"INSERT INTO DATETEST (DATETEST, ZAHL) VALUES (TO_DATE('"+$date+", "+$time+"', 'DD/MM/YYYY, HH:MI:SS'), 21)"

Consider also composite formatting for easier readability. Like so,

"INSERT INTO DATETEST (DATETEST, ZAHL) VALUES (TO_DATE('{0} {1}', 'DD/MM/YYYY, HH:MI:SS'), 21)" -f $date, $time
# Output
INSERT INTO DATETEST (DATETEST, ZAHL) VALUES (TO_DATE('24.10.2022 14.19.70', 'DD/MM/YYYY, HH:MI:SS'), 21)