smartsql.cls

VB中的ADO操作教程

        For Each v In FieldList
            If Not ValidateValues(v) Then Err.Raise ERR_INVALID_LISTITEM, , ERR_INVALID_LISTITEM_DESC
            If Len(v) > 0 Then
                If Len(TableName) Then
                    sAns = DoAutoBracket(TableName) & "."
                End If

                sItem = Trim(CStr(v))
                sAns = sAns & DoAutoBracket(sItem)

                pColFieldNames.Add sAns
                sAns = ""
             End If 'len(v)
        Next v
    Else
        On Error Resume Next
        v = FieldList(0)
        lStartPoint = IIf(Err.Number = 0, 0, 1)
        Err.Clear
        
        On Error GoTo 0
    
        For l = lStartPoint To UBound(FieldList)
            sAns = ""
            If Not ValidateValues(FieldList(l)) Then Err.Raise ERR_INVALID_LISTITEM, , ERR_INVALID_LISTITEM_DESC
                If Len(FieldList(l)) > 0 Then
                    If Len(TableName) Then
                        sAns = sAns & DoAutoBracket(TableName) & "."
                    End If
                    sAns = sAns & DoAutoBracket(FieldList(l))
                pColFieldNames.Add sAns
            End If 'len(FieldList(l) > 0
        Next
    End If 'bcollection
End Sub

Public Sub ListAddValues(ByVal ValueList As Variant)
    Dim bValid      As Boolean
    Dim bCollection As Boolean
    Dim sAns        As String
    Dim l           As Long
    Dim sSplit()    As String
    Dim iCtr        As Integer
    Dim vTest       As String
    Dim lStart      As Long
    Dim v           As Variant
    Dim lStartPoint As Long

    'PURPOSE: ADD A list of values to the values collection
    'Values are for Update or Insert queries
    'The List can be either an array or a collection

    If IsObject(ValueList) Then
        If TypeOf ValueList Is Collection Then
            bValid = True
        Else
            bValid = False
        End If
        bCollection = True
    Else
        bValid = IsArray(ValueList)
    End If

    If Not bValid Then
        Err.Raise ERR_LIST_REQUIRED, , ERR_LIST_REQUIRED_DESC
        Exit Sub
    End If
    
    If bCollection Then
        For Each v In ValueList
            If Not ValidateValues(v) Then Err.Raise ERR_INVALID_LISTITEM, , ERR_INVALID_LISTITEM_DESC
            If VarType(v) <> vbString Or Not pbAutoQuote Then
                   pColValues.Add v
            Else
                pColValues.Add prepStringForSQL(CStr(v))
            End If
        Next
    Else
        'Determine if we are dealing with 0 or 1 bound arrays
        Err.Clear
        On Error Resume Next
        vTest = ValueList(0)
        lStartPoint = IIf(Err.Number = 0, 0, 1)
        Err.Clear
        
        On Error GoTo 0
        
        For l = lStartPoint To UBound(ValueList)
            If Not ValidateValues(ValueList(l)) Then Err.Raise ERR_INVALID_LISTITEM, , ERR_INVALID_LISTITEM_DESC
            sAns = ""
            If VarType(ValueList(l)) <> vbString Or Not pbAutoQuote Then
                pColValues.Add ValueList(l)
            Else
                pColValues.Add prepStringForSQL(CStr(ValueList(l)))
            End If
        Next
    End If
End Sub

Public Sub Reset()
    ClearFromClause
    ClearWhereClause
    ClearOrderClause
    ClearFields
    ClearValues
    'key for field, value for value
    piStatementType = TYPE_SELECT 'default
End Sub

