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

Advertisment to support michlstechblog.info

Add a comment »One comment to this article

  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).

    Reply

Time limit is exhausted. Please reload CAPTCHA.

Original Theme by Schiy · Powered by WordPress