Solved

Powershell Script to Create DB Connection and retrieve data

Posted on 2014-02-06
2
5,679 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 70

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 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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
A project that enables an administrator to perform actions within a user session context not just at the time of login but any time later on day(s) or week(s) later.
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

628 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