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

1 UDF Library

UDF LIBRARY
Always Room for Improvement

The selection of UDFs this month starts off with a "new and improved"
concept

Quite a while ago, we published a tip for figuring out the width of a
window. Here's a new and improved way to accomplish the same result.
Call MaxCol() while inside the active window.

Stretching Onscreen

Having the ability to vertically stretch a long character field in a
report is a nice option, but what if you wanted to have the same
effect on output to your screen? The ? has a picture function
(FUNCTION "V") to do this but the problem with it is that it uses
physical spaces to perform the alignment. This means if there is
something already on the screen and you want to see something
vertically stretched to the right of it, the existing text will be
written over with spaces. Here's a UDF that gets around that problem
by defining and activating a window in which to do the vertical
stretching. This insulates it from the rest of the screen and
prevents it from overwriting any existing information on the screen.
You can use the ?? command to call it:

?? Vstretch(Longfld, 5, 5, 10, 74 )

This would stretch the character string Longfld using a window whose
coordinates are from 5, 5 to 10, 74. The original cursor postion is
restored by the UDF. Unfortunately, this UDF cannot be called
directly by the @.SAY command.
A faster alternative to the VStrectch() function is to use a small
window and the @.SAY command to display the long text. VStrectch2()
demonstrates this technique.

There are two potential problems. One is that your window must be
long and wide enough to display all the information or characters will
scroll out of the window and no longer be visible. Making your window
longer or wider will remedy this problem. The other problem is that
this method makes no attempt to preserve whole words. It simply
splits them up where they meet the border of the window. If you don't
mind these two breaches of aesthetics, VStrectch2() pays off by
performing the task more rapidly.

What Week Is It?

The WoY() UDF tells you what week of the year a specified date falls
on. It starts counting from the first Sunday of the year of the date
that it is given as an argument and each Sunday thereafter is a new
week. So January 1 through January 5, 1991 returns 1, and January 6
through January 12, 1991 return 2, and so on.

Executing Commands In Other Work Areas

There are a lot of commands that can be used not only on the database
currently in use, but on databases in other work areas as well. For
example, while using a database in work area 1, you can perform
commands like

SKIP IN 2
* SKIP 1 Record in the database in work area 2
USE ? IN 3
* USE a database (selected from a popup list)
* in work area 3

but cannot perform either of the following:

APPEND BLANK IN 2
PACK IN 3

With the UDF CommIn(), you can execute any command on a database in
another work area that would work on the database in the currently
selected work area. Some examples of the syntax for the above
commands would be

. ? CommIn("APPEND BLANK", 2)
. ? CommIn("PACK", 3)
*Ä mvar = CommIn(, )

The following commands are equivalent:

USE Myfile IN 2 ALIAS MF
mvar = CommIn("USE Myfile ALIAS MF", 2)

The former command is preferable because it is built into the dBASE IV
code. This UDF is for those commands that do not accept the [IN area number>] as an optional clause. Although it is acceptable to use

. mvar = Commin("? RECCOUNT()", "MF")

to display the total number of records for 'Myfile' (alias, MF, which
is in work area 2), remember that there is a dBASE IV function that
will do the same thing:

. ? RECCOUNT("MF") && or ? RECCOUNT(2)

This UDF simply stores the name and work area of the database you are
using, selects another work area, performs the command, and puts you
back where you started. If an error occurs, it will return a value of
.F., otherwise (completes without error), .T.

How to Test for a Read-Only File

It is sometimes necessary to test to see if a database file is
read-only or if it is USEd NOUPDATE. Basically, the way it works, is
to trap for error number 111: "Cannot write to a read-only file". The
database is tested in two differently depending on the number of
records. If testing for errors when APPENDing blank records in a
large database, it would take a while to DELETE and PACK it. If a
DELETE is performed on an empty database, it would not return an
error, even if the database is USEd NOUPDATE.
The PARAMETER passed is the error condition set by ON ERROR. This
line may be removed or commented out along with the next to last line
(ON ERROR &merr cond). If you have pre-existing ON ERROR conditions,
it will be necessary for you to save them prior to executing the
function. The syntax is :

mvar = ISRO("DO Err Proc WITH ERROR()")

This will store the logical (.T. or .F.) result to mvar, and set the
ON ERROR condition to be

ON ERROR DO Err Proc WITH ERROR()

With the two lines removed, the syntax would be :

mvar = ISRO()

One Page at a Time

Many times a programmer wants to provide a means for those who use
their programs to view the contents of a memo without giving the user
access to the memo. Currently, the only way to see the contents of a
long memo is to let them into the memo editor on the "honor system"
and hope that nothing is inadvertently changed. Although you can use
the NOEDIT option in a BROWSE or EDIT operation, it's simply not a
controlled means of accessing the memo.

If you've been in this pinch, MemoPagr() is just for you. It allows
you to define a box in which you can display the contents of the memo,
a page at a time, allowing you to page up and down through the memo.
It displays an arrow in the border of the displayed box to indicate if
there is more text above, below, or above and below the current page.
Although it doesn't let you scroll a single line at a time, it
responds to the the up and down cursor keys like the corresponding
PageUp and PageDn keys. The parameters are:

MemoPagr(,,
,,)

The memo field name is passed as a string and all the other arguments
are numeric.

A box is drawn using the specified coordinates and the contents of the
specified memo field are displayed within the box using pre-selected
colors. You should use coordinates that make the box 67 characters
wide (the inside of the box would end up being 65 characters wide), as
the memo editor wraps at 65 and this would make the memo look like
what it did when it was typed in.

The intended method of using this UDF is to put it in the WHEN clause
of an @.GET on a memo field. For example:

@ 5,5 SAY "Notes: " GET Memofld ;
WHEN MemoPagr("Memofld", 5, 6, 17, 73)

The function always returns .F., not permitting the memo to be
edited. You could add a key trap for a specific key that allows the
user to actually edit the memo. You would do this by having the trap
for that key RETURN .T. to the GET.

Note

A WHEN clause should not be confused with an Accept value when or
Permit Edit IF. A SET PROCEDURE TO command must be issued before
editing the format screen and Esc must be pressed before Ctrl-End in
order to save it.

You may want to add additional parameters like what type of border to
use, what colors to use, and maybe a permanent message to be displayed
below the box or on the message line. This function could be used for
displaying help information stored in a memo field in a help database.


MaxCol
FUNCTION MaxCol
* by Martin Leon.
* Determines the maximum column in an active window.

PRIVATE ln_Col
SET CURSOR OFF
ln_Col = 0
@ 0,ln_Col SAY " "
DO WHILE ROW() = 0
ln_Col = ln_Col + 1
?? " "
ENDDO
SET CURSOR ON
RETURN (ln_Col)


FUNCTION VStretch
* by Martin Leon.

PARAMETER lFld, begrow, begcol, endrow, endcol
mWinWidth = LTRIM( STR( (EndCol - begCol) - 1, 2 ) )
DEFINE WINDOW X FROM begrow, begcol TO endrow, endcol NONE
ACTIVATE WINDOW X
CLEAR
?? lfld PICTURE "@V" + mWinWidth AT 0
SAVE SCREEN TO TmpScrn
ACTIVATE SCREEN
RELEASE WINDOW X
RESTORE SCREEN FROM TmpScrn
RELEASE SCREEN TmpScrn
RETURN ""

VStetch2
FUNCTION VStretch2
* by Martin Leon.

PARAMETER lFld, begrow, begcol, endrow, endcol
DEFINE WINDOW X FROM begrow, begcol TO endrow, endcol NONE
ACTIVATE WIND X
CLEAR
@ 0,0 SAY lFld
SAVE SCREEN TO TmpScrn
ACTIVATE SCREEN
RELEASE WINDOW X
RESTORE SCREEN FROM TmpScrn
RELEASE SCREEN TmpScrn
RETURN ""

FUNCTION WOY
* by Martin Leon.

PARAMETER mDateval
mYear = YEAR( mDateval )
mYearBeg = CTOD( "01/01/"+str(myear,4) )
mYearBeg = mYearBeg - DOW( mYearBeg ) + 1
mDiff = mDateval - mYearBeg
RETURN INT( mDiff / 7 ) + 1


FUNCTION CommIn
*Ä Author : Adam L. Menkes.
*Ä Will not work RUNTIME.

PARAMETERS mCommand, mArea
ON ERROR mErrorNum = ERROR()
mErrorNum = 0
mAlias = ALIAS()
SELECT (mArea)
&mCommand
SELECT (mAlias)
ON ERROR
RETURN (mErrorNum = 0)

IsRO
FUNCTION IsRO
*Ä Adam L. Menkes.
*Ä Will only function properly if DBTRAP is OFF

PARAMETERS merr_cond
isro = .F.
merror_no = 0
ON ERROR merror_no = ERROR()

IF "" <> DBF()
IF RECCOUNT() > 0
mskip_amt = IIF(BOF(), 1, IIF(EOF(), - 1, 0))
SKIP mskip_amt
DELETE
isro = (merror_no = 111)
RECALL
SKIP - mskip_amt
*Ä Skips back (If 1, -1, if -1, 1, if 0, -0 which is 0)
ELSE
APPEND BLANK

