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
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
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
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
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:
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 (:).
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"
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
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:
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,