Dec 052017
 
Ashton tate DBASE IV Tech Notes for Dec 90.
File TN9012.ZIP from The Programmer’s Corner in
Category Dbase Source Code
Ashton tate DBASE IV Tech Notes for Dec 90.
File Name File Size Zip Size Zip Type
CNVT_QBE.TXT 11755 4392 deflated
CORRIGEN.TXT 476 303 deflated
D4INDEX.TXT 61677 9897 deflated
DATERMBR.TXT 7497 2829 deflated
DIALOGUE.TXT 7401 3092 deflated
ETC.TXT 5699 2585 deflated
LUXKBMAC.TXT 7423 3378 deflated
MACROMN2.TXT 7910 3271 deflated
PORTPROG.TXT 18363 6960 deflated

Download File TN9012.ZIP Here

Contents of the CNVT_QBE.TXT file



This article is reprinted from the December 1990 edition of TechNotes/dBASE
IV. Due to the limitations of this media, certain graphic elements such as
screen shots, illustrations and some tables have been omitted. Where
possible, reference to such items has been deleted. As a result,
continuity may be compromised.

TechNotes is a monthly publication from the Ashton-Tate Software Support
Center. For subscription information, call 800-545-9364.

Converting .qbe Files
Wesley Leung

Are you still waiting for your query to finish processing? Or are you
trying to edit a "Read Only" query? Well wait and worry no more, modify or
convert the query into a regular program (*.prg) or view (*.vue) file.
Processing time will be minimized, temporary database files will no longer
be generated, and you will be able to edit all the fields. Programming
experience will not be needed, but an understanding of the dBASE commands
is strongly suggested. The amount of modification depends on how
complicated the query is.

dBASE IV creates Read Only queries to protect users from editing fields.
This is done to keep data integrity and to create sorts that are too
complicated for the code generator to understand. So after converting your
query to a program, you must be aware of what you are allowing the user to
do and how you can keep your data safe. Here are the conditions that cause
dBASE IV to create Read Only queries:

Sorting on more than one field
Descending, Dictionary Ascending, or Dictionary Descending Sort
Linking more than one database

Creating Indexes

If the query's processing time seems to take forever, the query must be
doing some type of complicated sorting. If the query is not doing one of
the first two conditions and you just want to rid yourself of the Read
Only, skip this part and continue onto the next section about "Converting a
Query to a Program/View". Although the query generator is smart enough to
detect and create an index if it is doing only one ascending sort in the
database, it cannot create complicated indexes. In addition to mutliple
field sorts are descending, and/or dictionary sorts which are indexed by
sorting to another temporary database. Copying the database(s) is why the
processing time takes so long. This is especially prevalent with slow hard
drives and with large database files. In order to alleviate the copying of
files, an index is required to replace all the sorts on the database(s).

Below is an example of what a query is doing: Master and Child databases
are linked together by a field called "Cust_No"; Master database has a
primary sort by "Lastname" and a secondary sort by "Firstname". Notice on
lines 33 and 37 are the copy and sort commands, respectively; all this
created from the simple query linking two databases that are sorted on
Lastname then Firstname.