IF merror_no = 111
isro = .T.
ELSE
msafety = SET("SAFETY")
SET SAFETY OFF
ZAP
SET SAFETY &msafety
ENDIF

ENDIF

ENDIF

ON ERROR &merr_cond
RETURN isro

MemoPagr
FUNCTION MemoPagr
* by Martin Leon.

PARAMETER mFldName, mBegRow, mBegCol, mEndRow, mEndCol
SET MEMOWIDTH TO mEndCol - mBegCol - 1
mCursor = SET( "CURSOR" )
SET CURSOR OFF
mEsc = 27
mPgDn = 3
mPgUp = 18
mUp = 5
mDn = 24
mNumLines = MEMLINES(&mFldName)
mLines = mEndRow - mBegRow - 1
SAVE SCREEN TO Tmpscrn

@ mBegRow+1, mBegCol+1 CLEAR TO mEndRow+1, mEndCol+1
@ mBegRow+1, mBegCol+1 FILL TO mEndRow+1, mEndCol+1 COLOR B/N
@ mBegRow+1, mBegCol+1 FILL TO mEndRow-1, mEndCol-1 COLOR RG+/B
@ mBegRow, mBegCol TO mEndRow, mEndCol DOUBLE COLOR RG+/B

IF mNumLines = 0
@ mBegRow + 1, mBegCol + 1 SAY ;
"Blank Memo. Press any key to continue..." COLOR RG+/B
mKey = INKEY(0)
RESTORE SCREEN FROM TmpScrn
RELEASE SCREEN TmpScrn
SET CURSOR &mCursor
RETURN .F.
ENDIF

mAtLine = 1
mAtRow = 1
DO WHILE mAtLine <= mNumLines
* Show one window full
DO WHILE mAtRow <= mLines .AND. mAtLine <= mNumLines
@ mBegRow+mAtRow, mBegCol + 1 SAY ;
MLINE( &mFldName, mAtLine ) COLOR RG+/B
mAtLine = mAtLine + 1
mAtRow = mAtRow + 1
ENDDO

* If at last line of memo...
IF mAtLine > mNumLines
* If memo is shorter than one page, put box character in
* bottom left corner of box, otherwise, put an up arrow
* symbol there.
@ mEndRow - 1, mEndCol SAY ;
IIF( mNumLines <= mLines, CHR(186), CHR(24)) COLOR W+/B

DO WHILE .T.
mKey = INKEY(0)
* If memo is shorter than one page, only allow Esc key
IF mNumLines <= mLines
IF mKey = mEsc
EXIT
ENDIF
* Otherwise, allow Esc or PgUp keys
ELSE
IF mKey = mEsc .OR. mKey = mPgUp .OR. mKey = mUp
EXIT
ENDIF
ENDIF
?? CHR(7)
ENDDO

IF mKey = mEsc
RESTORE SCREEN FROM TmpScrn
RELEASE SCREEN TmpScrn
SET CURSOR &mCursor
RETURN .F.
ENDIF
@ mBegRow+1, mBegCol+1 CLEAR TO mEndRow-1, mEndCol-1
@ mBegRow+1, mBegCol+1 FILL TO mEndRow-1, mEndCol-1 ;
COLOR RG+/B
mAtLine = mAtLine - mAtRow - mLines + 1
mAtLine = IIF( mAtLine < 1, 1, mAtLine )
mAtRow = 1
LOOP
ENDIF

* Not at end of memo yet...
* If on first page, show down arrow only, otherwise show
* up/down arrow on border of box.
@ mEndRow - 1, mEndCol SAY ;
IIF( mAtLine - mLines = 1, CHR(25), CHR(18)) COLOR W+/B

DO WHILE .T.
mKey = INKEY(0)
* If this is the first page of the memo on screen...
IF mAtLine - mLines = 1
* Only honor PgDn, up cursor, and Esc keys
IF mKey = mPgDn .OR. mKey = mDn .OR. mKey = mEsc
EXIT
ENDIF
* otherwise honor PgUp and up cursor as well key as well
ELSE
IF mKey = mPgUp .OR. mKey = mUp .OR. mKey = mPgDn .OR. ;
mKey = mDn .OR. mKey = mEsc
EXIT
ENDIF
ENDIF
?? CHR(7)
ENDDO

DO CASE
CASE mKey = mEsc
RESTORE SCREEN FROM TmpScrn
RELEASE SCREEN TmpScrn
SET CURSOR &mCursor
RETURN .F.

CASE mKey = mPgUp .OR. mKey = mUp
@ mBegRow+1, mBegCol+1 CLEAR TO mEndRow-1, mEndCol-1
@ mBegRow+1, mBegCol+1 FILL TO mEndRow-1, mEndCol-1 ;
COLOR RG+/B
mAtLine = (mAtLine - (2 * mLines))
mAtLine = IIF( mAtLine < 1, 1, mAtLine )
mAtRow = 1
LOOP

CASE mKey = mPgDn .OR. mKey = mDn
@ mBegRow+1, mBegCol+1 CLEAR TO mEndRow-1, mEndCol-1
@ mBegRow+1, mBegCol+1 FILL TO mEndRow-1, mEndCol-1 ;
COLOR RG+/B
mAtRow = 1
LOOP
ENDCASE
ENDDO

RETURN .F.



2 Scrollable Text

A Scrollable Text File in a Popup Window
Joe Stolz

This variation on the "try before you buy" concept is unique and
ingenious enough to be very useful for custom previewing of printed
reports

I was recently approached by a customer with an interesting
requirement. The application described to me produces a custom report
that is dependent on a number of user supplied factors. He wanted to
offer the user the opportunity to view the report results prior to
printing. The twist is that the programmer wants to incorporate the
ability to scroll the report up and down, giving greater
scroll-ability than solutions that have been proposed previously that
extend the "View report to screen" capability to any report outside of
the Control Center. To that end, the customer was saving the report
to a file on disk.

The first question I was asked was, "Can you have a read-only session
of MODIFY COMMAND to view the file but to disallow editing?" I
thought this was an ingenious although somewhat odd angle. But this
is not possible with MODIFY COMMAND. Instead, I suggested the
possibility of placing the text into a popup. A popup offers several
excellent benefits: scroll-ability, page-up/page-down capability, it
displays in a window that will clear itself from memory and the screen
when done, and most importantly, it's easy to program. On the down
side, it can potentially eat up a lot of memory. I found that the
popup I tested at the dot prompt could only contain 600 or so lines of
text (I used the README.DOC file in dBASE IV version 1.1 and was able
to display 12 pages). Run from an application, the amount of pages
would be less but probably sufficient for many applications.

The programming is a piece of cake! As a note for programmers, I am
loading the text file into a database (Text.DBF) that has a single
field called Line which has a width of 80 characters. I am declaring
a popup that has a width of about 75 and I am defining the text of
each bar as the contents of the current record in Text.DBF. For 600
lines there is a small time lag as the popup is created which will
vary depending upon the processing speed of your computer. When you
are done viewing the popup you simply press Esc and the whole thing
clears from the memory. That is to say that I have not assigned an
action to this popup.

An unusual benefit of the popup is the ability to press a key that
represents the first non-blank character on a line and to have the
highlighted bar jump to that line. If your text file has line numbers
this can be quite useful. Also the Home key takes you to the top of
the document, the End key takes you to the end and PgUp at the top of
the file also takes you to the end of the document.

If you so desire, you can change the COLOR OF HIGHLIGHT to the COLOR
OF MESSAGE to hide the popup bar. It may be preferable to show the
popup bar because it gives a clearer indication of which line is the
current line.

An alternative method would be to create a window and to list a
particular set of lines of the text file into the window, then to set
up an INKEY() routine to trap keys like PgUp and PgDn. This solution
is not quiet as elegant, but is not memory intensive and therefore
more independent of text file size. I've included this alternative
code for those who like a choice.

SET TALK OFF
SET STATUS ON

USE TEXT
ZAP
APPEND FROM Read.DOC SDF
* 5-600 line maximum for the text file,
SET COLOR OF MESSAGE TO N+/BG
SET COLOR OF HIGHLIGHT TO BG/N+
DEFINE POPUP TEXTER FROM 1,1 TO 21,75

bar_num=1
SCAN
DEFINE BAR bar num OF TEXTER PROMPT LINE
bar_num = bar_num + 1
ENDSCAN

ACTIVATE POPUP TEXTER

DO Colors && resets colors to defaults
CLEAR ALL
* Ä EOP: TEXTREAD.PRG

* W_TEXTRD.PRG
* This program allows for an adjustable view window size
* Text file is kept in the database TEXT.DBF in a field LINE.
* You could easily adapt this as a procedure where the view window
* and/or text file name are passed as parameters.

SET TALK OFF
SET STATUS OFF

USE TEXT
ZAP
APPEND FROM READ.DOC SDF
GO TOP

SET COLOR OF NORMAL TO N+/BG
set color of message to W+/B

* Ä Window size determined here
w_top = 1
w_left = 2
w_bot = 20
w_right = 77

DEFINE WINDOW Textscrl FROM w top, w left TO w bot, w right
* Ä set system memvars to known quantities
_plineno = 0 && current line in window
_plength = (w_bot - w_top -2) && window size adjustable
_padvance = "linefeeds"
_pageno = 1

