February 1996Create "Enduring Variables" in FoxProPeter de Valença

(1)

Here's a creative way to store variables that persist throughout an entire FoxPro or Visual FoxProsession, yet are immune to destruction by commands like CLEAR MEMORY. "EnduringVariables" are great for desktop accessories, add-ons to the development environment, and as astandard interface between "plug and play" application components.

The problem: FoxPro has a wide range of options t store and retrieve information, includingtypes of variables (PRIVATE, PUBLIC, REGIONAL), memory files, databases, virtual tablesand even ordinary text files. However, if you want to develop a variety of applications that shareinformation in some regard, each method has a disadvantage. Some applications CLEARMEMORY in cleanup code, thus ruling out the use of memory variables. Or, the disk location ofdatabases, memory files, and ordinary text files may be unknown to some applications. Virtualtables (cursors) may have been closed and destroyed in the cleanup code of an application.

The problem can be solved if each application is developed by the same software house.In-house protocols could instruct developers to maintain certain variables or databases. But sucha protocol won't help if a customer combines applications and tools from different vendors (forexample, in a menu system).

Desk accessories and other FoxPro add-ons like development utilities present another problem.Add-ons often need to store values between invocations without destruction by a stray CLEARMEMORY command.

"Enduring variables" to the rescue

What's needed are variables that will endure during an entire active FoxPro session, similar toFoxPro's system memory variables. I developed the function varX() to meet the need for such"enduring variables."

"Enduring variables" aren't a substitute for conventional variables. They're simply an additionalvariable class. Think of them as a user-defined equivalent to the FoxPro system memoryvariables. Use them when you want the safety and persistence of a system memory variable, butthe global accessibility of a PUBLIC variable. A varX() variable can be used wherever you'd useSTATIC variables in languages that support them, or object properties in an OO language likeVisual FoxPro. Visual FoxPro developers can also use varX() as an alternative to globalvariables that need to be accessed by many different objects (although unfortunately, varX() can'tbe used to store object references variables of type "O").

I also offer varX() to the FoxPro community in hope that it will become an international standard

for communication between applications and application components. Using varX(), we coulddevelop a variety of applications that can share information and simultaneously appear in thesame menu system. Each such application could be allowed to do destructive tasks such asclosing databases, clearing memory, and redefining the "default" directory, without causingtrouble for other modules.

How varX() works

The core of varX() is an out-of-use FoxPro system memory variable. In FoxPro for DOS, thevariable _SPELLCHK is used, since no spell checker is implemented in the DOS version; in allother versions of FoxPro, _FOXGRAPH is used, since that variable is unused on all platformsexcept DOS. All enduring variables are stored in this system memory variable, and it is treatedmuch as if it were a memo field. Thus, enduring variables are insensitive to commands that clearmemory variables or close databases. The varX() function is the interface to this buffer. It'simportant that you don't change the values of these system memory variables directly when usingvarX().

I suggest you place a copy of VARX.APP in the FoxPro directory. That way, you'll be able totell an application the location of varX(). Simply add sys(2004) to the FoxPro path prior to thefirst call. The procedure ADDPATH.PRG (see the sidebar "Add to the FoxPro Path") offers asecure way to add sys(2004) to the path.

Some developers may want the source code to varX() in order to be able to customize things orinclude the code in each project. However, with varX(), version control is very important.Compatibility with older versions and copies used by other parties should have the highestpriority. That's why the FoxPro community should use VARX.APP and keep it excluded fromprojects, and why I haven't released the source code. It's my intention to release updates on aregular base on CompuServe's FoxForum.

VarX() is provided as two applications: VARX.APP runs under FoxPro 2.6 (any platform) andVVARX.APP runs under Visual FoxPro. Visual FoxPro users should rename VVARX.APP toVARX.APP so that portability of varX() calls between FoxPro 2.6 and 3.0 is preserved.

Reading built-in enduring variables

The first and second parameters that varX() expects make up the name of an enduring variable.The function will return the value of this variable. The type returned depends on the data typeoriginally stored.

The varX() function comes with three built-in enduring variables, which can be read as follows:

C = varX( 'SYMBOLS_VARX', '' )C = varX( 'INFO_VARX' )C = varX( 'SERIALID_VARX' )

`'SYMBOLS_VARX' returns the symbols table that varX() uses. The first 25 columns of output

are reserved for future enhancements. Each symbol occupies an additional 26 columns. Of these26 columns, columns 1 and 2 are used for a separator, the next 20 columns for a unique symbolID, and the last four to define the data type. ASCII codes 13+10 are used as separators. Thisenables you to extract symbol information with the FoxPro MLINE() function. Notice that thesecond parameter is empty. In this particular case it may be left out, as has been done on theother two calls.

The `INFO_VARX' call returns some information about varX(). Here's how you can extract thisinformation with MLINE():

MLINE() call DescriptionMLINE(varX('INFO_VARX'),1) Name (always "varX()")MLINE(varX('INFO_VARX'),2) varX() version numberMLINE(varX('INFO_VARX'),3) Date of this version of varX()MLINE(varX('INFO_VARX'),4) Author's ID ("PDV" = Peter de Valença)MLINE(varX('INFO_VARX'),5) Copyright NoticeMLINE(varX('INFO_VARX'),6) Current number of symbols

The "SERIALID_VARX" call returns a character "Serial ID" based on your Serial Number.You'll need this information when declaring your own enduring variables; more on this in amoment.

Creating enduring variables

It's quite easy to add new enduring variables to the list. Simply call varX() with three or fourparameters. Here's the calling syntax:

= varX(cSymbolName,cUniqueID,cDataType[,uInitialValue])

= varX(cSymbolName,cUniqueID,cDataType[,uInitialValue])

cSymbolName is the name of the enduring variable you want to create.

cUniqueID is your unique developer ID.

cDataType is the data type of the value you want to create (described in text below).

uInitialValue is the initial value of the enduring variable (optional).

The unique ID should be a character constant that's used by you or your company only, so thatyour declarations won't ever interfere with the correct declarations of other parties. You canobtain a unique ID for a given copy of FoxPro by calling varX('SERIALID_VARX') ininteractive mode. The maximum length for the symbol name plus the unique ID is 20 characters.

Don't ignore or minimize the importance of the unique ID! As a matter of fact, when I developedthis function, I saw the need to recognize an international community of FoxPro developers. We

should take care not to overwrite each other's enduring variables. The unique ID is based on theserial number of your copy of FoxPro. While it's possible to administrate Symbol Namesin-house, it's impossible to do so for the international community.

The description of the data type must be one of the following:

"C" Character Expression"Niij" Numerical Value; ii=Length, j=Decimals (Example: "N082"is a number with two decimal places

and a maximum of eight digits includingdecimal point.)"D" Date"L" Logical"E" Evaluated Character Expression; Maximum of 10 evaluations(more on this will follow)"Eii" Evaluated Character Expression; ii=Maximum numberof evaluations (more on this below)

For example, the following command would create a symbol, "TVAL!#0}Sa59V, that's numericand initialized to the value 12.345:

varX("tval", "!#0}Sa59V", "N103" ,12.345)

There's virtually no limit to the size of character expressions. In one test I performed, a100,000-byte string was stored as an enduring variable in varX()without creating problems..Performance in creating, reading, and changing enduring variables is acceptable. As aconsequence, you can store the contents of memo fields as enduring variables (but note mywarning near the end of this article about the ASCII code sequence 17 + 10; this could be aproblem for some binary data in memo fields, but should pose no problem for textual data).

Dates are stored as a Julian day number and returned in the format specified with SET DATE.

varX() has another feature. You can create enduring variables that are evaluated when read; youcan even invoke entire tools and applications! The data type of such an enduring variable is "E."Bytes between text merge delimiters in the expression will be evaluated. (You can change thesedelimiters with the FoxPro command SET TEXTMERGE DELIMITERS.) I give some examplesin the next section.

The data type parameter is ignored on succeeding calls referencing an existing enduring variable(except for the data type "E;" see the next section). This implies that it's not necessary toinitialize a symbol before actual usage. Thus, a call like the following will always create theenduring variable and return the expected result:

#declare varXid "!#0}Sa59V"if varX("tval", varXid, "N103") > 0

Reading your own enduring variables

Reading your own enduring variables isn't different from reading built-in variables. Again, theresult will be of the declared data type:

result = varX("mystring", varXid, "c")

You can omit the third parameter if you're certain that the enduring variable was declared inadvance.

Changing an enduring variable

The value associated with a symbol can be changed with the fourth parameter. The result willreflect the new information. For example:

= varX("mystring", varXid, "c", "my string")

The function varX() is meant for advanced users. There's no read-only mode; even the built-inenduring variable "SYMBOLS_VARX" can be overridden. Of course, this would corrupt thebuffer, so I don't recommend doing so.

How to use the data type "E"

If an enduring variable is of data type "E," bytes between text merge delimiters in the expressionwill be evaluated. The following (somewhat contrived) examples should help make this clear.

While the data type parameter is normally ignored once an enduring variable is created, it's notentirely ignored when the data type is "E." You may choose to specify "Eii" where ii is a numberin the range 0-99. For that particular call, the result will reflect the situation after a maximum ofii evaluations. For example, specifying "E0" would return the plain string instead of an evaluatedstring.

The following code shows how to invoke a function -- in this case, the FoxPro functionGETDIR(). You could also choose to call one of your own applications or tools.

wait window varX("getDir", varXid, "E", ; "Directory <<getdir()>>")

In this next example, you can see how a logical result is handled. The IF expression will evaluateto "YES" if the application is running on the Windows platform. The result returned by varX() isalways of type character when the "data type" is "E," so .T. is returned as "YES" and .F. as"NO." If you want a different value, use IIF() to transform "YES" to .T. or to some other string.

if varX("yesno", varXid, "E", "<<_WINDOWS>>") = "YES"

Numeric values are also returned as character strings with type "E" enduring variables, as shownin the following example, which uses VAL() to convert back to numeric:

number = val(varX("number", varXid, "E", "<<_TALLY+5>>"))

The previous example also illustrates that you can pass any valid expression to varX(); in fact,varX() evaluates expressions using the FoxPro EVALUATE() function, so anything thatEVALUATE() can process can be passed to varX().

The following example can cause an infinite loop. In order to prevent that, the evaluation routinestops evaluating expressions after ten evaluations. This number can be adjusted for a specifiedenduring variable; for instance, in the previous examples, the data type parameter might havebeen "E01" or "E1".

aaa = "<<bbb>>"bbb = "<<aaa>>"result = varX("aaabbb", varXid, "E", ; "Result is: <<aaa>>")

Here's a final example to show how an incorrect call to varX() is handled:

wait window varX("wrong", varXid, "E", ; "Not possible: <<sys()>>")

The result of the above call will be: "Not possible: {{???: sys()}}." In other words, varX()echoes back three question marks, a colon, a space, and the passed expression -- all enclosed indouble curly braces -- if it can't successfully evaluate the expression.

Error messages

Several error conditions are recognized by varX() and will result in the a simple error messagebeing displayed with WAIT WINDOW NOWAIT. No error routine (either the default FoxProroutine or yours) is triggered and program execution will continue. These messages all have todo with reporting incorrect arguments passed to varX(). The exception is the message"Corrupted varX() buffer," which probably indicates that the system memory variable used asvarX's buffer has been corrupted by some code outside of varX().

One kind of error may go unnoticed in some cases. Data is stored in a variable length format byvarX(). The ASCII codes 17 + 10 are used as separators. If this character sequence is part of avalue, varX() will erroneously assume this is the spot where the next value is stored.Consequently, the buffer is corrupted from this position on.. You should take care not to storethis sequence of ASCII codes, particularly when dealing with binary data from memo fields.

Conclusion

I realize that varX() isn't a panacea for all problems, but it's a step ahead. I think of it as a usefultool in my toolkit -- a tool that simply extends my possibilities.

Peter de Valença is a freelance software developer who works on projects for large companies and hasspecialized in FoxPro for the past three years. He has a Ph.D. in social psychology from the University ofAmsterdam. Peter solicits your feedback about varX(). pvalenca@digiface.nl.

Sidebar: Add to the FoxPro PathThe following code will conditionally add a path to the FoxPro search path:

PROCEDURE addPathparameter _px && make this lparameter in Visual FoxPro

if atc(';' + _px + ';', ';' + set( 'path' ) + ';') = 0set path to (set( 'path' ) + ';' + _px)RETURN .T.endif

RETURN .F.

The character parameter _px is required and represents the path that must be added to the FoxPropath list. The path won't be added if it' s already in the FoxPro path.

The returned logical value can be used to determine whether or not the path has been changed.

As a usage example, to tell your application to also search the FoxPro directory, you might addthe following call to addPath:

do addPath with sys(2004)

Rockin' with FoxProWhil HentzenWhat do you say when you're asked to join the All-Stars? Well, first of all you say "Yes." ButI'm still a little cowed, following in the footsteps of Hart, Slater, and Grommes as editor ofFoxTalk. As Bob has mentioned previously, this has been an orderly and planned transition --well, as orderly as could be between two guys who are both working 100-hour weeks. As you

know, demands on Bob grew beyond the time he had available, and something had to give.Fortunately, having written for FoxTalk for several years, I was in a good position to jump in.

The changing of the guard is a good opportunity to review what's worked in the past, and topreview what we might see in the future. As you're aware, FoxTalk has been the high-endtechnical journal for FoxBASE and FoxPro for nearly a decade, and we're committed to retainingthis ground. FoxPro has grown into such a huge product that it would be virtually impossible forany single publication to cover it completely -- and fortunately, there are a number of excellentresources serving the needs of FoxPro users and developers. Our niche is the serious,professional developer interested in developing robust, high-quality custom and vertical marketapplications with the various dialects of FoxPro, and who doesn't need introductory lessons inthe product. We'll continue to target those individuals.

