<#
.SYNOPSIS
Read APPX ODBC query results as reusable PowerShell objects.
.DESCRIPTION
Use 32-bit Windows PowerShell 5.1 and the matching cookbook DSN.
Each invocation opens and disposes its own connection. Nothing is formatted
or exported automatically. Supply SELECT statements for reading data;
this helper does not replace database permissions or parse SQL for safety.
.EXAMPLE
$credential = Get-Credential -UserName appx
$rows = @(./read-appx-odbc-32.ps1 -Credential $credential)
$rows | Select-Object name, region_label | Export-Csv ./country.csv -NoTypeInformation -Encoding UTF8
.EXAMPLE
./read-appx-odbc-32.ps1 -Credential $credential -Sql 'SELECT * FROM DMO_DMO_COUNTRY WHERE name = ?' -Parameter @(@{Type='VarChar'; Size=14; Value='Canada'})
#>
[CmdletBinding(DefaultParameterSetName='Query')]
param(
    [ValidateNotNullOrEmpty()][string]$Dsn = 'AppxODBC',
    [System.Management.Automation.PSCredential]$Credential,
    [Parameter(ParameterSetName='Query')]
    [ValidateNotNullOrEmpty()][string]$Sql = 'SELECT * FROM DMO_DMO_COUNTRY ORDER BY name',
    [Parameter(ParameterSetName='Query')][hashtable[]]$Parameter = @(),
    [Parameter(ParameterSetName='Query')][switch]$Describe,
    [Parameter(Mandatory=$true,ParameterSetName='Tables')][switch]$Tables,
    [Parameter(Mandatory=$true,ParameterSetName='Columns')][switch]$Columns,
    [Parameter(Mandatory=$true,ParameterSetName='ConnectionInfo')][switch]$ConnectionInfo,
    [ValidateRange(1,300)][int]$CommandTimeout = 30,
    [ValidateRange(1,1000000)][int]$MaxRows = 10000
)

$ErrorActionPreference = 'Stop'
if ([Environment]::Is64BitProcess) {
    throw 'Open 32-bit Windows PowerShell: C:\Windows\SysWOW64\WindowsPowerShell\v1.0\powershell.exe'
}
if (-not $Credential) {
    $Credential = Get-Credential -UserName appx -Message 'APPX login for the selected ODBC data source'
}
if (-not $Credential) { throw 'Login cancelled' }

$builder = New-Object System.Data.Odbc.OdbcConnectionStringBuilder
$builder['DSN'] = $Dsn
$builder['UID'] = $Credential.UserName
$secret = $Credential.GetNetworkCredential().Password
$builder['PWD'] = $secret
$connection = New-Object System.Data.Odbc.OdbcConnection($builder.ConnectionString)
$builder.Clear()
$command = $null
$reader = $null
$schema = $null
$watch = [Diagnostics.Stopwatch]::StartNew()
$rows = New-Object 'System.Collections.Generic.List[object]'

