0

Trying to use runspaces to do some web scraping and database inserts. when trying to multithread the job, any more than a single thread causes database lock errors and my script to crash. I'm using adodb connection to an access database. 

Function InsertToTable1 {
    param ($database,$query,$col2,$col3,$col4,$col5,$col6,$col7,$col8,$col9,$col10,$col11)
    $OpenStatic = 3
    $LockOptimistic = 3
    $Connection = New-Object -ComObject ADODB.Connection
    $Connection.Open("Provider = Microsoft.ACE.OLEDB.12.0;Data Source=$database") 
    $Recordset = new-object -ComObject ADODB.Recordset
    $Recordset.open($query,$Connection,$OpenStatic,$LockOptimistic)
    $Recordset.AddNew()
    $Recordset.Fields.Item("col2") = $col2
    $Recordset.Fields.Item("col3") = $col3
    $Recordset.Fields.Item("col4") = $col4
    $Recordset.Fields.Item("col5") = $col5
    $Recordset.Fields.Item("col6") = $col6
    $Recordset.Fields.Item("col7") = $col7
    $Recordset.Fields.Item("col8") = $col8
    $Recordset.Fields.Item("col9") = $col9
    $Recordset.Fields.Item("col10") = $col10
    $Recordset.Fields.Item("col11") = $col11
    $Recordset.Update()
    $Recordset.close()
    $Connection.close()
}
$code = @"
Function InsertToTable1 {$(Get-Command InsertToTable1 | Select -expand Definition)}
"@ #

$pool = [RunspaceFactory]::CreateRunspacePool(1,1) #[int]$env:NUMBER_OF_PROCESSORS+1
$pool.ApartmentState = "MTA"
$pool.Open()
$runspaces = @()

$scriptblock = {
                ......
                InsertToTable1 $DB $Q1 $varState $varTown $varStreet $fullURL $varStyle $varModel $varDescription $varUseCode $varDate $varDate
                ......
                }

    foreach ($x in $xxxxx) {
        $runspace = [PowerShell]::Create().AddScript($scriptblock).AddArgument($x).AddArgument($code)
        $runspace.RunspacePool = $pool

    # Add runspace to runspaces collection and "start" it
        # Asynchronously runs the commands of the PowerShell object pipeline
        $runspaces += [PSCustomObject]@{ Pipe = $runspace; Status = $runspace.BeginInvoke() }
    }
     while ($runspaces.Status.IsCompleted -notcontains $true) {}

    $results = @()
    foreach ($runspace in $runspaces ) {
        $results = $runspace.Pipe.EndInvoke($runspace.Status)
        $runspace.Pipe.Dispose()
    }
    $pool.Close() 
    $pool.Dispose()
timbojones
  • 1
  • 2
  • 2
    what is the error message you're receiving? – jtate Oct 07 '18 at 22:40
  • Faulting application name: powershell.exe, version: 10.0.14393.206, time stamp: 0x57daccf5 Faulting module name: OLEAUT32.dll, version: 10.0.14393.2248, time stamp: 0x5ae3f93e Exception code: 0xc0000005 Fault offset: 0x0000000000011293 Faulting process id: 0x8d8 Faulting application start time: 0x01d45de763265f9d Faulting application path: C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe Faulting module path: C:\Windows\System32\OLEAUT32.dll Report Id: 41799045-944b-4e82-ba1e-6c58bd6329d6 Faulting package full name: Faulting package-relative application ID: – timbojones Oct 09 '18 at 22:31
  • 2018.10.09 - 18:38:44 - ErrorRecord - Could not update; currently locked. – timbojones Oct 09 '18 at 22:37

0 Answers0