Building Better SQL Queries

Listing 3 A rudimentary version of CSQL

Option Compare Database
Option Explicit

' =====================================================
' Collections of the Query Elements
' =====================================================
Private Sels As New Collection
Private Whrs As New Collection
Private Tbls As New Collection

' =====================================================
' Clears out all collections
' =====================================================
Sub Clear()
    clear_collection Sels
    clear_collection Whrs
    clear_collection Tbls
End Sub

' =====================================================
' Clears a single passed collection
' =====================================================
Private Sub clear_collection(coll As Collection)
    Dim ix As Long
    For ix = coll.Count To 1 Step -1
        coll.Remove ix
    Next ix
End Sub

' =====================================================
' When a user specifies s.Selct = "SOME_FIELD" 
' this appends the table to the selections collection
' =====================================================
Property Let Selct(sl$)
    Sels.Add sl
End Property

' =====================================================
' When a user specifies s.Table = "SOME_TABLE" 
' this appends the table to the collection
' =====================================================
Property Let Table(tb$)
    Tbls.Add tb$
End Property

' =====================================================
' When a user specifies s.Where = "SOMETHING = SOMETHING_ELSE" 
' this appends the table to the Wheres collection
' =====================================================
Property Let Where(whr$)
    Whrs.Add whr
End Property

' =====================================================
' When the user does a get as in SQL$ = s.SelectQuery (Select Query)
' The Selects, Tables and Wheres are retrieved and assembled
' and then concatenated
' =====================================================
Property Get SelectQuery() As String
    SelectQuery = Selct & Table & Where
End Property

' =====================================================
' The get Logic for each specific property actually
' pulls the list together using this function.
' The function starts by building an output
' string with the passed StartString$
' it appends the collection items one by one
' and in between each inserts the Delimiter$ string
' And a new line which makes the result easier to read
' Finally if the string has any content, and optional
' CloseString$ is added. Note that the function
' is structured in such a way that an empty collection
' returns an empty string
' =====================================================
Private Function get_item_list(coll As Collection, _
                       StartString$, Delimiter$, Optional CloseString$ = "")As String
    Dim tx$
    Dim ix As Long
    For ix = 1 To coll.Count
        If ix = 1 Then
            tx$ = StartString$ & vbCrLf
        End If
        If ix > 1 Then
            tx = tx$ & Delimiter$ & " "
        End If
        tx = tx$ & coll(ix) & vbCrLf
    Next ix
    If tx$ <> "" Then
        tx$ = tx$ & CloseString$
    End If
    get_item_list = tx$
End Function

' =====================================================
' When the Get SelectQuery() property requests the Selct
' attribute This is assembled using the get_item_list
' helper function.
' =====================================================
Property Get Selct() As String
    Selct = get_item_list(Sels, "SELECT", ",")
End Property

' =====================================================
' When the Get SelectQuery() property requests the Table
' attribute This is assembled using the get_item_list
' helper function.
' =====================================================
Property Get Table() As String
    Table = get_item_list(Tbls, "FROM", ",")
End Property

' =====================================================
' When the Get SelectQuery() property requests the Table
' attribute This is assembled using the get_item_list
' helper function.
' =====================================================
Property Get Where() As String
    Where = get_item_list(Whrs, "WHERE", "AND")
End Property