Solved

Powershell Script to Create DB Connection and retrieve data

Posted on 2014-02-06
2
5,186 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
2 Comments
 
LVL 69

Accepted Solution

by:
Qlemo earned 500 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 18

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

I thought I'd write this up for anyone who has a request to create an anonymous whistle-blower-type submission form created using SharePoint 2010 (this would probably work the same for 2013). It's not 100% fool-proof but it's as close as you can get…
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

772 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