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

ProcessSelect

Because a SELECT statement returns an unknown number of rows of data, it must be handled differently than other statements. Subroutine ProcessSelect uses the QueryDef object initialized in ProcessCommand to create a Recordset object. It then examines the data types of the Recordset’s fields to determine how wide each column in the result will be. For example, a short integer can be up to six characters long, as in -32767.

ProcessSelect then examines each field’s name to make sure each column is wide enough to display the name. It also makes sure each column is at least four characters wide so there is room to display the value “null.”

The subroutine then adds column headers to the result string. Finally, it loops through the records in the Recordset, adding each of the fields’ values to the result string.

Private Sub ProcessSelect(result As String)
Dim rs As Recordset
Dim col_type() As Integer
Dim col_wid() As Integer
Dim max_col As Integer
Dim i As Integer
Dim j As Integer
Dim col_value As String
Dim max_rec As Integer

    ‘ Open the Recordset.
     Set rs = TheQD.OpenRecordset(dbOpenSnapshot, _
     dbReadOnly)

    ‘ See how wide each column should be.
     max_col = rs.Fields.Count - 1
    ReDim col_wid(0 To max_col)
    ReDim col_type(0 To max_col)
    For i = 0 To max_col
     col_type(i) = rs.Fields(i).Type
     Select Case col_type(i)
         Case dbDate         ‘ Date/Time
                 col_wid(i) = 8
         Case dbText         ‘ <= 255 characters
                 col_wid(i) = rs.Fields(i).Size
         Case dbMemo         ‘ <= 1.2 GB
                 col_wid(i) = 0      ‘ Hide this.
             Case dbBoolean      ‘ Boolean
                 col_wid(i) = 5
         Case dbInteger      ‘ Integer
          col_wid(i) = 6
         Case dbLong         ‘ Long
                 col_wid(i) = 11
         Case dbCurrency     ‘ Currency
                 col_wid(i) = 16
         Case dbSingle        ‘ Single
          col_wid(i) = 12
         Case dbDouble       ‘ Double
                 col_wid(i) = 21
         Case dbByte         ‘ Byte
                 col_wid(i) = 3
         Case dbLongBinary   ‘ Long Binary (OLE Object)
                 col_wid(i) = 0      ‘ Hide this.
         End Select

        ‘ Allow room for the field‘s name.
         col_value = rs.Fields(i).Name
     If col_wid(i) < Len(col_value) Then _
         col_wid(i) = Len(col_value)

        ‘ Allow at least 4 spaces for “Null”.
         If col_wid(i) < 4 Then col_wid(i) = 4

        ‘ Add an extra space between fields.
         col_wid(i) = col_wid(i) + 1
    Next i  ‘ End setting column widths.

    ‘ Display column headers.
     For i = 0 To max_col
        ‘ Add the name for field i.
         col_value = rs.Fields(i).Name
     result = result & col_value & _
         Space$(col_wid(i) - Len(col_value))
    Next i
    result = result & vbCrLf
    For i = 0 To max_col
        ‘ Add underscores beneath field i's name.
         col_value = “”
     For j = 1 To Len(rs.Fields(i).Name)
         col_value = col_value & “-”
     Next j
     result = result & col_value & _
         Space$(col_wid(i) - Len(col_value))
    Next i
    result = result & vbCrLf

    ‘ Display the data.
     Do Until rs.EOF
     For i = 0 To max_col
            ‘ Add the value for field i.
             If IsNull(rs.Fields(i)) Then
                 col_value = “Null”
             ElseIf col_type(i) = dbMemo Or _
                col_type(i) = dbLongBinary Then
                 col_value = “*”
             Else
                 col_value = rs.Fields(i)
             End If
             result = result & col_value & _
                  Space$(col_wid(i) - Len(col_value))
     Next i
     result = result & vbCrLf

     max_rec = rs.RecordCount
     rs.MoveNext
    Loop

    ‘ Say how many records were selected.
     If max_rec = 1 Then
     result = result & “Selected 1 record.” & _
         vbCrLf & vbCrLf
    Else
     result = result & “Selected” & _
         Str$(max_rec) & “ records.” & _
         vbCrLf & vbCrLf
    End If

    ‘ Delete the Recordset.
     Set rs = Nothing
End Sub

Privileges

Query is an extremely powerful application. With a few keystrokes, a user can delete every record in a table or even delete the table itself. Because this can be potentially disastrous, it is important that Query never falls into careless hands.

At the same time, Query makes a handy reporting utility. For a large database project used by many people, allowing more experienced users the convenience Query provides is reasonable. To ensure that these users do not damage the database, either intentionally or accidentally, Query can include user privileges and password protection features similar to those demonstrated by the PeopleWatcher application described in Chapter 8.

Query’s ProcessCommand subroutine already checks for the command verbs SELECT, DELETE, INSERT, and UPDATE so it can take special actions for these commands. It could also verify that the user has permission to execute one of these commands. If the user does not have DELETE privilege, for example, Query can add a message to the output text saying that the operation is not allowed.

The program could also add other potentially dangerous commands to the list of privileges. Some of these are CREATE, DROP, and ALTER. These commands are potentially more dangerous than INSERT, UPDATE, and DELETE.

A program should refuse to allow a user to execute a command unless permission is explicitly granted in the Privileges tables. Then if the program overlooks a command, or if a future version of the data access objects supports a new DESTROY command, the user cannot slip past the program’s safeguards and wreak havoc on the database.

Query is designed to be used by database administrators so it does not perform these checks. It assumes the user is responsible and knows SQL well enough not to damage the database accidentally.

Summary

The Query application uses the QueryDef object to execute practically any database command. If the user attempts to open a database that does not exist, the program can even create a new database. These features make Query a powerful tool for ad hoc reporting and database maintenance.