Some Powershell functions for Preparing Mysql Statements and executing them.
Requires the MySQL .NET Connector
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 | $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
1 2 3 4 | $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
1 2 3 4 5 6 7 8 | $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 } |