Category : Paradox DBMS
Archive   : TECH91.ZIP
Filename : TI154.ASC

PRODUCT : Paradox NUMBER : 154
VERSION : 2.0 & up
OS : DOS
DATE : November 12, 1991 PAGE : 1/5

TITLE : Variables and Wildcards in PAL Queries




There are two ways of performing queries in a PAL application.
The first way is through a script that uses commands that follow
the interactive Paradox menu. The other way is through a script
using the PAL QUERY/ENDQUERY command. Each method has its
comparative advantages and disadvantages. In this document, we
will call the first method a Menu Equivalent Query and the
second, a Saved Query Image.

Menu Equivalent Queries can be saved by selecting {Scripts}
{BeginRecord} then choosing the keystrokes for filling out a
query. This is a typical Menu Equivalent Query on a table called
SAMPLE:

{Ask} {Sample} Right Check "John Doe" Enter Check "123 Anystreet"
DO_IT!

Saved Query Images are created by filling out a query form
interactively then selecting {Scripts} {Querysave}. This is a
typical example as it appears in the script.

Query

Sample | Name | Address |
| Check John Doe | Check 123 Anystreet |
| | |
| | |

Endquery

These two methods are useful for saving time when performing the
same query repeatedly, but some modifications must be made to use
variables and wildcard operators in a query.

First, for Saved Query Images you can use variables by preceding
them with a tilde (~). In the example above, if you wanted a
variable name and address you would change it to the following:




















PRODUCT : Paradox NUMBER : 154
VERSION : 2.0 & up
OS : DOS
DATE : November 12, 1991 PAGE : 2/5

TITLE : Variables and Wildcards in PAL Queries




NAME="John Doe" ; Assign values to the
ADDRESS="123 Anystreet" ; NAME and ADDRESS variables
Query

Sample | Name | Address |
| Check ~NAME | Check ~ADDRESS |
| | |
| | |

Endquery
DO_IT!

For this query to work properly, the variables NAME and ADDRESS
must be assigned values (e.g. "John Doe" and "AnyStreet"). This
query searches for an exact match based on the values of NAME and
ADDRESS at the time of the query. To use wildcard operators, you
must make the wildcard operator part of the query statement, not
part of the variable assignment. For instance, to find all
addresses on Anystreet, assign, "Anystreet" to the ADDRESS
variable. Then type ..~ADDRESS in the Address field of the query
statement. For example,

NAME="John Doe" ; Assign values to the
ADDRESS="123 Anystreet" ; NAME and ADDRESS variables
Query

Sample | Name | Address |
| Check ~NAME | Check ..~ADDRESS |
| | |
| | |

Endquery
DO_IT!

To use variables and wildcard operators in a Menu Equivalent
Query, there are two methods, the TYPEIN command and a Field
Assignment statement (e.g. [Name] = NAME). The TYPEIN command
simulates typing characters into Paradox from the keyboard. Its
effect is similar to a quoted string (e.g. "John Doe"), but it
allows the characters to be stored in a variable. For example:

TBL="sample" ; Assign the TBL, NAME
NAME="john.." ; and ADDRESS variables













PRODUCT : Paradox NUMBER : 154
VERSION : 2.0 & up
OS : DOS
DATE : November 12, 1991 PAGE : 3/5

TITLE : Variables and Wildcards in PAL Queries




ADDRESS="123 Anystreet"

These tools in combination with other PAL commands can create
very flexible queries. For example:

{Ask} SELECT TBL Right Check TYPEIN NAME Enter Check TYPEIN
ADDRESS DO_IT!

Note: we used the "Right" command to move to the Name field
before using the TYPEIN command.

Like the TYPEIN command, a Field Assignment statement (=) also
simulates typing the value from the keyboard. Unlike the TYPEIN
command, it is not necessary to move to the field before
performing a Field Assignment statement. The following is an
example of how to use a Field assignment statement. For better
readability, the commands are placed on different lines and each
line is commented.

{Ask} SELECT TBL ; Select table to query
Right Check ; Move right and check Name field
[Name]=NAME ; Assign NAME variable to Name
; field
Enter ; Enter moves cursor to the right
Check ; Check Address field
[Address]=ADDRESS ; Assign Address variable to
; Address
DO_IT! ; Press [F2] to process query

We also have the flexibility to make the Menu Equivalent query
perform wildcard searches. By setting the NAME variable, equal
to "john.." the query will be a wildcard search in the NAME
field. The query still searches for an exact match in the
Address field. In contrast to a Saved Query Image, wildcard
operators can be included as part of the variable assignment.

To perform this query on a variable table, a tilde variable can
not be substituted for the table name "Sample."

To perform the same query on variable tables you need to use a
Menu Equivalent Query along with the SELECT command. If you
wanted the first example to be performed on several tables
instead of just SAMPLE you would change it to the following:













PRODUCT : Paradox NUMBER : 154
VERSION : 2.0 & up
OS : DOS
DATE : November 12, 1991 PAGE : 4/5

TITLE : Variables and Wildcards in PAL Queries




TBL="sample" ; Assign value to TBL variable

{Ask} SELECT TBL Right Check "John Doe" Enter Check
"123 Anystreet" DO_IT!

Note: Where the first example had the actual table name in curly
braces "{Sample}", we have placed the SELECT command and the
variable TBL which is equal to a table name. Changing the value
of the TBL variable changes the table that is queried.

An important issue exists when using Menu Equivalent queries and
Query Saved images. In your application, you may not want a query
to be performed using every variable. For example, you may want
to search for "John Doe" regardless of what the address is. To
do so, you might think that not assigning a value to the ADDRESS
variable is sufficient. However, it is not! When a variable is
not assigned a value, the variable by default equals null. Thus,
the query would search for "John Doe" in the name field AND the
null value "" in the Address field.

To perform this query correctly, you must use the ISASSIGNED
function to determine if the variable has been assigned a value.
When the variable has been assigned value, the value in the
variable is placed into the query. For example,

TBL="sample" ; Assign the TBL variable

@ 5,5
?? "What name would you like to search for? "
ACCEPT "A20" to NAME ; Get NAME from user
@ 10,5
?? "What address would you like to search for? "
ACCEPT "A35" to ADDRESS ; Get ADDRESS from user

{Ask} SELECT TBL
Right Check
[Name] = NAME ; Assign Name field
Enter Check
IF NOT ISASSIGNED(ADDRESS) THEN ; If Address is assigned
[Address] = ADDRESS ; place into the field
ENDIF
DO_IT! ; Process query














PRODUCT : Paradox NUMBER : 154
VERSION : 2.0 & up
OS : DOS
DATE : November 12, 1991 PAGE : 5/5

TITLE : Variables and Wildcards in PAL Queries




As you can see, we use the ACCEPT command to get values from the
user, and we have the option of leaving the ADDRESS variable
empty by using an IF statement and the ISASSIGNED function to
test for ADDRESS being assigned a value. If ADDRESS is assigned
a value then it is assigned to the field.

For additional commands that can be used in conjunction with
queries see the PAL User's Guide.