Powershell Oracle查询-动态分配读取器对象成员 [英] powershell oracle query - dynamically assign reader object member

问题描述

我必须使用powershell查询许多oracle数据库表. 有没有一种方法可以动态地构建供读者对象成员使用的变量?

I have to query many oracle database tables using powershell. Is there a way to dynamically build the variables to use for the reader object members?

我必须指定读取器对象成员名称，例如GetInt64(3)，GetString(4)等，以便我检索表中每一列的值.

I have to specify the reader object member names like GetInt64(3), GetString(4), and so on for me to retrieve values for each column in the table.

由于我必须枚举这么多具有​​不同列名的表，将其写出来将需要大量的工作.

Since I have to enumerate so many tables with varying column names, it will be a lot of work to write this out.

如何编写它，以便可以使用变量名代替成员名? thnx-这是我在此网站上的第一个帖子.

How can I write it so I can use the variable names instead of the member names? thnx - this is my very first post here on this web site.

这是我的问题中引用的以下代码的一部分.

This is the part of the below code that is being referenced in my question.

.
.
$premise=$reader.GetInt64(3)
$license=$reader.GetString(4)
$invoice_num=$reader.GetInt64(5)
.
.
.

我从此链接获得了这个示例(我不确定是否应该在同一线程上发布后续问题.: 在powershell中处理空值

I got this example from this link(I wasn't sure if I was supposed to post a follow up question on the same thread.: Handling nulls in powershell

# Create a datareader for a SQL statement 
$sql="select * from legacydb.ebt_invoice_dtl where premnum = 397743"
$command = New-Object Oracle.DataAccess.Client.OracleCommand( $sql,$conn)
$reader=$command.ExecuteReader()

# Write out the result set structure 
for ($i=0;$i -lt $reader.FieldCount;$i++) {
Write-Host  $reader.GetName($i) $reader.GetDataTypeName($i) 
}

# Write out the results 
while ($reader.read()) {
$dtl_id=$reader.GetDecimal(0)  
$invoice_id=$reader.GetDecimal(1)
$debtor=$reader.GetInt64(2)
$premise=$reader.GetInt64(3)
$license=$reader.GetString(4)
$invoice_num=$reader.GetInt64(5)
$deb_cred=$reader.GetString(6)
$inv_ref=$reader.GetString(7)
$rate=$reader.GetDecimal(8)
$charge=$reader.GetDouble(9)
$usage=$reader.GetInt64(10)
$tax=$reader.GetDouble(11)
$rate_code=$reader.GetString(12)
$inv_date=$reader.GetDateTime(13)
$missed=$reader.GetString(14)
$change_date=$reader.GetDateTime(15)
$dnp=$reader.GetString(16)

Write-Host "$dtl_id $invoice_id $debtor $premise $license $invoice_num $deb_cred        $inv_ref $rate $charge $usage $tax $rate_code $inv_date $missed $change_date $dnp "
}

推荐答案

当然，您可以使用$reader.item('columnname')通过名称获取指定列中的值.

Certainly, you could use $reader.item('columnname') to get the value in the specified column by name.

一种更理想的方法是创建对象，然后将它们通过管道传递到Format-Table进行输出.

A more idomatic way to go would be to create objects and then pipe them to Format-Table to do output.

假设列名是您的变量名，下面是一个帮助器函数，用于从读取器读取对​​象:

Assuming the column names are your variable names, here's a helper function to read the objects from the reader:

function Get-OracleDbObject
{
   param(
       [string]$Query,
       [Oracle.DataAccess.Client.OracleConnection]$Connection
   )

   $command = New-Object Oracle.DataAccess.Client.OracleCommand $Query, $Connection
   $reader = $command.ExecuteReader()

   while($reader.Read()) {
        $props = @{}
        for($i = 0; $i -lt $reader.FieldCount; $i+=1) {
            $name = $reader.GetName($i)
            $value = $reader.item($i)
            $props.Add($name, $value)   
        }
        $obj = new-object PSObject -Property $props
        Write-Output $obj
   }

   $reader.Dispose()
   $command.Dispose()
}

然后，您可以这样做将事情写到列中:

Then you could just do this write things into columns:

$sql = "select * from legacydb.ebt_invoice_dtl where premnum = 397743"
Get-OracleDbObject -Query $sql -Connection $conn | Format-Table *

这篇关于Powershell Oracle查询-动态分配读取器对象成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！