无法使用IBM.Data.DB2连接器将从DB2数据库提取的数据数组写出到PSObject

dsf9zpds  于 2022-11-07  发布在  DB2
关注(0)|答案(1)|浏览(135)

当我使用Export-CSV定向到$extractFile时,我可以使用$extractFile = 'D:\TEST\Q_sampler_result_file.csv'将数据直接提取到CSV文件。
我使用PSObject将同一个表放入一个数组中,如果成功的话,我会将该数组定向到一个HTML电子邮件,这有什么问题吗?

Windows PowerShell
Copyright (C) 2009 Microsoft Corporation. All rights reserved.

PS C:\Users\ME> $factory = [System.Data.Common.DbProviderFactories]::GetFactory("IBM.Data.DB2")
PS C:\Users\ME> $cstrbld = $factory.CreateConnectionStringBuilder()
PS C:\Users\ME> $cstrbld.Database = 'dbname'
PS C:\Users\ME> $cstrbld.UserID = 'ME'
PS C:\Users\ME> $cstrbld.Password = 'PWD'
PS C:\Users\ME> $cstrbld.Server = 'AIXbox:50001'
PS C:\Users\ME> $dbconn = $factory.CreateConnection()
PS C:\Users\ME> $dbconn.ConnectionString = $cstrbld.ConnectionString
PS C:\Users\ME> $dbconn.Open()
PS C:\Users\ME> $dbcmd = $factory.CreateCommand()
PS C:\Users\ME> $dbcmd.Connection = $dbconn
PS C:\Users\ME> ########################################################################################################################################
PS C:\Users\ME> #DEMONSTRATE --> showing count of how many failed work units in  Application.PFKIWORKUNIT
PS C:\Users\ME> $dbcmd.CommandText = 'SELECT PFIWORKUNIT,ACTOR,FLOWDEFINITION,WORKTITLE,STATUS FROM LAWSON.PFIWORKUNIT WHERE STATUS BETWEEN 0 AND 3'
PS C:\Users\ME> ########################################################################################################################################
PS C:\Users\ME> $dbcmd.CommandType = [System.Data.CommandType]::Text
PS C:\Users\ME> $da = $factory.CreateDataAdapter()
PS C:\Users\ME> $da.SelectCommand = $dbcmd
PS C:\Users\ME> $ds = New-Object System.Data.DataSet
PS C:\Users\ME> $rowCount = $da.Fill($ds)
PS C:\Users\ME> $dbconn.Close()
PS C:\Users\ME>
PS C:\Users\ME> write-host $rowCount
10 ##<-- There are TEN rows of data with the five columns named below
PS C:\Users\ME>
PS C:\Users\ME> if($rowCount -gt 0) {
>>
>>
>> $exportObject=@()
>> $Object = New-Object PSObject
>> $ds | ForEach{
>>         $Object | Add-Member -MemberType NoteProperty -Name "PFIWORKUNIT" -Value $row.PFIWORKUNIT
>>         $Object | Add-Member -MemberType NoteProperty -Name "ACTOR" -Value $row.ACTOR
>>         $Object | Add-Member -MemberType NoteProperty -Name "FLOWDEFINITION" -Value $row.FLOWDEFINITION
>>         $Object | Add-Member -MemberType NoteProperty -Name "WORKTITLE" -Value $row.WORKTITLE
>>         $Object | Add-Member -MemberType NoteProperty -Name "STATUS" -Value $row.STATUS
>>         $exportObject += $Object
>> }
>>
>>
>> write-host $exportObject
>> }
>>
@{PFIWORKUNIT=; ACTOR=; FLOWDEFINITION=; WORKTITLE=; STATUS=} ##<-- listing out ONLY Headers of Columns
ippsafx7

ippsafx71#

问题是你在ForEach循环中使用了$row。在你使用ForEach的上下文中,它实际上是ForEach-Object的缩写。当它遍历一个数组时,每次迭代都用$_来表示,但是当你想引用它时,这种表示就丢失了,因为你把$Object连接到了某个东西。因此,在引用$row时,$_值将引用$Object
你可能想尝试ForEach($row in $ds){,它将遍历$ds,对于数组中的每一项,该项都将用$row表示。但是,由于$ds是一个数据集,你难道不想使用$ds.tables[0].rows吗?类似于:

$exportObjects = ForEach($row in $ds.tables[0].rows){
    [pscustomobject]@{
         "PFIWORKUNIT" = $row.PFIWORKUNIT
         "ACTOR" = $row.ACTOR
         "FLOWDEFINITION" = $row.FLOWDEFINITION
         "WORKTITLE" = $row.WORKTITLE
         "STATUS" = $row.STATUS
    }
}

我很确定最终的结果是$exportObjects是您希望能够转换为HTML的数组。它还避免了您之前所做的+=,这是一个代价高昂的PowerShell过程,因为每次发生时,它都会破坏并重新创建引用数组。相反,我只是在循环中创建对象,并将它们一次性收集到一个变量中。

相关问题