Category : Dbase (Clipper, FoxBase, etc) Languages Source Code
Archive   : TN9101.ZIP
Filename : ETC.TXT

This article is reprinted from the January 1991 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.

Look! It's SQL,
And other usage tips

MODIFY STRUCTURE in SQL

How do you change a column name in dBASE IV SQL? How do you eliminate
or drop a column in dBASE IV SQL? ALTER TABLE only allows the user to
add a column/field. Changing the name or definition of a column/field
or removing one is not allowed. dBASE IV will not let you MODIFY
STRUCTURE on an SQL table. Here are three examples showing how to
change the structure of an SQL table.

For simplicity a field, which is a column, is hereafter referred to as
a column.

To begin, create all data objects using scripts, in .PRS files, which
can be used later to recreate or modify these objects.

*table.prs
create table ships (boat1 char(6), boat2 char(6));
create table boats (shipdate date, shipcolor char(12));
return

*ndexxs.prs
create index shipmain on ships (boat1, boat2);
create index boatdate on boats(shipdate DESC);
return

*buildem.prs
insert into ships values ("u boat", "sub");
insert into ships values ("life", "row");
insert into ships values ("sail", "canoe");
insert into ships values ("steam", "minow");
return

One way to change the name or dimensions of a column is to UNLOAD
DATA, DROP the table, modify the script that CREATEs the table, run
the script to reCREATE the table and LOAD DATA back in. You cannot
eliminate a column using this method. To illustrate:

METHOD #1:

SQL . UNLOAD DATA TO Temp1 FROM TABLE Ships DELIMITED;
SQL . DROP TABLE Ships;
SQL . MODIFY COMMAND Table.prs

SQL . DO Table.prs && reCREATEs table
SQL . LOADE DATA FROM Temp1 INTO Ships DELIMITED;

If you do not save your scripts and want to be able to MODIFY
STRUCTURE on the fly:

METHOD #2:

. SET SQL ON
SQL . START DATABASE Rambo;
SQL . CREATE VIEW View1 (s1, s2) AS SELECT * FROM Ships;
SQL . SELECT * FROM View1 SAVE TO TEMP Temp1 KEEP;
SQL . DROP TABLE Ships;
SQL . RUN REN Temp1.dbf Ships.dbf && USE EXACT PATH IF NOTFOUND
SQL . DBDEFINE Ships;

The above method only changes names. It cannot drop a column. To
drop a column using method #2, either scale down the VIEW by SELECTing
only the columns you need when CREATEing the VIEW as in:

SQL . CREATE VIEW View1 AS SELECT Boat1 FROM Ships;
SQL . SELECT * FROM View1 SAVE TO TEMP Temp1 KEEP;
or by SELECTing only the columns you need from the full VIEW as in:
SQL . CREATE VIEW View1 AS SELECT * FROM Ships;
SQL . SELECT Boat1 FROM View1 SAVE TO TEMP Temp1 KEEP;
Neither method #1 nor method #2 used alone will both drop a column and
change the dimensions or characteristics of a column. To do this, you
must use both methods, one after the other or use method #3.
METHOD #3:

SQL . SET SQL OFF
. USE Rambo\Ships && Use exact path if necessary
* use COPY.FIELDS option if dropping column(s)
. COPY TO Temp1 DELIMITED
. COPY TO Shelltemp STRUCTURE EXTENDED
. CREATE Delet me FROM Shelltemp
. USE Delet me
. MODIFY STRUCTURE

. APPEND FROM Temp1 DELIMITED
. SET SQL ON
SQL . START DATABASE Rambo;
SQL . DROP TABLE Ships;
SQL . RUN COPY Delet me.dbf Rambo\Ships.dbf
SQL . DBDEFINE Ships;

Method #3, in addition to being able to drop, rename, and change the
definition of a column is also the most straight forward for
conventional dBASE users.

Re-CREATE INDEXES and RUNSTATS after using any of these three
methods. In methods #1 and #3, SDF may be used instead of DELIMITED
to convert columns from character to numeric.

New Printer Drivers

Two new printer drivers are now available for use with dBASE IV.
IBMLas.PR2 is to be used with IBM's newest printer model 4019 called
LaserPrinter. IBM4072.PR2 driver is to be used with IBM InkJet
printer.

IBMLAS.PR2:
This driver is designed to be used with the IBM LaserPrinter and
LaserPrinter E and supports the following features:

Box Chars. Bold Italic Underline Elite Compressed
Letter Quality
YES YES Underline YES YES YES
YES

