Advanced Visual Basic Techniques
(Publisher: John Wiley & Sons, Inc.)
Author(s): Rod Stephens
ISBN: 0471188816
Publication Date: 06/01/97

Processing SQL Statements

The Query application processes a series of SQL statements separated by semi-colons. Executing the commands is actually easier than breaking the individual statements apart. The following sections explain the routines Query uses to separate and execute SQL commands.

ProcessAllCommands

The process of separating and executing the commands begins with the ProcessAllCommands subroutine. ProcessAllCommands uses the StripCommands subroutine to remove comments, carriage returns, and excess spaces from the commands. It then repeatedly uses the GetToken subroutine to find the separate commands delimited by semi-colons. The GetToken routine is similar to the version used by ExpenseReporter and described in Chapter 2.

ProcessAllCommands places the separated commands in a collection. When it has finished breaking apart all of the commands, it loops through the collection calling the ProcessCommand subroutine to execute each command individually.

Private Sub ProcessAllCommands()
Dim cmd_string As String
Dim cmd As String
Dim cmds As New Collection
Dim got_cmd As Boolean
Dim i As Integer
Dim result As String

    WaitStart

    ‘ Remove comments, new lines, and tabs.
     cmd_string = Trim$(InputText.Text)
    StripCommands cmd_string

    ‘ Seperate the commands.
     got_cmd = GetToken(cmd_string, “;”, cmd)
    Do While got_cmd
        cmds.Add Trim$(cmd)
        got_cmd = GetToken(“”, “;”, cmd)
    Loop

    ‘ Process the commands.
     result = “”
    For i = 1 To cmds.Count
        ‘ Process this command.
         ProcessCommand cmds.Item(i), result
    Next i

    ‘ Display the output.
     OutputText.Text = result
    WaitEnd
End Sub

StripCommands

The StripCommands subroutine uses GetToken to locate different comment delimiters in the command text. First, it looks for the open braces that mark the start of a multiline comment. For each open brace it finds, the routine locates the next closing brace and discards the text between them.

After it has removed comments enclosed in braces, StripCommands uses a similar procedure to remove comments that begin with a double dash and end with a carriage return. Finally, the routine replaces new line and tab characters with spaces. This will make it easier for the ProcessCommand subroutine to determine whether a command is completely blank.

Private Sub StripCommands(cmd As String)
Dim new_cmd As String
Dim token As String
Dim got_token As Boolean

    ‘ Remove { } style comments.
     new_cmd = “”
    ‘ Get the command up to the first “{”.
     got_token = GetToken(cmd, “{”, token)
    Do While got_token
        ‘ Add this piece to the new command.
         new_cmd = new_cmd & token & “ ”

        ‘ Get (and ignore) the comment.
         got_token = GetToken(“”, “}”, token)
         If Not got_token Then Exit Do

        ‘ Get the command up to the next “{”.
         got_token = GetToken(“”, “{”, token)
    Loop

    ‘ Remove -- style comments.
     cmd = “”
    ‘ Get the command up to the first “--”.
     got_token = GetToken(new_cmd, “--”, token)
    Do While got_token
        ‘ Add this piece to the new command.
         cmd = cmd & token & “ ”

        ‘ Get (and ignore) the comment.
         got_token = GetToken(“”, vbCrLf, token)
     If Not got_token Then Exit Do

        ‘ Get the command up to the next “--”.
         got_token = GetToken(“”, “--”, token)
    Loop

    ‘ Remove new lines.
     new_cmd = “”
    ‘ Get the first line.
     got_token = GetToken(cmd, vbCrLf, token)
    Do While got_token
        ‘ Add this piece to the new command.
         new_cmd = new_cmd & token & “ ”

        ‘ Get the next line.
         got_token = GetToken(“”, vbCrLf, token)
    Loop

    ‘ Remove tabs.
     cmd = “”
    ‘ Get the first line.
     got_token = GetToken(new_cmd, vbTab, token)
    Do While got_token
        ‘ Add this piece to the new command.
         cmd = cmd & token & “ ”

        ‘ Get the next line.
         got_token = GetToken(“”, vbTab, token)
    Loop

    ‘ Trim leading and trailing blanks.
     cmd = Trim$(cmd)
End Sub

ProcessCommand

ProcessCommand executes a single SQL statement and adds the results to the end of a result string. After checking that the statement is not empty, the routine sets the SQL property of a QueryDef object equal to the statement.

SELECT statements must be handled a bit differently from other SQL statements. SELECT statements return rows of data; other SQL statements perform actions. ProcessCommand examines the first word in the command to see if it is “SELECT.”

If the command is a SELECT statement, ProcessCommand invokes the ProcessSelect subroutine to execute it. Otherwise, ProcessCommand uses the QueryDef object’s Execute method to execute the command. It then performs some final calculations to present an informative message indicating that the command succeeded.

Private Sub ProcessCommand(cmd As String, result As String)
Dim rows As Integer
Dim verb As String
Dim reply As String

    ‘ If it is blank, do nothing.
     If cmd = “” Then Exit Sub

    ‘ “Compile” the QueryDef.
     On Error GoTo QueryDefError
    TheQD.SQL = cmd

    ‘ See what the command verb is.
     If Not GetToken(cmd, “ ”, verb) Then Exit Sub
    verb = UCase$(Trim$(verb))

    ‘ Execute the command.
     If verb = “SELECT” Then
        ‘ It‘s a SELECT statement.
         ProcessSelect result
    Else
        ‘ It‘s an action query. Execute it.
         TheQD.Execute

     If verb = “DELETE” Or verb = “INSERT” Or _
        verb = “UPDATE” _
     Then
            ‘ These affect rows.
             rows = TheQD.RecordsAffected
            If rows = 1 Then
                 reply = “1 record.”
            Else
                 reply = Format$(rows) & “ records.”
         End If
         Select Case verb
              Case “DELETE”
                   verb = “Deleted ”
              Case “INSERT”
                   verb = “Inserted ”
              Case “UPDATE”
                  verb = “Updated ”
         End Select
         result = result & verb & reply & _
          vbCrLf & vbCrLf
     Else
            ‘ These affect tables, indexes, etc.
             result = result & verb & “ OK.” & _
          vbCrLf & vbCrLf
     End If
    End If  ‘ End if SELECT ... Else ...

    On Error GoTo 0
    Exit Sub
QueryDefError:
    result = result & “Error” & _
     Str$(Err.Number) & _
     “ executing command.” & vbCrLf & _
     vbCrLf & Err.Description & vbCrLf & vbCrLf
    Exit Sub
End Sub