MySQL and PowerShell - Revision history

Sstrutt: /* Connect */ Made function names consistent

2017-08-14T21:00:09Z

Connect: Made function names consistent

← Older revision		Revision as of 21:00, 14 August 2017
Line 36:		Line 36:
	Improved connect function with error catcher...		Improved connect function with error catcher...
	<source lang="powershell">		<source lang="powershell">
	function ~~ConnectMySQL~~([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) {		function Connect-MySQL([string]$user, [string]$pass, [string]$MySQLHost, [string]$database) {
	# Load MySQL .NET Connector Objects		# Load MySQL .NET Connector Objects
	[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")		[void][system.reflection.Assembly]::LoadWithPartialName("MySql.Data")
Line 61:		Line 61:
	</source>		</source>
	{{#widget:Widget:GoogleAdSenseSkyscraper}}		{{#widget:Widget:GoogleAdSenseSkyscraper}}

	= Commands =		= Commands =
	All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are...		All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are...

Sstrutt: Add Google Ads

2016-10-04T21:49:20Z

Add Google Ads

← Older revision		Revision as of 21:49, 4 October 2016
Line 1:		Line 1:
			{{#widget:Widget:GoogleAdSenseSkyscraper}}
	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/		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/

Line 59:		Line 60:
	}		}
	</source>		</source>
			{{#widget:Widget:GoogleAdSenseSkyscraper}}
	= Commands =		= Commands =
	All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are...		All database operations are done through methods of the <code> MySqlCommand </code> object, the two methods of main interest are...

Sstrutt: Minor reformatting, and Meta

2012-08-17T10:29:40Z

Minor reformatting, and Meta

← Older revision		Revision as of 10:29, 17 August 2012
Line 1:		Line 1:
	To be able to interact with a MySQL database from PowerShell you need to have the MySQL .NET Connector installed first - http://dev.mysql.com/downloads/connector/net/		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 =		= Connect =
Line 19:		Line 19:
	$conn.Close()		$conn.Close()
	}		}
			</source>

	# So, for example...		So, for example...
			<source lang="powershell">
	# Connection Variables		# Connection Variables
	$user = 'myuser'		$user = 'myuser'

Sstrutt: /* Test for NULL */ Added additional error example

2012-06-21T09:38:42Z

Test for NULL: Added additional error example

← Older revision		Revision as of 09:38, 21 June 2012
Line 145:		Line 145:

	= Test for NULL =		= 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~~'ll~~ get ~~an error~~ similar to...		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...

	~~<code>~~ Error: "Cannot convert the "0" value of type "System.Int32" to type "System.DBNull" ~~</code>~~		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...		So to correctly test, use the following...

Sstrutt: /* Reader Query */ Corrected error in example

2012-06-13T15:25:57Z

Reader Query: Corrected error in example

← Older revision		Revision as of 15:25, 13 June 2012
Line 115:		Line 115:
	$query = "SELECT * FROM subnets;"		$query = "SELECT * FROM subnets;"
	$result = Execute-MySQLQuery $query		$result = Execute-MySQLQuery $query
	Write-Host "Found" ($result.~~Length)~~ "rows..."		Write-Host ("Found " + $result.rows.count + " rows...")
	$result \| Format-Table		$result \| Format-Table
	</source>		</source>

Sstrutt: Initial creation - content from PowerShell Examples page

2012-04-18T13:30:28Z

Initial creation - content from PowerShell Examples page

New page

To be able to interact with a MySQL database from PowerShell you need to have the MySQL .NET Connector installed first - http://dev.mysql.com/downloads/connector/net/

= Connect =
Make sure you have the .NET connector installed 1st - http://dev.mysql.com/downloads/connector/net/

<source lang="powershell">
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
</source>

Improved connect function with error catcher...
<source lang="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"
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
}
</source>

= Commands =
All database operations are done through methods of the <code> MySqlCommand </code> 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 <code> MySqlDataReader </code> object.
* '''ExecuteScalar''' - Used for normal queries that return a single. The result needs to be received into a variable.

== Non-Query ==
<source lang="powershell">
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"
</source>

== Reader Query ==
In theory, this should work, but it doesn't seem to for me. There's something wrong with the <code>while ($results.Read())</code>, 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.
<source lang="powershell">
$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()
}
}
</source>

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.
<source lang="powershell">
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.Length) "rows..."
$result | Format-Table
</source>

== Scalar Query ==
<source lang="powershell">
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
}
</source>

== Other ==
To perform other random commands that don't read or write data to/from a database...
<source lang="powershell">
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand("USE $database", $conn)
</source>

Basic escaping of text can be performed by...
<source lang="powershell">
function Escape-MySQLText([string]$text) {
[regex]::replace($text, "'", "\'")
[regex]::replace($text, "\\", "\\")
}
</source>
...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 [http://www.php.net/mysql_real_escape_string 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'll get an error similar to...

<code> Error: "Cannot convert the "0" value of type "System.Int32" to type "System.DBNull" </code>

So to correctly test, use the following...
<source lang="powershell">
if ([System.DBNull]::Value.Equals($db_query_result)) {
Write-Host "Result is NULL"
}
</source>
The above was sourced from http://blogs.technet.com/b/industry_insiders/archive/2008/09/15/testing-for-database-null-values-from-powershell.aspx

[[Category:PowerShell]]
[[Category:MySQL]]