ACTIVATE WINDOW Textscrl

DO WHILE LASTKEY() # 27
SCAN WHILE _plineno < _plength-1 && paint a single
screen
?? SUBSTR(LINE,1,w_right - w_left - 2) && adjusted for window
width
?
ENDSCAN

k_action = INKEY(0)
DO CASE
CASE LASTKEY() = 18 .AND. _pageno # 1 && PgUp
SKIP - ((w_bot - w_top -2)) * 2
* Ä SKIP up two pages, the one we're on and the previous one
_pageno = _pageno - 1
EJECT PAGE && will do a linefeed
CASE LASTKEY() = 27
EXIT
OTHERWISE && any other keystroke advances one page
SKIP
EJECT PAGE
ENDCASE
ENDDO
DEACTIVATE WINDOW Textscrl

DO Colors && reset colors to their defaults
CLEAR ALL
SET STATUS ON
* Ä EOP: W TEXTRD.PRG

PROCEDURE Colors
SET COLOR OF NORMAL TO W+/B
SET COLOR OF HIGHLIGHT TO GR+/BG
SET COLOR OF MESSAGES TO W/N
SET COLOR OF TITLES TO W/B
SET COLOR OF BOX TO BR/R
SET COLOR OF INFORMATION TO B/W
SET COLOR OF FIELDS TO N/BG
RETURN



3 Recrusion

I Can Code It For You Wholesale!
Adam L. Menkes

No need for lots of redundancy where logic is concerned

What is recursion? Recursion is when a PROCEDURE or FUNCTION can call
itself, passing the results from one pass as a parameter to the next,
until some condition is met, causing the perpetual loop to terminate.
Given the UDFs rFact() and Fact() Ä listed on page 5 Ä which determine
the FACTORIAL of a number), take a look at the code and see if you
notice any differences.

What is a Factorial?

The factorial of a number is the product of all the numbers up to and
including the number. For example, the factorial of 6 (denoted by 6!)
is

6 * 5 * 4 * 3 * 2 * 1
6! = 720
3! = 6
0! = 1

Factorials are not calculated for negative numbers.

You may have noticed a few obvious differences. For instance, rFact()
uses an IF...ELSE...ENDIF construct while FACT() uses DO WHILE...ENDDO
or that RFACT() RETURNs mResult while FACT() RETURNs mResult /
mNumber. Both contain the same conditional test: while the number is
greater than zero (> 0). Both return the correct answer.

What may not have been obvious is that line 4 of rFact() calls another
UDF. This other UDF is really itself, passing the value of (mNumber -
1) to itself as the PARAMETER mNumber. This may seem confusing
because, well, it is. This line will keep executing until mNumber =
0. The values of the previous calls are kept in memory as hidden
variables.

If you step through rFact() using DEBUG and then SUSPEND at the point
where mNumber is 0, then perform a DISPLAY MEMORY, you will see:

mNumber priv N 0
mNumber (hid) N 1
mNumber (hid) N 2
mNumber (hid) N 3
mNumber (hid) N 4
...

The variable mResult will not yet be defined. Continuing the DEBUG
session, (DISPLAYing values of mNumber and mResult) you will see the
values changing correctly but only showing the last 2 lines executing
(ENDIF, RETURN mResult). The values are actually being passed back to
line 4 (mResult = .) such that the first mResult returned is the value
1, then 2, 6, and finally 24. This occurs as (line 4) mResult =
mNumber (1, not 0) * (the RETURNed value) 1, then mResult = mNumber
(2, was hidden) * (the RETURNed value) 1 = (2 * 1) = 2 which gets
RETURNed to the previous call where mResult = mNumber (3, was hidden)
* 2 (the RETURNed value) = 6 which becomes the value for the last call
where 6 * 4 = 24 and we have our answer! Confused? You should be.
Recursion is not easy to follow, as results depend on which level of
the UDF call you are in.

Although dBASE IV allows recursive calls, use of them is not
recommended because of the difficulty in programming and debugging.
The memory stack can get filled, causing an abrupt program termination
usually with the error message "PROCEDUREs/FUNCTIONs nested too deep",
an error that can not be trapped with an ON ERROR routine.
One common cause of this error is from recursion due to poor
programming style. An example of this is shown with two example
programs, MAIN and ProgA:

* PROGRAM MAIN
...

...
* Display menu choices
...
* Check item selected from menu
DO CASE
CASE choice = "A"
DO ProgA
CASE choice = "B"
DO Something Else
...
ENDCASE
...
RETURN
* END OF PROGRAM MAIN

* PROGRAM (PROCEDURE) ProgA
...

...
DO MAIN && This is the culprit!
* END OF PROGRAM (PROCEDURE) ProgA

Although this will work (for a little while anyway), it is not
recommended to call another PROGRAM, PROCEDURE, or FUNCTION if it was
the calling program. Where does this occur? The last line of ProgA
is: DO MAIN. ProgA was called from MAIN. Better programming style
would be to replace this line with a simple RETURN. This will cause
the flow of control of the program to return to MAIN at the line
immediately following 'DO ProgA'. Without the RETURN in ProgA, every
time choice 'A' was selected, your program would nest deeper and
deeper, possibly altering the true values of some of your memory
variables (they may now be 'hidden' with the correct values).
Eventually, however, you may crash the program.

RETURN is an effective way to finish processing the PROCEDURE from
which it was called at the line from which it was called. If control
needs to be passed to the same line, RETRY is used (but use caution as
this can put you in an endless loop if used improperly). If you need
to go to a specific calling program (several levels up) you can use
the command RETURN TO or to the main program, RETURN TO
MASTER. Again, these should be used sparingly because with proper
programming, the natural flow of control using RETURN statements will
get you back to where you need to be. For example, let's say program
MAIN calls ProgA, which calls ProgB and so on to ProgY which calls
ProgZ. When finished executing the code in ProgZ, we want to be back
at MAIN to GET another variable before executing programs ProgA
through ProgZ. MAIN could look like one of the two following examples:

Example 1

* PROGRAM MAIN
DO WHILE .T.
...
@ row, col get mVar
READ

DO ProgA
DO ProgB
...
DO ProgZ
...
ENDDO
RETURN
* END OF PROGRAM MAIN

* PROGRAM ProgA
...
RETURN
* END OF PROGRAM ProgA

* PROGRAM ProgB
...
RETURN
* END OF PROGRAM ProgB

* PROGRAM ProgZ
...
RETURN
* END OF PROGRAM ProgZ
Example 2

* PROGRAM MAIN
DO WHILE .T.
...
@ row, col GET mVar
READ

DO ProgA
...
ENDDO
RETURN
* END OF PROGRAM MAIN

* PROGRAM ProgA
...
DO ProgB
RETURN
* END OF PROGRAM ProgA

* PROGRAM ProgB
...
DO ProgC
RETURN
* END OF PROGRAM ProgB

* PROGRAM ProgZ
...
RETURN
* END OF PROGRAM ProgZ

In Example 1, as each program executes, it RETURNs back to MAIN and
executes the next line of code. In example 2, although this is
over-simplified, what occurs is that each program executes until ProgZ
finishes, then returns control to the next line in ProgY (which is
RETURN) which gives control to the next line in ProgX (RETURN) which
gives control to the next line in ProgA (RETURN) which RETURNs to the
next line in MAIN. Using a TREE-like diagram, Example 1 would appear
like

MAIN
--- ProgA
--- ProgB
...
--- ProgZ

whereas Example 2 would be more like

MAIN
-- ProgA
--- ProgB
---...
...
--- ProgZ

If each program was going to execute no matter what, than either
programming method is as good as the other (although example 1 is
certainly clearer and easier to follow). But, suppose around ProgM a
condition is not satisfied, and you want to go back to MAIN. With
example 1, if you RETURN or RETURN TO MASTER or RETURN TO Main, you
will still execute the next line in main which is DO ProgN. With
example 2, you will RETURN to the line following DO ProgA, which is
your next series of commands (...) without executing programs ProgN
through ProgZ.

* PROGRAM ProgM
...
IF ()
RETURN
ENDIF
DO ProgN
RETURN
* END OF PROGRAM ProgM

or better yet:

* PROGRAM ProgM
...
IF ()
DO ProgN
ENDIF
RETURN
* END OF PROGRAM ProgM

Most recursion can be avoided by using these principles of writing
your programs. Use loops (DO WHILE...ENDDO) instead of recursive
FUNCTION calls and RETURN instead of DO to avoid recursive
PROCEDURE calls.

Note to programmers:

Off hand, I can not think of any situation where only recursion can be
used for the desired results. Any suggestions would be greatly
appreciated.

RFact()
FUNCTION rFact
PARAMETER mNumber
IF mNumber > 0
mResult = mNumber * rFact(mNumber - 1)
ELSE
mResult = 1
ENDIF
RETURN mResult

Fact()
FUNCTION Fact
PARAMETER mNumber
mLoop = mNumber
mResult = mNumber
DO WHILE mLoop > 0
mResult = mResult * mLoop
mLoop = mLoop - 1
ENDDO
RETURN mResult / mNumber



4 Q&A

Q & A
The Hierarchy of Design Files

I recently noticed that when working with a format file and modifying
the .FMT file, that my modifications aren't reflected in the Screen
designer. Should I be updating the .SCR file along with the .FMT
file, and, if so, how is it done?