Public Sub SetupJoin(ByVal Table1 As String, ByVal Field1 As String, ByVal Table2 As String, ByVal Field2 As String, Optional Op As CLAUSE_OPERATOR = CLAUSE_EQUALS, Optional JoinType As JOIN_TYPE)
    'check for bracketing and add if not present
    Dim sTable1 As String, sTable2 As String
    Dim sField1 As String, sField2 As String

    sTable1 = DoAutoBracket(Table1)
    sTable2 = DoAutoBracket(Table2)
    sField1 = DoAutoBracket(Field1)
    sField2 = DoAutoBracket(Field2)

    psJoinTables = sTable1 & Delimiter & sTable2
    psJoinFields = sField1 & Delimiter & sField2
    piJoinOp = Op

    Select Case JoinType
        Case Is <= 0, Is > FULL_JOIN
            If piJoinType = 0 Then piJoinType = INNER_JOIN
        Case FULL_JOIN
            piJoinType = IIf(piSQLType = SQL_TYPE_ANSI, FULL_JOIN, IIf(piJoinType = 0, INNER_JOIN, piJoinType))
        Case Else
            piJoinType = JoinType
    End Select

    'reset other from related options
    ReDim psTableNames(0) As String
    psFromClause = ""
End Sub

'#########################################################################################
'                                   Public Properties
'#########################################################################################
Public Property Get AutoBracket() As Boolean
    AutoBracket = pbAutoBracket
End Property
Public Property Let AutoBracket(ByVal NewValue As Boolean)
    pbAutoBracket = NewValue
End Property

Public Property Get AutoLike() As Boolean
    AutoLike = pbAutoLike
End Property
Public Property Let AutoLike(ByVal NewValue As Boolean)
    pbAutoLike = NewValue
End Property

Public Property Get AutoQuote() As Boolean
    AutoQuote = pbAutoQuote
End Property
Public Property Let AutoQuote(ByVal NewValue As Boolean)
    pbAutoQuote = NewValue
End Property

Public Property Let ComplexFromClause(ByVal NewValue As String)
    'reset everything else
    Dim sWkg     As String
    Dim sSplit() As String

    sWkg = Trim$(NewValue)
    
    If Left$(NewValue, 4) = "FROM" Then
        sSplit = Split(sWkg, "FROM")
        psFromClause = Trim$(sSplit(1))
    Else
        psFromClause = sWkg
    End If

    'reset to defaults
    ReDim psTableNames(0) As String

    psJoinTables = ""
    psJoinFields = ""
    piJoinOp = CLAUSE_EQUALS
    piJoinType = INNER_JOIN
End Property
Public Property Get ComplexFromClause() As String
    ComplexFromClause = psFromClause
End Property

Public Property Get JoinType() As JOIN_TYPE
    JoinType = piJoinType
End Property
Public Property Let JoinType(ByVal NewValue As JOIN_TYPE)
If NewValue >= INNER_JOIN And NewValue <= FULL_JOIN Then
    If NewValue = FULL_JOIN And piSQLType = SQL_TYPE_ACCESS And piJoinType = 0 Then
        piJoinType = INNER_JOIN
    Else
        piJoinType = NewValue
    End If
End If
End Property

Public Property Get StatementType() As STATEMENT_TYPE
    StatementType = piStatementType
End Property
Public Property Let StatementType(ByVal NewValue As STATEMENT_TYPE)
    piStatementType = IIf(NewValue <= TYPE_DELETE, NewValue, TYPE_SELECT)
End Property

Public Property Get SQLType() As SQL_TYPE
    SQLType = piSQLType
End Property
Public Property Let SQLType(ByVal NewValue As SQL_TYPE)
    If NewValue = SQL_TYPE_ACCESS Or NewValue = SQL_TYPE_ANSI Then piSQLType = NewValue
End Property

Public Property Get SQL() As String
    MakeStatement
    SQL = psSQL
End Property

Public Property Get TableCount() As Long
    Dim lAns As Long
    If psTableNames(0) = "" And UBound(psTableNames) = 0 Then
        TableCount = ComplexTableCount
    Else
        TableCount = UBound(psTableNames)
    End If
End Property

'#########################################################################################
'                                   Private Properties
'#########################################################################################
Private Property Get LikeCharacter() As String
    LikeCharacter = IIf(piSQLType = SQL_TYPE_ACCESS, "*", "%")
End Property

