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

This type of security is important if some of the reports contain confidential information such as employee salaries. It is also important for powerful reports such as the free-format SQL query capability provided by the SQLServer program. This “report” allows the user to execute any SQL SELECT statement so a knowledgeable user who executes this report can view any data in the database.

With only a small change to the free-format query server, the program could allow a user to execute any SQL statement. The Query application described in Chapter 9 does this. In that case, the program would need to be even more careful in granting access to the report server. Only the most trusted and skilled users should be allowed to execute powerful SQL statements such as DELETE, ALTER TABLE, or DROP TABLE.

To prevent potential damage, the program can implement user privilege checks centrally in ReportLister and allow the user access to only the appropriate report servers.

Building SQLServer

The SQLServer program in this example contains five classes, each of which serves one of the available reports. The main program does nothing, though the main module does provide two support routines used by the server classes. The ProcessSelect function executes an SQL query, much as the ProcessSelect function used by the Query application described in Chapter 9 does.

The WhereClause function takes as parameters an array of field names with operators (=, >=, <>, ...), a string containing a semi-colon-delimited list of field values, and an array of delimiters. Using these values, it constructs an appropriate SQL WHERE clause.

For example, suppose the WhereClause function is passed the following values:

Field names and operators:
    Quantity>=
    DateSold<
    Name=
Value string:
    12;4/1/97;Michaelson;
Delimiters:
	 (empty)
    #    (number sign)
    ‘    (single quote)

Then the corresponding WHERE clause would be as follows:

WHERE Quantity>=12 AND DateSold<#4/1/97# AND Name=‘Michaelson‘

The WhereClause function skips any fields with empty values. For instance, the value string “72;;12” contains no value for the second field so that field would be omitted from the WHERE clause.

Public Function WhereClause(names() As String, values As String, delimiters() As String) As String
Dim where_clause As String
Dim need_where As Boolean
Dim not_done As Boolean
Dim token As String
Dim i As Integer
  
    ‘ Compose the clause.
   need_where = True
    where_clause = “”
    not_done = GetToken(values, “;”, token)
    For i = LBound(names) To UBound(names)
	If Trim$(token) <> “” Then
	    If need_where Then
		where_clause = where_clause & “ WHERE ”
		need_where = False
	    Else
		where_clause = where_clause & “ AND ”
	    End If
	    where_clause = where_clause & names(i) & _
		delimiters(i) & token & _
		delimiters(i)
	End If
  
	not_done = GetToken(“”, “;”, token)
    Next i
  
    WhereClause = where_clause
End Function

A Typical SQLServer

The server classes in SQLServer are very similar. Each provides two functions: ListParameters and LoadReport. ListParameters returns to the ReportList client program a semi-colon-delimited list of prompts and default values. As described in an earlier section, ReportList separates the prompts and default values and, using a ParameterForm, allows the user to enter values for the fields.

The following code shows the ListParameters function for the Report1 class. This report takes as parameters minimum and maximum date and cost values. ListParameters uses the Visual Basic Now function to make the maximum report date be the current date.

Public Function ListParameters() As String
Dim date_now As String
    date_now = Format$(Now, “Short Date”)
    ListParameters = “Start Date;1/1/80;” & _
	“End Date;” & date_now & “;” & _
	“Minimum Cost;0.00;” & _
	“Maximum Cost;;”
End Function

After ReportList uses the parameter string to obtain values from the user, it passes the results to the server object’s LoadReport function. LoadReport builds an array listing the field names and operators, plus an array listing delimiters appropriate for each field’s data type. Date values, for example, must be surrounded by number signs (#) in SQL queries. LoadReport passes these arrays and the values returned by ReportList into the ProcessSelect function to produce the final report. The report results are returned to ReportList for display.

Public Function LoadReport(params As String) As String
Dim query As String
Dim field_names(1 To 4) As String
Dim delimiters(1 To 4) As String

    ‘ Fill field_names with the database field
    ‘ names and appropriate operators (=, >, etc.)
   field_names(1) = “PurchaseDate>=”
    field_names(2) = “PurchaseDate<=”
    field_names(3) = “Cost>=”
    field_names(4) = “Cost<=”
  
    ‘ Fill in delimiters for different data types.
   delimiters(1) = “#”     ‘ Surround dates with #.
   delimiters(2) = “#”
    delimiters(3) = “”      ‘ Numeric fields do not need delimiters.
    delimiters(4) = “”
  
    ‘ Compose the query.
   query = “SELECT * FROM Purchases” & _
	WhereClause(field_names, params, delimiters) & _
	“ ORDER BY PurchaseDate”
  
    ‘ Execute the query.
   LoadReport = query & vbCrLf & vbCrLf & _
	ProcessSelect(DB_NAME, query)
End Function

Free-Format SQL

Because it provides greater flexibility, the free-format SQL query may seem more complicated than other reports. Actually it is simpler. Because the user enters the SQL statement directly, the server does not need to construct a WHERE clause. It simply takes the statement provided by the user and passes it to the ProcessSelect function. ProcessSelect executes the indicated SQL query much as the ProcessSelect function used by the Query application described in Chapter 9 does.

The free-format query server code that follows is in the Report5 OLE server class.

Public Function ListParameters() As String
    ListParameters = “SQL Query;SELECT * FROM Purchases;”
End Function
Public Function LoadReport(params As String) As String
Dim query As String
Dim not_done As Boolean
    ‘ Get the query.
   not_done = GetToken(params, “;”, query)
  
    ‘ Execute the query.
   LoadReport = query & vbCrLf & vbCrLf & ProcessSelect(DB_NAME, query)
End Function

Summary

The QueryServer application uses a client application and a collection of server programs to provide centralized report services. Keeping the report services centralized reduces network traffic and makes maintenance easier. It also provides opportunities for advanced features such as customization based on user privileges.

Maintenance becomes even simpler by the fact that the ReportList client need know only little about reports, because ReportList allows the user to specify report parameters. Using the ListParameters and LoadReport routines provided by the report server objects, ReportList can access the full power of the report servers.