A primary concern of many of you may be, "How will FoxTalk balance FoxPro 2.x and VFP?"and more generally, "What will I get out of FoxTalk in the next year or two?" To answer that, letme first explain what my work-a-day world looks like. There's sometimes a concern thatsomeone on the author/speaker circuit has lost touch with the real world -- that so much time isspent writing that the writer doesn't deal with customers anymore -- and viewpoints becomemore theoretical than practical. While I've had my share of the "circuit," my business is writingcustom applications. I run a shop with a half dozen people, and all we do is custom FoxPro work.About a third is still in FoxPro for DOS (believe it or not), half is in FoxPro for Windows 2.x,and the remainder is Visual FoxPro. As I write this, I'm wrapping up the delivery of a FoxPro forDOS application for about 50 sites around the country that must run on a 486/33 with 4M ofRAM.

My firm has the same needs as many of you: supporting DOS 2.x legacy apps, working withWindows 3.1 on machines that don't have a local hard disk, and trying to explain to a potentialcustomer about the "tradeoffs" (isn't that a diplomatic way of putting it?) of running a VFPapplication on an 8M Windows for Workgroups 3.11 machine.

I'm just as interested in cutting edge (or over the edge) work in VFP as the next developer, andwe're going to be at the forefront of VFP coverage in FoxTalk, but I have one foot firmly plantedin the real world as well.

What does this mean to you? First and foremost, you're not going to be stranded if your breadand butter still comes from earlier versions of FoxPro. I don't think we'll be dealing much withFoxBASE or FoxPro 1.0 anymore; there is a limit, after all. But FoxPro 2.x applications aregoing to be with us for a while, and we'll continue to cover these platforms as well asstate-of-the-art development techniques with Visual FoxPro.

Second, remember that FoxTalk has been the high-end publication for FoxPro developers fornearly a decade. I remember when I started reading it back in the late 1980s. I considered myselffortunate if I understood half of the articles, and I know many of your professional careers havegrown up paralleling FoxTalk as well. As I mentioned, we're committed to providing high-level,in-depth techniques and strategies for FoxPro developers. You won't find articles like "How touse DO WHILE" or "Writing your first application with VFP's wizards" in FoxTalk. Other

publications cover those subjects well.

However, we are going to spend more time not only on complex technical issues, but also onstrategic use of the tools we have. In other words, we won't be content with showing you how tocreate the latest cool class library or super-duper builder -- we'll go one step further and help youturn this knowledge into a competitive advantage in your business. The information you find inFoxTalk will not only help you write better programs, but also deliver them with higher quality.

I'm passionately interested in making this happen. I'm also interested in hearing what you'relooking for. Please contact me on CompuServe at 70651,2270 whenever the urge strikes. Ourgoal is to help you write and deliver better FoxPro applications. As I've been known to saybefore, "Software is always better when written at 105 decibels."

Let's rock.

Do You Need Client/Server?Robert Green

How do you decide if and when to move your data to a client/server environment? It's deceptivelysimple: evaluate the extras client/server has to offer and benchmark typical operations using yourdatabase.

I'm repeatedly asked the question, "How do you know if you need to go client/server?" I'll try toanswer, or at least point you in the right direction. I don't want to leave the impression that aquestion of this magnitude can be dealt with in a single monthly column -- it can't. But at leastwe can toss a few ideas around.

One can build a large number of powerful applications using Visual FoxPro and your developertoolkit of acronyms (APIs, DLLs, OCXs, OLE, ODBC, MAPI, and so forth). The hard questionis this: when do you need to scale a database application up to the power of a SQL back end,such as SQL Server or Oracle?

How about this for an answer, "When the back end can handle the data better than Visual FoxProcan." Let's look at scenarios where this might be the case. Since I'm most familiar with SQLServer, I'll use that for my examples.

Visual FoxPro tables can store up to one billion rows and can be up to two gigabytes in size.This seems like a heck of a lot, and it is. But what if you need to store more data? What if youneeded to store two billion rows or five gigabytes of data? You could break the data up into morethan one table, but this imposes a cost in terms of application design and maintenance, as well asyour ability to search for a row or group of rows.

Consider this: SQL Server tables can store an unlimited number of rows and can be up to eight

terabytes.

Let's say you need to store 1.5 gigabytes in a table. This is less than FoxPro's maximum, but is ittaxing the engine? What will performance be like with that amount of data? Remember thatFoxPro isn't a client/server database. When you query a table, all records examined get sent overthe network to your workstation. If you find only one record, a huge amount of network traffic isstill generated, both in getting the data and in sending it back to the server.

If you were using a client/server architecture, you'd construct the FoxPro front end to requestonly the amount of data needed at any time. To update one row, you'd first send a Selectstatement to the back end to retrieve that one row. The back end then sends that single row to theworkstation. When it's time to save changes, FoxPro sends the back end an Update statement,and the back end makes the change. There's a minimal amount of network traffic involved here.

So even though FoxPro can store the amount of data involved in an application, that's not thesole consideration. If moving the data to a client/server environment will lead to betterperformance and reduced network traffic, consider doing it.

Another possibility is that an SQL back end may handle large amounts of data better thanFoxPro can. Suppose you need to run an aggregation on a large data set as part of a weekly ormonthly process. You write a FoxPro Select statement and it takes 10 hours to run. What if thesame Select statement took one hour to run on Oracle? I'm not suggesting this will be the case,but if your testing showed it to be true, that would indicate that you might want to move toclient/server.

Another fact is that SQL Server provides much better crash protection than Visual FoxPro. If thefile server were to die, an open FoxPro table could be corrupted. You run the risk of losing largeamounts of data. Anything modified since the last backup would be at risk. You can protect thefile server with all kinds of fault tolerance, but a workstation crash could also cause this type ofdata loss.

SQL Server can be set up so that you will never lose data. All data modifications are written intoa transaction log, giving SQL Server the ability to rollback a transaction. This is also key inautomatic recovery. Data modifications are written first to the log, then later to the database.When SQL Server starts, it checks to make sure all committed transactions in the log arereflected in the database. It also makes sure that any uncommitted transactions that made it intothe database are rolled back. Additionally, the transaction log can be backed up separately. If it'son its own hard disk, you could lose the database but still be able to back up the log. No datawould be lost. This type of data protection isn't possible in a FoxPro application.

SQL Server is a multi-threaded application and is written to take advantage of Windows NTscalability. If you added a processor to the machine, the performance of SQL Server wouldimprove. Add another processor and it would improve again. Visual FoxPro is notmulti-threaded, so adding a processor won't change performance. SQL Server can also be run onthe more powerful MIPS and Alpha processor machines. FoxPro can't be run on these machines.This means that the performance ceiling of Visual FoxPro is below that of SQL Server. IfFoxPro maxes out your single processor Pentium machine, it can't be scaled up to more powerful

hardware.

The number of users will have an impact on performance. Your FoxPro application may performsuperbly with 10 users, but what happens when 20, or 50, or 100 users are banging away at thedata? How much of a drop-off in application response time will occur as users are added? SQLback ends tend to suffer much less of a performance drop when the number of users increases.

The time required to perform system maintenance tasks is also a consideration. How long does ittake to backup the data, re-create indexes, import data, and so on? If there is enough data, willFoxPro perform these tasks in a reasonable amount of time? Will SQL Server? The old versionof SQL Server, 4.21, was not all that fast when dealing with very large databases. The newversion, 6.0, is dramatically faster. At the same time that it's reading data, it can also read aheadand place upcoming data into cache memory. In addition, SQL Server can perform a stripedparallel backup. This means that instead of writing the backup to one file, it can write to multiplefiles, all at the same time.

In addition, backup in SQL Server is dynamic. You don't have to kick users off the system. Thisis crucial if you are in a seven-days-a-week, 24-hours-a-day operation. Finally, remember thatSQL Server can back up the transaction log separately. This allows you to back up the entiredatabase, say, once or twice a week, then backup the incremental transaction logs only once ortwice a day. The incremental backups take nowhere near as much time as the full databasebackup. Again, this is a big plus if there are people using the database while you're backing it up.

How do you know whether SQL Server will out perform Visual FoxPro? You have to test it. Getthe product, install it, create a nice big test database and run some benchmarks. Practice not onlychanging data but running big Select statements and data maintenance tasks. Simulate real-lifesituations and see for yourself.

I've mentioned just a few of the things to look for in evaluating the move to client/server. We'llcontinue this discussion in the months ahead.

Robert Green is a vice president with The Information Management Group, a Chicago-based MicrosoftSolution Provider and Authorized Technical Education Center specializing in consulting and training inMicrosoft database products. 312-280-1007, CompuServe 76104,2514 RGreen@imginc.com.

Extend the Report Writer/Report Designerwith UDFsJohn Stepp (2)

Are you working on the Report from Hell and ready to abandon the 2.x Report Writer or 3.0Report Designer and resort to hand-coded report programs? Try adding some code to the report

layout instead.

FoxPro's report writer can probably satisfy most of your basic reporting requirements. However,you've also probably encountered complex or special reporting requirements that tax the reportwriter. This article discusses using user defined functions (UDFs) to extend the functionality ofthe report writer.

You may have tried to use the report writer to develop a complex report, only to realize that youcan't quite get it to do what you want. Before FoxPro for Windows, I often relied on customreports to satisfy my complex reporting requirements. Those reports consisted entirely ofprocedural code. Because I was working in a character-based environment and generally usedone font, formatting the output was a simple process. Once I determined the location on the pagefor each piece of information, I simply used the appropriate @SAY command to print it.

With the release of FoxPro for Windows, developers were given the ability to easily generatesophisticated-looking reports using various fonts, font sizes, styles, shading, bitmaps, and more.Users not only appreciated these new reports, but expected them. I could now use the reportwriter to generate professional reports for basic reporting requirements, but what about complexreports? Although it may be necessary at times, I consider formatting a custom FoxPro report inthe Windows environment as nothing more than a last resort. If at all possible, I opt for the easeof use and quick formatting capabilities the report writer offers.

When designing complex FoxPro reports in the Windows environment, you need to consider twoseemingly contradictory requirements: access to the power and flexibility of procedural code,and the ease of use and precise formatting of the report writer.

Does this mean you have to choose one over the other? Certainly not. UDFs provide an excellentbridge between the two approaches, serving as the "hook" between the report form andprocedural code. The last time I checked the FoxPro manuals, they provided few insights or tipsregarding UDFs within the report writer, so some of you may not be aware of their flexibilityand power. This article demonstrates the use of UDFs within the report writer so you too cantake advantage of the power and flexibility they offer.

UDFs in reports: the fundamentals

I'm sure many of you use UDFs on a regular basis for application development, since theyencapsulate functionality into a black box that produces a single output. When properly written,calling routines needn't be concerned with how the UDF accomplishes its task -- they only needto be aware of and provide the required inputs and anticipate the subsequent output.

For example, imagine a UDF named CustNm().When passed a unique customer id as input, thefunction returns the properly formatted customer name (or the empty string if the customer id isnot found). Any routine can invoke CustNm() as long as it passes the required numeric inputvalue. The syntax of the UDF call is as follows:

m.TheCustNm = CustNm(CustId)

After CustNm() executes, memory variable TheCustNm contains the name corresponding to thecustomer id indicated by CustId, or the empty string if the customer id wasn't found.

You can invoke a UDF just as easily within your reports. If you need to display or calculate avalue that isn't readily available or isn't possible within the report writer, invoke a UDF to do it.The UDF can do almost anything you want, including execute SQL queries, perform complexcalculations, or build output expressions. The possibilities are almost endless.

You use a UDF such as CustNm() by making the UDF call a Report Expression. The customername, instead of being stored in a memory variable, will be printed on the report at the locationof the Report Expression that contains the UDF. This isn't a very practical way to get thecustomer name, but it demonstrates how simple it is to invoke a UDF within the report writer.

A real-life case study

To give you a better idea how you can use UDFs within your reports, I'll review the UDFs usedwithin a sample report. Please keep in mind that the report is simply an example and doesn'tnecessarily represent an ideal solution.

I'll first provide you with some background regarding the sample database. The database dealswith environmental waste and the treatment systems available to treat it. The database consists offive tables, each of which I'll describe briefly. I've indicated the primary keys (denoted by PK),foreign keys (denoted by FK), and some of the other relevant columns for each table.

Wstrm -- Individual units of waste requiring treatment (called waste streams).

WstrmId (PK) -- Unique waste stream identifier.WasteType -- Type of waste (HLW, MLLW, or MTRU).Inv_m3 -- Current inventory of waste expressed in cubic meters.Fiveyr_m3 -- Projected five-year generation of waste expressed in cubicmeters.TrtOpt (FK) -- References the treatment option designated to treat the wastestream.

Trt -- Treatment systems available to treat the waste.

SystemNo -- Unique treatment system number.

Option -- Treatment options available to treat waste streams. This table associates a wastestream with

the treatment system that will treat the waste.

TrtOpt (PK) -- Unique treatment option code.OnOff -- Indicates if waste targeted to this treatment option will be treatedon or off site.WasteType -- Primary type of waste this treatment option supports.SystemNo (FK) -- References the treatment system where treatment is to occur.

OptCost -- Costs by fiscal year for each treatment option.

TrtOpt (PK) -- Unique treatment option code.Fy (PK) -- Fiscal year for which costs are being captured.OpEsc -- Treatment option operating costs, expressed in thousands of escalateddollars.CapEsc -- Treatment option capital costs, expressed in thousands of escalateddollars.

CostWbs -- Milestones for each treatment option.

TrtOpt (PK, FK) -- Treatment option code.WbsCode (PK) -- Code corresponding to milestone.StartFy -- Fiscal year in which milestone activity begins.EndFy -- Fiscal year in which milestone activity ends.

The requirements for the sample report are as follows:

Generate a matrix for each treatment system that displays the aggregate costs associated with thetreatment system by fiscal year. Fiscal years will appear as columns and the costs as rows. Thecosts reported for each fiscal year will be broken down as follows: on-site capital, on-siteoperating, off-site operating, and total costs.