The design surfaces each have their own "design file" that stores the
information you see and modify on the screen. Then a generation
process takes place and creates source code, which is compiled into
object code when you run it:

Object type Design File Source code Object code

Program (homemade) None PRG DBO
Report form FRM FRG FRO
Screen form SCR FMT FMO
Label form LBL LBG LBO
Query QBE QBE (yes, QBE) QBO
Update query UPD UPD (yes, UPD) DBO (yes, DBO)

When you modify the .FMT directly, or any other source file generated
by one of the design surfaces, it is out of sync with the design
file. There is no way to make the design file sync up with the source
file. You can only have the design file update/ overwrite the source
file.

MainFrame Imports and Nulls

I'm having trouble importing a certain text file that I get from our
mainframe computer. The problem is that when I try to APPEND the data
into dBASE IV it won't fill in all the fields. It stops at a certain
field where the data from the mainframe indicates there should be a
blank field.

The problem is probably that your mainframe puts a null character
(ASCII character 0) in the place of that field for the records with
blank or 0 values. dBASE IV considers that it has reached the end of
the record when it reads the null and skips the remaining fields.
There are a few utilities available on our BBS (such as NULLOUT.ZIP,
CHARCHNG.ZIP and NULLSTRIP.PRG) that will allow you to convert those
null characters into different characters, such as spaces (ASCII 32
decimal). After you convert those characters, you will be able to
successfully import the file.

Being Too Descriptive

In a program I have written, I create a temporary database from a

STRUCTURE EXTENDED file. Whenever I do this dBASE IV asks me to edit
the file description. This also happens when I COPY STRUCTURE TO or
COPY TO a database that doesn't already exist. How do I turn this
off?

This indicates that you have both SET CATALOG ON and SET TITLE ON.
Setting either one off will prevent dBASE IV from asking you for a
description for the file.

Appending Deleted Records

Is there a way to APPEND records from one file to another on the basis
of the deleted status? I have tried APPEND FROM FOR DELETED()
but that doesn't seem to work.

As noted in Language Reference, the problem is that when you APPEND a
record, its DELETED flag gets turned off before checking to see if
DELETED() returns .T., so dBASE IV accepts the record. As a
workaround, you could use a temporary database and copy only the
deleted records to that one first and then append to the other
database:

SET DELETED OFF
USE Source
COPY TO Temp FOR DELETED()
USE Destiny
APPEND FROM Temp

Mating Data Types in an Index

How can I index a file with two fields that have different data
types? dBASE IV doesn't let you create a tag with different data
types. Is there a work around?

This is as designed. When combining data types in any type of
expression, you always have to do some sort of conversion so that the
data can be combined. There are plenty of conversion functions in
dBASE IV to allow you to combine data types into an index expression
or any other expression.

To combine date types and character types, use the DTOS() function on
the date value. To combine numerics and characters, use the STR()
function. Finally, if it's necessary to incorporate a logical field
into an index expression, the IIF() function can be used to return
opposing string values such as "T", "F", "1" or "0".

Hyperdisk Mystery

When I exit from dBASE IV lately, I get a message that the Hyperdisk
uninstall failed due to Error messages:"Vector in use by another
program.";"Vector in use by another program." I don't have a clue as
to what causes this.

A lot of the newer systems are coming with a hard disk cache built in
to the system, and if that's the case, you shouldn't use the Hyperdisk
cache. You may be able to get rid of the error message by loading
DBCINIT in your AUTOEXEC.BAT, but if your system already has a cache
running, it's not a good idea to load the Hyperdisk cache as well. To
turn of the dBASE IV cache, go to your dBASE IV directory while in DOS
and type:

CACHEDB OFF

No Blueprints in the Code File

Can you recover an .FRM from an .FRG given that the .FRG was created
through the Control Center and is operational? My .FRM file was
corrupted and I don't want to have to spend hours reconstructing it in
order to modify.

Sorry, but you can't. The .FRG is simply dBASE IV source code. The
.FRM is a binary file that gets interpreted by the REPORT.GEN
template. There isn't any mechanism for converting source code into
an .FRM file. Always keep reliable backups of your design files so
things like this don't smart so much. See page 6 for design/source
relationships.

MailMerge Pagination Problems

I'm running an application through the Control Center to print a mail
merge letter. I have all the bands except the Detail band turned off
and the New Page option in the Print menu set to None. I get a blank
page AFTER every letter.

This is the default behavior for a Mailmerge layout. To change this,
put your cursor in the Detail band, open the Bands menu and change the
Begin band on new page option to NO.

Forms to Reports

Is there a way for me to copy the layout of my format screen to a
report layout?

No, not directly. There is a way to achieve the same result though.
Make a copy of your .FMT file into a .PRG file. Modify this .PRG by
deleting all the lines before and after the @...SAY @...GET commands
and change all the "GET" references into the word "SAY". Then add a
SCAN..ENDSCAN loop to cycle through the records, and add a SET DEVICE
TO PRINTER to get the @..SAY commands to be redirected to the printer
instead of to the screen. The end result would be a program that
looks something like this:

SET DEVICE TO PRINTER
SCAN
@ 5, 5 SAY "Last Name: "
@ 5, 16 SAY Lastname
ENDSCAN
SET DEVICE TO SCREEN

Number Blackout

Having gotten rid of the license notice in the little box when RunTime
loads by using the /T switch, does anybody know how to blank out the
"NUM" that flashes on line 0 as RunTime is loading?

To get rid of the flashing "Num", put

SCOREBOARD = OFF

in your CONFIG.DB.

One Screen at a Time

Is there any way to run a report from the Applications Generator that
will allow the user to see it one screen at a time?

Yes, instead of choosing the action "Display or print" for that item,
make it "Run program: Insert dBASE Code" and insert the following
code:

_pwait = .T.
_plength = 23
SET PRINTER TO Nul
REPORT FORM TO PRINTER
SET PRINTER TO

Individual Protection

I have a need to make only the first field in my BROWSE field a
non-editable field because fast typists tend to "overrun" the field
when they're doing data entry. This results in a spill over into a
field which is also a key for an index. If they accidentally modify
that field, the record jumps to its new location and they lose their
place in the file.

The simplest way is to use the /R switch of the FIELDS clause of the
BROWSE command. You can individually protect fields from being edited
this way. For example, if you want to be able to see that fields
LASTNAME and FIRSTNAME, but only allow editing of the FIRSTNAME field,
issue the following command:

BROWSE FIELDS Lastname /R, Firstname

You could also create a query and add all fields to the View
skeleton. Then, go to the View skeleton and give the fields you want
to protect an alternate name by just typing in a new name above the
field marker. s



5 PROTECT and SQL Server

PROTECT and SQL Server
Jeff McCrimon

The rules and requirements of PROTECT change when you enter
the realm of Structured Query Language, but for the better

SQL and Protect;PROTECT and SQL;PROTECT has been around since the
release of dBASE III PLUS and was introduced in dBASE III PLUS as a
way to implement data security for users. In dBASE III PLUS, PROTECT
was actually a separate utility that had to be loaded from the DOS
prompt, outside of the dBASE environment. In dBASE IV, PROTECT is now
implemented as an internal command, so now it can be invoked from the
dot prompt or the Control Center.

PROTECT offers three levels of security. The first level is
login-security which prevents unauthorized users from starting dBASE
unless they sign in through a login screen. The only setup that is
required to implement this level of security is by defining the users
that are allowed access to dBASE IV. The second level of security is
that of database file level security which allows users in a group
particular privileges to a database file depending on their access
levels. These privileges are read, update, delete, and extend which
apply to the database file level. The privileges at the field level
are read-only, full, and no access. The third level of security is
data encryption which prevents unauthorized users from reading the
contents of a database file by scrambling the data through a
sophisticated encryption scheme. Encryption provides good security
because dBASE IV normally stores the fields in a database file in
ASCII form which means that anyone with just a text editor can read
the information in a dBASE IV database file. The setup that is
required to attain database file security requires a user to assign
access levels and access privileges to each database file, the
database file is then encrypted when this information is saved.

PROTECT Under SQL Mode

With the addition of SQL in dBASE IV, the PROTECT system was enhanced
to accommodate users that use the SQL mode. Using PROTECT with SQL
mode delivers the same benefits of using PROTECT as within dBASE IV,
those benefits being login-security, database file security, and data
encryption.

In addition to showing how to use PROTECT with the SQL mode of dBASE,
this article also highlights the operational differences of using
PROTECT in the dBASE SQL mode versus the dBASE mode.

Look Ma, No Access Levels!

The first step before assigning database file privileges to users in
dBASE mode is to assign an access level to each user. This access
level determines which privilege level a user has on a particular
database file. You also have to assign an access level for each and
every database file and field-level privilege. Using PROTECT to do
this for dBASE mode users can be a bit confusing at first glance, but
when mastered, it provides a great level of security.

dBASE SQL mode users can relax, as access levels are thrown out the
door. As a matter of fact, you need only define the users that access
dBASE IV in SQL mode. Then, users only need to use the simple SQL
GRANT and REVOKE commands to control access to data.

Creating Users