'#########################################################################################
'                                   Private Subroutines
'#########################################################################################
Private Sub MakeStatement()
    Dim sAns            As String
    Dim sWhereClause    As String
    Dim sOrderClause    As String
    Dim sJoinClause     As String
    Dim sCommand        As String
    Dim i               As Integer
    Dim sOp             As String
    Dim sTemp           As String
    Dim sSplitTables()  As String
    Dim sSplitFields()  As String
    Dim lUpLimit        As Long
    
    Select Case piStatementType
        Case TYPE_SELECT
            If UBound(psTableNames) > 0 Or psJoinTables <> "" Or psFromClause <> "" Then
                'MAKE SELECT CLAUSE
                sCommand = "SELECT "
                'RULES FROM FROM CLAUSES ARE:
                'IF NOT JOINS OR COMPLEX FROM STATEMENTS,
                'YOU CAN HAVE AS MANY TABLES AS YOU WANT:
                'OTHERWISE, USE JUST ONE JOIN OR ONE COMPLEXFROM STATEMENT
                'COLLISIONS:
                'OR: GO WITH THE LATEST ADDED: WHEN ADDING OF ONE TYPE, CLEAR THE OTHER TWO
                If pColFieldNames.Count = 0 Then
                    sCommand = sCommand & "* "
                Else
                    For i = 1 To pColFieldNames.Count
                        sCommand = sCommand & pColFieldNames(i)
                        If i <> pColFieldNames.Count Then sCommand = sCommand & ","
                        sCommand = sCommand & " "
                    Next
                End If
                sCommand = sCommand & "FROM "
                On Error Resume Next
                If Len(psFromClause) > 0 Then
                    sCommand = sCommand & psFromClause
                Else
                    If UBound(psTableNames) >= 1 Then
                        For i = 1 To UBound(psTableNames)
                            sCommand = sCommand & psTableNames(i)
                            If i <> UBound(psTableNames) Then sCommand = sCommand & ", "
                        Next
                    Else
                        sSplitTables = Split(psJoinTables, Delimiter)
                        sSplitFields = Split(psJoinFields, Delimiter)
                        sCommand = sCommand & sSplitTables(0)
                        sCommand = sCommand & " "
                        'FIX TO DEAL WITH JOIN TYPES
                        If piJoinType < 1 Or piJoinType > 4 Then piJoinType = INNER_JOIN
                        Select Case piJoinType
                            Case INNER_JOIN
                                If piSQLType = SQL_TYPE_ACCESS Then sCommand = sCommand & "INNER "
                            Case LEFT_JOIN
                                sCommand = sCommand & " LEFT "
                            Case RIGHT_JOIN
                                sCommand = sCommand & " RIGHT "
                            Case FULL_JOIN
                                sCommand = sCommand & IIf(piSQLType = SQL_TYPE_ACCESS, " INNER ", " FULL ")
                        End Select
                        sCommand = sCommand & "JOIN " & sSplitTables(1) & " ON "
                        If InStr(sSplitFields(0), ".") > 0 Then
                            sCommand = sCommand & sSplitFields(0)
                        Else
                            sCommand = sCommand & sSplitTables(0) & "." & sSplitFields(0)
                        End If
                        sCommand = sCommand & " " & TransformOp(piJoinOp) & " "
                        If InStr(sSplitFields(1), ".") > 0 Then
                            sCommand = sCommand & sSplitFields(1)
                        Else
                            sCommand = sCommand & sSplitTables(1) & "." & sSplitFields(1)
                        End If
                    End If
                End If
            End If 'first condition, testing for at least one table
        Case TYPE_INSERT '?
            If Trim(psTableNames(1)) = "" Then
                Err.Raise ERR_TABLE_REQUIRED, , ERR_TABLE_REQUIRED_DESC
                Exit Sub
            End If
            sCommand = "INSERT INTO " & psTableNames(1)
            If pColFieldNames.Count > 0 Then
                sCommand = sCommand & " ("
                For i = 1 To pColFieldNames.Count
                    sCommand = sCommand & pColFieldNames(i)
                    If i <> pColFieldNames.Count Then sCommand = sCommand & ", "
                    
                Next
                sCommand = sCommand & ")"
            End If
            If pColValues.Count > 0 Then
                sCommand = sCommand & " VALUES ("