MySQL and PowerShell

{{#widget:Widget:GoogleAdSenseSkyscraper}} To be able to interact with a MySQL database from PowerShell you need to have the MySQL .NET Connector installed first on the machine that you're going to run PowerShell from - http://dev.mysql.com/downloads/connector/net/

Connect

Make sure you have the .NET connector installed 1st - http://dev.mysql.com/downloads/connector/net/

function Connect-MySQL([string]$user,[string]$pass,[string]$MySQLHost,[string]$database) { 
  # Load MySQL .NET Connector Objects 
  [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") 

  # Open Connection 
  $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE" 
  $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
  $conn.Open() 
  return $conn 
} 

function Disconnect-MySQL($conn) {
  $conn.Close()
}

So, for example...

# Connection Variables 
$user = 'myuser' 
$pass = 'mypass' 
$database = 'mydatabase' 
$MySQLHost = 'database.server.com' 

# Connect to MySQL Database 
$conn = Connect-MySQL $user $pass $MySQLHost $database

Improved connect function with error catcher...

function ConnectMySQL([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) { 
    # Load MySQL .NET Connector Objects 
    [void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data") 
 
    # Open Connection 
    $connStr = "server=" + $MySQLHost + ";port=3306;uid=" + $user + ";pwd=" + $pass + ";database="+$database+";Pooling=FALSE" 
    try {
        $conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr) 
        $conn.Open()
    } catch [System.Management.Automation.PSArgumentException] {
        Log "Unable to connect to MySQL server, do you have the MySQL connector installed..?"
        Log $_
        Exit
    } catch {
        Log "Unable to connect to MySQL server..."
        Log $_.Exception.GetType().FullName
        Log $_.Exception.Message
        exit
    }
    Log "Connected to MySQL database $MySQLHost\$database"

    return $conn 
}

{{#widget:Widget:GoogleAdSenseSkyscraper}}

Commands

All database operations are done through methods of the MySqlCommand object, the two methods of main interest are...

• ExecuteNonQuery - Used for queries that don't return any real information, such as an INSERT, UPDATE, or DELETE.
• ExecuteReader - Used for normal queries that return multiple values. Results need to be received into MySqlDataReader object.
• ExecuteScalar - Used for normal queries that return a single. The result needs to be received into a variable.

Non-Query

function Execute-MySQLNonQuery($conn, [string]$query) { 
  $command = $conn.CreateCommand()                  # Create command object
  $command.CommandText = $query                     # Load query into object
  $RowsInserted = $command.ExecuteNonQuery()        # Execute command
  $command.Dispose()                                # Dispose of command object
  if ($RowsInserted) { 
    return $RowInserted 
  } else { 
    return $false 
  } 
} 

# So, to insert records into a table 
$query = "INSERT INTO test (id, name, age) VALUES (1, 'Joe', 33)" 
$Rows = Execute-MySQLNonQuery $conn $query 
Write-Host $Rows " inserted into database"

Reader Query

In theory, this should work, but it doesn't seem to for me. There's something wrong with the while ($results.Read()), in that you end up displaying the last row returned by the SQL query multiple times. Suspect its due to the way that a Reader object only seems to hold a result temporarily.

$query = "SELECT * FROM subnets;"
$cmd = $connMySQL.CreateCommand() 
$cmd.CommandText = $query 
$results = $cmd.ExecuteReader() 
$cmd.Dispose() 
while ($results.Read()) {
  for ($i= 0; $i -lt $reader.FieldCount; $i++) {
      write-output $reader.GetValue($i).ToString()
  }
}

Instead, this approach seems to work more reliably. By loading the data into a dataset, it becomes available for offline manipulation and isn't reliant on the database once the data is loaded in.

function Execute-MySQLQuery([string]$query) { 
  # NonQuery - Insert/Update/Delete query where no return data is required
  $cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connMySQL)    # Create SQL command
  $dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)      # Create data adapter from query command
  $dataSet = New-Object System.Data.DataSet                                    # Create dataset
  $dataAdapter.Fill($dataSet, "data")                                          # Fill dataset from data adapter, with name "data"              
  $cmd.Dispose()
  return $dataSet.Tables["data"]                                               # Returns an array of results
}

# So, to produce a table of results from a query...
$query = "SELECT * FROM subnets;"
$result = Execute-MySQLQuery $query
Write-Host ("Found " + $result.rows.count + " rows...")
$result | Format-Table

Scalar Query

function Execute-MySQLScalar([string]$query) {
    # Scalar - Select etc query where a single value of return data is expected
    $cmd = $SQLconn.CreateCommand()                                             # Create command object
    $cmd.CommandText = $query                                                   # Load query into object
    $cmd.ExecuteScalar()                                                        # Execute command
}

Other

To perform other random commands that don't read or write data to/from a database...

$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)

Basic escaping of text can be performed by...

function Escape-MySQLText([string]$text) {
    [regex]::replace($text, "'", "\'")
    [regex]::replace($text, "\\", "\\")
}

...you'd need to add additional cases to this to allow for string formats that you expect, there isn't an equivalent of PHP's mysql_real_escape_string()

Test for NULL

In order to be able to test for a NULL value in a set of results from a query, you need to compare against a specific DB NULL value. Otherwise you can get errors similar to...

Error: "Cannot convert the "0" value of type "System.Int32" to type "System.DBNull" 
Method invocation failed because [System.DBNull] doesn't contain a method named 'xxx'.

So to correctly test, use the following...

if ([System.DBNull]::Value.Equals($db_query_result)) {
    Write-Host "Result is NULL"
}

The above was sourced from http://blogs.technet.com/b/industry_insiders/archive/2008/09/15/testing-for-database-null-values-from-powershell.aspx