The first step in establishing users for dBASE SQL mode is to use the
PROTECT system. To invoke the PROTECT system, just type PROTECT from
the dot prompt or the Security option from the Tools menu of the
Control Center. If you have never used the PROTECT system, you are
asked to enter and verify the PROTECT system administrator password.
The PROTECT system administrator password allows the designated user
to add or remove users, encrypt database files, and produce reports on
this information. This password must always be entered before
successfully entering the PROTECT system.

You are then presented with a menu. The only menu that is needed for
dBASE SQL use is the Users menu. To add a user select the Login ID
option. The Login ID is the name with which a user accesses dBASE
IV. The Login ID can be up to eight characters in length.

The next option to fill in is the Password option. The Password is
assigned to the Login ID for their use, only the PROTECT system
administrator can assign or change the Password entry. The Password
can be up to eight characters in length.

The Group Name option is only used in dBASE mode, but does require an
entry for dBASE SQL use. Every Login ID must belong to a group. The
Group Name option is used in dBASE mode to control users access to a
particular database file. This means that a database file can only be
assigned to one group, and the users that belong to this group can
only access the database file.

Login IDs are also unique to a group meaning that a Login ID cannot be
used in more than one group. It is the opinion of this author that
this is a big limitation because when you need to join data from more
than one database, you must LOGOUT and login under a different Login
ID to use a different database file. This is just unacceptable
because many real database applications need access to more than one
database file. Rejoice, SQL fans for this restriction does not apply
to dBASE SQL users. For dBASE SQL use, you can assign all of your
Login IDs to just one group or to many if you like. As long as the
SQL GRANT command is used to give the users the privilege to access
the different tables, which are actually database files in local dBASE
SQL mode, they can access them all under the same Login ID without
having to LOGOUT and Login.

The Full Name option can be used to enter a description of the Login
ID that is being created. This is usually the full name of the Login
ID. dBASE SQL does not use this information but is good for
documentation purposes. You can use the Reports menu of PROTECT to
display a listing of all of the currently created users in which this
information would be listed. As previously mentioned, the Access
Level option has no use under dBASE SQL, so you can ignore this
option. To save the entry for the Login ID that is being created,
select the Store User Profile option.

Creating the Super User (SQLDBA)

If you are using PROTECT with local dBASE SQL data, it is advised that
you use PROTECT to create the SQL Super User Login ID 'SQLDBA'. The
SQLDBA Login ID is a user level that has all privileges to the data in
every SQL database regardless of its creator and also gives you the
ability to modify the dBASE SQL catalog tables. Regular users can
only read the catalog tables, but can never modify them. Create the
SQLDBA Login ID like any other Login ID, but use the name SQLDBA for
the Login ID.

There is no super user Login ID for dBASE mode use. If you are using
PROTECT to encrypt database files, create a user ID that has full
access to each database file. This will allow full access to a
database file can be achieved when needed.

To save all of the changes that were made during your current PROTECT
session, select the Exit: Save option. Then exit the PROTECT system
by selecting the Quit option.

The user information (Login IDs) are saved in two files: DBSYSTEM.DB
and DBSYSTEM.SQL. dBASE mode uses the DBSYSTEM.DB file. dBASE SQL
mode uses the DBSYSTEM.SQL file. As with all versions of PROTECT, it
is important to make backup copies of these files because they are
accessed when users attempt to start dBASE IV. If these files are
lost by erasure or damaged, you will be able to start dBASE IV but any
subsequent attempts to access an encrypted database file in dBASE mode
is denied with an error message. Furthermore, you cannot unencrypt
your database files. This is not a problem in dBASE SQL mode because
the super user SQLDBA Login ID can always be recreated to regain
access to the SQL data. Remember, security requires responsibility.

Using GRANT and REVOKE

After your users (Login IDs) have been created with the PROTECT
system, each and every newly created SQL object (table, view, synonym,
index) is associated with its creator and only its creator can access
it. Furthermore, newly created SQL tables are encrypted. The user
can then use the SQL GRANT command to give access on a particular
object to another user. The REVOKE command can be used when the user
wants to remove a privilege that was previously granted to a user.
The WITH GRANT OPTION of the GRANT command can be used to give the
user that is granted a privilege on a particular object the ability to
be able to grant that same privilege to even another user.

There is one advantage that database files encrypted through PROTECT
have over encrypted SQL tables. This advantage is that PROTECT can
prevent a user from viewing the contents of a database file at the
field level. In dBASE SQL, when a user is granted SELECT privilege on
a table the user can view all of the columns (fields) of that table.
This can be circumvented by creating a view on the table that would be
based on a subset of the columns and then granting the user SELECT
privilege on the view and not the table. SQL Server's version of the
GRANT command does allow for the granting of the SELECT privilege down
to the column level of a table, dBASE IV Server Edition users can use
the SENDSQL command to access this special version of the GRANT
command.

The USER Keyword

USER is a special SQL keyword that can be used as an expression in the
SELECT clause or the WHERE clause. The USER keyword returns the Login
ID of the user that is currently running dBASE IV with PROTECT
installed login security. If dBASE IV is started without PROTECT
installed with login security, the USER keyword returns an empty
string value. Similarly, there is a USER() function which returns the
same value as the USER keyword. For example,

SELECT Tname, Tbtype FROM Sysauth
WHERE Userid = USER AND Pselect > 0;

returns a listing of the tables and views for which the current user
has the SELECT privilege on.

File Information Reports

The PROTECT system was enhanced with the release of dBASE IV to
include a new menu option for producing reports on the user
information and encrypted database file information. The user
information report includes the details on every Login ID that has
been defined in PROTECT. The file information report includes the
details on the privileges and the access levels required for each
privilege for the supplied encrypted database file. This file
information report only works for database files that were encrypted
by the PROTECT system. You cannot use PROTECT to produce a file
information report for dBASE SQL tables.

Hold onto your hats SQL fans, for this article includes a dBASE SQL
program, CpRights.PRS, that produces reports on privileges granted to
a particular user or the privileges granted to users on a particular
SQL table or view.

Cprights.PRS uses the information that is stored in the dBASE SQL
catalog tables to produce its reports. The SQL catalog tables that
hold this information are Sysauth and Syscolau. Sysauth and Syscolau
are updated for every successful GRANT and REVOKE operation. For more
information on the dBASE SQL catalog tables, please consult the
manuals that were previously mentioned.

Cprights has three modes of operation. The first mode is when a user
wants to find out what privileges they have in the current SQL
database. To have Cprights report this information, make sure a SQL
database has been started, then issue the command

DO Cprights WITH "JMOORE", ""

where the first parameter passed to Cprights is the name of the logged
in user that the privileges are to be displayed for. The second
parameter is ignored in this mode. An example of the report for this
command is shown below.

Name Type Action Grantor Column
STAFF T Select SQLDBA
STAFF T Insert SQLDBA
STAFF T Delete SQLDBA
STAFF T Alter SQLDBA
STAFF T Update SQLDBA STAFF NO
STAFF T Update SQLDBA LASTNAME
STAFF T Update SQLDBA FIRSTNAME
STAFF T Update SQLDBA HIREDATE
STAFF T Update SQLDBA LOCATION
STAFF T Update SQLDBA SUPERVISOR
STAFF T Update SQLDBA SALARY
STAFF T Update SQLDBA COMMISSION

The second mode is when a user wants to find out what privileges were
granted and to who on a particular table or view in the current SQL
database. To have Cprights report this information, again make sure
that a SQL database has been started, then issue the command

DO Cprights WITH "STAFF",""

where the first parameter passed to Cprights is the name of the table
or view for which the granted privileges are to be displayed. An
example of the report for this command is

Grantor Action User Column
SQLDBA Select JMOORE
SQLDBA Select JMCCRIMO
SQLDBA Select GBLAKEY
SQLDBA Insert JMOORE
SQLDBA Delete JMOORE
SQLDBA Alter JMOORE

The second parameter is the basis of the third mode of operation.
Since the second mode displays all of the users that have been granted
privileges on the particular table or view, the second parameter can
be used to display the granted privileges on the table or view for a
particular user. For example,

DO Cprights WITH "STAFF", "JMOORE"

displays all of the privileges that user JMOORE has on the STAFF table
as shown below.


Grantor Action User Column
SQLDBA Select JMOORE
SQLDBA Insert JMOORE
SQLDBA Delete JMOORE
SQLDBA Alter JMOORE





6 CpRights.PRS

CpRights.PRS
* Program ...: CpRights.PRS
* Author ...: Jeffrey McCrimon
* Date ......: 07/18/91
* Version ...: dBASE IV / dBASE IV Server Edition
* Note(s) ...: This procedure will return information
* on the privileges that a user has or
* the privileges on a particular object.
PARAMETERS object, muser
* Ä Setup environment and initialize variables.
SET TALK OFF
st exact = SET("EXACT")
st head = SET("HEADING")
SET EXACT ON
SET HEADING OFF
TRUE = .T.
FALSE = .F.
ispresent = TRUE
isuseronly = FALSE
object = UPPER(object)
muser = UPPER(muser)
* Ä Test for required object parameter.
IF LEN(TRIM(object)) = 0
?? CHR(7)
? "Error: Missing object parameter!"
? 'Syntax: DO Cprights WITH "object"[,"user"]'
SET EXACT &st exact
SET HEADING &st head
SET TALK ON
RETURN
ENDIF
* Ä Set flag for existence of user parameter.
isuserparm = IIF(LEN(TRIM(muser)) > 0, TRUE, FALSE)
* Ä Check for SQLDBA usage.
IF object = "SQLDBA" .OR. muser = "SQLDBA"
?? CHR(7)
? "The SQLDBA user has ALL permissions!"
SET EXACT &st exact
SET HEADING &st head
SET TALK ON
RETURN
ENDIF
* Ä Check for object existence.
SELECT Tbname INTO mtbname FROM Systabls
WHERE Tbname = object;

