Building Better SQL Queries

Building SQL statements is a common activity in database-driven application development. It is frequently ugly and difficult to understand when you want to do something like the example shown in Listing 1. This query runs against an awkwardly designed SQL database. Its purpose is to extract pricing and supply and demand information for a particular year, week, day of the week, and hour. The information for each hour is updated more than once, so the query must ensure that the results are being retrieved from the PRICE_CALCULATION row that matches the year, week, day, and hour in question. But that has the highest PRICE_CALCULATION_ID, an identity-like column that increments for the arrival of each new row.

Listing 1 compiles correctly, but the query will not run because of an almost invisible bug produced by using this style of query creation. The line just before the FROM MARKET_CLEARING_PRICE does not end with a space. Once the string concatenation is completed, that portion of the query will read FROM MARKET_CLEARING_PRICEWHERE with no space before WHERE.

The whole construction is awkward, with blanks being appended at the end of lines just to ensure that spaces exist between the words of the query.

In order to check this, you have to physically review the resulting string, and even that isn't foolproof because the query is very long. It would be possible to misread the result.

In addition, during the development of this query, chances are you will take a few passes at the query before you get it to return the result set that you want. This means editing and reformatting the mess in Listing 1, which can only make matters worse.

Enter the CSQL Class

The CSQL class builds a correctly formatted query from parameters entered one piece at a time. Listing 2 illustrates how much simpler and readable the results are. Notice that a whole section of code can be removed just by commenting out the appropriate lines.

Let's look at the details of the class. A CSQL class is created as a Class Module in Access or Visual Basic. It is instantiated as an object inside a function. Elements (table names, conditions, and field names for selection) are added to the object by using property LET methods. Each element assigned to the object is added to a collection for that type of element. These collections sit untouched as collection lists until the request is made for the GET property, SelectQuery(). All of the lists of elements are correctly formatted into a query string that is returned from the SelectQuery() GET property.

Listing 3 is an abbreviated version of CSQL so that you can see what it is doing at the simplest level. It consists of property GETs and LETs for Selct(), Table(), and Where(). "Select" is a reserved word, so I had to use Selct() instead. There is also a property GET to return the formatted query, SelectQuery(). The trick in the property LET functions is that each assignment (as in Listing 2) actually adds the value to the appropriate collection list.

The property GET versions of Selct(), Table(), and Where() are not used directly (though they can be). Each GET retrieves the complete list of all values and returns the string with formatting and spacing correctly inserted. The property GET for SelectQuery() assembles the three strings built from the Selct(), Table(), and Where() properties into one string and returns it, again with appropriate formatting added.

The get_item_list() function actually does most of the work of the property GET methods by combining the string starter, item delimiters, and string closer with the items themselves into a correctly formatted string. Note that I insert a vbCRLF between each item. You do not need to do this, but I prefer this style of formatting. If you do not use vbCRLF, you will usually need to insert spaces between the strings in the SelectQuery() GET method.

Of course, it will be immediately apparent that ORDER BY, GROUP BY, and HAVING can be added to the CSQL class by using the same basic techniques as those used for Selct(), Table(), and Where(). The complete code for CSQL, Listing 4, includes the changes for these options. Note the change to the assembly of strings in SelectQuery.

From this point, the class can be expanded into several useful extensions:

• Add a flag to indicate the type of query being formatted (MS Access, SQL Server, Sybase, Oracle, MySQL) so that SQL dialect variations can be returned correctly by the object.
• Add the properties and functions needed to handle INSERT, UPDATE, and DELETE queries.

• Add functions to handle the variations in quoting and formatting methods used by various SQL dialects.

Handling Inserts and Deletes

An insert query looks like a select query with some extra insert information added before the select statement or a simple insert of values. Listing 5 illustrates two simple insert styles. The first is inserting a new employee, and the second is inserting department totals by week into a week-totaling table.

Therefore, an INSERT query can have all of the parts of a SELECT query, plus two extra pieces of information: a table into which the inserts will happen, and a list of field names into which data will be placed. In truth, InsertTable() can only include one table into which an insert can occur, but using the existing logic keeps it simpler. A more thorough version of CSQL would check for more than one table and generate some sort of error. In practice, I have never needed this.

Some dialects of SQL will not allow an INSERT SELECT combination when values are not selected from a table. In other words, the first INSERT statement in Listing 5 would not work, but the second would. Others allow the first style. In order to play it safe, when retrieving an INSERT query, use the VALUES option if no tables are listed. The InsertQuery() property GET in Listing 4 shows how to do this.

In practice, all inserts must be in pairs of data, a target field for the insert and a value to be inserted. In very large queries, it is easy to lose your finger in the book and produce too few or too many values, or leave out a field. I added an additional InsertValues() method that is not a LET property. It is a method that actually takes two arguments and then uses the property LET to add them to the appropriate collections. Listing 4 includes this additional method.

Delete queries are fairly easy as they use no additional collections. A DELETE query is basically a table and a "where" condition. In Access, use "DELETE * FROM"; in other SQL dialects, use a simple DELETE or DELETE FROM.

Listing 4 takes care of these dialectic differences. The list of fields to set values to for an update query looks somewhat like "where" conditions. It turns out to be easier to create a new collection of UpdVals() rather than try to use any of the existing collections. Listing 4 is the complete CSQL class module. It also includes a SubQuery() property GET that returns a select query surrounded by parentheses. It is a handy property.

There are several advantages to building a query using the CSQL class. It makes conditional content of the query very easy to specify. You also do not have to follow the sequence dictated by SQL syntax. This makes it possible to code it the way you 'think it'; whatever way that happens to be for you. Listing 6 shows how the CSQL class is used.

Listing 7 is the output from the insert query shown in Listing 6. If you like the style of putting each item on a separate line, or using the leading commas, feel free to change the get_item_list() method to your own preferences. The layout style I used in CSQL causes each element of the query to be placed on a separate line, as illustrated in Listing 7.

This approach to query string generation is so useful that I have extended the class to include Make Table queries and Create Table (Data Definition) queries. I have rewritten the class in Access, VB, and C++, and I am now creating the Java version. I use it regularly and have seen several programmers cut the code out of an existing application and add it to their toolkits. I hope you find it as useful as many others have.