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

ODBC backslash problem error with MySQL

Today, I must use a software from CA names DSM reporter that permit me to export by ODBC some information to a MySQL database
All informations displayed in DSM reporter. But when I schedule this report to export datas to MySQL, it doesn't work.
Problem: I use the same ODBC , the same user than an other report that works very well. Moreover, all tables are well created, but no insertion was done.

So I use tracing. For this, click on start / Settings / Control Panel / Administrative Tools / Data Sources (ODBC). Select your System or User Data sources then click on Configure button. In details there is a Debug tab. Just enable "log queries to myodbc.sql"


Run your MySQL ODBC export again (for me,here, with DSM Reporter export / ODBC Database Table on a result). Now just to analyse C:\myodbc.sql file.
Finally, I found my problem come from \ (backslash) which is considered in MySQL like an Escape character. So I resolve problem as indicated in http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html, using NO_BACKSLASH_ESCAPES mode.

You can see your current sql mode by

SELECT @@SESSION.sql_mode;

or globally by

SELECT @@GLOBAL.sql_mode;

Now, to change sql mode in your current connection:

SET sql_mode='NO_BACKSLASH_ESCAPES';

To set this mode globally any connections:

SET GLOBAL sql_mode='NO_BACKSLASH_ESCAPES'

And if you don't want any sql mode options in your current connection:

SET sql_mode='';

Anf globally for all connections:

SET GLOBAL sql_mode=''