1 * dBASE IV .QBE file 8
2 CLOSE DATABASES
3 SELECT 2
4 USE CHILD.DBF
5 QBE___CT = 1
6 DO WHILE LEN(TAG(QBE___CT)) <> 0
7 IF TAG(QBE___CT) = "CUST_NO"
8 EXIT
9 ELSE
10 QBE___CT = QBE___CT+1
11 ENDIF
12 ENDDO
13 IF LEN(TAG(QBE___CT)) = 0
14 USE CHILD.DBF EXCLUSIVE
15 INDEX ON CUST_NO TAG CUST_NO
16 ENDIF
17 RELEASE QBE___CT
18 CLOSE DATABASES
19 SELECT 1
20 USE MASTER.DBF NOUPDATE
21 USE CHILD.DBF NOUPDATE IN 2 ORDER CUST_NO
22 SET EXACT ON
23 SET FILTER TO FOUND(2)
24 SET RELATION TO A->CUST_NO INTO B
25 SET SKIP TO B
26 QBE___SAFE = SET("SAFETY")
27 QBE___CATA = SET("CATALOG")
28 SET SAFETY OFF
29 SET CATALOG OFF
30 GO TOP
31 SET FIELDS TO A->COMPANY,A->LASTNAME,A->FIRSTNAME,B->ADDRESS
32 QBE___11 = '.\'+LTRIM(STR(RAND(-1)*100000000,8))
33 COPY TO &QBE___11
34 USE &QBE___11 NOSAVE NOUPDATE
35 IF RECCOUNT() > 1
36 QBE___12 = '.\'+LTRIM(STR(RAND(-1)*100000000,8))
37 SORT TO &QBE___12 ON LASTNAME/A,FIRSTNAME/A
38 USE &QBE___12 NOSAVE NOUPDATE
39 ENDIF
40 SET SAFETY &QBE___SAFE
41 SET CATALOG &QBE___CATA
42 SET FIELDS TO
43 SET FIELDS TO COMPANY,LASTNAME,FIRSTNAME,ADDRESS
44 GO TOP

If you are using another editor, many lines of graphic characters may
appear about 30-40 lines down. Please delete these lines.

In order to link two databases, one of the databases needs to be indexed by
the linking field. So the query first checks to see if there is an index
for the "Cust_No" already created. If not, the query will create one.
Next, the query sets the relation that creates the link between the two
databases and then copies the fields specified in the view skeleton to a
temporary database (line 24). The temporary file is then sorted to another
temporary file by "Lastname" and "Firstname" (line 37).

If the query is doing a Dictionary sort, sorting on more than one data
type, or mixing ascending with descending sorts, then indexing can get
quite complicated, the concepts of which extend beyond the scope of this
article. However, there are a few helpful hints worth mentioning. For
instance, it is possible to perform a Dictionary ascending index by using
the UPPER() function. A Descending numeric is easily accomplished by
multiplying a negative 1 to the fieldname you wish to index. However,
Dictionary descending indexes are more programmatic and would require the
integration of a UDF() that analyzes each character of the field to be
indexed.

To continue on, the index must first be created in the modify
structure/organize menu option of the Control Center.

Highlight the data file in the Data section of the Control Center
Select the Modify structure/order option
Select Create index
Enter an index name
Select Expression and enter the complex index
(such as Lastname + Firstname)
If you are setting a filter and are using version 1.1, you can enter
the filter condition in the For option prompt. For example,

State = "CA"

Select Descending if you are doing a descending sort
Enter Ctrl-End to save index
Exit from the modify structure screen and return to the Control Center
Highlight your query in the Control Center
Select Modify layout
Remove all sorts from your first database
Press Alt-F to display your fields menu
Highlight Include indexes and turn it ON
Press End to go to the last field and the index you created will
appear there.
Enter Asc1 to activate an ascending index or Dsc1 if the Descending
option was selected in the Modify structure/order screen.
Press Ctrl-End to save the query

The modified query will now operate much faster because the temporary file
is no longer created. If the query was working with only one database then
our job is done. The speed will be greatly increased and the Read Only
will now disappear. However if you were linking more than one database,
then the query must be converted to either a program or a view file, to
remove the Read Only condition.

Below is a sample of the query using an index. Notice that there are no
COPY or SORT commands.

