Experimental/Research-FoxPro.EqualVsExactlyEqual.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
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
<#
.SYNOPSIS
How does "equal" vs. "exactly equal" ("=" vs. "==") really work?
 
.LINK
http://www.yaldex.com/fox_pro_tutorial/html/31f9c8c3-414e-4930-aa7f-2239f07b19e6.htm
Talks about EQUALS vs. EXACTLY EQUALS
 
.LINK
http://www.yaldex.com/fox_pro_tutorial/html/7d2d6409-f972-4452-9c6d-91ac5c9a2a5e.htm
Talks about ANSI
#>

function main {
    [cmdletbinding()]
    param ()

    $FoxProDbPath = Join-Path -Path $PSScriptRoot -child "TestData"

    Write-Verbose -Message "FoxPro Db Path: $FoxProDbPath"

    if (-not (Test-Path $FoxProDbPath)) {
        New-Item -Path $FoxProDbPath -ItemType 'Directory' | Out-Null
    }


    # clean up from last time
    Get-ChildItem -Path $FoxProDbPath -Filter 'foo.*' | Remove-Item

    try {
        <#
        An experiment: I tried executing this CREATE TABLE statement with a 'dbase connection'.
        It failed on syntax.
 
        ACE doesn't like CREATE TABLE DDL? How would I create a dBase table with ACE, then?
         
        $cn = Get-dbaseConnection -DataSource $FoxProDbPath
        Invoke-Dbase Query -as NonQuery -Connection $cn -Query "CREATE TABLE foo(ref v(20), diff v(20))"
        #>


        $cn = Get-FoxProConnection -DataSource $FoxProDbPath
        write-verbose -message "Got a connection"

        # This creates a test table
        # this is "reference" vs. "difference"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query "CREATE TABLE foo(ref v(20), diff v(20))"

        write-verbose -Message "Created a table"

        # Now that we have the table built, we can put some data into it.
        # Note that some of these have trailing space chars and some don't-
        # this can affect = results
        $QueryInsert = "insert into foo (ref,diff) values ('abc','abc')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        $QueryInsert = "insert into foo (ref,diff) values ('ab','abc')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        $QueryInsert = "insert into foo (ref,diff) values ('abc','ab')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        $QueryInsert = "insert into foo (ref,diff) values ('abc','ab ')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        $QueryInsert = "insert into foo (ref,diff) values ('ab ','ab')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        $QueryInsert = "insert into foo (ref,diff) values ('','ab')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        $QueryInsert = "insert into foo (ref,diff) values ('ab','')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        $QueryInsert = "insert into foo (ref,diff) values (' ','')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        $QueryInsert = "insert into foo (ref,diff) values ('',' ')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        # Examples behond what is in the linked URL
        $QueryInsert = "insert into foo (ref,diff) values ('TWIN','TWINE')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert
        $QueryInsert = "insert into foo (ref,diff) values ('TWINE','TWIN')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert
        $QueryInsert = "insert into foo (ref,diff) values ('TWINE','TWINE')"
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QueryInsert

        $Report = Invoke-FoxProQuery -Connection $cn -Query 'SELECT COUNT(*) cnt FROM foo'

        write-verbose -Message "Inserted $($Report.cnt) row(s) into the table"

        # if ANSI is off and EXACT is off and we use = (not ==), then it's a problem.
        # "TWIN" equals "TWINE"? What the heck?
        # IF ANSI is ON and EXACT is ON, there is no problem if we use = or ==

        # ANSI=ON means "trim trailing spaces from string". SqlServer has a similar thing.
        # turning ANSI OFF or ON makes a big difference. ANSI is ON by default

        $EQUALS = '='
        # $EQUALS = '=='


        # First, try it OFF
        $ANSI = "OFF"
        $EXACT = "OFF"

        $QuerySet = "SET EXACT " + $EXACT
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QuerySet

        $QuerySet = "SET ANSI " + $ANSI
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QuerySet

        # show the data we just inserted, but look for equivalencies
        $Query = "SELECT '$EXACT' exact, '$ANSI' ansi, '$EQUALS' equals, recno() recno, *, IIF(ref$($EQUALS)diff, .T.,.F.) match FROM foo "
        Invoke-FoxProQuery -Connection $cn -Query $Query | Format-Table -AutoSize

        write-verbose -Message "Ran the query with ANSI = $ANSI and EXACT = $EXACT"


        # Now try it ON
        $ANSI = "ON"
        $EXACT = "ON"

        $QuerySet = "SET EXACT " + $EXACT
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QuerySet

        $QuerySet = "SET ANSI " + $ANSI
        Invoke-FoxProQuery -as NonQuery -Connection $cn -Query $QuerySet

        # show the data we just inserted, but look for equivalencies
        $Query = "SELECT '$EXACT' exact, '$ANSI' ansi, '$EQUALS' equals, recno() recno, *, IIF(ref$($EQUALS)diff, .T.,.F.) match FROM foo "
        Invoke-FoxProQuery -Connection $cn -Query $Query | Format-Table -AutoSize

        write-verbose -Message "Ran the query with ANSI = $ANSI and EXACT = $EXACT"
    }
    catch {
        Throw $Error[0]
    }

    Finally {
        $cn.Close()
        $cn.Dispose()
        $cn = $null
        # clean up
        Get-ChildItem -Path $FoxProDbPath -Filter foo.* | Remove-Item
    }

}

# call main
main -verbose