在PowerShell 中, 可以透過叫用 .net framework library, 執行指令. 這裡利用 OLD DB 去對Orcale 進行存取如下:
# DB related settings. $dbProvider="MSDAORA.1" $dbHostName="ORACLE.DB" $dbUserName="TEST" $dbPassword="P@ssw0rd" $dbConnectionString="Provider=$dbProvider;Data Source=$dbHostName;User ID=$dbUserName;Password=$dbPassword" $dbConn = New-Object System.Data.OleDb.OleDbConnection($dbConnectionString) try { $dbConn.Open() $searchSql="select * from staff" $searchCmd=$dbConn.CreateCommand() $searchCmd.CommandText=$searchSql $searchDataAdapter=New-Object system.Data.OleDb.OleDbDataAdapter($searchCmd) $ds=New-Object system.Data.DataSet [void]$searchDataAdapter.Fill($ds) $firstName=$ds.Tables[0].Rows[0]['FirstName'] } catch { throw [Exception] "Execute error during SQL process." } finally { if($dbConn -ne $null) { $dbConn.Close() $dbConn.Dispose() } if($searchCmd -ne $null) { $searchCmd.Dispose() } }
因為工作需要, 故用到較舊的MS DAO, 但它不能在64bit平台中執行, 現在通常都會用Oracle OLAD 進行. 但這個不是重點. 透過.net library, 建立connection object, 之後建立command, 填入SQL statement 後執行. 完事後須要dispose以釋出系統資源.
Leave a Reply