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屋!