First, you have to indicate when each system reaches any of three critical scheduling milestones,referred to as KD-0, Start Operations, and End Operations. An appropriate indicator will bedisplayed under the fiscal year column in which the milestone occurs. Also, you must calculatethe waste volume to be treated each fiscal year within a treatment system's operating period. Theoperating life for a system is defined as the number of fiscal years between End Operations andStart Operations. The waste volume to be treated each year is simply the total waste volumedivided by the operating life. You also have to maintain a cumulative waste volume across thisperiod.

Treatment schedules can extend 100 years and beyond, so be prepared to accommodate adynamic number of columns in the report. The detail portion of the report will look somethinglike this (treatment system information is also printed in the page header):

1995 1996 1997 1998On-site Capital $1,400 $1,400 $1,500On-site Operating $4,800 $5,500 $5,600Off-site Operating $100 $200Total $6,200 $7,000 $7,300Milestones KD-0 StartOps EndOpsWaste Treated 350 350 350Cumulative Waste Treated 350 700 1050

For brevity, I'm going to include only code segments that are most relevant to this article. For thecomplete source code, please refer to the accompanying Download file.

The beginning of the program defines the parameters required to invoke the report as well as afew important constants.

* FundProf.prg

* Sample report to demonstrate the use of user* defined functions (UDFs) in support of complex* reports.Generates a treatment system schedule* and funding profile report.

* Calling syntax:* DO FundProf WITH "STP Configuration", 'S', .T.

* Parameters:* Subtitle - Text description of data source.* RptDest - Character value indicating report* destination. An 'S' sends output to the screen,* anything else sends output to the printer.* RollFys - Logical value indicating if cost data* for years beyond that specified by MAXRPTFY* are to be rolled up into one column. This* shortens the report by consolidating numerous* Fy data into one aggregate column.

* Assumptions:* Presence of '95 cost data for at least one* treatment system.

PARAMETER Subtitle, RptDest, RollFysPRIVATE ALL LIKE l*

* Max number of years that can fit on one page.#DEFINE DATACOLS 14

* Latest year for which detail costs will be* reported when the RollFys parameter is .T.#DEFINE MAXRPTFY "2019"

* Column label for aggregated costs when* the RollFys parameter is .T.#DEFINE MAXRPTFY1 "2020+"

There are 14 columns available for cost data in the report form, hence the definition ofDATACOLS. This constant provides the basis for "shifting" the range of cost data on each passof the report. MAXRPTFY is used in conjunction with the RollFys parameter. When RollFys is.T., instead of viewing many pages of data when costs extend well into the twenty-first century,all costs beyond 2019 are rolled up into one aggregate value for reporting, thereby limiting thereport to a maximum of two pages per treatment system. MAXRPTFY1 is simply the columnlabel to be displayed for this aggregate value.

The following query retrieves the data set identified in the report requirements.

SELECT B.SystemNo, Fy, "Onsite Capital " ;AS CostDesc, SUM(CapEsc) AS EscCost ;FROM OptCost A, Option B ;WHERE ; A.TrtOpt = B.TrtOpt ; AND OnOff_Site = "ON" ;GROUP BY 1, 2 ;UNION ;SELECT B.SystemNo, Fy, "Onsite Operating " ;AS CostDesc, SUM(OpEsc) AS EscCost ;FROM OptCost A, Option B ;WHERE ; A.TrtOpt = B.TrtOpt ; AND OnOff_Site = "ON" ;GROUP BY 1, 2 ;UNION ;SELECT B.SystemNo, Fy, "Offsite Operating" ;AS CostDesc, SUM(OpEsc) AS EscCost ;FROM OptCost A, Option B ;WHERE ; A.TrtOpt = B.TrtOpt ; AND OnOff_Site = "OFF" ;GROUP BY 1, 2 ;UNION ;SELECT B.SystemNo, Fy, "Total " ;AS CostDesc, SUM(CapEsc+OpEsc) AS EscCost ;FROM OptCost A, Option B ;WHERE ; A.TrtOpt = B.TrtOpt ; AND OnOff_Site IN ("ON", "OFF") ;GROUP BY 1, 2 ;ORDER BY 1, 3, 2 ;INTO CURSOR DataSet

This provides the data you need, but not in the format you need for the report. There are severaloptions here, one of which is to use FoxPro's cross-tabulation procedure, GENXTAB.PRG, toconvert the row-based fiscal year data into columns for the report. But using GENXTAB wouldrequire a few work-arounds and enhancements to the code itself. Another option is keeping thedata in the row-based format, and allowing the UDFs to calculate totals on the fly for each cell inthe report. This is the most generic approach, since an unlimited number of fiscal years would besupported.

For the purposes of this example, I opted to develop code to invert the fiscal years in the data setso they would appear as columns. In effect, I wrote my own custom cross-tabulation code. Theresulting table can then serve as the primary data source for the report. As briefly mentionedabove, this approach places an upper limit on the number of fiscal years that can appear in thereport. Since FoxPro supports a maximum of 255 columns per table, the cross-tabulation routinehas to come in under this ceiling. For the purposes of the report requirements, 255 is more thanadequate.

The following code segment makes up the main body of the program, from which all supportingfunctions are invoked. Following this code segment I'll point out some highlights:

* Global array to contain FYI that* appear in the data set.DIMENSION Fa(1)

* Create reporting table to contain columnar* cost data.= CostTable ()

* Record length of Fy arraym.FyALen = ALEN(FyA)

* Populate newly created reporting table with* four rows per treatment system (one row for each* dollar type reported).= PopCostTbl ()

m.lMsg = "Generating treatment system report header " ; + "information ..."DO MsgDisp.SPR WITH m.lMsg, "Cost Report", "Status"

* Create and populate table to contain treatment* system header data.= PopulateHdr ()

INDEX ON SystemNo TAG SystemNo

SELECT CostsINDEX ON SystemNo TAG SystemNoSET RELA TO SystemNo INTO Header

* Offset is used to support multiple pages per* treatment system in order to accommodate wide* ranges of Fys.Offset will be incremented by the* number of data columns for each subsequent page.m.Offset = 0

* Calculate no. of pages required to accommodate* all Fys.m.lPages = CEILING(m.FyALen / DATACOLS)

* Referenced in report to maintain cumulative* waste volumes across Fys for each system.m.CumWaste = 0RELEASE WINDOW Status

* Calculate latest Fy that can fit on the* first page of the report.m.lPgFyMax = STR(VAL(FyA(1)) + DATACOLS - 2, 4)

GO TOP

* Loop through the required number of times to* accommodate all Fys of data. Pages are to * be printed even if a treatment system's costs* don't appear until a subsequent page.FOR m.li = 1 TO m.lPages

Function CostTable() creates the table structure that the report is based on. It starts by creating acursor with two columns, "SystemNo" and "CostDesc", to identify each treatment system andcost category in the data set. It then copies the structure of this table to a temporary table usingCOPY STRUCTURE EXTENDED. The routine uses this temporary table to dynamically buildthe required table structure based on the fiscal years in the data set. Next, it populates array FyAwith the list of fiscal years in the data set and loops through each one, defining a columncorresponding to that fiscal year (named FyXXXX, where "XXXX" is a fiscal year). There are afew additional columns defined to support subtotals for various fiscal year ranges. Once allcolumns are defined, a new table, called Costs, is created from this structure. There is now astructure with a column for every fiscal year appearing in the data set.

PopCostTbl() populates the Costs table with the data from the data set. Since the report coversfour levels of cost data per treatment system, PopCostTbl() first appends four records pertreatment system to the Costs table. It then loops through each row in the data set and transfersall cost data to the corresponding row and column in table Costs.

PopulateHdr() creates and populates a treatment system header table with information associatedwith each treatment system. This information is printed as part of the page header for eachtreatment system.

Now you're ready to begin printing the report, so let's divert and take a look at the report form(see Figure 1).

All the information in the upper part of the treatment system page header is straightforward.There's a UDF reference for the field labeled "Primary Waste Type Treated." This UDF simplytakes a waste type as a parameter and returns a string consisting of the corresponding textualdescription to be printed on the report. Using UDFs in the report writer is as simple as that!

Except for the "CostDesc" field reference, there are nothing but UDF references in the detailsection and in the areas immediately adjacent to the detail section! There isn't a single field,memory variable, or constant reference. Most complex reporting requirements won't require sucha heavy dependence on UDFs, but it's nice to know it can be done. In this report, the UDFsdeliver the ability to process and print data for multiple fiscal years of data that can span multiplepages.

I'll now review the UDFs to give you a feel for how they work within the confines of the reportwriter. Don't be too concerned about understanding all of the logic within each UDF -- just try torecognize the flexibility the UDFs offer. Each UDF accepts one numeric parameter representingthe report column from which it is called.

ColLabel() generates the appropriate column label for each column in the report:

FUNCTION ColLabel* Returns the appropriate column label for the* current report column.PARAMETER ColIdxPRIVATE lRetVal, lColfy

* By default, the empty string is returned.m.lRetVal = "" IF m.ColIdx + m.Offset = 1 OR InRange (m.ColIdx)

IF m.ColIdx + m.Offset != 1 m.lColFy = FyA(m.ColIdx + m.Offset - 1) ENDIF

DO CASE CASE m.RollFys AND (m.ColIdx + m.Offset != 1) AND ; m.lColFy = MAXRPTFY AND ; Header.MaxFy >= MAXRPTFY * User requested that Fys beyond MAXRPTFY by * aggregated into one column and the previous * column represented the last Fy to be * individually reported, so return Fy * aggregation column label. m.lRetVal = MAXRPTFY1

CASE (m.ColIdx + m.Offset != 1) AND ; (m.lColFy = Header.MaxFy OR ; (m.lColFy = MAXRPTFY1 AND ; Header.MaxFy >= MAXRPTFY)) * The previous report column was the last * costed Fy for this system or it was the * aggregation column,so print the total cost * column label. m.lRetVal = "Total"

CASE FyA(m.ColIdx + m.Offset) > Header.MaxFy AND ; !(FyA(m.ColIdx + m.Offset) = "95-00" AND ; Header.MaxFy > "2000") * If this column corresponds to an Fy beyond * what's available for this system, don't * print a column header. Check for the * special case of 1995-2000 subtotal column, * ensuring that it is only printed if cost * data goes beyond Fy2000. m.lRetVal = ""

OTHERWISE * If none of the special cases apply, return * the appropriate Fy label. m.lRetVal = FyA(m.ColIdx+Offset) ENDCASE

ENDIF

RETURN m.lRetVal

Shade95_00() determines if the current column is to be shaded. To simplify the report, I've madean assumption that some cost data will be available between fiscal years 1995 and 2000, so thislogic needs only to be applied to the seventh cost column. The UDF is invoked from the PrintWhen clause of each of the shaded boxes defined in this column.

FUNCTION Shade95_00* Determines if the 'Print When' clause of shaded* boxes are to be drawn.PRIVATE lRetVal

IF m.Offset = 0 AND Header.MaxFy > "2000" * We are on the first page of the report for the * current treatment system, and it has costs * extending beyond Fy2000, so the shaded box * must be printed to highlight the calculated sum. m.lRetVal = .T.ELSE m.lRetVal = .F.ENDIF

RETURN m.lRetVal

You could easily place this logic in the Print When clause as an expression for the variousshaded boxes. The drawback: you'd need to duplicate the logic in several places, so if it had to bechanged you'd have to change it everywhere. Using a UDF also saves you the hassle of leafingthrough various dialog boxes for different objects as you search for all references. By using aUDF, you consolidate the logic and isolate potential changes to one function. You therefore takeadvantage of the modular nature of UDFs within the report writer, just as you do within yourapplication code.

FyCost() generates the appropriate cost data for each detail record and fiscal year in the report.

FUNCTION FyCost* Returns the appropriate cost data for the current* report column.PARAMETER ColIdxPRIVATE lRetVal

m.lRetVal = 0

IF m.ColIdx + m.Offset = 1 m.lRetVal = EVAL("Fy" + FyA(m.ColIdx+Offset))ELSE

IF InRange (m.ColIdx) AND ; !(FyA(m.ColIdx + m.Offset - 1) = MAXRPTFY1 AND ; Header.MaxFy < MAXRPTFY)

DO CASE CASE m.RollFys AND ; FyA(m.ColIdx + m.Offset - 1) = MAXRPTFY AND ; Header.MaxFy >= MAXRPTFY * The previous column represented the last * Fy to be individually reported; print * Fy range column label. m.lRetVal = Fy2020P

CASE FyA(m.ColIdx + m.Offset - 1) = ; Header.MaxFy OR ; FyA(m.ColIdx + m.Offset - 1) = MAXRPTFY1 * The previous column represented the last * costed Fy for this system or it was the * aggregation column; print the total cost * column label. m.lRetVal = Total

OTHERWISE * Current column represents data for a * fiscal year of data. Create the field * name using the current column index and * offset. m.lCurrFy = FyA(m.ColIdx+Offset) * Evaluate constructed field name to obtain * cost. m.lRetVal = EVAL("Fy" + m.lCurrFy) ENDCASE

ENDIF

ENDIF

RETURN m.lRetVal

KD0(), EndOps(), and StartOps() (hidden behind KD0) determine if their respective milestones

occur in the current fiscal year. If so, the appropriate indicator is returned for display in thatcolumn. These UDFs are very similar, so I'll show you the source for just one of them:

FUNCTION StartOpsPARAMETER ColIdxPRIVATE lRetVal

IF InFyBounds (m.ColIdx) AND ; FyA(m.ColIdx+Offset) = Header.StartOps AND ; !EMPTY(Header.StartOps) m.lRetVal = "Start"ELSE m.lRetVal = ""ENDIF

RETURN m.lRetVal

