Public/Get-DBaseConnection.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
function Get-DbaseConnection {
    <#
    .SYNOPSIS
    Returns an OleDB connection to a DBase database
 
    .DESCRIPTION
    Returns an OleDB connection to a DBase database.
     
    This *seems* to work for FoxPro as well, but there has not been extenstive testing.
 
    www.connectionstrings.com is an excellent resource for connection strings for specfic drivers.
 
    .NOTES
    This cmdlet uses "ACE", which is available for 32 bit and 64 bit versions of Windows. It is freely downloadable from
    Microsoft's site. Installing both, side-by-side, seems to be difficult/impossible. Google for details.
 
    AFAIK, this cmdlet will work with FoxPro data for simple reads and writes, up to and including Visual Fox Pro 9.0.
    Tables with memo fields are not compatible between dBase and FoxPro. If you have a FoxPro table with a memo field, dBase will
    barf.
     
    Indexes are also a problem. I wouldn't trust using indexes in cross FoxPro/dBase environment.
 
    If you have trouble using this cmdlet with FoxPro data, try the *-FoxPro* cmdlets in this module. They use Vfpoledb, which has
    it's own quirks and features, but should work better.
 
    Back in the 1990s, many dBase and (non-Visual) FoxPro applications got into trouble because file caching was a new feature
    that was implemented in the networking layer of Windows workstations and file caching was common on Windows file servers. Dbase
    doesn't understand file caching. It can't open files in write-through mode. This can lead to issues where different workstations
    try to update the same records in the wrong order, try to insert records into the same "empty" spot at the end of the file and
    so forath. This is a ++ likely source of trouble in your applications.
 
    .OUTPUTS
    [System.Data.OleDb.OleDbConnection]
 
    .PARAMETER DataSource
    This is a file path to a DBase database.
 
    .PARAMETER ExtendedProperties
    Some providers use a bevy of 'extended properties' in the connection string, this is a bucket into which you can throw them.
    The exact capability and syntax is provider-specific.
    The are usually key-value pairs in the format "Prop=something;AnotherProp=SomethingElse".
    No attempt is made to validate these properties or even validate the string. The first indication of trouble is usually that the Open() call will fail.
 
    .PARAMETER Credential
    This should be a 'standard' PSCredential object. Defaults to User="Admin", password = ""
 
    .EXAMPLE
    $cn = Get-DbaseConnection -DataSource (get-psdrive temp).root
    Invoke-DbaseQuery -Connect $cn -query 'select * from nrthwind'
    $cn.close()
    $cn.dispose()
    $cn = $null
 
    .EXAMPLE
    try {
        $cn = Get-DbaseConnection -DataSource 'c:\dbfiles'
        # do stuff
    }
    finally {
        # clean up. This step is key or you risk leaking resources
        $cn.Close()
        $cn.Dispose()
 
    }
 
    .LINK
    https://www.connectionstrings.com/
 
    #>

    [OutputType([System.Data.OleDb.OleDbConnection])]
    param (
        [Parameter(Mandatory = $true)]
        [string] $DataSource,
        [System.Management.Automation.PSCredential] $Credential

    )

    # When it comes to provider choice, I've always just hard-coded whatever is on my workstation. This has the problem of
    # breaking every few years or when I move to a different workstation. There must be a better way to pick the version of ACE.
    # How do I know which version of the provider to use? Can't I just say "use ACE" and let the system find the best version?
    # IOW, not all of these are on my system, so how do I know what to pick
    [string] $Provider = 'Microsoft.ACE.OLEDB.12.0'
    # [string] $Provider = 'Microsoft.ACE.OLEDB.15.0'
    [string] $Provider = 'Microsoft.ACE.OLEDB.16.0'

    [string] $ExtendedProperties = 'dBASE IV;'

    if (-not $Credential) {
        # I found the ToCharArray() and AppendChar() magic here:
        # https://stackoverflow.com/questions/6239647/using-powershell-credentials-without-being-prompted-for-a-password
        # IIRC, every dBase or FoxPro file I've ever accessed were protected only by file ACLs and used credentials like such:
        $username = "Admin"
        $password = ""
        $secstr = New-Object -TypeName System.Security.SecureString
        $password.ToCharArray() | ForEach-Object { $secstr.AppendChar($_) }
        $Credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $username, $secstr
    }

    Try {
        Get-OleDbConnection -DataSource $DataSource -Provider $Provider -ExtendedProperties $ExtendedProperties -Credential $Credential
    }
    Catch {
        Throw
    }
}