Filter Records with SQL Parameters

Use a WHERE clause to reduce records read from APPX. Client-side Where-Object filters run only after the query has transferred the selected rows. Pass user-entered values through positional ODBC parameters instead of joining them into SQL text:

$country = 'Canada'
$selected = @(.\read-appx-odbc-64.ps1 -Credential $credential `
    -Sql 'SELECT name, region_label, no_words FROM DMO_DMO_COUNTRY WHERE name = ?' `
    -Parameter @(@{Type='VarChar'; Size=14; Value=$country}))
$selected | Format-Table -AutoSize

The native expected result is one Canada record, Province and 2. The current 64-bit stack instead returns zero records for this parameter. Do not accept this text predicate for business use. Parameter types should match the APPX field exposed by the SQL definition. COUNTRY's name has a maximum of 14 characters. An ODBC ? represents a value; it cannot stand for a table name, column name or ORDER BY direction. Keep identifiers in reviewed SQL.

For multiple parameters, add their specifications in exactly the same left-to-right order as the question marks. The names in a PowerShell hashtable are helper options, not named SQL parameters:

$selected = @(.\read-appx-odbc-64.ps1 -Credential $credential `
    -Sql 'SELECT name, no_words FROM DMO_DMO_COUNTRY WHERE no_words >= ? AND name <> ? ORDER BY name' `
    -Parameter @(@{Type='Int'; Value=2}, @{Type='VarChar'; Size=14; Value='Canada'}))
$selected | Format-Table -AutoSize

The native expected names are Netherlands and UK. The observed result instead contains C, N and U, incorrectly retaining Canada. Numeric-only PROSPECT key filtering separately returned the correct key and employee count, with its company name still truncated. The Microsoft ODBC parameter reference explains this positional behavior. Use IS NULL to find SQL NULLs; = ? with a null parameter is not the same predicate. Check empty strings, padding, case and dates against native APPX values before extending a filter to a production file.