Powershell: Some examples to use a MySQL Database

Hi,

sometimes it makes sence to write Data in a “real” Databasesystem instead of writing to a text- or xml file.

MySQL offers an native Windows .NET Connector to its Database. You can get it here.


Some examples:-)

First of all load the assembly

PS D:\> [System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

GAC    Version        Location
---    -------        --------
True   v4.0.30319     C:\Windows\Microsoft.Net\assembly\GAC_MSIL\MySql.Data\v4.0_....

 

Build the connection string for your Database

[string]$sMySQLUserName = 'dbusername'
[string]$sMySQLPW = 'DatabasePa$$word'
[string]$sMySQLDB = 'db_test'
[string]$sMySQLHost = 'localhost'
[string]$sConnectionString = "server="+$sMySQLHost+";port=3306;uid=" + $sMySQLUserName + ";pwd=" + $sMySQLPW + ";database="+$sMySQLDB

Open a Database connection

$oConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($sConnectionString)
$Error.Clear()
try
{
    $oConnection.Open()
}
catch
{
    write-warning ("Could not open a connection to Database $sMySQLDB on Host $sMySQLHost. Error: "+$Error[0].ToString())
}

The first query 🙂

# Get an instance of all objects need for a SELECT query. The Command object
$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
# DataAdapter Object
$oMYSQLDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
# And the DataSet Object 
$oMYSQLDataSet = New-Object System.Data.DataSet	
# Assign the established MySQL connection
$oMYSQLCommand.Connection=$oConnection
# Define a SELECT query
$oMYSQLCommand.CommandText='SELECT username,login,telephone from  `db_test`.`users`'
$oMYSQLDataAdapter.SelectCommand=$oMYSQLCommand
# Execute the query
$iNumberOfDataSets=$oMYSQLDataAdapter.Fill($oMYSQLDataSet, "data")

Processing the returned Dataset(in this example just one table is return)

foreach($oDataSet in $oMYSQLDataSet.tables[0])
{
     write-host "User:" $oDataSet.username "Login:" $oDataSet.login "Telephonenumber:" $oDataSet.telephone 
}

Insert some data

# I defined a PSCustom Object for a User
$oNewUser=New-Object psobject -Property @{"username"="NewUser";"login"="login126";"telephone"="+491234 56789"}
$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.CommandText='
INSERT into `db_test`.`users` (`username`,`login`,`telephone`) VALUES("'+$oNewUser.username+'","'+$oNewUser.login+'","'+$oNewUser.telephone+'")'
$iRowsAffected=$oMYSQLCommand.ExecuteNonQuery()

The same INSERT with a prepare statement for inserting a bulk of user.

$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.CommandText='
INSERT into `db_test`.`users` (`username`,`login`,`telephone`) VALUES(@username,@login,@telephone)'
$oMYSQLCommand.Prepare()
$oMySqlCommand.Parameters.AddWithValue("@username", "")
$oMySqlCommand.Parameters.AddWithValue("@login", "")
$oMySqlCommand.Parameters.AddWithValue("@telephone", "")

And insert a bunch of User(all from an array of my custom PSObject)

[PSObject[]]$aNewUsers=@(
(New-Object psobject -Property @{"username"="NewUser1";"login"="login126";"telephone"="+491234 56789"}),
(New-Object psobject -Property @{"username"="NewUser2";"login"="login127";"telephone"="+491234 98765"}),
(New-Object psobject -Property @{"username"="NewUser3";"login"="login128";"telephone"="+491234 03456"})
)
foreach($oUser in $aNewUsers)
{
    $oMySqlCommand.Parameters["@username"].Value=$oUser.username
    $oMySqlCommand.Parameters["@login"].Value=$oUser.login
    $oMySqlCommand.Parameters["@telephone"].Value=$oUser.telephone
    $iRowsInsert=$oMySqlCommand.ExecuteNonQuery()
}

And as an trancation. Only one open transaction by connection is allowed. When you call BeginTransaction() again before an already started transaction is “Commited” or “Rolledback” you got an error “Nested transactions are not supported.”
Note: By default autocommit is enabled in MySQL Server. Means that MySQL automatically commits an transaction after an INSERT or UPDATE statement. You can disable autocommit by setting autocommit = 0.

$oTransAction=$oConnection.BeginTransaction()
$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.Transaction=$oMYSQLTransaction
$oMYSQLCommand.CommandText = "SET autocommit = 0";
$iRows=$oMYSQLCommand.executeNonQuery();
# Do some Inserts or updates here and commit your changes
try
{
	$oMySQLTransaction.Commit()
}
catch
{
	write-warning ("ERROR occured while commit")
	$oMySQLTransaction.Rollback()
}
finally
{
	$oMySqlCommand.Connection.Close()
}
# Start a new transaction....

The UPDATE of a table is straightforward to the previous examples.

$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.CommandText='
UPDATE
 `db_test`.`users` `TUsr` SET `telephone`="+49 9876 54321"
 where `TUsr`.`username`=`NewUser2` and `TUsr`.`login`=`login127`'
$iRowsAffected=$oMYSQLCommand.executeNonQuery();

If you do not want to query if a Dataset exist and want to INSERT on not exists or UPDATE on exists use the following statement. It assumed that the columns `username` and `login` are unique together and are therefore the key of the table so only the `telephone` field has to updated.

$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.CommandText='
INSERT into `db_test`.`users` (`username`,`login`,`telephone`) VALUES(@username,@login,@telephone)
ON DUPLICATE KEY UPDATE 
`telephone`=VALUES(`telephone`)'
$oMYSQLCommand.Prepare()
$oMySqlCommand.Parameters.AddWithValue("@username", "NewUser2")
$oMySqlCommand.Parameters.AddWithValue("@login", "login127")
$oMySqlCommand.Parameters.AddWithValue("@telephone", "+39 1234 56676")
$iRowsAffected=$oMYSQLCommand.executeNonQuery();

Calling a stored procedure. We assume that there is a stored procedure defined in the database that accepts the following parameter


PROCEDURE `getTelephoneNumber`(
IN USERNAME VARCHAR(45),
IN LOGIN VARCHAR(45),
OUT TELEPHONENUMBER VARCHAR(45)
)

and call the storedprocedure

$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand
$oMYSQLCommand.Connection=$oConnection
$oMYSQLCommand.CommandText="getTelephoneNumber"
$oMYSQLCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$oMYSQLCommand.Parameters.AddWithValue("USERNAME", "NewUser2")
$oMYSQLCommand.Parameters["USERNAME"].Direction = [System.Data.ParameterDirection]::Input
$oMYSQLCommand.Parameters.AddWithValue("LOGIN", "login127")
$oMYSQLCommand.Parameters["LOGIN"].Direction = [System.Data.ParameterDirection]::Input
$oMYSQLCommand.Parameters.AddWithValue("TELEPHONENUMBER", [MySql.Data.MySqlClient.MySqlDbType].GetMember("String").GetRawConstantValue())
$oMYSQLCommand.Parameters["TELEPHONENUMBER"].Direction = [System.Data.ParameterDirection]::Output
$iReturn=$oMYSQLCommand.ExecuteNonQuery()
write-host ("Telephonenumber: "+$oMYSQLCommand.Parameters["TELEPHONENUMBER"].Value);

I have to use [MySql.Data.MySqlClient.MySqlDbType].GetMember("String").GetRawConstantValue() for the constant of an MySQL String.

[MySql.Data.MySqlClient.MySqlDbType]::String did not work due to an non complient definition of DateTime DateTime in [MySql.Data.MySqlClient.MySqlDbType] 🙁

Error: The field or property: “Datetime” for type: “MySql.Data.MySqlClient.MySqlDbType” differs only in letter casing from the field or property: “DateTime”. The type must be Common Language Specification (CLS) compliant.
However, you can also simply write 🙂

$oMYSQLCommand.Parameters.AddWithValue("TELEPHONENUMBER", 254)

to be continued……

Michael

7 thoughts on “Powershell: Some examples to use a MySQL Database”

  1. Hi,

    I had the same issue with MySQL in PowerShell utilizing the .Net library.
    I resolved this by using [System.TypeCode]::Int32 instead of the MySqlDbType.
    this resolves the type casting of procedures into PowerShell.

    Hope this will assist you in the future (i assume that it is also the reason it works with entering 254 since 254 is of System.TypeCode Int32).

  2. I created a MySQL invoke function on Windows and was surprised that it also works great on PS Core (changing the assembly path of course).. so here it goes :

    function Invoke-MySQL {
    Param(
    [Parameter(
    Mandatory = $true,
    ParameterSetName = ”,
    ValueFromPipeline = $true)]
    $MySQLDatabase = ‘yourDB’,

    [string]$Query=””
    )

    $MySQLAdminUserName = ‘yourUser’
    $MySQLAdminPassword = ‘yourPASS’

    $MySQLHost = ‘localhost’
    $ConnectionString = “server=” + $MySQLHost + “; port=3306; uid=” + $MySQLAdminUserName + “; pwd=” + $MySQLAdminPassword + “; database=”+$MySQLDatabase
    $Error=””;
    Try {
    # for linux, add location where DLL files are
    add-type -Assembly /Library/MysqlData.dll/MySql.Data.dll
    # For Windows just load the assembly once the driver is installed
    # [void][System.Reflection.Assembly]::LoadWithPartialName(“MySql.Data.dll”)
    $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection
    $Connection.ConnectionString = $ConnectionString
    $Connection.Open()

    $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)
    $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)
    $DataSet = New-Object System.Data.DataSet
    $RecordCount = $DataAdapter.Fill($DataSet)
    #$RecordCount | out-file c:\Record.txt
    $DataSet.Tables[0]
    }

    Catch {
    $Error=$_.Exception.Message
    $body=”$Error $query”
    $log=”/Users/carlosmartins/Documents/scripts/esxhosts/MissedQuery.log”
    get-date | out-file $log -append
    $body | out-file $log -append
    return $Error
    }

    Finally {
    $Connection.Close()
    }
    }
    ############
    I usually put that into a file and use it like this :

    . ./Invoke-MySQL.ps1

    $query = “SELECT * from `databse`.`table`; ”
    #write-warning $query
    $dataRow=Invoke-MySQL -MySQLDatabase ‘diskmon’ -Query $query

    $dataRow | FT

    Works also for all insert, delete …

    Hope this helps

  3. Firstoff all thanks for this post. It is a great summary how to connect to MySQL via powershell!

    I wanted to perform an import of a CSV file with those methods and using prepared statements but I get an error after some time which I am unable to solve.
    Do I need to close and reopen connection? It should commit after each ExecuteNonQuery() and I do not use BeginTransaction()?
    autocommit=ON.

    Error:
    1461, Can’t create more than max_prepared_stmt_count statements (current value: 16382)

Leave a Reply