Stuck on Function to Create MySQL Connection

PowerShell Help
1

I am stuck at a point in my script to call a function to make a connection to a MySQL DB. Outside of the function, the script runs fine. As soon as I throw it into a function, it breaks. I am going to be calling this portion of script multiple times in the overall script, hence the reason for the function.

function MySQLConnect {
	$connStr = "server=$sqlserver;userid=$sqluser;password=$sqlpwd;database=$database"
	$conn = New-Object Mysql.Data.MySqlClient.MySqlConnection($connStr)
	$conn.Open()
}

Once I am outside of the function, the variable $conn is empty.

Here is where I first call the function:

MySQLConnect

$cmd = $conn.CreateCommand()
$cmd.CommandText = "Select * FROM assets WHERE name='$pcname' AND deleted_at IS NULL"
$reader = $cmd.ExecuteReader()
$pcfound = $false
while ($reader.Read()) {
	$pcfound = $true
}
$pcfound

Just to take the complexity out, I tried just open a connection to MySQL and then closing it.

MySQLConnect
$conn.Close()

Here is the error I receive:

ERROR: You cannot call a method on a null-valued expression.
hwdinventory.ps1 (73, 2): ERROR: At Line: 73 char: 2
ERROR: + $conn.Close()
ERROR: + ~~~~~~~~~~~~~
ERROR: + CategoryInfo : InvalidOperation: (: ) , RuntimeException
ERROR: + FullyQualifiedErrorId : InvokeMethodOnNull
ERROR:

This error occurs because the variable $conn is now empty.

2

Define conn outside of the function. Looks like you have a scope issue.

3

If the $conn variable is going to be consistent, put it outside of your function.
Functions are wrapped cmdlets that unless you specify, only use whats in them and wont pass to other parts of the script.
If theres going to be more than one $conn variable, you’ll need to add it as a parameter to your function.

4

Sometimes I forget the basic things. This works.

function MySQLConnect {
	$connStr = "server=$sqlserver;userid=$sqluser;password=$sqlpwd;database=$database"
	$global:conn = New-Object Mysql.Data.MySqlClient.MySqlConnection($connStr)
	$conn.Open()
}
5

Global scope may work but this would be one of the least preferred ways to solve this issue. Perhaps use Script: scope or output the connection details from the function and capture it in a variable in the proper scope.