PoshCode Archive  Artifact [ef258e6834]

Artifact ef258e683470c299628dac16df9c86bb4582468e50ea203aa13f3dd1e68bc805:

  • File Get-OracleData.ps1 — part of check-in [1f5c37de8c] at 2018-06-10 13:03:02 on branch trunk — Generic function to execute query and return DataTable from any OLEDB data source. Tested against Excel, Informix, Oracle and SQL Server sources. (user: Chad Miller size: 1998)

# encoding: ascii
# api: powershell
# title: Get-OracleData
# description: Generic function to execute query and return DataTable from any OLEDB data source. Tested against Excel, Informix, Oracle and SQL Server sources.
# version: 12.0
# type: script
# author: Chad Miller
# license: CC0
# function: Get-OLEDBData
# x-poshcode-id: 1938
# x-archived: 2010-07-18T09:43:54
#
#
###########################################################################
# Get-OLEDBData
# --------------------------------------------
# Description: This function is used to retrieve data via an OLEDB data
#              connection.
#
# Inputs: $connectstring  - Connection String.
#         $sql            - SQL statement to be executed to retrieve data.
# 
# Usage: Get-OLEDBData <connction string> <SQL statement>
#
#Connection String for Excel 2007:
#"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=`"$filepath`";Extended Properties=`"Excel 12.0 Xml;HDR=YES`";"
#Connection String for Excel 2003:
#"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=`"$filepath`";Extended Properties=`"Excel 8.0;HDR=Yes;IMEX=1`";"
#Excel query
#'select * from [sheet1$]'
#Informix
#"password=$password;User ID=$userName;Data Source=$dbName@$serverName;Persist Security Info=true;Provider=Ifxoledbc.2"
#Oracle
#"password=$password;User ID=$userName;Data Source=$serverName;Provider=OraOLEDB.Oracle"
#SQL Server
#"Server=$serverName;Trusted_connection=yes;database=$dbname;Provider=SQLNCLI;"
###########################################################################
function Get-OLEDBData ($connectstring, $sql) {
   $OLEDBConn = New-Object System.Data.OleDb.OleDbConnection($connectstring)
   $OLEDBConn.open()
   $readcmd = New-Object system.Data.OleDb.OleDbCommand($sql,$OLEDBConn)
   $readcmd.CommandTimeout = '300'
   $da = New-Object system.Data.OleDb.OleDbDataAdapter($readcmd)
   $dt = New-Object system.Data.datatable
   [void]$da.fill($dt)
   $OLEDBConn.close()
   return $dt
}