Powershell Mysql helper functions

Some Powershell functions for Preparing Mysql Statements and executing them.

Requires the MySQL .NET Connector

$Dbserv = "server"
$Dbname = "db"
$Dbuser = "user"
$Dbpass = "pass"

#MySQL Database connection
[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data") | Out-Null
$SQLConnection = New-Object MySql.Data.MySqlClient.MySqlConnection
$SQLConnection.ConnectionString = "server=$Dbserv;port=3306;uid=$Dbuser;pwd=$Dbpass;database=$Dbname;Integrated Security=False"

function MySQLprepare {
	[OutputType([MySql.Data.MySqlClient.MySqlCommand])]
	param (
		# Query
		[Parameter(
			Mandatory = $true
		)]
		[String]
		$query,

		# Connection
		[Parameter(
			Mandatory = $true,
			ValueFromPipeline = $true)
		]
		[MySql.Data.MySqlClient.MySqlConnection]
		$connection
	)

	$command = New-Object MySql.Data.MySqlClient.MySqlCommand
	$command.Connection = $connection
	$command.CommandText = $query

	(Select-String "@\w+" -input $query -AllMatches).Matches.Value | ForEach-Object {
		if ($_) { $command.Parameters.AddWithValue($_, "") }
	} | Out-Null

	$command.Prepare()

	return , $command
}

function MySQLexec {
	[OutputType([Int])]
	param (
		# QueryParams
		[Parameter(
			Mandatory = $false
		)]
		[hashtable]
		$queryParams = @{},

		# Command
		[Parameter(
			Mandatory = $true,
			ValueFromPipeline = $true
		)]
		[MySql.Data.MySqlClient.MySqlCommand]
		$command
	)

	(Select-String "@\w+" -input $command.CommandText -AllMatches).Matches.Value | ForEach-Object {
		if ($_) { $command.Parameters[$_].Value = $queryParams[$_] }
	} | Out-Null

	$affectedRows = $command.ExecuteNonQuery()

	return $affectedRows
}

function MySQLquery {
	[OutputType([System.Data.DataTable])]
	param (
		# QueryParams
		[Parameter(
			Mandatory = $false
		)]
		[hashtable]
		$queryParams = @{},

		# Command
		[Parameter(
			Mandatory = $true,
			ValueFromPipeline = $true
		)]
		[MySql.Data.MySqlClient.MySqlCommand]
		$command
	)

	(Select-String "@\w+" -input $command.CommandText -AllMatches).Matches.Value | ForEach-Object {
		if ($_) { $command.Parameters[$_].Value = $queryParams[$_] }
	} | Out-Null

	$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($command)
	$dataSet = New-Object System.Data.DataSet
	$dataAdapter.Fill($dataSet, "data") | Out-Null
	return , $dataSet.Tables["data"]
}

Examples

Preparing some statements

$insertVM = $SQLConnection | MySQLprepare -query "INSERT INTO virtual_machines ( id, name, description, status ) VALUES ( @id, @name, @description, 'new' );"
$selectVMs = $SQLConnection | MySQLprepare -query "SELECT * FROM virtual_machines WHERE status LIKE @status;"
$updateVM = $SQLConnection | MySQLprepare -query "UPDATE virtual_machines SET status = @status WHERE id = @id;"

Executing them

$insertVM | MySQLexec -queryParams @{"@id" = 1; "@name" = 'test'; "@description" = 'example vm'} | Out-Null

$dtVMs = $selectVMs | MySQLquery -queryParams @{"@status" = 'new'}
foreach ($vm in $dtVMs.Rows) {
	Write-Host "Working on VM" $vm["id"] -ForegroundColor Cyan

	$updateVM | MySQLexec -queryParams @{"@id" = $vm["id"]; "@status" = 'ready'} | Out-Null
}

Cisco Catalyst 9300 ios upgrade

  1. Remove Unwanted Packages
    install remove inactive
  2. Copy New Image to Flash
    copy usbflash0:/cat9k_iosxe.x.x.x.SPA.bin flash:/
  3. Set Boot Variable
    (config) boot system flash:packages.conf
    wr
    show boot system
  4. Software Install Image to Flash
    install add file flash:cat9k_iosxe.x.x.x.SPA.bin activate commit
  5. Verify New Packages and Image after reload
    dir flash:*.pkg
  6. Check Version and New Bootloader
    show version
  7. Clean up
    install remove inactive

UTF encoding

Code point range UTF-8 UTF-16
hexadecimal binary Bytes
(code bits)
binary hexadecimal Bytes
(code bits)
binary hexadecimal
00 00 00
00 00 7F
0000 0000 0000 0000 0000 0000
0000 0000 0000 0000 0111 1111
1
(7)
0xxx xxxx 00
7F
2
(16)
xxxx xxxx xxxx xxxx 00 00
FF FF
00 00 80
00 07 FF
0000 0000 0000 0000 1000 0000
0000 0000 0000 0111 1111 1111
2
(11)
110x xxxx
10xx xxxx
C2 80
DF BF
00 08 00
00 FF FF
0000 0000 0000 1000 0000 0000
0000 0000 1111 1111 1111 1111
3
(16)
1110 xxxx
10xx xxxx
10xx xxxx
E0 A0 80
EF BF BF
01 00 00
10 FF FF
0000 0001 0000 0000 0000 0000
0001 0000 1111 1111 1111 1111
4
(21)
1111 0xxx
10xx xxxx
10xx xxxx
10xx xxxx
F0 90 80 80
F4 8F BF BF
4
(20)
1101 10xx xxxx xxxx
1101 11xx xxxx xxxx
D8 00 DC 00
DB FF DF FF

ESXi Raw Device Mapping RDM

To configure a local device as an RDM disk:

  1. Open an SSH session to the ESXi host.
  2. Run this command to list the disks that are attached to the ESXi host:
    ls -l /vmfs/devices/disks
  3. From the list, identify the local device you want to configure as an RDM and copy the device name.
    Note: The device name is likely be prefixed with t10. and look similar to: t10.F405E46494C4540046F455B64787D285941707D203F45765
  4. To configure the device as an RDM and output the RDM pointer file to your chosen destination, run this command:
    vmkfstools -z /vmfs/devices/disks/diskname /vmfs/volumes/datastorename/vmfolder/vmname.vmdk

For example:

vmkfstools -z /vmfs/devices/disks/t10.F405E46494C4540046F455B64787D285941707D203F45765 /vmfs/volumes/Datastore2/localrdm1/localrdm1.vmdk

https://kb.vmware.com/selfservice/microsites/search.do?language=en_US&cmd=displayKC&externalId=1017530