{"id":2725,"date":"2015-05-28T23:23:50","date_gmt":"2015-05-28T21:23:50","guid":{"rendered":"http:\/\/michlstechblog.info\/blog\/?p=2725"},"modified":"2015-05-29T12:56:28","modified_gmt":"2015-05-29T10:56:28","slug":"powershell-some-examples-to-use-a-mysql-database","status":"publish","type":"post","link":"https:\/\/michlstechblog.info\/blog\/powershell-some-examples-to-use-a-mysql-database\/","title":{"rendered":"Powershell: Some examples to use a MySQL Database"},"content":{"rendered":"<div class=\"twoclick_social_bookmarks_post_2725 social_share_privacy clearfix 1.6.4 locale-en_US sprite-en_US\"><\/div><div class=\"twoclick-js\"><script type=\"text\/javascript\">\/* <![CDATA[ *\/\njQuery(document).ready(function($){if($('.twoclick_social_bookmarks_post_2725')){$('.twoclick_social_bookmarks_post_2725').socialSharePrivacy({\"services\":{\"flattr\":{\"uid\":\"Michl\",\"status\":\"on\",\"the_title\":\"Powershell%3A%20Some%20examples%20to%20use%20a%20MySQL%20Database\",\"the_excerpt\":\"Hi%2C%0D%0A%0D%0Asometimes%20it%20makes%20sence%20to%20write%20Data%20in%20a%20%22real%22%20Databasesystem%20instead%20of%20writing%20to%20a%20text-%20or%20xml%20file.%0D%0A%0D%0AMySQL%20offers%20an%20native%20Windows%20.NET%20Connector%20to%20its%20Database.%20You%20can%20get%20it%20%20here.%0D%0A%0D%0A%20%28more%26hellip%3B%29\",\"txt_info\":\"2 clicks for more data protection:\\r\\n\\r\\nOnly when you click here, the button will be come active and you can send your recommendation to Flattr. When activating, data are transmitted to third parties. \",\"perma_option\":\"off\"}},\"txt_help\":\"When you activate these fields by clicking, information to Flattr may be transferred abroad, and probably may also stored there.\",\"settings_perma\":\"Enable permanently and accept data transmission. \",\"info_link\":\"http:\\\/\\\/www.heise.de\\\/ct\\\/artikel\\\/2-Klicks-fuer-mehr-Datenschutz-1333879.html\",\"uri\":\"https:\\\/\\\/michlstechblog.info\\\/blog\\\/powershell-some-examples-to-use-a-mysql-database\\\/\",\"post_id\":2725,\"post_title_referrer_track\":\"Powershell%3A+Some+examples+to+use+a+MySQL+Database\",\"display_infobox\":\"on\"});}});\n\/* ]]> *\/<\/script><\/div><p>Hi,<\/p>\n<p>sometimes it makes sence to write Data in a &#8220;real&#8221; Databasesystem instead of writing to a text- or xml file.<\/p>\n<p>MySQL offers an native Windows .NET Connector to its Database. You can get it <a title=\"MySQL .NET Connector\" href=\"https:\/\/dev.mysql.com\/downloads\/connector\/net\/\" target=\"_blank\"> here.<\/a><\/p>\n<p><!--more--><br \/>\nSome examples:-)<\/p>\n<p>First of all load the assembly<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\nPS D:\\&gt; &#x5B;System.Reflection.Assembly]::LoadWithPartialName(&quot;MySql.Data&quot;)\r\n\r\nGAC    Version        Location\r\n---    -------        --------\r\nTrue   v4.0.30319     C:\\Windows\\Microsoft.Net\\assembly\\GAC_MSIL\\MySql.Data\\v4.0_....\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>Build the connection string for your Database<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n&#x5B;string]$sMySQLUserName = 'dbusername'\r\n&#x5B;string]$sMySQLPW = 'DatabasePa$$word'\r\n&#x5B;string]$sMySQLDB = 'db_test'\r\n&#x5B;string]$sMySQLHost = 'localhost'\r\n&#x5B;string]$sConnectionString = &quot;server=&quot;+$sMySQLHost+&quot;;port=3306;uid=&quot; + $sMySQLUserName + &quot;;pwd=&quot; + $sMySQLPW + &quot;;database=&quot;+$sMySQLDB\r\n<\/pre>\n<p>Open a Database connection<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oConnection = New-Object MySql.Data.MySqlClient.MySqlConnection($sConnectionString)\r\n$Error.Clear()\r\ntry\r\n{\r\n    $oConnection.Open()\r\n}\r\ncatch\r\n{\r\n    write-warning (&quot;Could not open a connection to Database $sMySQLDB on Host $sMySQLHost. Error: &quot;+$Error&#x5B;0].ToString())\r\n}\r\n<\/pre>\n<p>The first query \ud83d\ude42<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n# Get an instance of all objects need for a SELECT query. The Command object\r\n$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand\r\n# DataAdapter Object\r\n$oMYSQLDataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter\r\n# And the DataSet Object \r\n$oMYSQLDataSet = New-Object System.Data.DataSet\t\r\n# Assign the established MySQL connection\r\n$oMYSQLCommand.Connection=$oConnection\r\n# Define a SELECT query\r\n$oMYSQLCommand.CommandText='SELECT username,login,telephone from  `db_test`.`users`'\r\n$oMYSQLDataAdapter.SelectCommand=$oMYSQLCommand\r\n# Execute the query\r\n$iNumberOfDataSets=$oMYSQLDataAdapter.Fill($oMYSQLDataSet, &quot;data&quot;)\r\n<\/pre>\n<p>Processing the returned Dataset(in this example just one table is return)<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\nforeach($oDataSet in $oMYSQLDataSet.tables&#x5B;0])\r\n{\r\n     write-host &quot;User:&quot; $oDataSet.username &quot;Login:&quot; $oDataSet.login &quot;Telephonenumber:&quot; $oDataSet.telephone \r\n}\r\n<\/pre>\n<p>Insert some data<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n# I defined a PSCustom Object for a User\r\n$oNewUser=New-Object psobject -Property @{&quot;username&quot;=&quot;NewUser&quot;;&quot;login&quot;=&quot;login126&quot;;&quot;telephone&quot;=&quot;+491234 56789&quot;}\r\n$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand\r\n$oMYSQLCommand.CommandText='\r\nINSERT into `db_test`.`users` (`username`,`login`,`telephone`) VALUES(&quot;'+$oNewUser.username+'&quot;,&quot;'+$oNewUser.login+'&quot;,&quot;'+$oNewUser.telephone+'&quot;)'\r\n$iRowsAffected=$oMYSQLCommand.ExecuteNonQuery()\r\n<\/pre>\n<p>The same INSERT with a prepare statement for inserting a bulk of user. <\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand\r\n$oMYSQLCommand.Connection=$oConnection\r\n$oMYSQLCommand.CommandText='\r\nINSERT into `db_test`.`users` (`username`,`login`,`telephone`) VALUES(@username,@login,@telephone)'\r\n$oMYSQLCommand.Prepare()\r\n$oMySqlCommand.Parameters.AddWithValue(&quot;@username&quot;, &quot;&quot;)\r\n$oMySqlCommand.Parameters.AddWithValue(&quot;@login&quot;, &quot;&quot;)\r\n$oMySqlCommand.Parameters.AddWithValue(&quot;@telephone&quot;, &quot;&quot;)\r\n<\/pre>\n<p>And insert a bunch of User(all from an array of my custom PSObject)<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n&#x5B;PSObject&#x5B;]]$aNewUsers=@(\r\n(New-Object psobject -Property @{&quot;username&quot;=&quot;NewUser1&quot;;&quot;login&quot;=&quot;login126&quot;;&quot;telephone&quot;=&quot;+491234 56789&quot;}),\r\n(New-Object psobject -Property @{&quot;username&quot;=&quot;NewUser2&quot;;&quot;login&quot;=&quot;login127&quot;;&quot;telephone&quot;=&quot;+491234 98765&quot;}),\r\n(New-Object psobject -Property @{&quot;username&quot;=&quot;NewUser3&quot;;&quot;login&quot;=&quot;login128&quot;;&quot;telephone&quot;=&quot;+491234 03456&quot;})\r\n)\r\nforeach($oUser in $aNewUsers)\r\n{\r\n    $oMySqlCommand.Parameters&#x5B;&quot;@username&quot;].Value=$oUser.username\r\n    $oMySqlCommand.Parameters&#x5B;&quot;@login&quot;].Value=$oUser.login\r\n    $oMySqlCommand.Parameters&#x5B;&quot;@telephone&quot;].Value=$oUser.telephone\r\n    $iRowsInsert=$oMySqlCommand.ExecuteNonQuery()\r\n}\r\n<\/pre>\n<p>And as an trancation. Only one open transaction by connection is allowed. When you call BeginTransaction() again before an already started transaction is &#8220;Commited&#8221; or &#8220;Rolledback&#8221; you got an error <strong>&#8220;Nested transactions are not supported.&#8221;<\/strong><br \/>\n<strong>Note: <\/strong>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.<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oTransAction=$oConnection.BeginTransaction()\r\n$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand\r\n$oMYSQLCommand.Connection=$oConnection\r\n$oMYSQLCommand.Transaction=$oMYSQLTransaction\r\n$oMYSQLCommand.CommandText = &quot;SET autocommit = 0&quot;;\r\n$iRows=$oMYSQLCommand.executeNonQuery();\r\n# Do some Inserts or updates here and commit your changes\r\ntry\r\n{\r\n\t$oMySQLTransaction.Commit()\r\n}\r\ncatch\r\n{\r\n\twrite-warning (&quot;ERROR occured while commit&quot;)\r\n\t$oMySQLTransaction.Rollback()\r\n}\r\nfinally\r\n{\r\n\t$oMySqlCommand.Connection.Close()\r\n}\r\n# Start a new transaction....\r\n<\/pre>\n<p>The UPDATE of a table is straightforward to the previous examples.<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand\r\n$oMYSQLCommand.Connection=$oConnection\r\n$oMYSQLCommand.CommandText='\r\nUPDATE\r\n `db_test`.`users` `TUsr` SET `telephone`=&quot;+49 9876 54321&quot;\r\n where `TUsr`.`username`=`NewUser2` and `TUsr`.`login`=`login127`'\r\n$iRowsAffected=$oMYSQLCommand.executeNonQuery();\r\n<\/pre>\n<p>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.<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand\r\n$oMYSQLCommand.Connection=$oConnection\r\n$oMYSQLCommand.CommandText='\r\nINSERT into `db_test`.`users` (`username`,`login`,`telephone`) VALUES(@username,@login,@telephone)\r\nON DUPLICATE KEY UPDATE \r\n`telephone`=VALUES(`telephone`)'\r\n$oMYSQLCommand.Prepare()\r\n$oMySqlCommand.Parameters.AddWithValue(&quot;@username&quot;, &quot;NewUser2&quot;)\r\n$oMySqlCommand.Parameters.AddWithValue(&quot;@login&quot;, &quot;login127&quot;)\r\n$oMySqlCommand.Parameters.AddWithValue(&quot;@telephone&quot;, &quot;+39 1234 56676&quot;)\r\n$iRowsAffected=$oMYSQLCommand.executeNonQuery();\r\n<\/pre>\n<p>Calling a stored procedure. We assume that there is a stored procedure defined in the database that accepts the following parameter<\/p>\n<pre><code>\r\nPROCEDURE `getTelephoneNumber`(\r\nIN USERNAME VARCHAR(45),\r\nIN LOGIN VARCHAR(45),\r\nOUT TELEPHONENUMBER VARCHAR(45)\r\n)<\/code><\/pre>\n<p>and call the storedprocedure<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oMYSQLCommand = New-Object MySql.Data.MySqlClient.MySqlCommand\r\n$oMYSQLCommand.Connection=$oConnection\r\n$oMYSQLCommand.CommandText=&quot;getTelephoneNumber&quot;\r\n$oMYSQLCommand.CommandType = &#x5B;System.Data.CommandType]::StoredProcedure\r\n$oMYSQLCommand.Parameters.AddWithValue(&quot;USERNAME&quot;, &quot;NewUser2&quot;)\r\n$oMYSQLCommand.Parameters&#x5B;&quot;USERNAME&quot;].Direction = &#x5B;System.Data.ParameterDirection]::Input\r\n$oMYSQLCommand.Parameters.AddWithValue(&quot;LOGIN&quot;, &quot;login127&quot;)\r\n$oMYSQLCommand.Parameters&#x5B;&quot;LOGIN&quot;].Direction = &#x5B;System.Data.ParameterDirection]::Input\r\n$oMYSQLCommand.Parameters.AddWithValue(&quot;TELEPHONENUMBER&quot;, &#x5B;MySql.Data.MySqlClient.MySqlDbType].GetMember(&quot;String&quot;).GetRawConstantValue())\r\n$oMYSQLCommand.Parameters&#x5B;&quot;TELEPHONENUMBER&quot;].Direction = &#x5B;System.Data.ParameterDirection]::Output\r\n$iReturn=$oMYSQLCommand.ExecuteNonQuery()\r\nwrite-host (&quot;Telephonenumber: &quot;+$oMYSQLCommand.Parameters&#x5B;&quot;TELEPHONENUMBER&quot;].Value);\r\n<\/pre>\n<p>I have to use <code>[MySql.Data.MySqlClient.MySqlDbType].GetMember(\"String\").GetRawConstantValue()<\/code> for the constant of an MySQL String.  <code><\/p>\n<p>\n[MySql.Data.MySqlClient.MySqlDbType]::String<\/code> did not work due to an non complient definition of DateTime DateTime in [MySql.Data.MySqlClient.MySqlDbType] \ud83d\ude41<\/p>\n<p>\nError: The field or property: &#8220;Datetime&#8221; for type: &#8220;MySql.Data.MySqlClient.MySqlDbType&#8221; differs only in letter casing from the field or property: &#8220;DateTime&#8221;. The type must be Common Language Specification (CLS) compliant.<br \/>\nHowever, you can also simply write \ud83d\ude42<\/p>\n<pre class=\"brush: powershell; title: ; notranslate\" title=\"\">\r\n$oMYSQLCommand.Parameters.AddWithValue(&quot;TELEPHONENUMBER&quot;, 254)\r\n<\/pre>\n<p>to be continued&#8230;&#8230;<\/p>\n<p>Michael<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, sometimes it makes sence to write Data in a &#8220;real&#8221; 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.<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[37,344,38],"tags":[657,658,620,133,660,656,659,20],"class_list":["post-2725","post","type-post","status-publish","format-standard","hentry","category-mysql","category-powershell-scripting","category-sql-snipets","tag-net-connector","tag-nested-transactions-are-not-supported","tag-mysql-2","tag-powershell","tag-prepare","tag-sql-insert-statement-update-on-exists","tag-transaction","tag-windows-2"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/posts\/2725","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/comments?post=2725"}],"version-history":[{"count":33,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/posts\/2725\/revisions"}],"predecessor-version":[{"id":2759,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/posts\/2725\/revisions\/2759"}],"wp:attachment":[{"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/media?parent=2725"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/categories?post=2725"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/michlstechblog.info\/blog\/wp-json\/wp\/v2\/tags?post=2725"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}