Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6527
  • Last Modified:

Powershell Script to Create DB Connection and retrieve data

Need help in establishing OLEDB connection to the database, i am using the below script to connect to a oracle database, When i run the script the output is not showing all the records/ data. Enclosed my output window at the bottom.

[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient")
 
$connectionString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(Host=XXXXXXX)(Port=1535)))(CONNECT_DATA=(SID=ABCD)));User ID=giltest;Password=XXXXXXXX"

$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)

$queryString = "SELECT PRODUCT_NAME FROM PRODUCTS"

$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)

$connection.Open()

$productNames = $command.ExecuteScalar();

write-host "List of the Products:" $productNames | ft

$connection.Close()

 [System.Reflection.Assembly]::LoadWithPartialName("System.Data.OraOLEDB.Oracle")
0
Jagadeeps
Asked:
Jagadeeps
1 Solution
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
ExecuteScalar only returns a single result (one column, one row). You'll have to use ExecuteReader instead, with some more code added:
Add-Type -Assembly System.Data.OracleClient
 
$connectionString = "Data Source=XXXXXXX:1535/ABCD; User ID=giltest; Password=XXXXXXXX"

$connection = New-Object System.Data.OracleClient.OracleConnection($connectionString)
$connection.Open()

$queryString = "SELECT PRODUCT_NAME FROM PRODUCTS"
$command = new-Object System.Data.OracleClient.OracleCommand($queryString, $connection)
reader = $command.ExecuteReader();

productNames = $(
  while ($reader.Read())
  {
    $reader.GetValue(0) 
  }
)

write-host "List of the Products:" $productNames | ft

$connection.Close()

# Add-Type -Assembly System.Data.OraOLEDB.Oracle

Open in new window

Note that I also changed to the PS 2.0 approach of loading assemblies. and used an EZConnect string instead of the old-fashioned connection description.
1
 
Raheman M. AbdulSenior Infrastructure Support Analyst & Systems DeveloperCommented:
Replace ExecuteScalar with ExecuteReader

Scalar returns only one data while reader returns multiple data
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now