Waste() determines if the current column represents data for a fiscal year that falls within theoperating period of the current treatment system. If it does, the function returns the estimatedamount of waste treated in that year (based on the assumption that waste treatment is spreadevenly over a treatment system's operating life).

FUNCTION Waste* Determines if the current column represents data* within the operating period of a treatment* system. If so, the inferred capacity is returned* to indicate the estimated quantity of waste * treated for the year.PARAMETER ColIdxPRIVATE lRetVal

m.lRetVal = 0

IF InFyBounds (m.ColIdx)

IF m.RollFys AND FyA(m.ColIdx+m.Offset) =MAXRPTFY1 AND; Header.EndOps > MAXRPTFY m.lRetVal = (VAL(Header.EndOps) - VAL(MAXRPTFY)) ; * Header.InfCap ELSE

IF BETWEEN(FyA(m.ColIdx+Offset), Header.StartOps, ; Header.EndOps) AND ; !EMPTY(FyA(m.ColIdx+Offset)) m.lRetVal = Header.InfCap ENDIF

ENDIF

ENDIF

RETURN m.lRetVal

CumWaste() maintains a cumulative sum of the waste volume treated each fiscal year within theoperating period of the current treatment system.

FUNCTION CumWaste* Calculates and returns cumulative waste volume* for the current Fy column. When RptOnly is .T.* (for totaling columns), the current cumulative* volume is returned only - it is not updated.PARAMETER ColIdx, RptOnly

IF !m.RptOnly

IF InFyBounds (m.ColIdx)

IF m.ColIdx = 1 AND m.Offset > 0 * We've just started another page for this * treatment system, so restore the cumulative * waste totals from the last page so we can * continue with the cumulative sum. m.CumWaste = Header.CumWaste ENDIF

DO CASE CASE m.RollFys AND ; FyA(m.ColIdx+m.Offset) = MAXRPTFY1 AND ; Header.EndOps > MAXRPTFY * We're on the cost aggregation column and * the operating life ends beyond the fiscal * year identified by MAXRPTFY, so we need * to return the total cumulative volume of * waste to be treated through all years, * which is simply the total waste volume * found in the Header table(there can be * discrepancies with the Mixed Waste * Treated total due to rounding). m.CumWaste = Header.TotWaste

CASE BETWEEN(FyA(m.ColIdx+Offset),Header.StartOps,; Header.EndOps) m.CumWaste = m.CumWaste + Header.InfCap

OTHERWISE m.CumWaste = 0 ENDCASE

IF m.ColIdx = DATACOLS * This is the last column for the page, so * s you must ave the current cumulative waste * total so the cumulative sum can resume on * the next page. REPLACE Header.CumWaste WITH m.CumWaste ENDIF

ELSE m.CumWaste = 0 ENDIF

ENDIF

RETURN C W t

That covers all of the relevant UDFs. Although some of the logic in these UDFs can be a bitawkward to follow, I hope this example helped you better understand and visualize how you canuse UDFs within your reports.

Refer to Figure 2 for two views of the final report output. You might want to try running thereport yourself so you can take a closer look at it.

Conclusion

UDFs provide a mechanism for incorporating relatively complex code directly into your FoxProreport forms, thereby extending the report writer's native capabilities. UDFs also eliminateredundant code by providing an excellent way to consolidate report writer expressions thatappear in multiple objects. The next time you're about to give up on the report writer, seewhether UDFs can provide the extra power and flexibility you need.

John Stepp is the systems manager for MAC Technical Services, an environmental managementconsulting firm. He uses FoxPro extensively to develop custom applications that support complexanalysis. After hours, John is the principal of Micro Integration, a provider of custom applicationsdevelopment services specializing in data modeling and data design. CompuServe 75553,1112.

Get a Better Calculator, Limit Forms, FixVFPs Memory Problems, and MoreJohn V. PetersenGet a Calculator Accessory

I write accounting software and would like to know how to call the calculator from aprogram, use it, then return the value to the program. First, can this be accomplished?Second, if this isn't possible, what alternatives exist?

Desmond L. O'Kelly, CompuServe 75567,320

You could call the calculator accessory in a program by issuing an ACTIVATE WINDOWCalculator command, then query the results of a calculation in the _CALCVALUEenvironmental variable. However, that method isn't readily useable in an application.Unfortunately, like most FoxPro system windows, the calculator window offers little or nocontrol. One reason is that the calculator window is modeless, meaning that when it is activated,control immediately passes back to the next line of code. Even wrapping the call in a UDF won'thelp. The only alternative is to create your own calculator. Fortunately, the work has been donefor you -- there are several contributions in the FOXFORUM on CompuServe. In addition, check

out the third-party products section in the FOXUSER forum for a commercially availablealternative. [Visual FoxPro developers can refer to "A Better Calculator for Visual FoxPro" inour October 1995 issue. -- Ed.]

Limiting Your Forms

Is there an easy way to limit the number of times a form can be created in an application?Sometimes I may want to allow one or two instances only.

The modeless development aspect of Visual FoxPro offers advantages not found in previousversions. Along with the good, however, comes new problems. Allowing your users unlimitedinstances of a form can lead to resource depletion. Unfortunately, however, limiting forminstances in Visual FoxPro isn't without cost. What's needed in this case is a Form class that willdo the checking for us to facilitate limiting the number of instances. The idea centers on loopingthrough the forms collection of the _SCREEN object. (For more background on _SCREEN, seemy article, "Explore the Visual FoxPro _SCREEN Object" in the October 1995 issue ofFoxTalk.)

You need two pieces of information in order to limit instances of a form: the name of the SCX,and how many instances of the form you want to permit.

To determine the SCX on which the form is based, you can use SYS(1271), a function that isobscurely documented in the README.HLP file that comes with the Professional Edition ofVisual FoxPro. By passing the form object reference to SYS(1271), the SCX filename, alongwith its full path, is returned. You can pass the maximum number of allowed instances to the Initmethod of the form class:

First, create a form class with the following code in the Init():

LPARAMETERS tnNumberOfInstancesLOCAL lnCount,lnTally,lcScx,llOKSTORE 0 TO lnCount,lnTally**fetch the SCX file of this formlcScx = SYS(1271,THIS)llOK = .T.

IF TYPE('tnNumberOfInstances') = 'N' tnNumberOfInstances = INT(tnNumberOfInstances)

FOR lnCount = 1 TO _SCREEN.FormCount * since toolbars are included in the forms * collection, we will check the BaseClass property.

IF UPPER(_SCREEN.Forms(lnCount).BaseClass) = 'FORM'

IF SYS(1271,_SCREEN.Forms(lnCount)) = lcScx lnTally = lnTally + 1 ENDIF

ENDIF

ENDFOR

IF lnTally > tnNumberOfInstances =MessageBox('Only '; + ALLTRIM(STR(tnNumberOfInstances)) ; + ' instance(s) is(are) permitted.',16,; 'Limited Instance Form') llOK = .F. ENDIF

ELSE =MESSAGEBOX('Parameter must be numeric') llOK = .F.ENDIF

RETURN llOK

Next, create a form based on this class and add the following code to its Init() Event:

IF !EVALUATE(THIS.Class+'::Init(2)') RETURN .F.ENDIF

Substitute whatever maximum number of instances you want for the "2" in the code sample. Asan alternative to adding the previous code to the form's Init, you could simply DO FORM<formname> WITH <MaxInstances>.

There are many alternatives, including a custom form handler class. However, this will get the

job done nicely and is straightforward and simple.

Work Around VFP's Memory Problems

I seem to be running out of memory when using Visual FoxPro under Windows 95.Specifically, this seems to happen when I use the Database Designer. Is this a bug, and if so,what workarounds exist and when will it be fixed?

Microsoft has acknowledged this problem and it will probably be addressed in a maintenancerelease. But since we can't be certain when the fix will appear, it's important to create someworkarounds. My suggestion consists of both monitoring system resources and optimizingmemory allocation.

One of the most useful utilities to ship with Windows 95 is the resource monitor, which iscontained in a file called RSRCMTR.EXE. Assuming you have installed this utility, you can findit in the root of the Windows 95 directory. When working with VFP, it's a good idea to have thisutility present on your taskbar. It represents a meter of available resources and will clearly showwhen resources are getting low. All three classes of resources are monitored: System, User, andGDI.

When resources get low, try closing any non-essential applications or Visual FoxPro windows; ifthis doesn't resolve the problem, close and restart Visual FoxPro.

A View Problem

On several occasions, I've had to make changes to field names in tables in a database, whenthose field names are also used in local views. Once these changes have been made, Iencounter problems with the views using the affected tables. How can I avert theseproblems and have the views automatically reflect the structure changes of underlyingtables?

Unfortunately, it's not possible for your views to automatically reflect the structure changesmade to associated tables. However, there's a remedy available to prevent you from having tore-create some or all of your view. The idea centers around "hacking" the DBC. Each view in adatabase has a corresponding record in the DBC file. Information regarding the view is stored ina memo field called Property. While much of the information in this memo is binary, the fieldsinvolved in the view are plain text. Being careful not to disturb the binary portions of the memo,change the text portions to reflect the new field names. Finally, close the DBC file and issueMODIFY VIEW <viewname> to make sure all information regarding your view has beenpreserved.

John V. Petersen, MBA is director of FoxPro and Visual FoxPro development for MaxTech, Inc., aconsulting firm based in Northern Virginia specializing in database consulting, project development andmentoring, and training. John, who works out of Philadelphia, is active in the FoxPro community, haswritten for publications, and has spoken at user group meetings and at the 1995 Developer DaysConference. CompuServe 103360,1031.

Use the Windows API to Print to MultiplePrint QueuesRichard Aman (3)

Windows provides a wealth of services for controlling network connections. Richard Aman showshow to use these services in either FoxPro 2.x or Visual FoxPro to easily send a report to anyavailable output device. In the process, you'll learn about Visual FoxPro's new variant ofDECLARE, which allows you to call external DLL routines as if they were native UDFs. You'll alsolearn how to use several useful Windows API routines.

One of the more difficult application types I've implemented is an automatic scheduler. Ascheduler performs procedures at various scheduled and unscheduled times throughout the day.The procedures must perform in unattended mode and make decisions without user input. Alongwith software-related decisions like branching and looping, the procedures must make hardwaredecisions. For example, the software must decide which printer to send output to, which networkconnection to use, when to reset the software, and which users are logged on when.

When I implemented the scheduler in Windows, the main design decision was how to allow thesystem to use different printers in different locations for the various output reports -- key in anorder-entry and work progress-based system like this one. I designed the system to makedecisions based on orders entered, and by the various stages of manufacture that pieces haveachieved.

The Win.INI route

To choose an output printer, I initially used sample code from the Microsoft Developer'sNetwork (MSDN) CD. That sample code had multiple printers installed in Windows, thenmodified the WIN.INI file to change the default printer. The method worked fine, although it hadlimitations. First, it required that all printers to be used be installed in Windows -- a problem ifyou want to use the application on different machines. The second limitation was that Windowshas only a limited number of printer ports. Though it's possible to have several printers assignedto the same port, keeping track of them can get messy. The third limitation is that it takes time tomodify WIN.INI and notify running applications to update themselves with the changes.

The method detailed on the MSDN CD used the Windows API functions GetProfileString() andPutProfileString() to access WIN.INI. This made me wonder what other functions for switchingprinters

might be buried in the Windows API. I started looking through the Microsoft Developer'sNetwork CD and the TechNet CD (both excellent resources) and the Windows API help file thatcomes with Visual FoxPro Professional Edition for other approaches to this problem.

A better way

That's when I came across the WNetGetConnection(), WNetAddConnection(), andWNetCancelConnection() functions. These three Windows API functions combine to give youalmost unlimited programmatic control over the network connections through an application.WNetGetConnection() returns the name of the network connection that a local device is mappedto, or NULL if the device isn't mapped on the network. WNetAddConnection() maps a localdevice to a network connection if it's not already connected. And WNetCancelConnection()disconnects a local device from a network connection. With these three functions, I implementedmultiple printer output through LPT1 alone. I also found two other useful Windows APIfunctions: WNetGetUser(), which returns the user network login ID, and ExitWindows(), whichcan be used to restart Windows from within FoxPro.

My automatic scheduler application scans the orders table at a recurring interval for any neworders. When new orders are found, they're copied to separate temporary tables for printing.Based on the type, orders can print at one of three places in the plant. The print routine is passedthrough three parameters, which tell the routine the name of the temporary table, the report formto use, and where to send output. When the routine processes the print location decision, it callsWNetGetConnection() to check whether LPT1 is connected to a network print queue. If a currentconnection exists, it calls WNetCancelConnection() to disconnect LPT1 from the currentnetwork print queue. It then calls WNetAddConnection() to connect LPT1 to the proper networkprint queue. (To prevent a connection error, you'll need to cancel the existing connection beforecreating a new connection..)

The basics

Before I get to my sample code, I want to quickly cover some basic requirements for using thismethod of printer control and for using any of the Windows API functions (or functions in anyWindows DLL).

If you're using FoxPro 2.6 for Windows, first load in the library file FoxTools.fll, (supplied withFoxPro 2.6). This library file loads with the command SET LIBRARY TO FoxTools. Once thelibrary is loaded, your program has access to a pair of functions called RegFn() and CallFn().RegFn() is used to register Windows API functions to FoxPro. CallFn() is used to call thefunctions previously registered with RegFn(). In Visual FoxPro 3.0, the DECLARE commandreplaces the need for FoxTools.fll and the RegFn() and CallFn() routines . This new DECLAREcommand is also used to register Windows API functions. Once the functions have beenregistered, they're called just like the internal FoxPro functions. However, for backwardcompatibility, FoxTools.fll, RegFn() and CallFn() can still be used in FoxPro 3.0. (Refer to theVisual FoxPro 3.0 help file for more information on the DECLARE command.)

If you're working in FoxPro 2.x and are new to FoxTools/RegFn/CallFn, you can get details from"Use Microsoft Windows Services from FoxPro" by Robert W. Lord (FoxTalk, March 1994).Back issues can be ordered at 800-788-1900. In the meantime, here's a brief rundown of thesecommands and the new Visual FoxPro replacements for them:

Loading the FoxTools.fll library

First, load the FoxTools.fll library for FoxPro 2.6:

SET LIBRARY TO SYS(2004) + 'FoxTools.fll' ADDITIVE

This command line uses the FoxPro function SYS(2004) to get FoxPro's home directory, whichis where FoxTools.fll is installed during normal installation. Also, use the ADDITIVE clause toadd the library to any existing loaded libraries; otherwise, FoxTools.fll will replace any existingloaded libraries.