1 * dBASE IV .QBE file 8
2 CLOSE DATABASES
3 SELECT 1
4 USE MASTER.DBF
5 QBE___CT = 1
6 DO WHILE LEN(TAG(QBE___CT)) <> 0
7 IF TAG(QBE___CT) = "FULLNAME"
8 EXIT
9 ELSE
10 QBE___CT = QBE___CT+1
11 ENDIF
12 ENDDO
13 IF LEN(TAG(QBE___CT)) = 0
14 USE MASTER.DBF EXCLUSIVE
15 INDEX ON FULLNAME TAG FULLNAME
16 ENDIF
17 RELEASE QBE___CT
18 CLOSE DATABASES
19 SELECT 2
20 USE CHILD.DBF
21 QBE___CT = 1
22 DO WHILE LEN(TAG(QBE___CT)) <> 0
23 IF TAG(QBE___CT) = "CUST_NO"
24 EXIT
25 ELSE
26 QBE___CT = QBE___CT+1
27 ENDIF
28 ENDDO
29 IF LEN(TAG(QBE___CT)) = 0
30 USE CHILD.DBF EXCLUSIVE
31 INDEX ON CUST_NO TAG CUST_NO
32 ENDIF
33 RELEASE QBE___CT
34 CLOSE DATABASES
35 SELECT 1
36 USE MASTER.DBF NOUPDATE ORDER FULLNAME
37 USE CHILD.DBF NOUPDATE IN 2 ORDER CUST_NO
38 SET EXACT ON
39 SET FILTER TO FOUND(2)
40 SET RELATION TO A->CUST_NO INTO B
41 SET SKIP TO B
42 GO TOP
43 SET FIELDS TO A->COMPANY,A->LASTNAME,A->FIRSTNAME,B->ADDRESS


Converting a Query to a Program/View file

To convert the query to a program or a view file and remove the Read Only
condition, the query (*.qbe) must be converted to a program (*.prg) file
before the modifications can be made.

Create a dBASE program in the Applications panel of the Control Center
In the program editor press Ctrl-K R, a short cut key sequence to bring
your file into the program editor
Enter the name of the .qbe file. In a few moments, it will be imported
in.
Page down to the end of the file
The last or second to last command line should read "SET FIELDS TO."
If the last line doesn't say this then the query did not activate the
index correctly. If any of the lines in the program have the COPY
or SORT command, then the query is creating a temporary database file.
You must recreate the query following the instructions mentioned above
about creating indexes.
Several commands that start with USE *.DBF NOUPDATE. should be found
five to ten lines from the bottom. The * will actually be the name of
one or more file names. (see lines 36 and 37 on the previous code
listing).
Edit these commands by deleting the word NOUPDATE on each line.
Enter Ctrl-End to save the file and give a name to the file with no
extension.
The query can now be executed from the Applications menu of the Control
Center.

Congratulations! You have now just created a program that does the
same functions as the query except faster and without the Read Only
condition. You can now execute the program and then run reports, labels,
and even create new screen forms. Upon creating a quick layout form you
may find out that some of your fields are still Read Only, that is because
those fields have the same name in both databases. If you modify the
structure of one of the databases such that field names no longer match
each other, then you can create another query and screen form which will
allow you to edit those fields. In addition to removing the Read Only from
fields you can also add the Read Only condition to specific fields in the
program. To make Read Only fields you must modify the program. Go to the
bottom of the file and modify the SET FIELDS TO. command. After each of
the fields you want to make Read Only type /R . For example,

SET FIELDS TO A->COMPANY/R, A->ADDRESS, B->CITY/R.

Let say you're not crazy about having the pseudo query be executed in the
Application section because it violates your sense of symmetry or
aesthetics. Instead, you would rather have it executed from the Queries
panel. In order to accomplish this, you must convert your program to a
view file. Execute the pseudo query in the Applications panel, exit to the
dot prompt, enter CREATE VIEW filename FROM ENVIRONMENT and press the F2
key to get back into the Control Center. This creates a .vue file (in the
classic dBASE III PLUS tradition). Upon return to the Control Center, you
see this file has now been created and added to the Query section of the
Control Center.

Nearly any .qbe file can be converted in this fashion. An exception would
be when the .qbe would contain a SORT TO or COPY TO statement. These are
usually done away with when indexes are employed.



 December 5, 2017  Add comments

Leave a Reply