Powershell with MySql

To do Mysql select with powershell, you first must download on MySQL site the MySQL Connector .NET

For information, on my computer, I have a "CAQuietExec Failed" error with mysql connector v6.2.5. No problem with v6.1.5 (MySQL Connector Previous Version)

To use Mysql connector, you must load assembly then use MySql connection. You can download script here

#
#
#  Export MySQL Infos
#
#  by F.Richard 2010-09
#

set-psdebug -strict


# Script Directory
$strCurDir = Split-Path -parent $MyInvocation.MyCommand.Path
Set-Location $strCurDir | Out-Null
#Write-Host "Current Dir: $strCurDir"

#$debug = $True
$debug = $False

# need MySQL Connector
#
# in PS2.0 Add-Type -AssemblyName
[void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") 

# ***********************************************



Function executeMYSQLQuery {
 Param ([hashtable]$options)
 $strServer = $(if ($options.ContainsKey("strServer")) {$options["strServer"]} else {"(localhost)"}) 
 $strDatabase = $(if ($options.ContainsKey("strDatabase")) {$options["strDatabase"]} else {"default"}) 
 $strCommandTimeout = $(if ($options.ContainsKey("strCommandTimeout")) {$options["strCommandTimeout"]} else {"15"}) 
 $strPort = $(if ($options.ContainsKey("strPort")) {";Port=" + $options["strPort"]} else {""}) 

 $strUser = $(if ($options.ContainsKey("strUser")) {";User Id=" + $options['strUser']} else {""}) 
 $strPassword = $(if ($options.ContainsKey("strPassword")) {";Password=" + $options['strPassword']} else {""}) 
 $strAuthentication = $(if ($strUser -and $strPassword) {"$strUser$strPassword"} else {""}) 
 $strMisc = $(if ($options.ContainsKey("strMisc")) {";" + $options['strMisc']} else {""}) 

 $strQuery = $(if ($options.ContainsKey("strQuery")) {$options['strQuery']} else {""}) 

 If ($debug) { 
  Write-Host "DEBUG: strServer:" $strServer
  Write-Host "DEBUG: strDatabase:" $strDatabase
  Write-Host "DEBUG: strCommandTimeout:" $strCommandTimeout
  Write-Host "DEBUG: strPort:" $strPort
  Write-Host "DEBUG: strUser:" $strUser
  Write-Host "DEBUG: strPassword:" $strPassword
  Write-Host "DEBUG: strAuthentication:" $strAuthentication
  Write-Host "DEBUG: strMisc:" $strMisc
  Write-Host "DEBUG: strQuery:" $strQuery
 }

 # Create SqlConnection
 $conn = New-Object ('MySql.Data.MySqlClient.MySqlConnection')
 If (!$conn)   {  
  Write-Host "ERROR: MySql Connection could not be created!" 
      Exit  
 }   
 $connString = "Server=$strServer$strAuthentication;Database=$strDatabase$strPort$strMisc"
 If ($debug) { 
  Write-Host "DEBUG: Sql Connection String:" $connString
 }

 # more information http://www.connectionstrings.com/mysql
 $conn.ConnectionString = $connString
 $dtResult = New-Object "System.Data.DataTable"
 $conn.Open()
 If ($conn.State -eq 1) {
  $sqlCmd = New-Object "MySql.Data.MySqlClient.MySqlCommand"
  If ($sqlCmd) {
   $sqlCmd.CommandTimeout = $strCommandTimeout
   $sqlCmd.CommandText = $strQuery
   $sqlCmd.Connection = $conn

   # INSERT, UPDATE or DELETE ExecuteNonQuery()
   # SELECT ExecuteReader()
   $data = $sqlCmd.ExecuteReader()
   $dtResult.Load($data) # fill
   $data.Dispose()
   $sqlCmd.Dispose() # frees all resources that were used by the object. 
  } Else {  
          Write-Host "ERROR: Cannot create SqlCommand object!";  
  }  
 } Else {  
  Write-Host "ERROR: Connection cannot be opened!";  
 }   
 $conn.Close()
 $conn = $Null
 
    #$dtResult | Format-Table -autosize
    return $dtResult
}


# ***********************************************

Function getInfos {
 $query = "SELECT * FROM YourTable"

 # Here Table name is YourTable
 # and fields name are Field1, Field1 and Field3

 $dtResult = New-Object "System.Data.DataTable"
   
 $hashOptions = @{ } 
 $hashOptions["strServer"] = "localhost"
 $hashOptions["strDatabase"] = "db_name" 
 $hashOptions["strUser"] = "user"
 $hashOptions["strPassword"] = "password"
 $hashOptions["strQuery"] = $query
 $dtResult = executeMYSQLQuery($hashOptions) 

 If ($dtResult -ne $Null) {
  Foreach ($row in $dtResult){
   $result = $row.Field1 + ";" + $row.Field2 + ";" + $row.Field3 
   Write-Host $result
  }

 }
 $dtResult = $Null
}

# ***********************************************

getInfos