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:
|
|
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