MySQL and PowerShell

From vwiki
Jump to navigation Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

{{#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 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" 
    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