Search code examples
powershelloledb

Problem calling OleDbConnection.GetSchema from PowerShell

I am making a PowerShell script to read schema information from Access databases. I am having trouble calling the OleDbConnection.GetSchema function with restrictions (for example to get only user tables, not system tables). Code that works in C# is:

OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=filename.mdb");
connection.Open();
DataTable schema = connection.GetSchema("Tables", new string[] {null, null, null, "TABLE"});

How can I correctly translate that last line to work in PowerShell?

My initial attempt was:

$schema = $connection.GetSchema('Tables', @($null, $null, $null, 'TABLE'))

This failed with an OleDbException with message: "The parameter is incorrect." I narrowed the problem down to the null values. Even passing a single null restriction:

$schema = $connection.GetSchema('Tables', @($null))

results in the same exception. Passing in a single null restriction should have the same effect as calling GetSchema without restrictions, which does work in PowerShell.

The exception I am getting is the same one you get if you pass an invalid value for one of the restrictions so I am assuming that $null is not being translated correctly somehow. For an Access database it is necessary for the first two restrictions (catalog and schema) to be null. Does anyone have an idea how to pass these null values correctly? Things I have tried include:

$connection.GetSchema('Tables',[string[]]@($null, $null, $null, 'TABLE'))

$connection.GetSchema('Tables',[string[]]@([string]$null, [string]$null, [string]$null, 'TABLE'))

[string[]] $restrictions = $null, $null, $null, 'TABLE'
$connection.GetSchema('Tables', $restrictions)

Also tried using ACE provider instead of JET. All attempts resulted in the same OleDbException with message: "The parameter is incorrect.".

Solution

  • This Microsoft article helped me to figure it out: [1]: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/schema-restrictions

    The key is to declare the Restrictions string array upfront with the required length and next populate only the elements that need to be restricted/filtered. The remaining elements should not be initialized, not even with null values.

    C# code from the article:

    // Specify the restrictions.  
string[] restrictions = new string[4];  
restrictions[1] = "Sales";  
System.Data.DataTable table = connection.GetSchema("Tables", restrictions);

    PowerShell:

    $restrictions = New-Object -TypeName string[] -ArgumentList 4
$restrictions[2] = $tableName
$restrictions[3] = 'TABLE'
$result = $connection.GetSchema('Tables', $restrictions)