Interroger une DB Oracle/SQL avec Powershell
Vous souhaitez interroger vos DBs mais les m?thodes fournies sont chiantes peu ergonomiques?
Facile! Voici 2 fonctions qui font le boulot.
Remarque utile : pas besoin d’installer un quelconque client! C’est natif !
SQL
Function querySQL ( $query, $database='master', $Server) {
$connection = New-Object System.Data.SqlClient.SqlConnection;
$connection.ConnectionString = "Server=$Server;Database=$database;Integrated Security=True;Connect Timeout=$timeOut;";
try {
$connection.Open();
$command = $connection.CreateCommand();
$command.CommandText = $query;
try{
$result = $command.ExecuteReader();
$SQLData = new-object System.Data.DataTable;
$SQLData.Load($result);
$connection.Close();
Remove-Variable connection, command, result
$SQLData
}
catch{Write-Host "Oups: Cannot query" -foregroundcolor Magenta}
}
catch{Write-Host "Oups: Connot open : Do you have the right to connect to this DB?" -foregroundcolor Magenta}
}
Oracle
function queryOracle ( $TNSName, $Query, $Username, $Password) {
[System.Reflection.Assembly]::LoadWithPartialName("System.Data.OracleClient") | out-null
$OracleConnection = New-Object System.Data.OracleClient.OracleConnection("Data Source=$TNSName;User Id=$Username;Password=$Password;Integrated Security=no");
$OracleConnection.Open()
$SelectCommand = New-Object System.Data.OracleClient.OracleCommand;
$SelectCommand.Connection = $OracleConnection
$SelectCommand.CommandText = $Query
$SelectCommand.CommandType = [System.Data.CommandType]::Text
$result = New-Object System.Data.DataTable
$result.Load($SelectCommand.ExecuteReader())
$reader = $SelectCommand.ExecuteReader()
while ($reader.Read()) {
$obj = New-Object PSObject
0..($reader.FieldCount-1) | % {
$obj | Add-Member -MemberType NoteProperty -Name ($reader.GetName($_) -replace "\W", "") -Value ($reader.GetValue($_))
}
$obj
}
}
1 commentaire