Using RegFn()

The FoxTools function RegFn() registers a Windows API function with FoxPro. RegFn() takesthree required parameters and one optional parameter. The first parameter is the name of theWindows API function you want to register. The second parameter is a string containing letterdesignations for the types of parameters the Windows API function requires ( `C' for character orstring, `I' for integer, and so forth). The third parameter is a letter designation for the type ofvalue the Windows API function will return to FoxPro. The fourth and optional parameter is thename of the Windows DLL that contains the function you want to register. If you don't includethe DLL name, FoxPro automatically looks in the standard Windows libraries (USER.EXE,KRNL386.EXE, and GDI.EXE in Windows 3.x) to try to find the Windows API function. If thefunction isn't found, an error code is returned. If the function is successfully registered, afunction handle is returned. That handle is then used by CallFn() to access the Windows APIfunction, as I describe later.

Using CallFn()

The FoxTools function CallFn() is used to access a Windows API function from within FoxProonce it has been registered with RegFn(). The parameters passed to CallFn() are the WindowsAPI function handle returned by RegFn(), and the parameters specified in the second parameterin RegFn() when the Windows API function was registered.

Using DECLARE in Visual FoxPro

DECLARE is an enhanced command in Visual FoxPro 3.0. In addition to defining arrays, theDECLARE command also removes the need for using FoxTools to access the Windows APIfunctions. DECLARE allows the application to directly register Windows API functions withFoxPro. Once the functions are registered, they can be called like any other FoxPro internalfunction. The first parameter to the DECLARE command is the Windows API function returntype. The second parameter is the name of the function you're registering. The third parameter isthe DLL containing the Windows API function. The remainder of the parameters are theparameter types that FoxPro will pass to the Windows API function.

The Windows API functions

WNetGetConnection()

WNetGetConnection() is used to retrieve the network connection to which a local device ismapped. The first parameter is a variable containing the name of the local device you want tocheck. The second parameter is a variable initialized to spaces (I use 255) and will be suppliedwith the connection name by WNetGetConnection(). The last parameter is a variable containingthe length of the second parameter (in this case 255). All three parameters need to be passed byreference. After initializing the variables, register the function with FoxPro. This lets FoxProknow that the function will be passed three parameters by reference, two strings and one integer,and the function will return an integer. Ensure the second parameter buffer is empty beforecalling this function, because an error won't clear the buffer and you might get incorrect results.

Here's the syntax for setting up the WNetGetConnection call, first for FoxPro 2.x, then forVisual FoxPro:

* FoxPro 2.xlnGetConn = RegFn('WNetGetConnection','@C@C@I','I')

* Visual FoxProDECLARE INTEGER WNetGetConnection IN win32api ; STRING @, STRING @, INTEGER @

Call the function to return the current connection for the specified device:

* FoxPro 2.xlnRetVal = CallFn(lnGetConn, @lcDeviceName, ; @lcConnName, @lnBuffLen)

* Visual FoxProlnRetVal = WNetGetConnection(@lcDeviceName, ; @lcConnName, @lnBuffLen)

After the call to WNetGetConnection(), the buffer lcConnName will either contain the name ofthe network connection for the specified local device or will be empty if no connection currentlyexists for the local device. It will also be empty if an error occurred. Also, be sure to check thereturn value for any error codes. The Windows API return codes for WNetGetConnection()follow, as found in the Windows SDK:

0 The function was successful.8 The system was out of memory.50 The function was not supported.59 An error occurred on the network.87 The local device name parameter was not a valid localdevice.234 The buffer was too small. (The connection name islonger than the allotted buffer length.)487 The pointer was invalid.

2250 The local device name parameter was not a redirectedlocal device.

WNetAddConnection()

WNetAddConnection() is used to map a local device to a network connection. The firstparameter is the network connection to map the device to. The second parameter is the passwordto use and should be a null string to use the default password. Finally, the third parameter is thelocal device to map. After initializing the variables, register the function with FoxPro. This letsFoxPro know that the function will be passed three parameters as strings, and will return oneinteger:

* FoxPro 2.6lnAddConn = RegFn('WNetAddConnection','CCC','I')

* Visual FoxProDECLARE INTEGER WNetAddConnection IN win32api ; STRING, STRING, STRING

Call the function to map the local device to the network connection:

* FoxPro 2.xlnRetVal = CallFn(lnAddConn,'\\SERVER1\HP4','','LPT1:')

* Visual FoxProlnRetVal = WNetAddConnection('\\SERVER1\HP4','','LPT1:')

This function returns 0 if successful, or an error number for any error that occurs whileattempting to create the network connection. I've listed the Windows API return codes forWNetAddConnection(), which follow, as found in the Windows SDK.

0 The function was successful.5 A security violation occurred.8 The system was out of memory.50 The function was not supported.59 An error occurred on the network.67 The network resource name was invalid.85 The local device was already connected to a remoteresource.86 The password was invalid.487 The pointer was invalid.1200 The local device name was invalid.

WNetCancelConnection()

WNetCancelConnection() is used to remove a network connection mapping from a local device.

This is necessary because the WNetAddConnection() function will return an error if the devicethat you are trying to map is already mapped to a network connection. For this reason, Irecommend checking the network mapping for the local device before attempting to create a newconnection. Also, I recommend releasing any network mapping that exists first. The firstparameter is the local device to cancel the network connection to. The second parameter tellsWindows whether to close any open files, or simply to return an error. The second parametershould be 0 to close open files before disconnecting. After initializing the variables, register thefunction with FoxPro. This lets FoxPro know that the function will be passed two parameters,one string and one integer, and will return one integer.

* FoxPro 2.6lnCancelConn = RegFn('WNetCancelConnection','CI','I')

* Visual FoxProDECLARE INTEGER WNetCancelConnection IN win32api ; STRING, INTEGER

Call the function to cancel the connection for the specified local device:

* FoxPro 2.6lnRetVal = CallFn(lnCancelConn,'LPT1:',0)

* Visual FoxProlnRetVal = WNetCancelConnection('LPT1:',0)

This function returns 0 if successful, or it returns an error number for any error that occurs whileattempting to cancel the network connection. Following are the Windows API return codes forWNetCancelConnection(), as found in the Windows SDK:

0 The function was successful.8 The system was out of memory.50 The function was not supported.59 An error occurred on the network.87 The local device name parameter was not a valid localdevice or network name.487 The pointer was invalid.2250 The local device name parameter was not a redirectedlocal device or currently accessed network

resource.2401 Files were open and the fForce parameter was 0. Theconnection was not canceled.

WNetGetUser()

WNetGetUser() is used to return the network login ID of the machine the application is runningon. The first parameter is a variable containing the local name to return the network login ID for.It should be NULL for the current machine. The second parameter is a variable initialized to

spaces (I use 255 spaces) and will be filled in by WNetGetUser() with the network login ID. Thethird parameter is the length of the second parameter. All three parameters need to be passed inby reference in order for the function to operate correctly. After initializing the variables, registerthe function with FoxPro. This lets FoxPro know that the function will be passed two parametersby reference, one string and one integer, and the function will return one integer. Use NULL forthe first parameter to get the current sign-on name because if the user is signed on more thanonce, the system makes a random choice of which login name to return:

* FoxPro 2.xlnGetUser = RegFn('WNetGetUser','@C@C@I','I')

* Visual FoxProDECLARE INTEGER WNetGetUser IN win32api ; STRING @, STRING @, INTEGER @

Call the function to get the network login ID of the machine:

* FoxPro 2.xlnRetVal = CallFn(lnGetUser,@lcUserID,@lnBuffLen)lnRetVal = WNetGetUser(@lcUserID,@lnBuffLen)

After the call to WNetGetUser(), the buffer lcUserID will contain either the network login ID orwill be empty if the machine isn't logged in to the network. Also, check the return value for anyerror codes. Following are the Windows API return codes for WNetGetUser(), as found in theWindows SDK:

8 The function could not allocate sufficient memoryto complete its operation.

50 This function is not supported.59 A network error occurred.234 The buffer was too small to hold the complete username.487 The pointer is invalid.2202 The user is not logged in; there is no current username.

ExitWindows() and ExitWindowsEx()

ExitWindows() has two uses of interest, based on the first parameter passed in. The firstparameter is a flag to tell Windows to either reboot or exit. The second parameter is reserved andshould be 0. If you pass in a 67 to the first parameter, Windows will close down any runningapplications and exit to the DOS prompt. If you pass in a 66, Windows will close down anyrunning applications and restart Windows. (the function is called ExitWindowsEx() in 32-bitWindows; the first parameter should be 0 to restart Windows.) I use a 66 in the automaticscheduler application . After initializing the variables, register the function with FoxPro. Thislets FoxPro know that the function will be passed two parameters, both integers, and the function

will return one integer:

* FoxPro 2.xlnExitWin = RegFn('ExitWindows','II','I')

* Visual FoxProDECLARE INTEGER ExitWindowsEx IN user32 ; INTEGER, INTEGER

Call the function to restart Windows:

* FoxPro 2.xlnRetVal = CallFn(lnExitWin,66,0)

* Visual FoxProlnRetVal = ExitWindowsEx(0,0)

ExitWindows() and ExitWindowsEx() have only two return codes: 0 or FALSE if an erroroccurred, and 1 or TRUE if the function call was successful. In this use of ExitWindows() orExitWindowsEx(), you probably need only check for a failure return code, since a successful callwould have restarted Windows and FoxPro, meaning the return code value would have beenreleased. But a check for success might be important in other uses.

Why use ExitWindows() or ExitWindowsEx()? I use ExitWindows() because of a "memoryleak" in FoxPro for Windows 2.6, which my scheduler runs under. When FoxPro generates areport, some of the memory used isn't released back to the pool of free memory. Consequently,after a certain number of reports, FoxPro can run out of memory. In my case, problems occurafter approximately 75 work production tickets, each with a bitmap of the item and severaldifferent fonts. To prevent this, I set up FoxPro in the Windows StartUp group with a commandline call to the automatic scheduler. Then, every 15 minutes I call a procedure called ResetWin(included in the accompanying Download file), which closes down FoxPro and restartsWindows. When Windows starts up, it runs FoxPro from the StartUp group, which then restartsthe automatic scheduler, which picks up where it left off -- but with refreshed memory.Everything runs smoothly with this scheme in place.

Presumably, this memory leak has been fixed in Visual FoxPro, but you may still want to useExitWindowsEx() to automatically shut down or restart some application types, perhaps atparticular times of the day when demand is minimal. It often helps to get a "clean slate"periodically for an automated server application on a dedicated workstation, particularly if theapplication has the potential of running unattended for days or weeks.

Listing 1 illustrates my basic method of printer control. Prior to this procedure, the applicationwill have selected the records to be printed, the report form to use, and the network print queuedestination, all of which are passed in through parameters. I first initialize my variables and savethe current library setting. I then make sure the Foxtools library is loaded for version 2.x. Next, I

register the Windows API functions with FoxPro ( using the DECLARE command for version3.0 or the RegFn() for version 2.x ). Then I check for and release any existing connection forLPT1: using WNetGetConnection(). I then set LPT1: to the desired network print queue withWNetAddConnection(). After all is set up properly, I loop through the source table and generatea full-page form for each record (this allows other users to insert print jobs between my frequentmulti-page output). Finally, I make a call to GetNetID() to see if I'm on the automatic scheduler.If so, I reset LPT1: to a default network print queue. I then restore the previous library settingand exit the routine.

The call to the GetNetID UDF deserves a little more explanation. GetNetID() is included in theaccompanying Download file. It calls the Windows WNetGetUser() function to return thenetwork login ID of the machine the application is running on. This can be used for a variety ofthings. In AutoPrnt, I check the network ID with GetNetID() to see if the application printroutine is running on the automatic scheduler machine. If so, I re-map the printer back to thedefault queue so normal output that doesn't need to be specifically redirected can be printed onthe central printer.

Listing 1. The AutoPrnt procedure.

********************************************************* PROCEDURE AutoPrnt********************************************************* Author............: Richard L. Aman*) Description.......: A scaled down version of the print*) : engine for the automatic scheduler* Calling Samples...: DO AutoPrnt WITH cSrcTable,*) : cFormName, cPrinter* Parameter List....: cSrcTable - table containing*) : records to print* : cFormName - name of form to use* : cPrinter - report destination

PROCEDURE AutoPrntPARAMETERS cSrcTable, cFormName, cPrinter

*-- define variablesPRIVATE lcFormName, lcPrinter, lcReport, lcOldPrinter, ; lcOldLibrary, lnAddConn, lnDelConn, lnGetConn, ; lcDeviceName, lcConnName, lnBuffLen, ; llVersion3, lcConnTo, lnRetVal

*-- init variableslcFormName = cFormNamelcPrinter = cPrinterlcReport = lcFormNamelcOldPrinter = ''lcOldLibrary = SET('LIBRARY')lcDeviceName = 'LPT1'lcConnName = SPACE(254)lnBuffLen = LEN(lcConnName)llVersion3 = '3.0' $ VERSION()lcConnTo = ''lnRetVal = 0

*--ensure that foxtools library is loadedIF NOT llVersion3

IF NOT 'FOXTOOLS' $ UPPER( lcOldLibrary ) SET LIBRARY TO SYS( 2004 ) + 'FOXTOOLS.FLL' ADDITIVE ENDIF

ENDIF

*-- register the Windows API functionsIF llVersion3 DECLARE INTEGER WNetAddConnection IN win32API ; STRING, STRING, STRING DECLARE INTEGER WNetCancelConnection IN win32API ; STRING, INTEGER DECLARE INTEGER WNetGetConnection IN win32API ; STRING @, STRING @, INTEGER @ELSE lnAddConn = RegFn('WNetAddConnection','CCC','I')

Things I learned the hard way about using Windows API functions

If the function declaration calls for a value to be passed by reference, you must use a variableand preface it with the "@" symbol. I thought that for the buffer length, I could set a variablewith the length of the buffer, then just pass the variable, but not in this case. I still had to use the"@" symbol.

