MySql dataset question

This topic contains 0 replies, has 1 voice, and was last updated by  Forums Archives 5 years, 10 months ago.

  • Author
    Posts
  • #5346

    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 :

    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

    }

    by nohandle at 2012-12-14 03:46:33

    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)
    [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.

    by Dwayne Dibbley at 2012-12-14 04:24:13

    Thanks for the pointer, when debugging the $groups it looks like this:

    {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?

    by Dwayne Dibbley at 2012-12-23 04:01:00

    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}

    🙂

You must be logged in to reply to this topic.