For correct pagination, page length setting must be set properly. The
default page length for portrait and landscape mode are 64 for
Portrait and 48 for Landscape.

This driver initializes the printer to the user set defaults, so if
the printer operator panel is used to change a function, the operator
panel setting must be saved as the default prior to printing. For
example, if you wish to print in landscape mode, first change the
orientation to landscape on the operator panel and then save it as the
default by holding down the Code and Orientation keys while the Ready
light is off.

IBM4072.PR2:

This driver is designed to be used with the IBM ink jet printer model
4072, also known as the ExecJet and supports the features listed at
the top of the following page.


Box Chars. Bold Italic Underline Elite Compressed
Letter Quality
YES YES YES YES YES YES YES

For correct pagination, page length setting must be set properly. The
default page length are 66 for continuous feed paper and 64 when using
a cut sheet feeder.

SAYing and GETting

Here's a simple little program/procedure that toggles between Display
and Edit modes (in other words, toggle between @ SAYs and @ GETs).

Setting Flag = .T. will assign the initial mode to Display only. When
Flag = .F. the screen will be set to Edit mode allowing changes to the
given fields. By adding a parameter statement to the code below, you
could pass the true/false value in a called procedure to allow your
Display programs and Edit programs to use the same screen
definitions. In that case, remove the "ON KEY LABEL F5 DO Flip"
command if you want to disable the ability to toggle between display
and edit modes.


* SAYGET.PRG
Flag =
.T. && OR
.F.
mFld1 = Field 1 && This is where you
define
mFld2 = Field 2 && your memory
variables.

DO WHILE LASTKEY() <> 27 && Until Esc is
pressed.
ON KEY LABEL F5 DO Flip && Press F5 to Toggle;
mSayGet = IIF(Flag,"SAY","GET")
@ 10, 10 &mSayGet mFld1 && Say or Get the defined
@ 12, 10 &mSayGet mFld2 && memory variables.
READ
ENDDO
RETURN
*
PROCEDURE Flip
Flag = IIF(Flag, .F., .T.)
KEYBOARD CHR(29) && Ctrl-Home
RETURN

High Intensity Background Colors

dBASE IV, like most software, can display color combinations that
blink. This blinking capability can be disabled, however, allowing
color combinations that can display with bright backgrounds. The
following commands, placed in a program, will create the file
Blink.BIN, that, once loaded into dBASE, will allow you to turn the
blinking feature on and off on EGA, VGA, MCGA and SVGA systems. The
resulting .BIN file will only work on these systems, however.

SET PRINTER TO FILE Blink.BIN
??? "{11}{201}{116}{15}{139}{243}{51}{219}{128}"
??? "{60}{70}{116}{1}{67}{184}{3}{16}{205}{16}{203}"
SET PRINTER TO
RETURN

After running this program, issue the command LOAD Blink. To turn
blinking off and activate the high intensity background, issue the
command CALL Blink WITH .F. To deactivate the high intensity
background colors issue the command CALL Blink WITH .T.
Accessing .DBF and .QBE Files in Template Language

Schema.def is now available on the Ashton-Tate BBS, CompuServe and
GEnie. Schema.def is simply a text file which contains definitions
which allow users to access DBF and QBE files via the dBASE Template
Language (DTL). Schema.def is most often used in connection with the
built-in DTL function IMPORT(). The IMPORT() function converts DBFs
into a special format which is readable by the DTL when used with the
Schema.def file.

The IMPORT() function accepts three arguments:
1. the name of the input object (a .DBF file, for example)
2. the name of the output object
3. the type of the input object (1 = QBE, 2 = UPD, 3 = DBF, 4
= CAT)
Below is some example code which better demonstrates IMPORT() and the
use of Schema.DEF.

Schema.COD
//start of code

This code will read the file CLIENT.DBF from the dBASE IV samples and
output the field names to CLIENT.OUT
{
include "schema.def"
include "builtin.def"

if not import("CLIENT.DBF","CLIENT.DTO",3) then
pause("ERROR: Unable to import CLIENT.DBF")
return 1
endif

if not newframe("CLIENT.DTO") then
pause("ERROR: Unable to load CLIENT.DTO")
return 2
endif

foreach field element x
print(field name+chr(13)+chr(10))
next x

return 0
}
// end of code
Copy the CLIENT.DBF, CLIENT.MDX, and CLIENT.DBT files from the dBASE
samples directory. To compile the code (from DOS) type:
DTC -ischema.cod
You should not get any errors. To run the code type:
DGEN -tschema.gen
If everything went ok you should now have a file called CLIENT.OUT
which lists
the field names in CLIENT.DBF.