If you're using the Visual FoxPro calling convention with the DECLARE command, theWindows API function names are case-sensitive.

Always remember to check the return value for any error codes. Unfortunately, I didn't haveenough space in this article to go into detail about handling errors, but at the very least youshould determine what the function returns when it's successful and test for that. Don't forgeahead in your code just assuming that the API function executed successfully.

Conclusion

FoxPro for Windows and Visual FoxPro provide a rich programming language that allows thedeveloper to create applications of amazing power and complexity. However, even with all thecommands included, there are still times when a task either can't be accomplished with nativeFoxPro code, or the overhead associated with the procedure written in native FoxPro causes toogreat a performance hit. When you hit a brick wall in your development and FoxPro just won'tcooperate, take time to browse through the Windows API help file (included with theProfessional Edition of Visual FoxPro and other Microsoft "visual" development products). Youmay find just what you need.

Having easy access to around 75 percent of the Windows API functions (the rest require abstractdata types such as C structures so you have to either be very tricky or write C routines to accessthem), opens up a world of possibilities for developers who do a little research. I hope theseexamples help you, and let you build on what I've presented here. I look forward to comments,questions or suggestions.

Richard Aman is director of software engineering at Loren Industries, a jewelry manufacturing companywith headquarters in Hollywood, Florida. Richard has been developing business solutions inFoxBASE/FoxPro since 1988 and regularly gives presentations at his local Fox User Group.CompuServe 73700,141.

Create Compound Boolean SearchesJim Haentzschel

(4)

One solution to the problem of user-friendly query facilities is limiting the search criteria to valid

values, then translating those values into something the underlying code can use. This article givesideas for implementing such an interface.

How often has a client asked you to write a compound Boolean search routine? For example, sayyour client has a list of customers who order items like jams, jellies, and butter. Furthermore,these items are keywords you attach to each customer so you can track what they order.

What your client needs is a simple report showing what customers have ordered jams ANDjellies AND butter. You might also want a report showing what customers have ordered jams ORjellies OR butter.

It turns out that some of the third-party reporting packages have trouble with compound Booleansearches (especially "AND"). Most of these third-party packages have a way to link a FoxProapplication to the engine to help overcome problems with compound Boolean searches(pre-processing and post-processing). Pure SQL would be very cumbersome (or nearlyimpossible) for the AND case. Therefore, in this article, we'll use just FoxPro and FoxPro'sreport writer. Although I've coded the solution for FoxPro for DOS, the concept is equally usablein all versions of FoxPro, including Visual FoxPro.

I'll show you how to create a Boolean compound search program combining search terms withlogical "ANDs" or "ORs". In this implementation, the user can enter from one to three searchterms, which I like to call "keywords," in a "mover" screen. I use three tables to implement this:Keywords, Customer and CustLink. The Keywords table (see Table 1) is a simple list of validkeywords the user can search for. The Customer table is a typical table containing customer ID,name, and address information. It has a single index tag based on the CustomerID field.

Table 1. The Keywords Table.

KEYWORDS.DBF (Indexed on KEYID, tag name KEY)

Field Name Type PurposeKEYID C(3) Holds a unique ID for the keywordKEYWORD C(20) Holds the keyword text

Typical KEYWORDS records:

KEYID KEYWORD001 Jams002 Jellies003 Preserves

The CustLink table links customers to keywords (see Table 2). Since each customer can havemany keywords and each keyword can appear with many customers, there is a many-to-many(M:M) relationship between keywords and customers.

Table 2. The CustLink table.

CUSTLINK.DBF (Indexed on CUSTOMERID, tag name CUSTOMERID)

Field Name Type PurposeCUSTOMERID C(3) Customer IDKEYID C(3) Keyword ID

Typical CUSTLINK records:

CUSTOMERID KEYID001 001001 003001 004002 001002 006003 002003 005004 002004 004004 005

The programs I'll describe in this article include the following:

BOOLMAIN.PRGMain calling program (includes Boolean search logic)BOOLPICK.PRGProgram that puts up the "mover" screen for keyword entryBOOLPICK.SPRThe "mover" screenBOOLKEY.FRXThe report form.

For this article, I'm not going to explain the mover screen (if you're interested in learning moreabout movers, the full source code is in the accompanying Download file, or see the "Cool Tool"on mover screens in the June 1995 issue). It's important to note, however, that a mover screen isjust one way to enter keywords from a fixed list. In some cases, it might be more appropriate tolet users themselves enter keywords in a text input field.

The main program is BOOLMAIN.PRG. The basic flow of the program is as follows:

1. The user enters from one to three keywords. This is handled by BOOLPICK.PRG andBOOLPICK.SPR.

2. The user selects the "AND" or "OR" radio button to define the search type.

3. The user clicks "OK" to begin the search, which occurs in BOOLMAIN.PRG.

4. The user sees any customers found in the search in a FoxPro report "PREVIEW" screen.

5. If desired, the user can print any results.

The screen that allows the user to pick keywords is shown in Figure 1. As you can see, the userhas already selected three keywords. Notice too that "AND" is the default logical searchexpression. In my experience, customers are generally more interested in AND than OR.

Once the user selects from one to three keywords and presses "OK," control returns toBOOLMAIN.PRG. The mover screen returns an array, laBoolArray[ ], to BOOLMAIN.laBoolArray[ ] is a two-dimensional array For each row, it lists the keyword, and the keyword IDfrom KEYWORDS.DBF.

Handling the AND case

To search for customers who have one to three keywords joined with "AND," the basic idea is tosearch for all customer IDs in the CustLink table with all the keywords listed in laBoolArray[ ]selected. Each customer ID that meets the criteria is then added to another array, laReportArray[], which will then be used to select records from the Customer table to include in the report.

Handling the OR case

The OR case is similar to the AND case, except that instead of selecting customers who matchall the keywords, OR selects customers who match any of the keywords; otherwise, the twocases are identical.

The code in Listing 1 is from BOOLMAIN.PRG. At the start of the listing, the criteria selectionscreen BOOLPICK.SPR has just returned laBoolArray[ ]. The next task is to begin searchingCustLink for matching Customer IDs. You'll notice two DO WHILE loops. The outer DOWHILE loop controls the scanning of records in CustLink. The inner DO WHILE controlslooping though records for a given CustomerID. In other words, the inner loop makes sure eachCustomerID is examined individually.

Listing 1. BOOLMAIN.PRG.

WAIT WINDOW "Searching for matching customers..." NOWAITSELECT custlinkSET ORDER TO CUSTOMERIDGO TOPlnNumKeysFound=0lnReportSize="0"lcOldEx=SET ("EXACT")SET EXACT ON

* Outer Do While loop controls going thru the *entire CUSTLINK.DBF

DO WHILE !EOF("custlink") and !llDone lcCustid = ALLTRIM(custlink.customerid)

DO WHILE ( lcCustid = ; ALLTRIM(custlink.customerid) ) AND ; !llDone IF ( ASCAN(laboolArray, ; ALLTRIM(custlink.keyid) ) !=0 ) * if the user selected "OR" and we just * found a matching keyword, then add this * customer to the report array now. ELSE, * increment counter for later and continue * (if lnBoolType=2, they selected "OR")

lcKeyid=custlink.keyid

IF lnBoolType=2 =AddToReport(lcKeyid, lcCustid) LOCATE REST FOR ; lcCustid # ALLTRIM(custlink.customerid) ELSE lnNumKeysFound = lnNumKeysFound + 1 SKIP IN custlink ENDIF

ELSE SKIP IN custlink ENDIF

IF (EOF("custLink")) SKIP -1 llDone=.T. ENDIF

ENDDO

* Customer just changed. Add customer to * report if this is an AND search and we * found *all* the keywords. IF lnBoolType = 1 AND ; lnNumKeysFound = ALEN(laBoolArray,1) =AddToReport(lcKeyid, lcCustid)

The call to AddToReport() will be made to add this customer to the report if this is an OR search,or if it's an AND search and all the keywords have been found:

PROCEDURE AddToReportPARAMETERS Keyid, Custid * Add customer to report

lnReportSize = lnReportSize + 1 DIMENSION laReportArray[1,lnReportSize] laReportArray[1,lnReportSize] = custid

RETURN

If you have anything to report, the variable lnReportSize will be positive; otherwise, the WAITWINDOW, which follows, will indicate that no matching Customers were found, and theprogram will exit. However, if one or more customers were found, BOOLMAIN builds a stringto display the Boolean search string that will be displayed at the top of the report. A report cursoris then created from laReportArray[]:

* CreateCursor() creates the empty cursor=CreateCursor()lcOldExact=SET("EXACT")SET EXACT OFFSELECT CUSTOMERSET ORDER TO customeridjnNumCols=ALEN(laReportArray,2)

FOR jnCount= 1 to jnNumCols

IF SEEK laReportArray[1,jnCount] SCATTER MEMVAR SELECT curImport APPEND BLANK GATHER MEMVAR SELECT customer ENDIF

ENDFOR

SELECT curImportGO TOP

REPORT FORM BOOLKEY.FRX PREVIEW

Figure 2 shows the report preview screen.

The customer and associated tables in the accompanying Download file are simple andstraightforward. I've used this code on large (real-life) tables with very good throughput. I'm

interested in hearing how this code works for you. I'm also interested in hearing from you if youhave faster or different suggestions for Boolean searches.

If you expect to find a lot of matching customers in your application, you might want to createthe report cursor in advance and add records to it in AddToReport, rather than add to an array of"hits" and build the cursor later. Otherwise, the "hits" array, laReportArray[], could getunwieldy. I designed this demonstration, as I did for tutorial purposes, to separate and simplifythe individual steps.

I hope that you can see from this article that selecting customer records with a compoundBoolean search string is straightforward. There are plenty of places you could add improvementsto my code. For example, you could add another set of radio buttons to let the user select thegrouping on the report (or some other useful application-specific feature). If you're really brave(and have lots of time), you could let the user mix "AND" and "OR" Boolean operators in asingle search expression. Since most users seldom need to do this, I've focused only on oneBoolean operator per search expression.

Jim Haentzschel is president of Hurricane Technology, a firm specializing in database applicationsdevelopment in Visual FoxPro, FoxPro 2.x, and in user training. 703-684-1393, CompuServe 75166,236.

Calling Win32 and 32-bit DLL Functionsfrom FoxPro 2.XRick Strahl

Gain Visual FoxPro-like access to Windows internals through this set of library functions that youcan use in FoxPro 2.x.

With Windows 95 and Windows NT moving into the mainstream, the new 32-bit implementationof the Windows API -- Win32 -- is becoming increasingly important to developers. This versionof the API provides many enhanced operating system features that were previously unavailable.Visual FoxPro, by using the new DECLARE-DLL syntax, has no problem taking advantage ofthe features and functionality provided within Win32, but FoxPro 2.x doesn't support access tothis new 32-bit API since it can't call 32-bit DLLs directly.

Although this lack of support has little effect on FoxPro's internal operation, it does make adifference for some system functionality you might need to provide in your applications. Forexample, access to the system registry requires use of the Win32 API. Several of the systeminformation functions have 16-bit API counterparts that return incorrect values. You have to callthe Win32 versions of those functions if you want reliable results. Finally, Win32 provides ahost of useful functionality that previously required add on libraries or DLLs to accomplish. But

don't feel left out. In this article I'll describe a DLL that bridges the gap between your FoxProprograms and 32-bit DLLs, using an intermediate translation interface called Call32.

What's with Win32?

Both Windows 95 and Windows NT implement their base operating systems service APIs basedon a 32-bit version of the Windows API called Win32. Win32 provides many features that werepreviously left out of the Windows API and many new features that weren't available or wereimplemented differently in Windows 3.1. The main reason for the enhanced functionality is thatWin32 essentially is the programmer's interface to the operating system, replacing the relianceon a separate MS-DOS and BIOS layer. For this reason Win32 provides a wider variety ofservices that previously were provided only through DOS and BIOS function calls, which meantyou had to have low-level access to the hardware and system interrupts using a language such asC.

Win32 has many functions that give you much more control and information about the operatingsystem. For example: Have you ever wanted to set file attributes? Try SetFileAttributes. Howabout retrieving the name of the current computer under Windows 95 or NT? You can't do itwithout GetComputerName in the Win32 API. In fact, many network-specific information keysthat used to be stored in SYSTEM.INI are no longer stored there under NT and Windows 95 andnow need to be retrieved via API calls. How about finding the current, correct operating systemversion? FoxPro's OS() and the 16-bit GetVersion API function return wrong results for bothWindows NT and Windows 95, while the Win32 version returns the correct value. The list couldgo on and on.

The single most important feature that you are likely to need from the Win32 API though is theregistry. The registry is Microsoft's replacement solution for the slow .INI file interface forconfiguration files that store information about system services in a registration database. Theregistry's database approach allows faster access to the system configuration information makingit possible to store a larger number of entries without suffering a performance hit. The registry issupported by 16-bit Windows, but is extremely limited to OLE and file extension registration viathe HKEY_CLASSES_ROOT key, which pretty much makes it useless. Only by using Win32can you retrieve any registry information about the computer and the applications installed on it.The CALL32.DLL I'll present here (available in the accompanying Download file), along withthe examples, provides read and write access to the registry from your FoxPro 2.x applications.

Using CALL32.DLL with FoxPro

A while back I ran into several problems that required the use of Win32 API calls in order tosolve a particular problem. One of the things I needed to do was to figure out the Windowsversion number correctly for all Windows platforms. The other was reading several values fromthe system registry. I couldn't find a way to do either using Win16 API calls or any other methodI knew of at the time. While searching for a solution, I ran into some public domain C DLL codespecific to Visual Basic that allowed VB to call Win32 API functions. With some tweaking ofthe C code and by creating a pair of FoxPro front end routines, I was able to get the DLL to workunder FoxPro 2.x, allowing me to access many Win32 API functions from my programs.

