Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Powershell Script to Create DB Connection and retrieve data

Posted on 2014-02-06
2
Medium Priority
?
5,980 Views
Last Modified: 2014-02-06
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
Comment
Question by:Jagadeeps
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 71

Accepted Solution

by:
Qlemo earned 2000 total points
ID: 39840608
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
 
LVL 19

Expert Comment

by:Raheman M. Abdul
ID: 39840612
Replace ExecuteScalar with ExecuteReader

Scalar returns only one data while reader returns multiple data
0

Featured Post

Are You Ready for GDPR?

With the GDPR deadline set for May 25, 2018, many organizations are ill-prepared due to uncertainty about the criteria for compliance. According to a recent WatchGuard survey, a staggering 37% of respondents don't even know if their organization needs to comply with GDPR. Do you?

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A quick Powershell script I wrote to find old program installations and check versions of a specific file across the network.
Auditing domain password hashes is a commonly overlooked but critical requirement to ensuring secure passwords practices are followed. Methods exist to extract hashes directly for a live domain however this article describes a process to extract u…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

661 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question