Temp
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.
<# ========================================================================================================================================
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, "'", "\'")
}