The Call32 DLL interface consists of two DLL functions that work similar to the way Foxtoolsuses RegFn() and CallFn() by registering the DLL function and then calling it with the functionhandle that is retrieved. A function called Declare32 registers functions much like the FoxtoolsRegFn() function does, registering the DLL function by describing the function name and theparameters it uses. The other function named Call32 acts like a router that takes the originalarguments and passes them on to the actual 32-bit DLL, translating the parameter types from 16bit to 32 bit in the process.

When using Call32 from FoxPro you end up having to register the W32 function twice: once forusing the Call32 interface and its registration rules, which are slightly different from Foxtools,and once for the actual function that you end up calling with CallFn(). Because of this doubleregistration of each 32-bit function, it's easy to get tangled up in the registration and callinglogic, which all together requires five separate lines of code for a single 32-bit DLL call. For thisreason I created a pair of front-end routines that simplify the job, leaving the user with only threesimple function calls instead of five that require intimate knowledge of the process.

Let's start with an example. The following code calls the SetFileAttributes function in the Win32API:

* Call WIN32 SetFileAttributes* BOOL SetFileAttributes(lpFileName,dwFileAttributes)

#DEFINE FILE_READONLY1#DEFINE FILE_HIDDEN 2#DEFINE FILE_SYSTEM4#DEFINE FILE_NORMAL128

*** Register 32-bit function with Call32 lhcall32=Reg32("SetFileAttributes",; "Kernel32.dll","pi")

*** get a handle for use with Foxtoolslhsetattr=RegFP("CL","L")

*** Now actually call the 32-bit function*** Note the final parm: Handle from Reg32 call=callfn(lhsetattr,"Test.txt",file_readonly,lhcall32)

The calls the Reg32 and RegFP functions in the previous code are the FoxPro front-end routinesthat simplify the interface to the actual Call32 DLL functions. In a nutshell, these two functionsare responsible for registering the Win32 function, once for the Call32 DLL (Reg32) and once innormal Foxtools fashion using RegFn (RegFP). The final call to the Win32 DLL function isaccomplished by using the familiar Foxtools CallFn function with one important addition: Thefinal parameter must be the handle returned from the Reg32 function. As you can see, twofunction handles are passed to this final call of CallFn() -- the first parameter is to satisfyFoxtools, the last for the Call32 handle.

Reg32 registers the Win32 function with the Call32 interface. It takes the name of the 32-bit

DLL function, the DLL it's contained in, and a list parameter types as parameters. As withFoxtools and RegFn, the parameter types are passed as individual characters, which are similarto, but not the same as, those used by RegFn. Reg32 returns a handle to the 32-bit function,which must be used as the last parameter of the final function call with CallFn(). Here's the fullsyntax for Reg32():

lh32Handle=reg32(<cDLLFunction>,<cDllName>,<cParmTypes>)

Table 1 contains a list of the parameter types.

Table 1. Parameter types.

Parameter DescriptionI 32-bit Integer. FoxPro must pass a Long when actuallyusing or returning parameters of this

type unless the function explicitlyreturns a short integer type such as SHORT or BYTE.P Pointers. Use this type for all string values(even string constants!) and any values that need

to be passed by reference.W Window handles. Use this type whenever you need topass a window handle. It automatically

translates the 16-bit handle to a32-bit handle. You can also use this for passing DWORDtype parameters andother unsigned integers if a plain integer type fails.

Setting up the parameter types in this step is separate from setting up the parameters used byFoxtools and RegFn in the next step. While Reg32 registers the function with the Call32 DLL,RegFP registers the function with FoxPro using the standard Foxtools interface. RegFP expectsthe function parameter and return types in typical Foxtools fashion. Here's the syntax:

lhHandle=RegFP(<cParmTypes>,<cReturnType>)

These are the types you pass and receive from RegFP map to standard Foxtools types, so you canuse Long and Character both by value or by reference by pre-pending the variable name with'@'. Keep in mind that in the Win32 API all integers are 32 bit, so usually you must pass them asLongs, unless the API call explicitly calls for a SHORT or BYTE value. RegFP automaticallyadds a final parameter of type Long to support the required function handle that must be passedas the final parameter when using CallFn().

Once the function is registered, you can now call it using a standard CallFn() call. The syntax forthe call looks like this:

lvResult=CallFn(lhHandle,Parm1,Parm2,ParmN,lh32Handle)

It's very important that the last parameter in the CallFn() statement is the 32-bit function handlethat was retrieved with Reg32 in order for the function to work correctly.

Let's take a look at another example. The following retrieves the correct Windows version underWindows 95 or Windows NT. This code uses a set of additional bit shifting functions I added toCALL32.DLL to make sense of the result returned from the GetVersion API call:

*** Win32 API call - INTEGER GetVersion(Void) lhcall32=reg32("GetVersion","Kernel32.dll","")lhwinversion=RegFP("","L")lnversion=callfn(lhwinversion,lhcall32) *** Large Number shown in Scientific Expression? "Win32 Getversion result:",lnversion *** Now decode the version number with *** bit shifting function provided in CALL32.DLL *** Result is returned in a single LONG*** LoWord contains version*** low byte=Major - HiByte=MinorlhLoword=regfn("LoWord","L","I")lhLoByte=regfn("LoByte","I","I")lhHiByte=regfn("HiByte","I","I")

lnversion=callfn(lhLoWord,lnversion)

lnPlatForm=callfn()lnmajor=callfn(lhLoByte,lnversion)lnminor=callfn(lhHiByte,lnversion)? "Win32 GetVersion (Converted Version): Major ",; lnmajor," - Minor ",lnminor

The code starts by registering the GetVersion API call using Reg32 and specifying theparameters types to pass and return. In this case there's no parameter, so the parameter type ispassed as a null string. Next the call is registered with Foxtools using the RegFP function, whichagain shows no parameters and a return type of Long. The actual API call returns an Integer, butremember that 32-bit integers are Longs to FoxPro and Foxtools. Finally, you make the actualcall to the API function passing the Foxtools handle and the handle returned from Reg32 usingthe CallFn function.

GetVersion returns a large Long that is encoded to contain a Windows platform flag and versioninformation. The low WORD (a word is a 16-bit half of a Long or DWORD value) of thereturned Long contains the version number, of which the low byte (or half a WORD) containsthe major version number with the high byte containing the revision number. In order to decodethe version numbers, you need to do some bit shifting in order to get at the individual versionnumbers. HiWord and LoWord, which take a Long as a parameter, and HiByte and LoByte,which takes an Integer as a parameter, are all contained in the CALL32.DLL file as individualfunctions that you can use for retrieving individual WORDs or BYTEs from a Long or Integervalue. This a common operation for API calls that encode multiple values in a single return valueto conserve memory and keep functions compact.

The previous example is provided for demonstration of Call32's functionality only. If you needto get the Windows version number, it would be much easier to use W32Version provided inCALL32.DLL instead. I created this abstracted custom function so that it returns the Windowsversion number as an integer where the major version is multiplied by 100, adding the revisionnumber to it. To call it use the following code:

PROCEDURE WinVersionlhw32ver=regfn("W32Version","","I","call32.dll")RETURN callFn(lhw32ver)

It returns 400 for Windows 95 and 351 for Windows NT on my machine for example.

How it works

The hard work for the Call32 interface is handled by the code in the C functions containedwithin CALL32.DLL. The Call32 function performs the thunking and function aliasing thatmake it possible to call 32-bit functions. If you're interested in the source code for Call32, it'sincluded in the accompanying Download file. I can't take credit for the actual Call32 code; PeterGolde created the thunking interface and put the code into the public domain.

Calling the Win32 functions from C is pretty messy, and if you're interested in this, take a look atthe abstracted functions that I added in the C program file. W32Version and Read/WriteRegistryboth use the Call32 function to provide their functionality.

On the FoxPro end I created the Reg32 and RegFP functions to reduce the number of linesrequired to make a 32-bit DLL call from five to three and hide the implementation details. Theuser doesn't need to know how it works, but simply pass the parameters. The only rule toremember is that the final CallFn() call must include the 32-bit function handle as the lastparameter.

Here's the code to the Reg32 and RegFP functions:

*****************************************************PROCEDURE Reg32********************* Author: Rick Strahl*** Function: Registers a 32-bit DLL function using*** CALL32.DLL. Thunk interface*** Assume: Foxtools is loaded. Uses CALL32.DLL *** Pass: pcDLLFunction - Name of 32-bit funct*** pcDLLName - DLL container*** pcParmTypes - Parameter types*** I - Integer (FP Long)*** P - Pointer *** Strings,Reference*** W - Handles*** Return: Function handle that must be used to *** CALL32 function*****************************************************PARAMETERS pcdllfunction,pcdllname,pcparmtypesPRIVATE lhcall32,lncall32

lhcall32=regfn("Declare32","CCC","L","CALL32.DLL")lncall32=callfn(lhcall32,pcdllfunction,; pcdllname,pcparmtypes)RETURN lncall32

*****************************************************PROCEDURE RegFP********************* Function: Registers 32-bit DLL function*** with Foxtools! *** Assume: Foxtools loaded, uses CALL32.DLL *** Pass: phFunction - Function handle provided*** via Reg32 *** pcParms - Parameter types*** Return: Function Return value*****************************************************PARAMETER pcparms,pcrettypeRETURN regfn("Call32",pcparms+"L",; pcrettype,"CALL32.DLL")

Accessing the registry

With 32-bit DLL access in place, my next problem was to access the system registry. For thosenot familiar with the system registry, it is accessed by providing a registry root key (HKEYvalues if you bring up the registry editor), a key name (which looks like a path"\SOFTWARE\Microsoft\Windows\CurrentVersion") and an entry ("Version") to work with.Registry paths take on a hierarchical structure very similar to DOS file paths, where the files arerepresented as the actual values stored in an entry. The registry API consists of a set of morethan 10 functions, which allow reading and writing of both keys and node values. I don't expectmy FoxPro 2.x programs to do much writing to the registry, so I created only basic read and

write functions that are described below.

This should be easy now that we can call Win32 API functions, right? Unfortunately, the answerdidn't turn out to be quite so easy because there appear to be some problems with Foxtool's useof large long integer values. FoxPro actually uses signed Longs while the registry uses unsignedintegers, which causes some problems at the extreme end of the number range for these values.Registry access requires use of very large negative values for the root registry keys and severalof these simply wouldn't work when passing them as parameters via CallFn. For example, thekey value for HKEY_LOCAL_MACHINE is -2147483646 (( HKEY ) 0x80000002 ). This valuecauses FoxPro to bomb when calling the RegOpenKey API function directly with CallFn.

I had to use a workaround by creating a wrapper DLL function for RegOpenKey and passing theroot registry keys as strings to the intermediate function. The function decodes the string andpasses the resulting Long value on to the API function, which returns a key handle. Once thisroutine was in place, I was able to create the individual registry access function wrappers usingthe Win32 extensions.

Here's the code for basic registry access wrapper functions:

*****************************************************PROCEDURE OpenKey********************* Author: Rick Strahl*** Function: Opens a registry key before reading *** writing entries.*** Assume: Calls 16-bitRegOpen in CALL32.DLL*** because of limitations in Longs & HKEY*** Pass: tcHkey - "HKEY_..." strings*** tcSubkey - Reg path "\Software\version"*** Return: key handle*****************************************************PARAMETER lchkey,lcsubkeylnrethandle=0lhropen=regfn("RegOpen","CC","L","CALL32.DLL")lnkey=callfn(lhropen,lchkey,lcsubkey)RETURN lnkey

*****************************************************PROCEDURE CloseKey********************* Function: Close registry key.*** Calls 16-bit WinAPI*** Pass: thHandle - Key handle*****************************************************PARAMETER thHandle lhClose=RegFn("RegCloseKey","L","L")RETURN callfn(lhClose,thHandle)

*****************************************************FUNCTION querystr********************* Function: Reads a registry string*** Notes: Also works with Binary types*** as long as it doesn't contain*** NULL values.*** Calls Win32 API - uses CALL32.DLL*** Pass: tnHandle - Key Handle*** tcEntry - Entry to retrieve*** Return: string or "" if empty or "*ERROR*"*****************************************************PARAMETER tnhandle,tcentryPRIVATE lhCall32,lhFP,lcDataBuffer

*** Register function with Win32 and Foxtools lhcall32=; reg32("RegQueryValueEx","ADVAPI32.dll","ipippp")lhfp=regfp("LCL@L@C@L","L")

*** Return buffer to receive valuelcdatabuffer=SPACE(1024)lnsize=LEN(lcdatabuffer)

lntype=1 && REG_SZ lnresult=callfn(lhfp,tnhandle,tcentry,0,@lntype,;

@l d t b ff @l i lh ll32)

Here's an example of how you use these functions (this accesses the Windows 95 registry; youmight have to change the keys to make this work under NT):

SET LIBRARY TO home()+"FOXTOOLS.FLL"SET PROCEDURE TO call32

lcroot="HKEY_LOCAL_MACHINE"lcsubkey="SOFTWARE\Microsoft\Windows\CurrentVersion"

*** Must open the key firstlhreg=openkey(lcroot,lcsubkey)

lcOldSetting=querystr(lhreg,"RegisteredOwner")? "Old Value: " + lcoldsetting

? "Setting Value: ", writestr(lhreg,"RegisteredOwner","New Owner")? "Showing New Value: ",querystr(lhreg,"RegisteredOwner")? "Rewriting old value: ", writestr(lhreg,"RegisteredOwner",lcOldSetting)? "Showing Old Value: ",querystr(lhreg,"RegisteredOwner")

*** Don't forget to close the key=closekey(lhreg)

SET LIBRARY TO

OpenKey calls the custom function I created in CALL32.DLL in order to work around the Longlimitation I mentioned earlier. Both the RegOpenKey and RegCloseKey API calls are availablein the Win16 API, so neither one of these actually needs to access Win32. The QueryStr functionuses the RegQueryValueEx Win32 API function to read a value from the registry. Once a valueis retrieved, the Null is stripped off. If the value can't be found, the function returns "*ERROR*"to differentiate between missing keys/values and an empty ("") value. WriteStr also uses aWin32 API call using the RegSetValueEx Win32 function. If you write to an entry that doesn'texist, it will be created, but only if the key (that is, the directory) exists. The function returns .T.on success or false if it fails.

