by Dwayne Dibbley at 2012-12-14 02:44:58
With the following code, I am pulling a list from mysql. The list looks like :by nohandle at 2012-12-14 03:46:33
group1
group2
Etc.
however when i run the code i seem to get a blank group to start with and not the first one in the list ie group 1, have i messed up the way the group list is returned from mysql or does the ForEach start at 0 or something?
heres the code:Param($ScriptFile = "c:\users\nigel\desktop\update_binaries.ps1",
$MaxThreads = 3,
$SleepTimer = 500,
$MaxWaitAtEnd = 600,
$OutputType = "Text")
[void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 6.6.4\Assemblies\v2.0\MySQL.Data.dll")
function Execute-MySQLQuery([string]$query) {
$cmd = New-Object MySql.Data.MySqlClient.MySqlCommand($query, $connMySQL)
$dataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)
$dataSet = New-Object System.Data.DataSet
$dataAdapter.Fill($dataSet, "data")
$cmd.Dispose()
return $dataSet.Tables["data"]
}
$connMySQL = New-Object MySql.Data.MySqlClient.MySqlConnection
$connMySQL.ConnectionString = “server=myserver;uid=myuser;pwd=mypassword;database=mydb;â€
$connMySQL.Open()
$query = "SELECT Name FROM groups WHERE active = 1 ORDER BY Name;"
$groups = Execute-MySQLQuery $query
"Killing existing jobs . . ."
Get-Job | Remove-Job -Force
"Done."
$i = 0
ForEach ($Group in $Groups){
While ($(Get-Job -state running).count -ge $MaxThreads){
Write-Progress -Activity "Creating Group List" -Status "Waiting for threads to close" -CurrentOperation "$i threads created - $($(Get-Job -state running).count) threads open" -PercentComplete ($i / $Groups.count * 100)
Start-Sleep -Milliseconds $SleepTimer
}
"Starting job - $Group.name "
$i++
Start-Job -FilePath $ScriptFile -ArgumentList $Group.Name -Name $Group.Name | Out-Null
Write-Progress -Activity "Creating Group List" -Status "Starting Threads" -CurrentOperation "$i threads created - $($(Get-Job -state running).count) threads open" -PercentComplete ($i / $Groups.count * 100)
}
$Complete = Get-date
While ($(Get-Job -State Running).count -gt 0){
$GroupsStillRunning = ""
ForEach ($System in $(Get-Job -state running)){$GroupsStillRunning += ", $($System.name)"}
$GroupsStillRunning = $GroupsStillRunning.Substring(2)
Write-Progress -Activity "Creating Group List" -Status "$($(Get-Job -State Running).count) threads remaining" -CurrentOperation "$GroupsStillRunning" -PercentComplete ($(Get-Job -State Completed).count / $(Get-Job).count * 100)
If ($(New-TimeSpan $Complete $(Get-Date)).totalseconds -ge $MaxWaitAtEnd){"Killing all jobs still running . . .";Get-Job -State Running | Remove-Job -Force}
Start-Sleep -Milliseconds $SleepTimer
}
"Reading all jobs"
If ($OutputType -eq "Text"){
ForEach($Job in Get-Job){
"$($Job.Name)"
"****************************************"
Receive-Job $Job
" "
}
}
ElseIf($OutputType -eq "GridView"){
Get-Job | Receive-Job | Select-Object * -ExcludeProperty RunspaceId | out-gridview
}
The last time I was getting some data from SQL I also got the first one blank (I queried only for the top 1 so I was surprised two items were returned)by Dwayne Dibbley at 2012-12-14 04:24:13
[0]–blank–
[1]data
Not sure why, and can’t test it right now. But I ended up taking the $data[1] because that was what I cared for.
Sorry I cant help you further but if you google type of the variable you should find plenty info on issues with it in powershell.
I know you did not ask for it but let me point out two things:
1] Using the subexpression ( $() ) is totally unnecassary if you don’t pass force expand in quoted string.
It also can be used to pass more than one command or more than one pipeline but doing this is rarely needed and most of the times only adds unnecessary complexity to the script.
This works also.(Get-Job -State Running).count
and this can be shortened to this:(New-TimeSpan -Start $Complete).totalseconds
etc.
2]$GroupsStillRunning = ""
ForEach ($System in $(Get-Job -state running)){$GroupsStillRunning += ", $($System.name)"}
$GroupsStillRunning = $GroupsStillRunning.Substring(2)
Write-Progress -Activity "Creating Group List" -Status "$($(Get-Job -State Running).count) threads remaining" -CurrentOperation "$GroupsStillRunning" -PercentComplete ($(Get-Job -State Completed).count / $(Get-Job).count * 100)
If the job state changes between the command execution you are gonna output wrong data. Also you can use -join "," to create a comma separated list from an array.
Thanks for the pointer, when debugging the $groups it looks like this:by Dwayne Dibbley at 2012-12-23 04:01:00{7, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow}
i am guessing it the first entry 7 in this case that is throwing the wobbly with the ForEach as its not a datarow?
found a fix for the $groups array problem, changing this$dataAdapter.Fill($dataSet, "data") > null
now it returns{System.Data.DataRow, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow, System.Data.DataRow}