* Ä If object is not a view or table, check for synonym.
IF SQLCNT = 0
SELECT Tbname INTO mtbname FROM Syssyns
WHERE Syname = object;
IF SQLCNT = 0
ispresent = FALSE
ENDIF
ENDIF
* Ä If not table, view, or synonym,
* Ä check to see if object parameter is really a user parameter.
IF .NOT.ispresent
SELECT COUNT(*) INTO mcount FROM Sysauth
WHERE Userid = object;
IF SQLCNT = 0
?? CHR(7)
? "User " + object + " does not have permissions on any existing
tables or views in the database!"
SET EXACT &st exact
SET HEADING &st head
SET TALK ON
RETURN
ELSE
STORE TRUE TO isuseronly, ispresent
ENDIF
ENDIF
* Ä Object does not exist in database.
IF .NOT.ispresent .AND. .NOT.isuseronly
?? CHR(7)
? "Object or User " + object + " does not exist in the database."
SET EXACT &st exact
SET HEADING &st head
SET TALK ON
RETURN
ENDIF
* Ä Display permission report for User or Object.
DO CASE
* Ä Display permission report for User.
CASE isuseronly
* Ä Build permissions report table for user.
CREATE TABLE Userlist (Tname CHAR(10), Tbtype CHAR(1), Priv
CHAR(6),
Grantor CHAR(10), Column CHAR(10));
INSERT INTO Userlist
SELECT Tname, Tbtype, "Select", Grantor, "" FROM Sysauth
WHERE Userid = object AND PSelect > 0;
INSERT INTO Userlist
SELECT Tname, Tbtype, "Insert", Grantor, "" FROM Sysauth
WHERE Userid = object AND PInsert > 0;
INSERT INTO Userlist
SELECT Tname, Tbtype, "Delete", Grantor, "" FROM Sysauth
WHERE Userid = object AND PDelete > 0;
INSERT INTO Userlist
SELECT Tname, Tbtype, "Alter", Grantor, "" FROM Sysauth
WHERE Userid = object AND PAlter > 0;
INSERT INTO Userlist
SELECT Tname, Tbtype, "Index", Grantor, "" FROM Sysauth
WHERE Userid = object AND PIndex > 0;
INSERT INTO Userlist
SELECT sa.Tname, sa.Tbtype, "Update", sa.Grantor,
sc.Colname FROM Sysauth sa, Syscolau sc
WHERE sa.Tname = sc.Tname AND
(sa.Userid = object AND PUpdate IN ("ALL",
"SOME"));
? " Name Type Action Grantor Column"
? "ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ"
SELECT Tname, Tbtype + SPACE(4), Priv, Grantor, Column FROM
Userlist ORDER BY Tname;
DROP TABLE Userlist;
CASE ispresent .AND. LEN(TRIM(mtbname)) > 0
* Ä Build permissions report table for object.
CREATE TABLE ObjList (Grantor CHAR(10), Priv CHAR(6),
Userid CHAR(10), Column CHAR(10));
INSERT INTO Objlist
SELECT Grantor, "Select", Userid, "" FROM Sysauth
WHERE TRIM(Tname) = mtbname AND PSelect > 0;
INSERT INTO Objlist
SELECT Grantor, "Insert", Userid, "" FROM Sysauth
WHERE TRIM(Tname) = mtbname AND PInsert > 0;
INSERT INTO Objlist
SELECT Grantor, "Delete", Userid, "" FROM Sysauth
WHERE TRIM(Tname) = mtbname AND PDelete > 0;
INSERT INTO Objlist
SELECT Grantor, "Alter", Userid, "" FROM Sysauth
WHERE TRIM(Tname) = mtbname AND PAlter > 0;
INSERT INTO Objlist
SELECT Grantor, "Index", Userid, "" FROM Sysauth
WHERE TRIM(Tname) = mtbname AND PIndex > 0;
* Ä If user parameter is present, give report on object by
user.
? " Grantor Action User Column"
? " ÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄÄ"
IF isuserparm
SELECT Grantor,Priv,Userid,Column FROM Objlist
WHERE Userid = muser;
ELSE
SELECT Grantor, Priv, Userid, Column FROM Objlist;
ENDIF
DROP TABLE ObjList;
ENDCASE
* Ä Cleanup and exit.
SET EXACT &st exact
SET HEADING &st head
SET TALK ON
RETURN




7 Cummulative Index