try {
    $connection.Open()
    if ($ConnectionInfo) {
        $loadedDriver = [Diagnostics.Process]::GetCurrentProcess().Modules |
            Where-Object { $_.ModuleName -ieq $connection.Driver } |
            Select-Object -First 1
        if (-not $loadedDriver) {
            # SDK14 reports DHSODBC32.DLL while the loaded file is dhsodbc.dll.
            # A registered path is only a candidate: confirm it is loaded.
            foreach ($hive in @([Microsoft.Win32.Registry]::CurrentUser, [Microsoft.Win32.Registry]::LocalMachine)) {
                $key = $hive.OpenSubKey('SOFTWARE\ODBC\ODBC.INI\' + $Dsn)
                if ($key) {
                    try { $registeredPath = [string]$key.GetValue('Driver') }
                    finally { $key.Close() }
                    if ($registeredPath) {
                        $loadedDriver = [Diagnostics.Process]::GetCurrentProcess().Modules |
                            Where-Object { $_.FileName -ieq $registeredPath } |
                            Select-Object -First 1
                        if ($loadedDriver) { break }
                    }
                }
            }
        }
        $rows.Add([pscustomobject][ordered]@{
            Dsn = $Dsn
            Driver = $connection.Driver
            DriverPath = $(if ($loadedDriver) { $loadedDriver.FileName } else { $null })
            DriverFileVersion = $(if ($loadedDriver) { $loadedDriver.FileVersionInfo.FileVersion } else { $null })
            ServerVersion = $connection.ServerVersion
            PowerShellVersion = [string]$PSVersionTable.PSVersion
            ProcessBits = 32
        })
    }
    elseif ($Tables -or $Columns) {
        # Unrestricted schema first: several legacy drivers mishandle
        # null/empty restrictions. Filter the returned objects in PowerShell.
        $collection = if ($Tables) { 'Tables' } else { 'Columns' }
        $schema = $connection.GetSchema($collection)
        if ($schema.Rows.Count -gt $MaxRows) {
            throw "Metadata exceeds MaxRows=$MaxRows. Increase the limit deliberately."
        }
        foreach ($dataRow in $schema.Rows) {
            $record = [ordered]@{}
            foreach ($column in $schema.Columns) {
                $value = $dataRow[$column.ColumnName]
                $record[$column.ColumnName] = if ($value -is [DBNull]) { $null } else { $value }
            }
            $rows.Add([pscustomobject]$record)
        }
    }
    else {
        $command = $connection.CreateCommand()
        $command.CommandText = $Sql
        $command.CommandTimeout = $CommandTimeout
        foreach ($spec in $Parameter) {
            if (-not $spec.ContainsKey('Type') -or -not $spec.ContainsKey('Value')) {
                throw 'Each Parameter requires Type and Value; character parameters also need Size.'
            }
            $item = New-Object System.Data.Odbc.OdbcParameter
            $item.OdbcType = [System.Data.Odbc.OdbcType]$spec.Type
            if ($spec.ContainsKey('Size')) { $item.Size = [int]$spec.Size }
            $item.Value = if ($null -eq $spec.Value) { [DBNull]::Value } else { $spec.Value }
            [void]$command.Parameters.Add($item)
        }
        $reader = $command.ExecuteReader()
        if ($Describe) {
            $schema = $reader.GetSchemaTable()
            if ($schema.Rows.Count -gt $MaxRows) { throw "Description exceeds MaxRows=$MaxRows." }
            foreach ($field in $schema.Rows) {
                $rows.Add([pscustomobject][ordered]@{
                    ColumnName = [string]$field.ColumnName
                    Ordinal = [int]$field.ColumnOrdinal + 1
                    DataType = $field.DataType.FullName
                    Size = [int]$field.ColumnSize
                    AllowDBNull = [bool]$field.AllowDBNull
                })
            }
        }
        else {
        $names = New-Object 'System.Collections.Generic.HashSet[string]' ([StringComparer]::OrdinalIgnoreCase)
        for ($i=0; $i -lt $reader.FieldCount; $i++) {
            if ([string]::IsNullOrWhiteSpace($reader.GetName($i)) -or -not $names.Add($reader.GetName($i))) {
                throw 'Every result column needs a distinct name. Add SQL AS aliases.'
            }
        }
        while ($reader.Read()) {
            if ($rows.Count -ge $MaxRows) {
                throw "Query exceeds MaxRows=$MaxRows. Add a WHERE filter or increase the limit deliberately. No rows returned."
            }
            $record = [ordered]@{}
            for ($i=0; $i -lt $reader.FieldCount; $i++) {
                $record[$reader.GetName($i)] = if ($reader.IsDBNull($i)) { $null } else { $reader.GetValue($i) }
            }
            $rows.Add([pscustomobject]$record)
        }
        }
    }
}
catch {
    $exception = $_.Exception
    while ($exception.InnerException) { $exception = $exception.InnerException }
    if ($exception -is [System.Data.Odbc.OdbcException]) {
        $message = ($exception.Errors | ForEach-Object {
            'SQLSTATE {0}; native {1}; {2}' -f $_.SQLState, $_.NativeError, $_.Message
        }) -join [Environment]::NewLine
    }
    else { $message = $exception.Message }
    if ($secret) { $message = $message.Replace($secret,'[redacted]') }
    throw $message
}
finally {
    if ($reader) { $reader.Dispose() }
    if ($command) { $command.Dispose() }
    if ($schema) { $schema.Dispose() }
    $connection.Dispose()
    $secret = $null
    $Credential = $null
    $watch.Stop()
}
Write-Verbose ('Returned {0} rows in {1:N2} seconds.' -f $rows.Count,$watch.Elapsed.TotalSeconds)
foreach ($row in $rows) { Write-Output $row }