In PowerShell, how do I use Read-Host input to get parameter value

Question

I have a script that contains a parameter list of connection strings to some databases. The script does the following things:

Takes a database name as input using "Read-Host"
Uses the input to meet any of the If/ElseIf conditions and...
- connects to the database
- runs a SQL script to update the db

Here is some example code:

param (
# Connection Strings
  [string] $TestDb1_ConnectStr = "username/pwd@//hostname1:port1/sid1",
  [string] $TestDb2_ConnectStr = "username/pwd@//hostname2:port2/sid2",
  [string] $TestDb3_ConnectStr = "username/pwd@//hostname2:port3/sid3",
  [string] $SQL_Path = "C:\bin\sql"
)

$Environment = Read-Host "Please enter environment name..."

If ($Environment -eq "TestDb1")
{
  function Invoke-SqlPlus($file) { (gc $file) | sqlplus $TestDb1_ConnectStr }
  new-alias sql Invoke-SqlPlus
  sql $SQL_Path\$Environment"_sometable.sql"
}
ElseIf ($Environment -eq "TestDb2")
{
  function Invoke-SqlPlus($file) { (gc $file) | sqlplus $TestDb2_ConnectStr }
  new-alias sql Invoke-SqlPlus
  sql $SQL_Path\$Environment"_sometable.sql"
}
ElseIf ($Environment -eq "TestDb3")
{
  function Invoke-SqlPlus($file) { (gc $file) | sqlplus $TestDb3_ConnectStr }
  new-alias sql Invoke-SqlPlus
  sql $SQL_Path\$Environment"_sometable.sql"
}
...

I actually have 14 databases that I can connect to and instead of having 14 separate If/ElseIf conditions I want to be able to use the input from Read-Host to pass in the connection string and SQL script. Currently I can use the input to pass in the SQL script but I'm not able to pass in the db connection string.

I want to do something like this:

function Invoke-SqlPlus($file) { (gc $file) | sqlplus $Environment_ConnectStr }
new-alias sql Invoke-SqlPlus
sql $SQL_Path\$Environment"_sometable.sql"

However, I'm not sure how I can use the input from Read-Host to get the value from the parameter list for the connection string. How can I achieve this?

$file is the SQL script: $SQL_Path\$Environment"_sometable.sql — Keith
– Keith, Commented Jan 6, 2012 at 2:30
@manojlds - Yes, I saw your answer. Thank you very much. I will need to give your solution a try and will let you know. — Keith
– Keith, Commented Jan 6, 2012 at 7:08
@Keith Why do you want pipe the SQL script to SQLPlus instead of just providing it as a parameter like this: SQLPlus.exe 'username/pwd@//hostname1:port1/sid1' '@C:\MyScript.sql' ? — Andy Arismendi
– Andy Arismendi, Commented Jan 6, 2012 at 9:01
@AndyArismendi - I tried your approach earlier and the connection to the db stays open. BTW: my code above was missing an 'exit' command, which kills the db connection. Here's the modified code using the suggestion from manojlds: function Invoke-SqlPlus($file) { (gc $file) + 'exit' | sqlplus (get-variable "$($Environment)ConnectStr").value } new-alias sql Invoke-SqlPlus sql $SQL_Path\$Environment"_db_table.sql" — Keith
– Keith, Commented Jan 6, 2012 at 17:06

manojlds · Accepted Answer · 2012-01-06 04:21:03Z

1

What you are doing is equivalent to this:

(gc $SQL_Path\$Environment"_sometable.sql") | 
    sqlplus (get-variable "$($Environment)_ConnectStr").value

answered Jan 6, 2012 at 4:21

manojlds

303k66 gold badges482 silver badges426 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Start-Automating · Accepted Answer · 2012-01-06 04:24:53Z

0

You should refactor each of these parameters to be mandatory: [Parameter(Mandatory=$true)] and make them ValueFromPipelineByPropertyName. Then you can use something like Import-CSV to read these in from a data file.

Hope this Helps

answered Jan 6, 2012 at 4:24

Start-Automating

8,4372 gold badges33 silver badges50 bronze badges

6 Comments

manojlds Over a year ago

I can't understand how this answers the question.

Start-Automating Over a year ago

The big core of the problem here is that this script was engineered (somewhat poorly) to accept user input from their script. In most cases where Read-Host is used, it's better to take advantage of the build in prompting. He should be using a data file, and prompting one by one is not the road.

manojlds Over a year ago

Agree on the poorly engineered part.Thing is they cannot be made mandatory. They need not have been parameters in the first place. One of them has to be selected based on what the selected environment is.

Keith Over a year ago

Using user input and storing the parameter/values in the script is just an option right now. Ideally I want to store the db connection strings and other db info in a separate database table, read from the db table, and pass the values into the script, but I'm not there at the moment. Maybe I could try the data file (Import-CSV) approach for now.

Keith Over a year ago

Let me also add some context around the problem I'm trying to solve. We have a web app where we have to manually specify a target db, connections strings, username/pwd info, etc. What I'm trying to do is automate the manual steps. Eventually my automation script will be chained to a larger end-to-end process where user input will be required (e.g. which db to target, what db connections strings to use, etc.). Until my script is chained to the end-to-end process it needs to be run stand-alone, which is why I chose to use Read-Host to get the user input. Let me know if there is a better way.

|

Collectives™ on Stack Overflow

In PowerShell, how do I use Read-Host input to get parameter value

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related