1991 Cummulative Index
A
ad hoc queries Apr 91-p 10
aging report Aug 91-p 6
aliases Jan 91-p 17
Appending
unique records Nov 91-p 30
appending deleted records Dec 91-p 6
Appgen Jan 91-p 6
Applications Generator May 91-p 30, Oct 91-p 1
AppZap.COD Oct 91-p 4
AppZap.PRG Oct 91-p 6
arrays Jun 91-p 24, Nov 91-p 21
tutorial Feb 91-p 6
Articles
A Pressing Issue Sep 91-p 13
ABCs of UDFs Jul 91-p 1
ABCs of UDFs (Part II), The Aug 91-p 11
Ad Hoc Queries Apr 91-p 10
Always Room for Improvement Dec 91-p 18
AppGen Users Anonymous Jan 91-p 6
Betcha Can't Pick Just One Mar 91-p 28
BIN Indexin' Too Long Sep 91-p 10
Calculated Efficiency Apr 91-p 26
Catching Some Arrays Nov 91-p 21
Complex Descending Indexes Jan 91-p 24
Converting Text Filesto PostScript Mar 91-p 8
Decisions, Decisions Apr 91-p 16
Desktop Calculator Mar 91-p 1
Dialogue Boxes and Buttons Nov 91-p 1
Directory Swapping Jan 91-p 8
DOS Utilities Jan 91-p 1
Dressing Up the Data Nov 91-p 7
Easy .BINs in C Feb 91-p 9
Error Trapping by Number Jun 91-p 14
Fixing Corrupted Database Files Oct 91-p 12
Function Junction Nov 91-p 16
Getting to Know the dBASE IV Report Generator Sep 91-p 1
Gone Fishing! Jun 91-p 1
I Can Code It For YouWholesale! Dec 91-p 1
Increasing the Performance of dBASE IV version 1.1 Aug 91-p 8
Installing the dBASE Family on LAN Manager Sep 91-p 22
Introducing dBASE IV Server Edition Aug 91-p 22
Introduction to Indexing Oct 91-p 26
Making More Money Aug 91-p 1
Marital Bliss Apr 91-p 28
Mastering Your HP Printer Jan 91-p 10
More IV U II C Oct 91-p 16
Passing Parameters from DOS Oct 91-p 22
Poetry in Slow Motion Apr 91-p 1
Popup Combinations To Go Mar 91-p 18
Programming Printer Selection Feb 91-p 24
PROTECT and SQL Server Dec 91-p 11
Replacing from Arrays Jun 91-p 24
Resetting Page Numbers Apr 91-p 20
Running dBASE IV 1.1in Windows 3.0 Jun 91-p 26
Scroll-able Text File in a Popup Window Dec 91-p 8
Self Documenting Forms Jul 91-p 8
Sizing Up an Application May 91-p 5
SQL Database Recovery Nov 91-p 14
Summed Summaries Jan 91-p 19
The Digital Notepad May 91-p 10
The Era of Protection May 91-p 1
The Fundamentals of Popups Nov 91-p 18
The Theory of Relativity Jul 91-p 22
Transparent VT Terminal Emulation from DOS to VMS Sep 91-p 28
Warming Upto Keyboard Macros Feb 91-p 1
Weight and Fluid Conversions Oct 91-p 20
What Is an Array? Feb 91-p 6
What's Good for the Goose. Oct 91-p 29
What's In a Name? Jun 91-p 18
Whoops! May 91-p 24
You Dirty RAT() Sep 91-p 18
Ashton-Tate/Borland merger Aug 91-p 31
averaging without zeros Mar 91-p 24
B
Banyan BIOS Mar 91-p 31
bi-directional printing Apr 91-p 25
BIN files Feb 91-p 9, Sep 91-p 10
BLINK.BIN Jan 91-p 30
binary to decimal conversion Oct 91-p 16
box characters Jan 91-p 12
BROWSE.FORMAT.FIELDS Jul 91-p 7
BUCKETS Jun 91-p 12
BUILD Feb 91-p 5, Jul 91-p 7, Dec 91-p 24
buttons, creating Nov 91-p 1
C
C programs
tutorial Feb 91-p 9
PostOut.C Mar 91-p 10
calculated fields Sep 91-p 8
calculator Mar 91-p 1
calling dBASE with .DBF name Nov 91-p 12
catalogs Aug 91-p 6
CD.PRG Jan 91-p 8
closing databases in UDFs Oct 91-p 1
COD files Jan 91-p 19
tutorial Jun 91-p 2
colors May 91-p 30, Sep 91-p 31
background, high intensity Jan 91-p 30
set dynamically Jul 91-p 6
Commands
? vs @.SAY Jul 91-p 20
@...GET...MESSAGE Sep 91-p 30
@...GET...VALID Aug 91-p 12
@...GET...WHEN Aug 91-p 11
APPEND Nov 91-p 30
APPEND FROM Feb 91-p 30
BROWSE Oct 91-p 29
BROWSE...FORMAT Jul 91-p 7
BROWSE.FORMAT.FIELDS Jul 91-p 7
CALCULATE Apr 91-p 26
COPY TO...WITH PRODUCTION Sep 91-p 30
LIST Sep 91-p 31
LOCATE Feb 91-p 4
ON KEY LABEL Jul 91-p 7
ON KEY LABEL with no TYPEAHEAD Jul 91-p 7
PRINTJOB Jul 91-p 21
REPLACE FROM ARRAY Jun 91-p 24
SCAN Feb 91-p 5
SET COLOR TO... Sep 91-p 31
TYPE Sep 91-p 31
conditional indexes Aug 91-p 30
CONFIG.DB Jun 91-p 12
Control Center Jan 91-p 1
Copy2.PRG Jan 91-p 7
Copying using Shift-F1 Jan 91-p 3
corruption Oct 91-p 12
D
data corruption Oct 91-p 12
data entry
automatic date entry Oct 91-p 11
forced initial caps Oct 91-p 11
date entered automatically Oct 91-p 11
date fields displayed wrong Aug 91-p 7
dBASE IV for VMS Jan 91-p 18
DBTrap Jan 91-p 26
DD.C Feb 91-p 14
decimal to binary conversion Oct 91-p 16
design surfaces Dec 91-p 6
dictionary indexes Jan 91-p 24
directories, check for existence May 91-p 30
disk drive status Aug 91-p 28
DOS Access Jan 91-p 2
DOS environmental variables Oct 91-p 10
DOS Utilities Jan 91-p 1
DTOS() Jan 91-p 5
E
editor access through Ctrl-Home Mar 91-p 6
editors, external Aug 91-p 29
encrypting files Dec 91-p 25
Error messages
"** WARNING ** Uncompleted transaction found" Oct 91-p 9
".DBT file cannot be opened" Oct 91-p 14
"Cancel Ignore Suspend" Aug 91-p 29
"Coordinates off the screen" Aug 91-p 30
"Data type mismatch" Sep 91-p 9
"End of file encountered" Oct 91-p 14, Oct 91-p 15
"Field not found" Apr 91-p 30
"File is not accessible" Oct 91-p 14
"Illegal value" Nov 91-p 13
"Record not in index" Oct 91-p 14
"Record out of range" Oct 91-p 15
"Vector in use by another program." Dec 91-p 7
error numbers Jun 91-p 14
error trapping Jun 91-p 14
export
WordPerfect Mar 91-p 6
F
factorial Dec 91-p 1
field lists in popups Nov 91-p 30
file attributes, changing May 91-p 24
file encryption Dec 91-p 25
financial functions Aug 91-p 1
floating poing accuracy Jun 91-p 12
fonts in printing Jan 91-p 13
Form_Doc.COD Jul 91-p 9
formats to reports Dec 91-p 7
function key programming Feb 91-p 5
Functions
AT() Sep 91-p 18, Oct 91-p 16
INKEY() Sep 91-p 15
LASTKEY() Sep 91-p 15
READKEY() Sep 91-p 16
G
garbage characters Oct 91-p 15
@.GETS Jun 91-p 12
graphics stored in memo fields Aug 91-p 28
H
Hewlett-Packard laser printers Jan 91-p 10
high intensity background colors Jan 91-p 30
Hyperdisk error Dec 91-p 7
I
importing Lotus 1-2-3 Jun 91-p 12
importing null characters Dec 91-p 6
indexing
BIN sets production index flag Sep 91-p 10
complex expressions Mar 91-p 7
conditional indexes Aug 91-p 30
eliminating tags Sep 91-p 11
tutorial Oct 91-p 26
with different data types Dec 91-p 6
with unique keys Jul 91-p 7
initial caps Oct 91-p 11
installation
LAN Apr 91-p 25
to root directory Dec 91-p 24
K
key trapping Sep 91-p 13
key words as field names Nov 91-p 31
keyboard macros Feb 91-p 1, Apr 91-p 1
keywords in parentheses Dec 91-p 25
L
Label Form Aug 91-p 30
LAN Manager Sep 91-p 22
laser printer page length Nov 91-p 30
leading zeros Nov 91-p 13
line characters Jan 91-p 12
Lotus 1-2-3 Jun 91-p 12
low-level interface Feb 91-p 9
M
mailmerge Dec 91-p 7
memo fields Apr 91-p 28
storing graphics in Aug 91-p 28
memory variables lost Apr 91-p 25
memory variables to DOS Files Jun 91-p 31
MODIFY STRUCTURE in SQL Jan 91-p 28
multi-user installation Sep 91-p 24
N
natural order May 91-p 30
note pad, pop-up May 91-p 10
Novell and the TOTAL command Aug 91-p 28
numeric accuracy Jun 91-p 12
numeric fields, sizing Sep 91-p 8
NumLock indicator Dec 91-p 7
O
optical drive Sep 91-p 8
OS/2 installation Sep 91-p 22
P
parameter passing Oct 91-p 22
password protection Jun 91-p 13
performance tips Aug 91-p 8
perfromance tips Mar 91-p 30
pick lists Apr 91-p 16, Jun 91-p 13
pop-up menus Mar 91-p 18, Mar 91-p 28
pick lists Jun 91-p 13
tutorial Nov 91-p 18
Popups using field lists Nov 91-p 30
PostOut.C Mar 91-p 10
PostScript printing Mar 91-p 8, Feb 91-p 4
print screen May 91-p 30, Jun 91-p 31
Printer drivers
IBM4072.PR2 Jan 91-p 30
IBMLAS.PR2 Jan 91-p 29
printers Feb 91-p 24
Printing
control codes Jan 91-p 10
fonts Jan 91-p 13
line and box characters Jan 91-p 12
set page length to 60 for lasers Nov 91-p 30
using print forms Jan 91-p 10
PrintMan.PRG Jan 91-p 14
Procedures and programs
AChange Nov 91-p 28
ACopy Nov 91-p 28
ADel Nov 91-p 26
AFill Nov 91-p 27
AFlip Nov 91-p 29
AIns Nov 91-p 25
ASort Nov 91-p 24
CD.PRG Jan 91-p 8
CD_Proc && Change Directory Jan 91-p 9
ChngOrdr Oct 91-p 29
Copy2.PRG Jan 91-p 7
Copy2a Jan 91-p 7
CShow.PRG May 91-p 31
DelPops Oct 91-p 7
DiskRTry Jan 91-p 7
EditRec.PRG Sep 91-p 16
FldList Nov 91-p 31
for manipulation arrays Nov 91-p 21
Hday2Mem.PRG Mar 91-p 25
MemWrite Jun 91-p 30
NoteIt.PRG May 91-p 11
PickPrnt.PRG Feb 91-p 27
PopiSQL Oct 91-p 31
PrintMan.PRG Jan 91-p 14
SayGet.PRG Jan 91-p 30
SetPops Oct 91-p 7
Shadow Nov 91-p 6
TextRead.PRG Dec 91-p 9
production indexes Sep 91-p 30
programming
before you code May 91-p 5
control techniques Sep 91-p 13
logic Dec 91-p 1
proportional printing Jan 91-p 11
PROTECT
and SQL Dec 91-p 11
tutorial May 91-p 1, Jul 91-p 22
PSFIX.COM Jun 91-p 31
Q
QBE
displaying multiple occurrences Aug 91-p 6
groups Apr 91-p 25
showing selected records Aug 91-p 6
using UDFs Aug 91-p 19
queries, ad hoc Apr 91-p 10
R
relation by record number Aug 91-p 6
REPORT FORM
grouping by the quarter Nov 91-p 30
page length for laser printers Nov 91-p 30
Report Form Apr 91-p 20, Sep 91-p 1
aging Aug 91-p 6
calculated fields Apr 91-p 1, Sep 91-p 5
calculating percentages Sep 91-p 6
"continued" message Oct 91-p 9
GEN file for self documentation Jul 91-p 8
group by week Jun 91-p 12
heading position on wide report Oct 91-p 11
non-zero averaging Sep 91-p 6
one screen at a time Dec 91-p 7
order of precedence Jul 91-p 6, Aug 91-p 16
page by group Apr 91-p 20
resetting page numbers Apr 91-p 20
running balances Sep 91-p 6
using UDFs Aug 91-p 15
Report Generator Sep 91-p 1
Report.GEN Jun 91-p 6
Repvert.GEN Jun 91-p 6
restricting access to source files Aug 91-p 6
Reverse.C Feb 91-p 10
roman numerals UDF Oct 91-p 16
RUN Sep 91-p 9
RUN CD Jan 91-p 17
RunTime Oct 91-p 29
S
Sample.C Feb 91-p 11
SCAN Jan 91-p 16
screen form generator creates forms with 22 lines Nov 91-p 13
SCROLL.C Feb 91-p 12
security Jun 91-p 13
Server Edition Aug 91-p 22
SET
CURSOR Aug 91-p 29
DBTRAP Sep 91-p 30
DEBUG Aug 91-p 29
DEFAULT Jan 91-p 17
DIRECTORY Jan 91-p 17
ORDER TO Oct 91-p 27
STATUS Aug 91-p 30
TITLES OFF in BROWSE Oct 91-p 10
TYPEAHEAD Jul 91-p 7
UNIQUE Jul 91-p 7
splitting character fields Nov 91-p 7
SQL
MODIFY STRUCTURE Jan 91-p 28
SQL and Protect Dec 91-p 11
SQL commands in UDFs Feb 91-p 30
start-up database Nov 91-p 12
streaming output Jul 91-p 21
T
Template Language
DBF and QBE file access Jan 91-p 31
tutorial Jun 91-p 1
U
UDFs
ACols() Nov 91-p 24
ActvKey() Apr 91-p 18
ActvMDX() Apr 91-p 18
ARows() Nov 91-p 24
array handling Nov 91-p 21
AtArray() Oct 91-p 18
Balance() Aug 91-p 5
Banner() Sep 91-p 20
Binary() Oct 91-p 19
ChMod() May 91-p 24
closing databases Oct 91-p 1
color setting UDFs Feb 91-p 20
CommIn Dec 91-p 20
Currency() Jan 91-p 20, Feb 91-p 20
CurrPos() Feb 91-p 20
CutPaste() Apr 91-p 18
Date7() Nov 91-p 16
DateForm() Feb 91-p 22
DateX() Nov 91-p 16
debugging Aug 91-p 21
Decimal() Oct 91-p 19
Descend() Jan 91-p 25
Dialog() Nov 91-p 2
DirSel Oct 91-p 6
Discount() Aug 91-p 5
DupeMark() Aug 91-p 17
Explode() Oct 91-p 10
FDoD() Jun 91-p 21
FileHelp() May 91-p 27
Fluid() Oct 91-p 21
FNames() Aug 91-p 27
FRAT() Sep 91-p 20
FWDoM() Jun 91-p 21
GNo0Avg() Mar 91-p 26
in format screens Aug 91-p 11
IndxBar() Aug 91-p 27
IsFound() Aug 91-p 15
IsOn() Jan 91-p 22
IsRO Dec 91-p 21
LDoD() Jun 91-p 22
limitations Jul 91-p 5
LWDoM() Jun 91-p 21
MarkIs() Feb 91-p 22
MaxCol Dec 91-p 20
MemoPagr Dec 91-p 21
Msg() Jan 91-p 20
MsgExp Sep 91-p 21
NBRS Jan 91-p 5
No0Avg() Mar 91-p 27
NotDupe() Aug 91-p 14
NumOfDay() Jun 91-p 20
OneMonth() Mar 91-p 27
PathFind() May 91-p 29
PickList() Apr 91-p 17
PickName() Jun 91-p 20
PVIF() Aug 91-p 5
RAT() Sep 91-p 19
record pointer movement Aug 91-p 14
Roman() Oct 91-p 17
SeeMatch() Sep 91-p 21
ShdoBox() Jan 91-p 20
Slyder() Mar 91-p 25
SQL commands in Feb 91-p 30
TempFile() May 91-p 27
Term() Aug 91-p 5
TimeOut() Aug 91-p 27
tips Aug 91-p 18
tutorial Jul 91-p 1, Aug 91-p 11, Nov 91-p 16
VStetch2() Dec 91-p 20
VStretch() Dec 91-p 20
WDays() Mar 91-p 26
WorkArea() Apr 91-p 18
WoY() Dec 91-p 20
Yield() Aug 91-p 4
UNIX, dBASE for Apr 91-p 30
USE.AGAIN for unique appends Nov 91-p 30
V
viewing text files in a window Dec 91-p 8
VMS emulation Sep 91-p 28
VT terminal emulation Sep 91-p 28
W
WAIT Jan 91-p 20
weight conversions Oct 91-p 20
Windows 3.0 Jun 91-p 26
Word Perfect Mar 91-p 6
writing programs May 91-p 5