You can find Integer versions of the Read and Write functions in the accompanying Downloadfile. If you plan on working with the registry extensively you'll likely want to add support foradding and deleting keys and values using RegCreateKey, RegDeleteKey, and RegDeleteEntry. Idon't foresee using the registry in FoxPro 2.x for much more than simple extraction andoccasional value modification, so I haven't bothered to implement them. You can use theseregistry functions I described earlier as templates.

In addition CALL32.DLL includes a simplified ReadRegistry function to retrieve registryvalues. It's easier to use for simple registry reads since you don't have to mess with opening andclosing registry keys or key handles. But keep in mind that if you read or write multiple entrieson the same key, it's faster to open the key then do the reads consecutively, rather than opening

and closing the key for each individual access.

Here's the code:

*****************************************************PROCEDURE rdregstr********************* Author: Rick Strahl*** Function: Reads String value from the Registry*** Assume: Requires CALL32.DLL*** Works on Binary entries as well*** as long as no NULLs are part of val*** Pass: pcRoot - string registry key value*** "HKEY_CLASSES_ROOT"*** "HKEY_CURRENT_USER"*** "HKEY_LOCAL_MACHINE"*** "HKEY_USERS"*** pcSubKey- Subkey 'path'*** pcValue - Actual entry to read*** Return: key value, "", "*ERROR*"*****************************************************PARAMETERS pcroot,pcsubkey,pckey,pnlengthPRIVATE lcresult,lnlength,lnresult

IF PARAMETERS()<3 OR ; TYPE("pcRoot")#"C" OR ; TYPE("pcSubKey")#"C" OR ; TYPE("pcKey")#"C" RETURN "*ERROR*"ENDIF

pnlength=IIF(TYPE("pnLength")="N",pnlength,2048)

lcresult=SPACE(pnlength)

*** long PASCAL _export ReadRegistry*** (LPCSTR cHKey,LPSTR Subkey,*** LPSTR Value,LPSTR Result,INT Length)lhreg=regfn("ReadRegistry","C@C@C@CI","L","call32.dll")lnresult=callfn(lhreg,lcroot,@pcsubkey,; @pckey,@lcresult,pnlength)

*** Return only left of Nulllcresult=TRIM(LEFT(lcresult,AT(CHR(0),lcresult)-1))

IF lnresult#0 RETURN "*ERROR*"ENDIF

RETURN lcresult

You'd read a value like this:

lcroot="HKEY_LOCAL_MACHINE"lcsubkey="SOFTWARE\Microsoft\Windows\CurrentVersion"? rdRegStr(lcRoot,lcSubkey,"RegisteredOwner")

I hope the tools I've presented here are useful to you and help you extend the life of your FoxPro2.x applications on 32-bit Windows platforms.

Rick Strahl is president of West Wind Technologies in Hood River, Oregon (Portland area), a companyproviding Visual FoxPro and FoxPro 2.x programming services, specializing in application add-ins,communications, Internet connectivity, and interfacing FoxPro with external applications and libraries.Rick is author of the popular shareware applications Time Trakker Plus, West Wind E-Mail, and WebConnection. 503-386-2087, e-mail 76427.2363@compuserve.com.

Colorize Your Editor with XILIGHTSWhil Hentzen (5)

Now that Xitech's XILIGHTS is here, there's one less reason to envy those fancy and expensivethird-party editors. Learn how easy it is to add color highlighting to FoxPro's native editor with aninexpensive shareware product that works with both FoxPro for Windows 2.6 and Visual FoxPro!

As big as Microsoft is, even it has limited resources. As a result, a few things didn't make it intothe first release of Visual FoxPro: outer joins, a more robust debugger, object-oriented menus,and an editor with color coding. We may be waiting a while for some of these features, but youcan have color coding in your editor now with Xitech's XILIGHTS library. Perhaps the best partof XILIGHTS is that it works both with FoxPro 2.6 for Windows and Visual FoxPro.

XILIGHTS comes packaged in two FLLs, one for each platform, and a DLL for the VisualFoxPro version that goes into the WINDOWS\SYSTEM directory. In either case, simply placethe FLL in your FoxPro path and issue this command:

SET LIBRARY TO Xilights

From then on, the various components of an editing window will be shown in color. Here are thedefault colors:

Text BlackKeywords (commands and functions) BlueStrings GreenNumeric values Aqua

Constructs (DO WHILE, SCAN) Light PurpleComments MustardCompiler directives Dark PurpleSystem memory variables Red

These colors are used in regular .PRG file editing windows as well as in the snippet editingwindows in FoxPro 2.6, and the code windows in Visual FoxPro.

Customizing XILIGHTS

XILIGHTS looks for a text file called XILIGHTS.INI in your WINDOWS directory. This filecontrols the color scheme used for each component in the editing window. You can manuallymodify the file to specify the color of each component. You can also use the SETCOLORfunction (part of the XILIGHTS library) to set the color of a specific component. The firstparameter to SETCOLOR is the component; the second is the RGB value the component shouldbe set to. For example, the following commands turn keywords to red and comments to blue,respectively:

= SetColor(2,RGB(192,0,0))= SetColor(6,RGB(0,0,192))

For easier color specification, the 2.6 version of XILIGHTS also has an RGB() function thatcorresponds to Visual FoxPro's RGB() function.

Caveats

This is shareware, and the folks at Xitech have decided to incorporate a splash screen thatappears every few minutes to remind you to register. It's annoying, but it doesn't prevent youfrom realistically evaluating the product. Second, in FoxPro 2.6, XILIGHTS expects the name ofFoxPro's main window. If you've changed the name of the window, either via your CONFIG.FPfile or with the MODIFY WINDOW SCREEN command, XILIGHTS will appear not to work.

You can use the FOXTITLE function to identify the name of the main window. For example, Itypically change the title of the window to something like the following:

"We're developing in FoxPro 2.6a!"

Then I can keep track of exactly what I'm using. As a result, XILIGHTS doesn't recognizeFoxPro. After installing XILIGHTS, the following command ensures that XILIGHTS recognizesthe window and functions correctly:

=FOXTITLE( "We're developing in FoxPro 2.6a!")

Where to find XILIGHTS

The file XLGHT105.ZIP contains both 2.6 and 3.0 versions of XILIGHT. It can be found in theaccompanying Download file, as well as in Library 7 of VFOX. Registration is about $45 (U.S.),which you can do through CompuServe's SWREG mechanism. Details are contained in theXILIGHT documentation that comes with the product.

Whil Hentzen is president of Henzenwerke, a Milwaukee, Wisconsin, software development firm thatspecializes in strategic FoxPro-based business applications for Fortune 500 companies. He is also theeditor of FoxTalk. 414-224-7654, fax 414-224-7650, CompuServe 70651,2270.

Sidebar: Book of the MonthRounding out the series of three Microsoft Press books on software development is SteveMaguire's Debugging the Development Process (ISBN 1-55615-650-2). The title says"Debugging" but the key words are "Development Process." In Writing Solid Code, Maguirefocused on the bug hunting process and on programming techniques aimed at their prevention; inthis book, he takes a step back to look at the management process of software development. Heexamines the ultimate goal -- getting quality products out the door on time and within budget --then analyzes the reasons why this rarely happens. Part of the problem, of course, is bugs, butjust as much a factor is the rest of the ingredients that go into software development.

A recurring theme is that many programmers spend too much time working on unnecessarytasks. For example, Maguire stresses that programmers should spend their time programming,not preparing for meetings, attending meetings, writing up follow-ups on meetings, and so forth.Another waste is the infamous "feature creep" -- that syndrome of additional project features thatare added without a corresponding adjustment in the schedule or resources. Of course, it's easy tosay "Don't allow new features to be added" but quite another to put this into practice when theperson making the request is your boss (or the boss's boss!). Maguire makes a number ofpractical suggestions and strategies for dealing with this.

Another repeated advice from the author is that delivering high-quality products on time doesn'trequire super-human efforts and 80-hour weeks as the norm, but rather careful attention to detailand keeping one's eye on the ball. Maguire provides a number of examples of having broughtout-of-control projects back in line without working miracles. Experienced developers will findhis examples engaging and true-to-life. You'll want to put his solutions to work immediately.

Sleeping with the EnemyLes Pinter

I just lost my first bid to a Visual Basic competitor. It was a cold shower, I can tell you. And theworst part is that I'm not entirely sure who's right.

The application is in the communications industry and involves a large database. The output isgraphical; maps are used to display the results of a series of "what-if" scenarios. Doug Blank ofBlank Software sells GeoGraphics (available through HALLoGRAM Software), which caneasily do the map display part. And FoxPro is the only database in town, right?

Wrong. Visual Basic 4.0 comes with a manual that describes how to build database applicationsin VB. Never mind that the same database functionality in FoxPro would pretty much consist of25 commands. With Microsoft's encouragement, Visual Basic programmers are ready to expandinto the database world.

It's always been possible to write database applications in BASIC. I wrote some using DartmouthBASIC on a mainframe almost 30 years ago. But BASIC has had 30 years to become thedatabase programmer's language of choice, and it hasn't made any significant inroads. Until now.

How good is the "access" to data in VB? There are two ways to access your data in VB4. One isthe ACCESS JET Engine; the other is SQL. Both are given equal time in the documentation -- asin "if you don't like the JET engine, try SQL."

My experience with SQL is that about two-thirds of Visual FoxPro projects that start as SQL areconverted to .DBF-based systems, based on complexity and poor performance -- as in "don't dothis unless you absolutely have to." The equivalency of SQL and the JET Engine is correct inthat regard. Recent optimization notwithstanding, VB's JET engine is considerably slower thanFoxPro.

How about interface design? I love Visual FoxPro's object orientation. Jack Hakim at SBTassures me that it's not as robust as it might be, but it clearly saves a huge amount of time in thedevelopment work we're doing for clients, reducing costs by as much as two-thirds! Believe it ornot, VB's not object-oriented. You reinvent the wheel on each screen. How quickly I've becomespoiled. I'm sure that, as of this moment, Visual FoxPro is the most advanced development toolon the market.

So why did Delphi win the Technical Excellence award from PC Magazine for developmentenvironments? It's based on SQL, costs several thousand dollars, and requires coding inPASCAL to build and use classes -- that sexy visual interface doesn't support visual objects. AsAlan Griver likes to say, "I just don't get it."

Why was Access chosen in the same magazine as the best database product available? Ifperformance were the criteria, Access would lose to FoxPro in a minute. Actually, it might takean hour to lose to FoxPro if I remember some of the benchmarks correctly. It's excellent for usersand light development tasks, but in the reports I hear, developers talk about running into one wallafter another. "It's like programming in a maze," a frustrated developer told me.

And yet, each of these products is light-years ahead of the tools we used just a couple of yearsago. And, to tell the truth, any of them will build pretty good software. They're more similar thanthey are different. I can see why a developer might be indifferent about choosing between saving

half the cost by writing an application in VB, or saving 80 percent of the cost by developing inVisual FoxPro.

But, just in case, I'm going to allocate a little of my limitless spare time each week to exploringVB, PowerBuilder, Delphi, and even the lowly Access. Each has some good ideas. And, somefine morning, Microsoft just may bring out Visual FoxPro 4.0 and call it VB5. It's like Vietnam:They could declare a victory and go home.

Les Pinter publishes the Pinter FoxPro Letter in the United States and Russia. 916-582-0595.

Fox World News

Bel Consulting has shipped FoxWord for Visual FoxPro. FoxWord is a report capture modulethat allows the capture of FoxPro report output in a rich text format (.RTF) file, preserving fontand formatting information including lines, colors, and bitmaps. FoxWord can be embedded in aFoxPro application and can be distributed with the application to users. The FoxWord module isinvoked with simple command-line syntax similar to FoxPro's REPORT FORM command. BelConsulting, 172 West 79th St., Suite 9D, New York, NY 10024, 212-799-0123. $99.

Azalea Software is shipping carrick, a new Windows-based encryption tool. carrick works asboth a standalone application or from within any Windows application that accesses a DLL,including most popular databases, word processors, and spreadsheets. With carrick, developerscan implement industrial strength encryption using the new Blowfish algorithm, which allows a448-bit password. DES, the current standard private key encryption algorithm, only allows a56-bit password. Azalea Software Inc., PO Box 16745, Seattle, WA 98116. 800-ENCRYPT, ,carrick@azalea.com. $159 single user, $199 two-copy bundle.

Book of the MonthRounding out the series of three Microsoft Press books on software development is SteveMaguire's Debugging the Development Process (ISBN 1-55615-650-2). The title says"Debugging" but the key words are "Development Process." In Writing Solid Code, Maguirefocused on the bug hunting process and on programming techniques aimed at their prevention; inthis book, he takes a step back to look at the management process of software development. Heexamines the ultimate goal -- getting quality products out the door on time and within budget --then analyzes the reasons why this rarely happens. Part of the problem, of course, is bugs, butjust as much a factor is the rest of the ingredients that go into software development.

A recurring theme is that many programmers spend too much time working on unnecessarytasks. For example, Maguire stresses that programmers should spend their time programming,not preparing for meetings, attending meetings, writing up follow-ups on meetings, and so forth.Another waste is the infamous "feature creep" -- that syndrome of additional project features that

are added without a corresponding adjustment in the schedule or resources. Of course, it's easy tosay "Don't allow new features to be added" but quite another to put this into practice when theperson making the request is your boss (or the boss's boss!). Maguire makes a number ofpractical suggestions and strategies for dealing with this.

Another repeated advice from the author is that delivering high-quality products on time doesn'trequire super-human efforts and 80-hour weeks as the norm, but rather careful attention to detailand keeping one's eye on the ball. Maguire provides a number of examples of having broughtout-of-control projects back in line without working miracles. Experienced developers will findhis examples engaging and true-to-life. You'll want to put his solutions to work immediately.