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 ConnectMySQL([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
}
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
[轉貼]MySQL and PowerShell (冷日