8 ETC

ETC.
Problem Areas

As often as possible, our technicians try to find workarounds to take
the sting out of the occasional bug. But sometimes, the best medicine
is avoiding a trouble spot

dBASE IV Server Edition will not install to the root directory of a
drive. We know users don't (or should not) install anything to the
root directory on a stand-alone machine. On the other hand, this
presents a problem when users attempt to install Server Edition in
multi-user mode to the root of a logical (network) drive.

Server Edition returns the error message "Unable to create the control
file" then returns to the DOS prompt. This problem is similar to the
dBASE IV version 1.1 install problem with creating the DBASE.OVL.
This workaround is to

ù install to a subdirectory instead or,
ù follow the "root" slash with a period. For example, instead of
installing to

F:\

install to

F:\.

Heard it Thru the Tech-Line

BUILD is a utility that can compile and DBLINK your program files and
design objects. There is nothing BUILD does that you cannot do
yourself, using the individual COMPILE and DBLINK commands.

BUILD first analyzes your files, then calls dBASE IV to do the
compilation, then calls DBLINK to link the object files, and writes a
report. This means BUILD requires the maximum free RAM, about 495 Kb
free since it runs dBASE IV above itself.
Here are some usage and troubleshooting tips for developers using
BUILD to create RunTime applications:

ù All files being referenced by BUILD must be in the same directory.
ù You can copy the Runtime system files without using BUILD, in fact,
that is what we recommend, as the overlay file is too big to fit on
floppies. Instead, simply use the DOS DISKCOPY command to make a
copy of each Runtime diskette. Then distribute the copied diskettes
with your application files, installing RunTime by running the batch
file called RINSTALL.BAT. Type RINSTALL and press Return to see the
syntax for running the RINSTALL batch file.

ù If running BUILD hangs your system and you are using the multi-user
version of dBASE IV, check the dBASE system directory for the files
DBASE.EXE and DBASE.COM. IF you have both, DBASE.EXE will be larger
than DBASE .COM. Delete the DBASE.EXE file, and try BUILD again.
Caution: do not delete DBASE.EXE unless you are certain that you have
DBASE.COM, or you will have to reinstall!

ù If you can run BUILD but it hangs or gets stuck in the Exit To DOS
menu, check your CONFIG.DB file for any lines that are 99 characters
or longer. Sometimes lines that start with "PRINTER=" are longer
than 99 characters. Temporarily remove this long line from your
CONFIG.DB file and try BUILD again.

ù BUILD does not copy printer driver files. These files are needed if
you use _pform or _pdriver to use a specific printer driver in your
program, or have built a report in the report generator with a
specific driver selected. Therefore, you must include a copy of the
printer driver with your application files. To extract printer
drivers from the DRIVERS.EXE file in your dBASE system directory,
type DRIVERS and the name of the printer driver file. For
example, to extract an HP Laserjet II driver that prints 6.5 lines per
inch portrait, enter

DRIVERS hplas2i.pr2

A list of the printer drivers may be found in Appendix F of Language
Reference.

ù Programs that use color may not be readable when displayed on a
monochrome monitor. If you are designing programs to run on a
monochrome monitor you may want to try different color settings to see
which combinations are best for that monitor. You may also want to
include the ISCOLOR() function as a test for color in your programs.
ù If you are using the Application Generator and want to save your
settings in the Preset Menu, make sure that the DBASE2.RES file is not
flagged as read-only. If you're running the Application Generator on
a network then you must have read and write privileges to the
directory containing the DBASE2.RES file. Refer to pages B-8, C-8,
and E-8 of the Network Installation booklet for more details.
Page 31 of the TechNotes Release Edition for dBASE IV version 1.1 has
more information for developers who are using and distributing
Run-time applications.

Encyrpting Large Files

Encryption of a 20 megabyte file fails to make a .CRP file. The
following work around is suggested to encrypt large files:

1. USE Original File
2. COPY STRUCTURE TO Temp File
3. Encrypt the Temp File
4. USE Temp File
5. APPEND FROM Original File
6. Rename Temp File to desired name

Watch Your Style

Everyone has their own programming style and, for the most part, dBASE
IV can deal with the code properly regardless of your style. There
are exceptions however. The following code sample illustrates this.

TRUE = .T.
IF(TRUE)
...
ENDIF

dBASE IV accepts this as a valid IF.ENDIF structure. The presence of
the parantheses and their proximity to the IF command doesn't disrupt
the parsing process. However, the following code sample will not
produce the expected results.

TRUE = .T.
DO WHILE(TRUE)
...
ENDDO

When you try to compile this piece of code, the compiler says that the
ENDDO doesn't have a corresponding DO WHILE.
Why? It is because parentheses are legal characters in DOS file names
and WHILE().PRG is a valid DOS file name. The parser assumes that you
want to DO WHILE(TRUE).PRG instead of assuming that you are making a
DO WHILE construct. So when it sees the ENDDO it complains because,
as far as it's concerned, no DO WHILE command has been issued prior to
the ENDDO.