Saturday, February 10, 2018

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 {


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

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

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

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

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

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


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

    try {
        $Connection = New-Object Devart.Data.MySql.MySqlConnection
    } catch {
        throw("Failed to get MySql Connection. " + $_.exception)  
    try {  
        $Connection.ConnectionString = $ConnectionString
            $Connection.Database = $Database
        $Command = New-Object Devart.Data.MySql.MySqlCommand($Query, $Connection)
        $affectedRows = $Command.ExecuteNonQuery()
        return $affectedRows

    catch { 

    finally {

1 comment :

  1. Great Mirko. Like your posts and thank you for sharing your knowledge with us. Very helpful.