-
Notifications
You must be signed in to change notification settings - Fork 6
/
SQLConditions.cls
executable file
·232 lines (169 loc) · 7.13 KB
/
SQLConditions.cls
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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
Persistable = 0 'NotPersistable
DataBindingBehavior = 0 'vbNone
DataSourceBehavior = 0 'vbNone
MTSTransactionMode = 0 'NotAnMTSObject
END
Attribute VB_Name = "SQLConditions"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = True
' ___________________________________________________
'
' © Hi-Integrity Systems 2007. All rights reserved.
' www.hisystems.com.au - Toby Wicks
' ___________________________________________________
'
Option Explicit
Private pcolLogicalOperators As Collection
Private pcolSQLConditions As Collection
Public Function Add( _
Optional ByVal strFieldName As String, _
Optional ByVal eCompare As SQLComparisonOperatorEnum = dboComparisonEqualTo, _
Optional ByVal vValue As Variant, _
Optional ByVal objTable As SQLSelectTable) As SQLCondition
EnsurePreviousLogicalOperatorExists
Set Add = New SQLCondition
Set Add.Table = objTable
Add.FieldName = strFieldName
Add.Compare = eCompare
If Not IsMissing(vValue) Then Add.Value = vValue
pcolSQLConditions.Add Add
End Function
Public Sub AddExpression(ByVal objExpression As ISQLExpression)
If objExpression Is Nothing Then
RaiseError dboErrorObjectIsNothing
End If
EnsurePreviousLogicalOperatorExists
pcolSQLConditions.Add objExpression
End Sub
Public Sub AddCompareExpression( _
ByVal objLeft As ISQLExpression, _
ByVal eCompare As SQLComparisonOperatorEnum, _
ByVal objRightExpression As ISQLExpression)
Dim objCompareExpression As SQLComparisonExpression
Set objCompareExpression = New SQLComparisonExpression
Set objCompareExpression.Left = objLeft
objCompareExpression.Compare = eCompare
Set objCompareExpression.Right = objRightExpression
AddExpression objCompareExpression
End Sub
Public Sub AddCondition( _
ByVal objCondition As SQLCondition)
If objCondition Is Nothing Then
RaiseError dboErrorObjectIsNothing
End If
EnsurePreviousLogicalOperatorExists
pcolSQLConditions.Add objCondition
End Sub
Public Sub AddConditions( _
ByVal objConditions As SQLConditions)
If objConditions.IsEmpty Then
RaiseError dboErrorInvalidArgument, "SQLConditions does not contain any conditions"
End If
EnsurePreviousLogicalOperatorExists
pcolSQLConditions.Add objConditions
End Sub
Public Function AddInSelect( _
Optional ByVal strFieldName As String, _
Optional ByVal objSelect As SQLSelect, _
Optional ByVal objTable As SQLSelectTable) As SQLConditionInSelect
EnsurePreviousLogicalOperatorExists
Set AddInSelect = New SQLConditionInSelect
Set AddInSelect.Table = objTable
AddInSelect.FieldName = strFieldName
Set AddInSelect.SelectSet = objSelect
pcolSQLConditions.Add AddInSelect
End Function
Public Function AddSelect( _
Optional ByVal objSelect As SQLSelect, _
Optional ByVal eCompare As SQLComparisonOperatorEnum = dboComparisonEqualTo, _
Optional ByVal vValue As Variant) As SQLConditionSelect
EnsurePreviousLogicalOperatorExists
Set AddSelect = New SQLConditionSelect
Set AddSelect.SelectSet = objSelect
AddSelect.Compare = eCompare
If Not IsMissing(vValue) Then AddSelect.Value = vValue
pcolSQLConditions.Add AddSelect
End Function
Public Function AddFieldCompare( _
Optional ByVal objTable1 As SQLSelectTable, _
Optional ByVal strFieldName1 As String, _
Optional ByVal eCompare As SQLComparisonOperatorEnum, _
Optional ByVal objTable2 As SQLSelectTable, _
Optional ByVal strFieldName2 As String) As SQLConditionFieldCompare
EnsurePreviousLogicalOperatorExists
Set AddFieldCompare = New SQLConditionFieldCompare
With AddFieldCompare
Set .Table1 = objTable1
.FieldName1 = strFieldName1
.Compare = eCompare
Set .Table2 = objTable2
.FieldName2 = strFieldName2
End With
pcolSQLConditions.Add AddFieldCompare
End Function
Private Sub EnsurePreviousLogicalOperatorExists()
'Add the AND operator if an operator hasn't been called after the previous Add or AddConditions call
If pcolLogicalOperators.Count < pcolSQLConditions.Count Then
Me.AddLogicalOperator dboLogicalAnd
End If
End Sub
Public Sub AddLogicalOperator( _
Optional ByVal eLogicalOperator As SQLLogicalOperatorEnum = dboLogicalAnd)
If pcolLogicalOperators.Count + 1 > pcolSQLConditions.Count Then
RaiseError dboErrorGeneral, "First call the Add function - this function has been called without a prior call to Add"
End If
pcolLogicalOperators.Add eLogicalOperator
End Sub
Public Property Get IsEmpty() As Boolean
IsEmpty = pcolSQLConditions.Count = 0
End Property
Private Function LogicalOperator( _
ByVal intIndex As Integer) As SQLLogicalOperatorEnum
LogicalOperator = pcolLogicalOperators(intIndex)
End Function
Friend Property Get SQL(ByVal eConnectionType As ConnectionTypeEnum) As String
Dim intIndex As Integer
Dim strSQL As String
Dim objSQLCondition As SQLCondition
Dim objSQLConditions As SQLConditions
Dim objSelectIn As SQLConditionInSelect
Dim objSelect As SQLConditionSelect
Dim objFields As SQLConditionFieldCompare
Dim objExpression As ISQLExpression
With pcolSQLConditions
For intIndex = 1 To .Count
If intIndex > 1 Then
strSQL = strSQL & " " & SQLConvertLogicalOperator(pcolLogicalOperators(intIndex - 1)) & " "
End If
If TypeOf .Item(intIndex) Is SQLCondition Then
Set objSQLCondition = .Item(intIndex)
strSQL = strSQL & objSQLCondition.SQL(eConnectionType)
ElseIf TypeOf .Item(intIndex) Is SQLConditions Then
Set objSQLConditions = .Item(intIndex)
strSQL = strSQL & "(" & objSQLConditions.SQL(eConnectionType) & ")"
ElseIf TypeOf .Item(intIndex) Is SQLConditionSelect Then
Set objSelect = .Item(intIndex)
strSQL = strSQL & objSelect.SQL(eConnectionType)
ElseIf TypeOf .Item(intIndex) Is SQLConditionFieldCompare Then
Set objFields = .Item(intIndex)
strSQL = strSQL & objFields.SQL(eConnectionType)
ElseIf TypeOf .Item(intIndex) Is SQLConditionInSelect Then
Set objSelectIn = .Item(intIndex)
strSQL = strSQL & objSelectIn.SQL(eConnectionType)
ElseIf TypeOf .Item(intIndex) Is ISQLExpression Then
Set objExpression = .Item(intIndex)
strSQL = strSQL & objExpression.SQL(eConnectionType)
End If
Next
End With
SQL = strSQL
End Property
Private Sub Class_Initialize()
Set pcolSQLConditions = New Collection
Set pcolLogicalOperators = New Collection
End Sub