ODBC utility functions in PowerShell

odbc_utils.ps1

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()
    }
}