OdbcDataReader.Read() works different in and out of function

by Matamba at 2012-10-29 19:46:31

Hi all.
I’m trying to get query results by my function. Function should read data by command or by reader - to support multiple Oracle procedure’s cursors out parameters as same as single query. So the code
[code2=powershell]function Get-SqlResults {
[cmdletbinding()]
Param (
[System.Data.Odbc.OdbcCommand]$Query,
[System.Data.Odbc.OdbcDataReader]$Reader
)

Begin {
if ($Reader -eq $Null) {
$Reader = $Query.ExecuteReader()
$ReaderCreated = $True
} else {
$ReaderCreated = $False
}
}

Process {
while ($Reader.Read()) {
$v = New-Object -TypeName ‘System.Management.Automation.PSObject’
foreach ($i in (0…($Reader.FieldCount - 1))) {
Add-Member -InputObject $v -MemberType NoteProperty -Name $Reader.GetName($i) -Value $Reader.Item($i)
}
$v #return fetch results
}
}

End {
if ($ReaderCreated) {
$Reader.Close()
} else {
$Dummy = $Reader.NextResult()
}
}
}

$Connection = New-Object system.data.odbc.odbcconnection
$Connection.ConnectionString = ‘DSN=;Uid=;Pwd=’ # Here is right connection string
$Connection.Open()

$Query = New-Object system.data.odbc.odbccommand
$Query.Connection = $connection
$Query.CommandText = 'select dummy, level lv from dual connect by level <= 3’

if ($True) {
$R = $Query.ExecuteReader()
Get-SqlResults -Reader $R -Verbose
$R.Close()
} else {
Get-SqlResults -Query $Query -Verbose
}

$Connection.Close()[/code2]
This code doesn’t works - $Reader.Read() returns false.
If I change $True to $False on line 44 then function works, if I move loop out from function, it also works fine.

I can’t understand where I’m wrong.
by DonJ at 2012-10-30 07:55:29
Without having access to your data to test this with, I’m not sure I’m going to be able to offer any help. You might consider posting in our advanced forum, or pinging Poshoholic and asking him to take a look.

At a guess, I suppose the Reader isn’t being passed into the function ByReference, so what the function gets isn’t a valid Reader object. I had some trouble coming up with a good approach to this myself when I wrote my first Toolmaking book. If you go to MonthOfLunches.com and scroll all the way down, you can find the original code samples, and one of the last chapters is where I dealt with databases. Ultimately, I don’t think I was able to pass a Reader in.
by Matamba at 2012-10-30 21:06:48
Thanks for answer!
There’s no something data specific in this test so you can reintroduce it on any available database and any correct query.
And you’re right about pass Reader to function. I reduced the test to look deeper, but I don’t know .Net framework to understand what it’s mean and how to deal with it.
[code2=powershell]function Test-DataReader {
[cmdletbinding()]
Param (
$Reader
)

if ($R -eq $Null) {
‘r is null’
}

$Reader -eq $R
$Reader.GetType()
$R.GetType()
}

$Connection = New-Object system.data.odbc.odbcconnection
$Connection.ConnectionString = ‘DSN=TicketsShopK’
$Connection.Open()

$Query = New-Object system.data.odbc.odbccommand
$Query.Connection = $connection
$Query.CommandText = ‘select null v from rdb$database’

$R = $Query.ExecuteReader()

#$R.GetType()
Test-DataReader -Reader $R
$R.Close()

$Connection.Close()[/code2]
Result is:
[quote]
False

IsPublic IsSerial Name BaseType
-------- -------- ---- --------
True False OdbcDataReader System.Data.Common.DbDataReader
True False OdbcDataReader System.Data.Common.DbDataReader
[/quote]
And $R.Read() in function returns false as well, so I think its also a wrong copy of original $R object.
I tried to pass $Query (OdbcCommand) object and it passed by reference - ($Query -eq $Reader) returns true.
Ok, if there are no workaround to make this work, I’ll split my Oracle procedure to several and return one cursor from one procedure.
by poshoholic at 2012-10-31 07:02:22
If you must pass the reader in by reference, PowerShell allows you to do that.

For example:
function Test-ByReference {
param(
[Parameter(Position=0, Mandatory=$true)]
[REF]
$InputString
)

“The value of the string is: $($InputString.Value).”
“Let’s change that to ‘New value’ instead.”
$InputString.Value = ‘New value’
}
$testString = 'Hello world’
Test-ByReference -InputString ([REF]$testString)
"Now, after our function call, the string has the value ‘$testString’."

If you update your function to require a reader to be passed by reference, does that solve your problem?
by Matamba at 2012-10-31 08:56:59
Yes, its solved my problem. Thanks a lot!

Here is code if somebody will be interesting

[code2=powershell]function Get-SqlResults {
[cmdletbinding()]
Param (
[System.Data.Odbc.OdbcCommand]$Query,
[REF]$ReaderRef
)

Begin {
if ($ReaderRef -eq $Null) {
$ReaderRef = [REF]$Query.ExecuteReader()
$ReaderCreated = $True
} else {
$ReaderCreated = $False
}
}

Process {
while ($ReaderRef.Value.Read()) {
$v = New-Object -TypeName ‘System.Management.Automation.PSObject’
foreach ($i in (0…($ReaderRef.Value.FieldCount - 1))) {
Add-Member <br> -InputObject $v
-MemberType NoteProperty <br> -Name $ReaderRef.Value.GetName&#40;$i&#41;
-Value $ReaderRef.Value.Item($i)
}
$v #return fetch results
}
}

End {
if ($ReaderCreated) {
$ReaderRef.Value.Close()
} else {
$Dummy = $ReaderRef.Value.NextResult()
}
}
}

$Connection = New-Object system.data.odbc.odbcconnection
$Connection.ConnectionString = ‘DSN=FBTest’ # Here is right connection string
$Connection.Open()

$Query = New-Object system.data.odbc.odbccommand
$Query.Connection = $connection
$Query.CommandText = ‘select * from rdb$database’

$R = $Query.ExecuteReader()
Get-SqlResults -ReaderRef ([REF]$R) -Verbose
$R.Close()

$Connection.Close()[/code2]