powershell - ExecuteScalar() does not always return a value from a MySQL query - Stack Overflow

admin管理员组

文章数量:1122846

I am running a PowerShell script where I query for an employeeid from our employees table within a MySQL database. Every employee in that table has an employeenumber. The queries I am running look like this:

SELECT e.employeeID FROM employees e WHERE e.EmployeeNum = 102130 AND e.Deleted = 0;

The query works fine for most employees, but for a few, the call to ExecuteScalar() returns null. If I run the above query for the problematic employees directly on the database (I am using HeidiSQL for that), I get the correct result in the form of exactly one ID.

I am a bit lost as to why ExecuteScalar() does not always work as expected.

For the MySQL connection, I use the following, where the parameters within the connection string are correct.

$ConnectionString='server=' + $cs_MySQL_Server + ';port=' + $cs_MySQL_Port + ';uid=' + $cs_MySQL_User + ';pwd=' + $cs_MySQL_Pwd + ';database=' + $cs_MySQL_database + ';default command timeout=' + $cs_MySQL_timeout + '; Connection Timeout=' + $cs_MySQL_timeout
$Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString=$ConnectionString}

$Connection.Open()
 
$sql = New-Object MySql.Data.MySqlClient.MySqlCommand
$sql.Connection = $Connection   

I also looked into a couple of old threads on Stack Overflow regarding the same problem. Their issue seemed to be that their value was missing, i.e., null. However, that is not the case here.

Update 1 The call to the with executeScalar to the mysql database is done via

$sql.CommandText = "SELECT e.SurName FROM employees e WHERE e.EmployeeID = " + $employeeID + ";"
$employee_SurName = $sql.ExecuteScalar()

Update 2 I found the problem. There were two entries for the same eomployee but with different employee numbers. Hence I had two results which were processed one after the other changing the employeenumber from 1 to 2 and back again. After changing my query I only get 1 result per employee.

本文标签： powershellExecuteScalar() does not always return a value from a MySQL queryStack Overflow