I am connection to a database via an interactive SQL-Command-Line-Utility and when I am executing an SQL command, I want to pipe the output to another power shell cmdlet, for example out-gridview.
Lets say for example I connect to a Sybase ASE database, and I login with
C:\users\user>isql -U<user> -S<SI>
[password]:
1>use master
2>go
1>SELECT * FROM sysusers
2>go | out-gridview
The pipe to out-gridview, nor any other cmdlet, is working. I can redirect the output to a file via > nonetheless, but I think its pre-implemented in the isql-command-line-utility.
Has anyone an idea how to pipe this stuff? I am well aware that there are scripts like this
$query="select * from syslogins"
$conn=New-Object System.Data.Odbc.OdbcConnection
$conn.ConnectionString= "driver={Adaptive Server Enterprise};
dsn=SERVER_NAME;db=master;na=IP,PORT;uid=SOMEUSER;pwd=******;"
$conn.open()
$cmd=new-object System.Data.Odbc.OdbcCommand($query,$conn)
$cmd.CommandTimeout=30
write-host $query
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.odbc.odbcDataAdapter($cmd)
write-host $ds
$da.fill($ds)
$ds.Tables[0] | out-gridview
$conn.close()
But I dont' want to store my password in clear text inside of a script. I want to login to a session, then execute the commands and then pipe my information.
You can't use cmdlets inside a console application. Console applications return text, while cmdlets work with object. You would need to use something like your second sample or run a "script" in isql and parse the text-output to objects.
Since you don't want to automate this you could simply use Read-Host to get the password when running the script.
#Ask for password
$pass = Read-Host Password
$query="select * from syslogins"
$conn=New-Object System.Data.Odbc.OdbcConnection
#User password-variable in string
$conn.ConnectionString= "driver={Adaptive Server Enterprise};
dsn=SERVER_NAME;db=master;na=IP,PORT;uid=SOMEUSER;pwd=$pass;"
$pass = ""
$conn.open()
$cmd=new-object System.Data.Odbc.OdbcCommand($query,$conn)
$cmd.CommandTimeout=30
write-host $query
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.odbc.odbcDataAdapter($cmd)
write-host $ds
$da.fill($ds)
$ds.Tables[0] | out-gridview
$conn.close()