Solved

Powershell Script to Create DB Connection and retrieve data

Posted on 2014-02-06
2
4,856 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 68

Accepted Solution

by:
Qlemo earned 500 total points
Comment Utility
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
Comment Utility
Replace ExecuteScalar with ExecuteReader

Scalar returns only one data while reader returns multiple data
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
In this previous article (https://oddytee.wordpress.com/2016/05/05/provision-new-office-365-user-and-mailbox-from-exchange-hybrid-via-powershell/), we made basic license assignments to users in O365. When I say basic, the method is the simplest way …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now