The technique of asking questions about data by providing an example of the answer you expect is called query by example(QBE). You can use this tool to ask questions about your data and analyze it. A query is a means of extracting sets of information from a database or table. You can base forms and reports on a subset of your data by using a query as the first table in your data model. You can even use a live query as this first table. And you can also create queries on the fly in ObjectPAL. The Query variable is a handle to a QBE query.
Queries are a very important part of any database. Having a large amount of data isn’t useful unless you can analyze it. Queries are what you use to analyze your data. Sometimes they are the only way to get certain information about a product. Paradox delivers a graphical QBE that makes it easier than ever to create queries and get fast answers. You can access up to 24 tables in a single query. Join, outer join, inclusion, and set operations are available for full relational queries.
QBE has two functions—namely, as an end-user tool and as a way to use a subset of all the data. You can use QBE to develop specialized forms and reports. Important query methods and procedures include the following:
Suppose that you want to bring up the query window with a particular query. To do this, you can use sendKeys() from the system type. For example, change your working directory to Paradox’s Samples directory and type line 3 into the pushButton event of a button.
emulates the user pressing keys. The first part of the preceding code emulates the user pressing alt-f-o-q, which is the same as selecting File | Open | Query. The second half, WORLD{ENTER}, specifies the name of the query to open and presses the return key.
The term "query" in ObjectPAL refers to the Query variable. The Query variable is your handle to a query. Just as you can declare a Form variable and open it, you can declare a Query variable, read a file into it, and execute it. For example, change your working directory to Paradox’s Samples directory and type lines 2–7 into the pushButton event of a button.
1: method pushButton(var eventInfo Event)
2: var
3: q Query ;Declare a Query variable.
4: endVar
5:
6: q.readFromFile("CUSTOMERLIST.QBE") ;Read in QBE.
7: q.executeQBE() ;Execute QBE.
8: ;Open up the answer table in your private directory.
9: endMethod
Suppose that you have an existing query, CUSTOMERLIST.QBE, that you want to run, then show the results in a table window and enable Paradox to delete the answer table automatically when it exits.
Change your working directory to Paradox’s Samples directory and create a query based on the CUSTOMER.DB table and check the Customer Name and Phone fields, as shown here. Save the query as CustomerList.qbe.
Illustration 1
Create a new form. Add a button labeled Run Query File to it, as shown here:
Illustration 2
Add lines 3–10 to the pushButton event of the Run Query File button to execute the query. Line 4 declares a Query variable, and line 5 declares a TableView variable. Line 8 uses readFromFile() to read the QBE file CUSTOMERLIST.QBE into the qQuery variable. Then, executeQBE() is used in line 9 to run the query and create a table named __ANS.DB in your private directory.
Check the syntax, save the form as Query.fsl, run the form, and click the button. The query runs and creates a table named __ANS.DB in your private directory. Then the table is opened (see Figure 11-1). When you exit Paradox, the table is deleted.
Figure 1: QUERY.FSL demonstrates how to run a query from ObjectPAL
This last step of using two underscores at the beginning of a file in your private directory is important. It is also interesting, because it takes advantage of an undocumented feature of Paradox. Any file in the private directory that starts with two underscores is deleted when the program is exited. This is a normal part of the cleanup process of Paradox. In addition, the files are not listed in the browser or Project Viewer.
In addition to executing a query file—for example, CUSTOMERLIST.QBE—you can code a query inside your code with ObjectPAL. First, you declare a Query variable.
1: var
2: q Query
3: endVar
Next, you use the defined Query variable to start the query section in your code. Then comes the actual Query string. For example:
Typing all these field names, checks, and values would be a hassle, to say the least. In essence, you have to learn a whole new programming language, the QBE language. Luckily, ObjectPAL provides an easier way. A saved QBE file is simply a text file. Therefore, you can use the Edit | Paste From option to paste the text file and alter it as needed. Then, use executeQBE() the way you did in the previous example. For example:
Change your working directory to the Paradox’s Samples directory and create a new query based on CUSTOMER.DB. Check the Name and Phone fields and query for all the records with Negril in the City field, as shown here. Save the query as CustomerQuery.QBE.
Illustration 3
Create a new form and add a button labeled Query Customer to it.
The easiest way to a build a query in ObjectPAL is to build it interactively and paste it into the Editor. A QBE file is simply a text file, as you can see in the illustration shown here. It can be pasted directly into the Editor. The easiest way to do this is to use the Edit | Paste From option of the editor.
Illustration 4
Paste the text from CustomerQuery.qbe into the pushButton event of the Query Customer.DB button and alter as follows. This illustration shows how the code looks after you insert the QBE file:
Illustration 5
Lines 4 and 5 declare the Query and TableView variables. Lines 8–14 contain the query that is pasted in. Only line 8 has been altered. Line 9 specifies where the answer table should be created. If you leave out line 9, the default is :PRIV:ANSWER.DB. In this case, if you delete line 9, you will get the same result. Line 16 uses executeQBE() to execute the Query variable, and line 17 displays the table that results.
You know how to execute a QBE file on disk and how to execute values stored in your code. The next step is to learn how to pass a value to a query. Often, you’ll want to enable the user to enter values and to query the table for the values entered. In effect, you simulate the query editor. Use a tilde variable whenever you want to pass a value to a query.
Suppose that you want to pass a value to a query and have the query search for that value. The next example demonstrates how to use a tilde variable to set values for a query, run the query, and display the result.
Change your working directory to Paradox’s Samples directory. Create a new form. Place an unbound field named Enter_City and labeled Enter Search String. Also, place a button labeled Query Customer Table on the form, as shown here:
Illustration 7
Before entering the code in step 3, open the CustomerQuery.qbe query you created in the previous example. Then, paste the query directly into your code and alter it by replacing the Negril value with the tilde and variable name (see the next step).
The altered pushButton event should look like the following code listing. Lines 4–6 declare the variables. Line 9 passes the value in the Enter Search String field to the s variable. Lines 11–18 are the query that you pasted. Lines 11 and 16 have been altered from the value that was pasted in. Line 20 executes the Query variable, and line 21 displays the result.
Check the syntax, save the form as QUERY2.FSL, and run it. Type a value, and click the button. The value appears. If you don’t type a value into the field, all records are displayed.
Illustration 8
Rather than bring the query editor up for the user to use (as demonstrated previously), you can simulate the query editor on a form with an interface that is more specific than File | Open | Query.
If you want, you can build a query in a string variable and then use readFromString() to read the string into a Query variable. After the query string is in a Query variable, use executeQBE() to execute it. Because you have tilde (~) variables, this technique is not really needed, but it can be useful. The following code executes the same CustomerQuery.qbe query you used in a previous example in this chapter.
Tip: Here is a tip if you are having problems with a query and you are not sure whether the problem is the query string itself or something else. Use writeQBE() to write the Query variable out to a file, and then try to run the query interactively. In addition, you can use isQueryValid(), which tells you if the query is valid. The benefit of using writeQBE() is that you can open the query and look at it.
Another debugging technique is to use the errorShow() procedure, as in the following example:
1: if not qryCustomer.executeQBE() then errorShow() endIf
Points to remember about using readFromString() are as follows:
End each line of the query with \n, which represents a line feed.
A quoted string is limited to 255 characters.
Use multiple quoted strings for quoted strings longer than 255.
Use the errorShow() procedure to check whether the query executes.
In-memory TCursors are interesting because they are fast and they are not connected to the live data. You can ask "what if" questions of the data before writing the data to the live database. The command wantInMemoryTCursor() is used when you want to specify how a TCursor is created resulting from a query. Following is the syntax:
wantInMemoryTCursor
(const yesNo Logical)
Note: The procedure wantInMemoryTCursor(Yes) is only necessary for queries that would normally produce live query views. If the query doesn’t meet the conditions for a live query, and the answer is a TCursor, the TCursor will be in memory nonetheless.
Specify Yes (or Null) to build the TCursor in-memory; the TCursor is not connected to a table. Specify No to attach the TCursor to the live data. If you do not call wantInMemoryTCursor() before you execute a query to a TCursor, the default is the same as if you called it and passed it No; the resulting TCursor is live to the data. The exception is with multitable queries that specify an answer TCursor, which will always produce an in-memory TCursor. Here is how you use wantInMemoryTCursor() with a query to gain access to an in-memory TCursor:
This command also works with SQL variables. In fact, SQL variables are very similar in nature to Query variables. You manipulate them with ObjectPAL using the same techniques. Other methods that are useful with in-memory TCursors include isInMemoryTCursor(), isView(), and instantiateView(). isInMemoryTCursor() returns True when the TCursor is an in-memory TCursor. isView() returns True if the TCursor is connected to live data. Finally, instantiateView() copies an in-memory TCursor to a physical table.
There are query methods to generate and modify queries using ObjectPAL. These methods include appendTable(), setCriteria(), setQuerySortOrder(), setCheck(), and setRowOp(). These methods are not as easy as they may seem. Here are a few pointers:
You can set a row operator to check all the fields, but if you want to check all fields but one, you cannot use checkRow() and you need to setCheck() each field.
To set the Answer table sort order, you must fill an array with every checked field; if you remove or add a field to the answer table of the query, you must fix the array and reuse setAnswerSortOrder().
setAnswerFieldOrder
() interferes with Delete, Insert and Changeto queries. It also will inhibit removeTable().
This next example uses appendTable() to add a table to a query image, checkRow() to check all the rows of the query image, executeQBE() to run the query, and ,finally, open() to open the answer table. Change your working directory to the Samples directory and type lines 3–14 into the pushButton event of a button. Figure 11-2 shows the final result.
In this chapter, you learned that a query is what you use to ask a table a question. You learned how to use sendKeys() to open up a query image for your users. You learned how to generate and use queries in ObjectPAL. You learned how to execute a saved query, and execute a query string with executeQBE(). You learned how to pass a value to a query using a tilde variable. Finally, you learned how to work with live queries, in-memory TCursors, and some of the Paradox query methods.
Paradox
Print This
Tags:
