Skip to content

Instantly share code, notes, and snippets.

@kspeeckaert
Created April 14, 2022 13:29
Show Gist options
  • Save kspeeckaert/4db4e08e779472713e3840cd000b7aed to your computer and use it in GitHub Desktop.
Save kspeeckaert/4db4e08e779472713e3840cd000b7aed to your computer and use it in GitHub Desktop.
ODBC utility functions in PowerShell
function Get-CredsConnString() {
[OutputType([hashtable])]
[cmdletbinding()]
param (
[Parameter(Mandatory=$true)][PSCredential] $Credential
)
$PlainCred = [ordered] @{
UID = $Credential.UserName
PWD = [PSCredential]::new(0, $Credential.Password).GetNetworkCredential().Password
}
return $PlainCred
}
function Get-DenodoConnString() {
[OutputType([string])]
[cmdletbinding()]
param (
[Parameter(Mandatory=$true)][string]$Server,
[ValidateSet("DenodoODBC Unicode","DenodoODBC Unicode(x64)")]
[string]$Driver = 'DenodoODBC Unicode(x64)',
[int]$Port = 9996,
[string]$Database = 'admin',
[string]$Useragent,
[PSCredential] $Credential,
[ValidateSet("disable","prefer","require")]
[string]$SSLMode = 'disable',
[int]$MaxLongVarcharSize = 8190
)
$ConnString = [ordered] @{
DRIVER = "{${Driver}}"
SERVER = $Server
DATABASE = $Database
PORT = $Port
SSLmode = $SSLMode
MaxLongVarcharSize = $MaxLongVarcharSize
}
if ($PSBoundParameters.ContainsKey('Useragent')) {
$ConnString['UserAgent'] = $Useragent
}
if ($PSBoundParameters.ContainsKey('Credential')) {
$ConnString += Get-CredsConnString -Credential $Credential
}
return ($ConnString.Keys | ForEach-Object { "$_=$($ConnString.$_)" }) -join ';'
}
function Get-MsSQLConnString() {
[OutputType([string])]
[cmdletbinding()]
param (
[Parameter(Mandatory=$true)][string]$Server,
[ValidateSet("SQL Server Native Client 11.0","ODBC Driver 18 for SQL Server")]
[string]$Driver = 'SQL Server Native Client 11.0',
[int]$Port = 1433,
[string]$Database,
[string]$Useragent,
[PSCredential] $Credential,
[ValidateSet("yes","mandatory","no","optional","strict")] [string] $Encrypt = "no",
[ValidateSet("ReadWrite","ReadOnly")] [string]$ApplicationIntent,
[ValidateSet("Yes","No")] [string]$MultiSubnetFailover
)
$ConnString = [ordered] @{
DRIVER = "{${Driver}}"
SERVER = $Server
}
foreach ($kvp in $PSBoundParameters.GetEnumerator()) {
$ParamName = $kvp.Key
if ($ParamName -in ('Credential','Driver', 'Server')) {
Continue
}
$ParamVal = $kvp.Value
if ($ParamVal -eq $null) {
Continue
}
$ConnString[$ParamName] = $ParamVal
}
if ($PSBoundParameters.ContainsKey('Credential')) {
$ConnString += Get-CredsConnString -Credential $Credential
} else {
$ConnString['Trusted_Connection'] = 'yes'
}
return ($ConnString.Keys | ForEach-Object { "$_=$($ConnString.$_)" }) -join ';'
}
<#
Connect to the server using the ODBC connectionstring and execute the query.
The data is returned as a DataTable
#>
function Run-OdbcSQL {
[OutputType([System.Data.DataTable])]
[cmdletbinding()]
param (
[Parameter(Mandatory)][string]$ConnString,
[Parameter(Mandatory)][string]$Query,
[int]$Timeout
)
$Conn = New-Object System.Data.Odbc.OdbcConnection
$Conn.ConnectionString = $ConnString
$Conn.Open()
try {
$Command = New-Object Data.Odbc.OdbcCommand($Query,$Conn)
if ($PSBoundParameters.ContainsKey('Timeout')) {
Write-Verbose "Modifying query timeout to $Timeout second(s)"
$Command.CommandTimeout = $Timeout
}
$Result = $Command.ExecuteReader()
$Table = New-Object "System.Data.DataTable"
$Table.Load($Result)
# Prevent PS from unravelling a table with a single row
Write-Output -NoEnumerate $Table
} finally {
$Conn.Close()
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment