Tuesday, September 29, 2009

Powershell MySQL (insert)

Here's a quick script to write (insert) data into a mysql database using powershell.

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"
$conn = New-Object MySql.Data.MySqlClient.MySqlConnection($connStr)
$conn.Open()
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
return $conn

}

function WriteMySQLQuery($conn, [string]$query) {

$command = $conn.CreateCommand()
$command.CommandText = $query
$RowsInserted = $command.ExecuteNonQuery()
$command.Dispose()
if ($RowsInserted) {
return $RowInserted
} else {
return $false
}
}

# setup vars
$user = 'myuser'
$pass = 'mypass'
$database = 'mydatabase'
$MySQLHost = 'database.server.com'

# Connect to MySQL Database
$conn = ConnectMySQL $user $pass $MySQLHost $database

# Read all the records from table
$query = 'INSERT INTO test (id,name,age) VALUES ("1","Joe","33")'
$Rows = WriteMySQLQuery $conn $query
Write-Host $Rows " inserted into database"

This code requires the MySQL ADO Connector. (http://dev.mysql.com/)