Temp

From vWiki
Jump to navigation Jump to search

<# ========================================================================================================================================

 MySQL function library
 =========================================================================================================================================
 Simon Strutt        Jan 2011
 =========================================================================================================================================
 Requires MySQL .NET connector to be installed on machine script is run from (tested with ver 6.2.4)
 Requires Standard.ps1 (for Log func)
 Version 1 
 - Initial creation
 
 Version 2
 - Added $SQLnoExit option
 
 Version 3
 - Renamed $LogNonQueries to $LogMySQLNonQueries
 - Added $LogMySQLQueries
 
 Version 4
 - Renamed funcs to more PowerShell standard
 
#>

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 $_
       if ($SQLnoExit) {
           return 0
       } else {
           Exit
       }
   } catch {
       Log "Unable to connect to MySQL server..."
       Log $_.Exception.GetType().FullName
       Log $_.Exception.Message
       Log $_
       if ($SQLnoExit) {
           return $false
       } else {
           Exit
       }
   }
   Log ("Connected to MySQL database " + $conn.Database + " on " + $conn.DataSource + " (ver " + $conn.ServerVersion + ")")
   return $conn 

}

function Disconnect-MySQL {

   $SQLconn.Close()

}

function Execute-MySQLNonQuery([string]$query) {

   # NonQuery - Insert/Update/Delete query where no return data is required
   
   if ($LogMySQLNonQueries) {
       Log ($query)
   }
   
   try {
       $cmd = $SQLconn.CreateCommand()               # Create command object
       $cmd.CommandText = $query                     # Load query into object
       $RowsInserted = $cmd.ExecuteNonQuery()        # Execute command
       $cmd.Dispose()                                # Dispose of command object
       if ($RowsInserted) { 
           return $RowInserted 
       } else { 
           return $false 
       }
   } catch {
       Log ("Execute-MySQLNonQuery($query) error...")
       Log $_.Exception.GetType().FullName
       Log $_.Exception.Message
       Log $_
       if ($SQLnoExit) {
           return $false
       } else {
           Exit
       }
   }

}

function Execute-MySQLQuery([string]$query) {

   # Query - Select etc query where return data is expected
   
   if ($LogMySQLQueries) {
       Log ($query)
   }
   
   try {
       $cmd = $SQLconn.CreateCommand()                                             # Create command object
       $cmd.CommandText = $query                                                   # Load query into object
       $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()
       $dataAdapter.Dispose()
       return $dataSet.Tables["data"]                                              # Returns an array of results
   } catch {
       Log ("Execute-MySQLQuery($query) error...")
       Log $_.Exception.GetType().FullName
       Log $_.Exception.Message
       Log $_
       if ($SQLnoExit) {
           return $false
       } else {
           Exit
       }
   }

}

function Execute-MySQLScalar([string]$query) {

   if ($LogMySQLQueries) {
       Log ($query)
   }
   
   # Scalar - Select etc query where a single value of return data is expected
   try {
       $cmd = $SQLconn.CreateCommand()                                             # Create command object
       $cmd.CommandText = $query                                                   # Load query into object
       $cmd.ExecuteScalar()                                                        # Execute command
   } catch {
       Log ("Execute-MySQLScalar($query) error...")
       Log $_.Exception.GetType().FullName
       Log $_.Exception.Message
       Log $_
       if ($SQLnoExit) {
           return $false
       } else {
           Exit
       }
   }

}

function Escape-MySQLText([string]$text) {

   $text = [regex]::replace($text, "\\", "\\")
   [regex]::replace($text, "'", "\'")

}