Wfa - Execute MySql NonQuery PowerShell function

You may or may not know that WFA, when coding in PowerShell, comes with the CmdLet "Invoke-MySqlQuery" (aliased as imysql for the gurus).

The cmdlet comes in handy very often but is built for "QueryCommands".
What I mean by that is that it assumes that your Query will return "Records".

But what if you want to execute an insert, update or delete statement ?

Some background

WFA comes with the Devart MySql DLL, which is a library to execute against MySql.

So the component is at our disposal.

How does the Invoke-MySqlQuery works ?

The Invoke-MySqlQuery works are follows.

• Record 1 contains the number of records returned
• The following records (if record 1 > 0), returns the actual dataset.

So by coding something like :

$results = Invoke-MySqlQuery "SELECT * FROM cm_storage.volume"
if($results[0] -gt 0){
    $results | select -skip 1
}

you get a list of volumes.

ExecuteNonQuery, how to

If you want to insert, delete or update, you could potentially use this cmdlet as well.  In 99% of the cases it will work, however, it's missing 2 features :

• Return the affected row count
• Set the database (scheme)

In some rare cases (1%), MySql will give you the error "No database selected".  Even if you hard-code the scheme name in the query, for some reason it just seems to ignore them.  I noticed this behavior in a DELETE FROM JOIN scenario.

A connection string allows you to set a database as well, and does fix the problem.

A second feature is the affected rows.  If you update, delete or insert, it's always nice to know how many records were affected.  With the Invoke-MySqlQuery, you wont get the information.

So that's why I hacked the original cmdlet and created a new Invoke-MySqlNonQuery, that optionally accept the parameter "Database".

And it returns the affected row count !

So here it is :

function Invoke-MySqlNonQuery {

    Param(

      [Parameter(Mandatory = $true,  HelpMessage="Query to Execute")]
      [string]$Query,

      [Parameter(Mandatory = $false,  HelpMessage="Database Host to connect.")]
      [string]$HostName="localhost",

      [Parameter(Mandatory = $false,  HelpMessage="Database Username")]
      [string]$User="wfa",

      [Parameter(Mandatory = $false,  HelpMessage="Database Port")]
      [string]$Port="3306",

      [Parameter(Mandatory = $false,  HelpMessage="Database Password")]
      [string]$Password="Wfa123",

      [Parameter(Mandatory = $false,  HelpMessage="Database")]
      [string]$Database

      )


    $ConnectionString = "server=" + $HostName + ";port=" + $Port + ";uid=" + $User + ";pwd=" + $Password

    try {
        [void][System.Reflection.Assembly]::LoadWithPartialName("Devart.Data.Mysql")
        $Connection = New-Object Devart.Data.MySql.MySqlConnection
    } catch {
        throw("Failed to get MySql Connection. " + $_.exception)  
    }
  
    try {  
        $Connection.ConnectionString = $ConnectionString
        if($Database){
            $Connection.Database = $Database
        }
        $Connection.Open()
        $Command = New-Object Devart.Data.MySql.MySqlCommand($Query, $Connection)
        $affectedRows = $Command.ExecuteNonQuery()
        return $affectedRows
    }

    catch { 
        throw($_.exception)
    }

    finally {
        $Connection.Close()
    }
}