connect to mysql using reusable functions

John_Dodo 

Hi, John.

I'm going to generalise here, as while I'm familiar with the SQL Server and Oracle libraries, I'm unfamiliar with MySQL. Even so, the concepts I'll speak to are the same.

Issue #1: Scope

The $Connection variables used in the ConnectToMySql() and RunQuery() functions are not the same. This is because each version only means something inside the function it's defined in.

The second those functions exit, there is no such variable as $Connection. So, opening a connection in one function and assigning it to a local variable within that function does not result in a usable variable in a different function.

Here's a very basic visual example.

What this is showing you is that that the variable defined within the function ($MyNewVar) does indeed exist within that function. However, when I look to see if it exists after exiting that function, it does not (i.e. it is $null.)

The most prudent way to fix this is:

1. Define your $Connection variable in the main script body;
2. Refer to that variable from within your functions by utilising the "$global:" prefix.

Technically, once you define it as a global variable, you can get away with not needing to use the "$global:" prefix, however, it is better practice to explicitly include it, as doing so make the code easier to understand and avoids conflicts where you have defined a global and local variable that share the same name.

Here's a skeleton code example demonstrating the "$global:" prefix:

#region Function definitions.
function ConnectToMySql()
{
    $mysql = "myserver";
    $DB    = "mydb";
    $user  = "myuser";
    $pass  = "mypass";

    # Load MySQL .NET Connector Objects
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data");
    # Open Connection
    $global:Connection = [MySql.Data.MySqlClient.MySqlConnection]@{ConnectionString="server=$MySQL;uid=$user;pwd=$pass;database=$DB"};
    $global:Connection.Open();
}

function RunQuery()
{
    # blah
    # blah

    $MYSQLCommand.Connection=$global:Connection;
    
    # blah
}
#endregion

#region Main script body.
# Define glohbal variables.
$Connection;
#endregion

Issue #2: Closing and disposing

When you're working with .NET type libraries, you need to ensure you're following .NET procedures such as cleaning up after you've finished with an object. Currently, you're not doing that, meaning memory consumption will grow.

In smaller projects that may not be visible but when you start iterating through tens or hundreds of thousands, this can result in PowerShell easily growing into high hundreds if not gigabytes of memory used territory.

For your script, where this will matter the most is in the RunQuery() function, where you're creating  brand new instances for three classes, yet not calling Dispose() or Close() (where applicable - not all classes have a Close().)

Once a variable falls out of scope, more often than not the garbage collector can reclaim that memory. However, it is best practice to explicitly call .Close() and .Dispose() where the class has those methods.

Here's some skeleton code specific to the RunQuery() function showing the .Dispose() calls:

function RunQuery()
{
    $MYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand;
    $MYSQLDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter;
    $MYSQLDataSet = New-Object System.Data.DataSet;

    # blah
    # blah

    $pcinf = [PSCustomObject] @{
        # blah
        # blah
    }

    $MYSQLDataAdapter.Dispose();
    $MYSQLCommand.Dispose();

    return $pcinf;
}

Optional optimisation:

In addition to the $Connection variable being defined globally (meaning it's only defined once), you should also consider defining a $Command variable globally and simply referencing it within the functions using the "$global:" prefix.

This way, you are not building up a new instance and testing it down again every single time you call RunQuery().

This isn't required - it's only a suggestion, but these smaller optimisations can collectively help your script scale upwards far more than if you choose not to.

Lastly, while MySql isn't my area - as I stated before, there's some good reference documentation on the MySqlClient here that demonstrates things like closing and disposing, etc.

MySql.Data.MySqlClient Namespace

Cheers,

Lain