How to dynamically build
SQL statements for dBASE data objects
by Gary White [dBVIPS - dBASE Volunteer Internet Peer Support]

WHILE it can be confusing at first, using SQL with dBASE data objects is not terribly complex once you understand it. The purpose of this article is not to teach SQL, but instead to show you how to use the SQL you already know, or can learn, with dBASE data objects.

For help with the actual sql statements, try to catch Steve Koterski in the Inprise news groups and have him e-mail you the updated LocalSQL help file.  It is very good and can help you understand the sql end of it. Another resource is the SQL Designer. Though some don’t seem to like it, it works quite well and can help you a lot with some of the more complex sql statements. One other resource is Ken Mayer’s X2SQL.HOW file. Just remember that most sql other than a simple select will return a read-only rowset.

I’ll try to explain. I’m going to try to be very basic to try to make it clear. Please don’t be insulted if I point out the obvious. The place most people get confused is in assigning an sql statement to a query object using variables. This is where the delimiters start confusing people.

Let’s start out even more basic than that. Let’s assume you want to create the simplest of all SQL statements:
 
 
select * from mytable
   

If you type that line in the command window and then type browse, you will see that you’ve selected all the fields from all the rows (records) in the table mytable. Now, suppose you wanted to assign that very basic SQL statement to a dBASE query object. It’s very simple:
 
 
q.sql := "select * from mytable"
   

As you can see, the SQL property of the query object is a string. Simply enclosing your SQL statement in double quotes ( " ) creates the necessary string. Had you constructed the sql statement dynamically and assigned it to a variable, it might look like this:
 
 
cSql = "select * from mytable"
q.sql := cSql
   

Now, let’s say you wanted a slightly more complex sql statement like the following:
 
 
select * from customer where lastname = "Jones"
   

If you wanted to assign that sql statement to a query object, that sql statement needs to be a string. You’re already using double quotes in the statement so you can’t simply enclose the statement in double quotes. Remember that dBASE uses three different sets of string delimiters: double quotes ( " ), single quotes ( ' ) and square brackets ( [ ] ). In this case, you can enclose the entire statement in either single quotes, or square brackets.  Most people favor square brackets because when you get single quotes next to double quotes it gets really hard to read. Is this "' a double quote and a single quote, or is it a single quote and a double quote? See what I mean? Thus:
 
 
q.sql := [select * from customer where lastname = "Jones"]
   

would be a legal assignment. Now, suppose that, instead of the literal name “Jones”, you wanted to use a variable to allow the user to choose (or enter) a name. The following WON’T WORK:
 
 
cName = "Jones"  // for example 
select * from customer where lastname = cName
   

The reason is that when the statement is evaluated, there are no quotation marks in the variable cName and it winds up looking like:
 
 
select * from customer where lastname = Jones
   

Without string delimiters, dBASE will try to evaluate Jones as a variable and will inform you that the variable does not exist. Instead, if you were typing this in the command window, the sql statement could look like:
 
 
select * from customer where lastname = "&cName"
   

But, if you’re going to use this as the sql property of a query object, you don’t need the macro evaluation.  You can embed the quotes in the string.
 
 
q.sql := [select * from customer where lastname = "] + cName + ["]
   

What the above does is builds a single string that looks exactly like the first one, including the quotation marks. Square brackets enclose the literal part of the string, including a beginning double quote. The variable, cName, is concatenated to that and a closing double quote is concatenated to that.  You can prototype that in the command window with:
 
 
? [select * from customer where lastname = "] + cName + ["]
   

The other thing that sometimes confuses people is when using something other than character fields. Date values require string delimiters just like character strings.  This is so that the date is not evaluated as a math operation: 3/10/1999 could be 3 divided by 10 divided by 1999. Numeric and Logical values are just handled as literals:
 
 
select * from customer where CustAge > 21 
select * from customer where Updated = false
   

So to use the above:
 
 

 
nAge = 21 
q.sql := [select * from customer where CustAge >] + nAge

 
bUpdated = false
q.sql := [select * from customer where Updated =] + bUpdated

   

dBASE will automatically handle the type conversions on those.

Working with the filter property of a rowset object is exactly the same as the above. The filter is essentially an SQL statement, or at least part of one. If you wanted to create a filter where the field lastname = “Jones”, it would look like this:
 
 
q.rowset.filter := [lastname = "Jones"]
   

If you were using a variable, instead of the literal name “Jones”, you’d do like before and embed the quotes in the string:
 
 
cName = "Jones"
q.rowset.filter := [lastname = "] + cName + ["]
   

One other little tip here about filters. If you try to create one in the form designer, do not use double quotes in the filter condition. The form designer streams the filter property with double quotes and this will not work if the filter condition contains double quotes.  If you need string delimiters for the filter property in the form designer, use either single quotes, or square brackets.

Let’s cover one other aspect of dynamic SQL statements. You can also use what is called a parameterized query. The query object has a params property. This is an associative array, much like the fields array property of the rowset object. Params are identified in an SQL statement by preceeding them with a colon (:). For example:
 
 
q.sql := "select * from customer where lastname = :somevalue"
q.params["somevalue"] := "Jones"
q.active := true
   

A couple of points to remember.  First, when the sql statement includes a param, identified by the colon, the value of the param must be assigned before attempting to set the active property to true. If you fail to accomplish this, dBASE will throw an exception. Second, is the fact that changing the value of the parameter requires you to requery() in order to see the results. Continuing from the above three statements:
 
 
q.params["somevalue"] := "Smith"
q.requery()
   

If you were using a variable, instead of the literals shown above, there is nothing tricky involved. Just assign the variable as you would to any other property:
 
 
cName = "Johnson"
q.params["somevalue"] := cName
q.requery()
   

There is anther type of parameterized query. This is only used when using a C/S backend server and uses the MasterSource and the params properties of the query object. For example:
 
 
ChildQuery.sql := "select * from ChildTable where LinkField = :ParentLinkField"
ChildQuery.MasterSource := ParentQuery.rowset
ChildQuery.params["ParentLinkField"] = ""
   

In this special instance, whenever navigation takes place in the parent table, a ReQuery() is automatically executed in the child rowset and the child rowset only displays records which match the parent rowset. This is the way you would establish relationships when using a Client/Server backend.

The last thing I’ll cover here that sometimes confuses people is when a field name has a space in it, or is a LocalSQL reserved word.  In those cases, the field name must be preceded by the table name and be enclosed by quotes:
 
 
tableName."field name"
   

This adds to the complexity of the statement when constructed on the fly and, quite honestly, I really try hard to avoid those situations.

Hope this helps a little,

Gary White


Some dBase Stuff at http://www.apptools.com/dbase