{"id":380,"date":"2015-07-01T16:12:58","date_gmt":"2015-07-01T15:12:58","guid":{"rendered":"http:\/\/snakenet.eu\/blog\/?p=380"},"modified":"2016-01-29T12:11:53","modified_gmt":"2016-01-29T11:11:53","slug":"interroger-une-db-oraclesql-avec-powershell","status":"publish","type":"post","link":"https:\/\/snakenet.eu\/blog\/interroger-une-db-oraclesql-avec-powershell\/380\/","title":{"rendered":"Interroger une DB Oracle\/SQL avec Powershell"},"content":{"rendered":"

Vous souhaitez interroger vos DBs mais les m?thodes fournies sont chiantes <\/del>peu ergonomiques?
\nFacile! Voici 2 fonctions qui font le boulot.
\nRemarque utile : pas besoin d’installer un quelconque client! C’est natif !<\/p>\n

SQL<\/h1>\n

Function querySQL ( $query, $database='master', $Server) {
\n\t$connection = New-Object System.Data.SqlClient.SqlConnection;
\n\t$connection.ConnectionString = \"Server=$Server;Database=$database;Integrated Security=True;Connect Timeout=$timeOut;\";
\n \ttry {
\n\t\t$connection.Open();
\n\t\t$command = $connection.CreateCommand();
\n\t\t$command.CommandText = $query;
\n \t\ttry{
\n\t\t\t$result = $command.ExecuteReader();
\n\t\t\t$SQLData = new-object System.Data.DataTable;<\/p>\n

\t\t\t$SQLData.Load($result);
\n\t\t\t$connection.Close();
\n\t\t\tRemove-Variable connection, command, result
\n\t\t\t$SQLData
\n\t\t}
\n\t\tcatch{Write-Host \"Oups: Cannot query\" -foregroundcolor Magenta}
\n\t}
\n\tcatch{Write-Host \"Oups: Connot open : Do you have the right to connect to this DB?\" -foregroundcolor Magenta}
\n}
\n<\/code><\/p>\n

Oracle<\/h1>\n

function queryOracle ( $TNSName, $Query, $Username, $Password) {
\n [System.Reflection.Assembly]::LoadWithPartialName(\"System.Data.OracleClient\") | out-null<\/p>\n

$OracleConnection = New-Object System.Data.OracleClient.OracleConnection(\"Data Source=$TNSName;User Id=$Username;Password=$Password;Integrated Security=no\");
\n $OracleConnection.Open() <\/p>\n

$SelectCommand = New-Object System.Data.OracleClient.OracleCommand;
\n $SelectCommand.Connection = $OracleConnection
\n $SelectCommand.CommandText = $Query
\n $SelectCommand.CommandType = [System.Data.CommandType]::Text <\/p>\n

$result = New-Object System.Data.DataTable
\n $result.Load($SelectCommand.ExecuteReader()) <\/p>\n

$reader = $SelectCommand.ExecuteReader()
\n while ($reader.Read()) {
\n $obj = New-Object PSObject
\n 0..($reader.FieldCount-1) | % {
\n $obj | Add-Member -MemberType NoteProperty -Name ($reader.GetName($_) -replace \"\\W\", \"\") -Value ($reader.GetValue($_))
\n }
\n $obj
\n }
\n}
\n<\/code><\/p>\n","protected":false},"excerpt":{"rendered":"

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 = \u00ab\u00a0Server=$Server;Database=$database;Integrated Security=True;Connect Timeout=$timeOut;\u00a0\u00bb; try { $connection.Open(); $command = […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[62,58],"tags":[],"class_list":["post-380","post","type-post","status-publish","format-standard","hentry","category-oracle","category-powershell"],"_links":{"self":[{"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/posts\/380","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/comments?post=380"}],"version-history":[{"count":2,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/posts\/380\/revisions"}],"predecessor-version":[{"id":382,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/posts\/380\/revisions\/382"}],"wp:attachment":[{"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/media?parent=380"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/categories?post=380"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/snakenet.eu\/blog\/wp-json\/wp\/v2\/tags?post=380"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}