In case you missed Andy Kramek's blog on this, let me reiterate his point: Southwest Fox 2006 is a must-see conference. The 2004 and 2005 editions were the best conferences in North America, and I'm sure 2006 will easily exceed everyone's expectations. It's organized around a weekend to minimize missing billable hours and feels more like a reunion of friends than a big formal conference. Come hang out with the speakers and other attendees at meals or over drinks and get all your VFP questions answered.
Monday, August 14, 2006
Friday, July 14, 2006
XZip: Free Zipping Utility
In a message on the Universal Thread, Frank Cazabon pointed out a free zipping utility named XZip. I downloaded and installed it today and already like it. It's a 138K DLL COM object, so it must be registered when you deploy it.
It's really easy to use: simply instantiate XStandard.Zip and call the Pack method to add a file or folder to a ZIP file (the file is created if it doesn't exist) or Unpack to extract a file or folder. There are also methods to delete or move files or folders, and one that returns a collection of items in a ZIP file.
Here's some sample code:
#define tFolder 1
#define tFile 2
loZIP = createobject('XStandard.Zip')
loZIP.Pack('SomeFile.txt', 'MyZip.zip')
loItems = loZIP.Contents('MyZip.zip')
for each loItem in loItems
? 'Name:', loItem.Name
? 'Date:', loItem.Date
? 'Path:', loItem.Path
? 'Size:', loItem.Size
? 'Type: ' + iif(loItem.Type = tFolder, 'Folder', 'File')
next
Vacation Time
Today's my last day at work for three weeks. I haven't taken a three-week vacation in four years; it's always been a week here or ten days there. I'm really looking forward to this; I'm mentally pretty tired right now and need a break, plus we have tons of fun planned.
We (my wife Peggy, son Nick, and I) fly to New York City and stay with friends in Connecticut for a couple of days. We then all drive to a beach house in NC, with an overnight stay in Washington, DC, which we've never visited before. After a week swimming, golfing, and eating and drinking too much, we drive back to CT, visiting with Tamar Granor and her husband on the way. We plan to do some sightseeing in NYC for a few days, then head home and take a few days to decompress from our vacation.
Another interesting aspect of this vacation is it's the first one since the last three-weeker that I'm not taking my laptop. I made a deal with my friend Tracey from Dallas, who will also be at the beach house with her family (three families altogether) that I wouldn't bring mine if she doesn't bring hers. She even more rabidly addicted to email than I am, so it'll be harder on her than it will on me. We'll see if she actually arrives sans computer or not, given the temptation of high-speed Internet access in the house.
What started as three friends (Betsy, Tracey, and I) who met in graduate school in Dallas in 1980 has expanded to three families who vacation together almost every year. The great thing is that you could take any two people out of the group, including kids, and they'd have a great time together. That's pretty rare in my experience. Like the saying goes, old friends are the best friends.
Friday, July 07, 2006
SQLXMLBulkLoad Rocks!
For reasons that will become obvious in September (sly grin), I've been working a lot lately on uploading data to SQL Server 2005. There are a variety of mechanisms you can use to do this, but all of them have their shortcomings under certain conditions. For example, using a series of INSERT statements is slow while bulk insert doesn't work with memo fields. While looking for something else in SQL Server Books Online, I happened across a topic on bulk XML loading. After playing with it for a while, it seemed pretty easy to do, so I did some testing on a relatively large file (365,741 records) containing memo fields (which means I couldn't use bulk insert). Using other mechanisms, it took more than two hours to load the data into a SQL Server table. Using bulk XML load, it took 11 minutes. That's a 90% improvement in speed. I love making things faster!
Using bulk XML load is easy: you instantiate SQLXMLBulkLoad.SQLXMLBulkload.4.0, set its ConnectionString property to an OLE DB connection string, set some other properties appropriately (for example, KeepNulls to .T. to insert null rather than default values for missing column values), and call Execute, passing it the name of a schema file and the name of the XML data file. Execute throws an error if there's something wrong with either file, and import errors are logged to a file whose name is in the ErrorLogFile property.
There's one slight wrinkle in working with VFP data: while the CURSORTOXML() function can create a schema file, it needs to be tweaked slightly to work with bulk XML load. Also, DateTime fields have to be flagged with the sql:datatype="dateTime" attribute (the latter was documented, the former took some trial and error to resolve).
Here's a generic program that will load the data from an open VFP cursor into an existing SQL Server table. You can download it from http://www.stonefield.com/articles/other/bulkxmlload.zip.
*==============================================================================
* Function: BulkXMLLoad
* Purpose: Performs a SQL Server bulk XML load
* Author: Doug Hennig
* Last revision: 07/06/2006
* Parameters: tcAlias - the alias of the cursor to export
* tcTable - the name of the table to import into
* tcDatabase - the database the table belongs to
* tcServer - the SQL Server name
* tcUserName - the user name for the connection (optional:
* if it isn't specified, Windows Integrated Security is
* used)
* tcPassword - the password for the connection (optional:
* if it isn't specified, Windows Integrated Security is
* used)
* Returns: an empty string if the bulk load succeeded or the text of
* an error message if it failed
* Environment in: the alias specified in tcAlias must be open
* the specified table and database must exist
* the specified server must be accessible
* there must be enough disk space for the XML files
* Environment out: if an empty string is returned, the data was imported into
* the specified table
*==============================================================================
lparameters tcAlias, ;
tcTable, ;
tcDatabase, ;
tcServer, ;
tcUserName, ;
tcPassword
local lnSelect, ;
lcSchema, ;
lcData, ;
lcReturn, ;
loException as Exception, ;
lcXSD, ;
loBulkLoad
* Create the XML data and schema files.
lnSelect = select()
select (tcAlias)
lcSchema = forceext(tcTable, 'xsd')
lcData = forceext(tcTable, 'xml')
try
cursortoxml(alias(), lcData, 1, 512 + 8, 0, lcSchema)
lcReturn = ''
catch to loException
lcReturn = loException.Message
endtry
* Convert the XSD into a format acceptable by SQL Server. Add the SQL
* namespace, convert the start and end tags to ,
* use the sql:datatype attribute for DateTime fields, and specify the table
* imported into with the sql:relation attribute.
if empty(lcReturn)
lcXSD = filetostr(lcSchema)
lcXSD = strtran(lcXSD, ':xml-msdata">', ;
':xml-msdata" xmlns:sql="urn:schemas-microsoft-com:mapping-schema">')
lcXSD = strtran(lcXSD, 'IsDataSet="true">', ;
'IsDataSet="true" sql:is-constant="1">')
lcXSD = strtran(lcXSD, '<xsd:choice maxOccurs="unbounded">', ;
'<xsd:sequence>')
lcXSD = strtran(lcXSD, '</xsd:choice>', ;
'</xsd:sequence>')
lcXSD = strtran(lcXSD, 'type="xsd:dateTime"', ;
'type="xsd:dateTime" sql:datatype="dateTime"')
lcXSD = strtran(lcXSD, 'minOccurs="0"', ;
'sql:relation="' + lower(tcTable) + '" minOccurs="0"')
strtofile(lcXSD, lcSchema)
* Instantiate the SQLXMLBulkLoad object, set its ConnectionString and other
* properties, and call Execute to perform the bulk import.
try
loBulkLoad = createobject('SQLXMLBulkLoad.SQLXMLBulkload.4.0')
lcConnString = 'Provider=SQLOLEDB.1;Initial Catalog=' + tcDatabase + ;
';Data Source=' + tcServer + ';Persist Security Info=False;'
if empty(tcUserName)
lcConnString = lcConnString + 'Integrated Security=SSPI'
else
lcConnString = lcConnString + 'User ID=' + tcUserName + ;
';Password=' + tcPassword
endif empty(tcUserName)
loBulkLoad.ConnectionString = lcConnString
*** Can set the ErrorLogFile property to the name of a file to write import
*** errors to
loBulkLoad.KeepNulls = .T.
loBulkLoad.Execute(lcSchema, lcData)
lcReturn = ''
catch to loException
lcReturn = loException.Message
endtry
* Clean up.
erase (lcSchema)
erase (lcData)
endif empty(lcReturn)
select (lnSelect)
return lcReturn
Wednesday, June 14, 2006
Updated SFTreeView
I wrote a two-part article in the July and August issues of FoxTalk called "The Mother of all TreeViews" that presented a class library providing all the features you (or at least I) would ever need in a TreeView, including automatically dealing with the twips coordinate system used by the control, handling drag and drop, only loading parent nodes at startup, saving and restoring expanded and selected nodes, and so forth.
Andrew Nickless emailed me today about a bug: navigating the TreeView using the up and down arrows didn't cause the sample form that accompanied the article to refresh and show the properties for the selected node. Fortunately, it was easy to fix:
1. In TreeNodeClick, change the IF statement as shown:
*** if isnull(.oTree.SelectedItem) or toNode.Key <> .oTree.SelectedItem.Key
if isnull(.oTree.SelectedItem) or not toNode.Key == .cCurrentNodeKey
2. Add a new cCurrentNodeKey property
3. Add this line to SelectNode right after the assignment to cCurrentNodeID:
.cCurrentNodeKey = loNode.Key
However, while I was looking into this, I decided to delve into another weird behavior that had bugged me for a while: sometimes clicking on the + to expand a parent node showed the placeholder "Loading..." node rather the actual children. The reason is that the Expand method of the TreeView didn't fire, and that method was responsible for removing the placeholder node and adding the child nodes. The weird thing is that it wasn't consistent; I could only make it happen about 25% of the time, if that. And given that I haven't seen that happen in other applications using a TreeView, it was clearly something I'd done in my class.
Long story short, it turned out (by trial and error, sadly) that code I had in the MouseMove method seemed to be the culprit. I say "seemed" because after removing it, I couldn't reproduce the behavior, but given that it didn't always happen, and of course it never happened when tracing the code, it's darned hard to confirm that it's gone for good. MouseMove called TreeMouseMove (thanks to Steve Black for drilling "events call methods" into my brain), which didn't do anything; I put it there in case I wanted to handle that in a subclass. Turns out I never have, so removing that behavior was no big deal.
Monday, June 05, 2006
DevCon Discount
This will be my 16th DevCon as an attendee (I only missed the first one in Toledo in 1989), 13th as an exhibitor (1993 was the first), and my 10th as a speaker (1997 was the first). As far as I know, I'll be the only person to have attended 16 consecutive DevCons. Anyone know of someone who's attended them all?
For posterity, here are the ones I've attended:
1990 Toledo
1991 Toledo
1992 Phoenix
1993 Orlando
1995 San Diego
1996 Scottsdale
1997 San Diego
1998 Orlando
1999 Palm Springs
2000 Miami
2001 San Diego
2002 Ft. Lauderdale
2003 Palm Springs
2004 Las Vegas
2005 Las Vegas
2006 Phoenix
Thursday, May 25, 2006
RUN and GetShortPathName
Stonefield Query has a function in its developer interface (the Configuration Utility) to generate an InnoSetup script and compile it into a setup executable. The idea is to make it as easy as possible for someone to deploy a custom Stonefield Query application without having to be an installer expert. Generating the script is easy because InnoSetup scripts are just text files. Compiling the script is also easy: use the RUN command to call the InnoSetup compiler, passing it the name of the script file to compile.
I get the location of the compiler from the Windows Registry, at HKEY_CLASSES_ROOT\'InnoSetupScriptFile\Shell\Compile\Command, which on my system gives "D:\Program Files\Inno Setup 5\Compil32.exe" /cc "%1". So, it's a simple matter to read this value into a variable (for example, lcInnoCompiler) and then:
lcInnoCompiler = strtran(lcInnoCompiler, '%1', lcScriptFile)
run /n1 &lcInnoCompiler
This works great on my system and lots of customers' systems. However, one of our sales guys (Jeff Zinnert) reported that he got a "RUN command failed; file does not exist" error when he tried it and so did a customer. We checked that the InnoCompiler was installed correctly, in the place the Registry said it was, and that the script file existed, but to no avail.
While pondering this, I came across a message on the Universal Thread that was completely unrelated but mentioned an issue with "short" (ie. the old DOS 8.3) paths. That reminded me of a similar issue I'd run into several years ago but had forgotten about. A function I'd written years ago calls the Windows API GetShortPathName function to convert a "long" path into a short one:
lparameters tcPath
local lcPath, ;
lnLength, ;
lcBuffer, ;
lnResult
declare integer GetShortPathName in Win32API ;
string @lpszLongPath, string @lpszShortPath, integer cchBuffer
lcPath = tcPath
lnLength = 260
lcBuffer = space(lnLength)
lnResult = GetShortPathName(@lcPath, @lcBuffer, lnLength)
return iif(lnResult = 0, '', left(lcBuffer, lnResult))
I used this function to convert the paths in the lcInnoCompiler variable to short paths and Jeff and the customer no longer get this error.
Once again, the UT saves my butt even though the answer wasn't directly there.
Wednesday, May 17, 2006
Varchar, SET ANSI, and the UT
I've been working on updating Stonefield Query for GoldMine to use the version 3.0 Stonefield Query Developer's Edition as its engine. While doing some testing, I found that one of the queries was taking significantly longer in the new version than the old version: 30 seconds rather than 3 seconds. My first thought is that it's a VFP 9 issue, since the old version uses VFP 8. I remembered from messages on the Universal Thread regarding query performance in VFP 9 that Rushmore, the key to VFP's magical speed in performing queries, is disabled if the code page of a cursor doesn't match the current code page (ie. CPDBF() <> CPCURRENT()). However, that wasn't the case here. In fact, if I ran the old version under VFP 9, it gave the same performance as VFP 8, so that wasn't the problem.
To dig into this further, I searched the Univeral Thread for messages regarding performance, and saw one where Sergey Berezniker (the king of the Universal Thread) mentioned that expressions using ALLTRIM() aren't optimized because Rushmore requires fixed length keys, but that SET ANSI ON would take care of that. Again, that wasn't the case here; the query didn't use ALLTRIM(). However, that got me to thinking: I wonder if the fields involving in the JOIN clause were Varchar fields. Sure enough, they were. Why the difference between the old and new versions? I added the following to the new version so Varchar and Varbinary fields are properly supported:
cursorsetprop('MapBinary', .T., 0)
cursorsetprop('MapVarchar', .T., 0)This means that in the new version, the fields retrieved from the database were Varchar rather than Character as they were in the old version. Because Varchar fields could have different lengths (in fact, they were the same length for the fields involved in this join), Rushmore won't optimize them unless ANSI is set on. So, a quick SET ANSI ON added to the code, and the query is now as fast in the new version.
So, two lessons: sometimes a little change in one part of an application can cause a big change in another part, and search the Universal Thread (or other online sources) before spending hours trying to track down a problem. This one only took me about 15 minutes to fix. Thanks, Sergey!
Wednesday, May 10, 2006
FoxUnit is Cool!
FoxUnit has been available for at least of couple of years, and I've always meant to work with it, but it was one of those things I just didn't get around to. However, after attending Nancy Folsom's session on refactoring at the recent GLGDW, in which she discusses the importance of testing both before and after refactoring to ensure the functionality remains the same, I figured it was time to get to it.
What is FoxUnit? As defined on the FoxUnit Web site, 'FoxUnit is an open-source unit testing framework for Microsoft Visual FoxPro®. It is based on unit testing frameworks as described in Kent Beck's book "Test Driven Development by Example" but takes a more pragmatic approach to unit testing for Visual FoxPro than a more purist xUnit implementation would.'
The idea is to create tests for the various pieces of your application. You then run some or all of the tests prior to releasing a new build (or performing refactoring or checking in the latest update or whenever you want) to ensure everything works correctly. Note that unit testing isn't a replacement for system or acceptance testing, but is one more tool in your professional developer's toolkit.
What got my current interest in FoxUnit started is the need to refactor some code. One method is particular is huge (several hundred lines long) and has been getting more convoluted with time. Before I add some new functionality, I want to refactor it so it's easier to comprehend, easier to maintain, and easier to test. However, I'm scared that during refactoring, I'll drop some functionality or introduce bugs. Hence the need for suite of tests I can run before and after each refactoring task. (Nancy stressed doing refactoring in small steps rather than one huge job. In addition to being easier to do, it's easier to test and less likely to cause broken functionality).
So, I downloaded and installed FoxUnit. My introduction was a little rough -- because I didn't follow instructions and SET PATH to the folder when I installed it, a few things didn't work right. In my opinion, a app should be able to find its own pieces without having to use a crutch like SET PATH, so I made a few minor tweaks (like having the program figure out what directory it's running in and using that path in a few places rather than assuming the files can be found without one). However, once I got past that, it was pretty easy to work with.
Tests are stored in PRGs which are managed by the FoxUnit UI. Each PRG contains a class subclassed from FXUTestCase, the base class FoxUnit test class. Each test is a method of the subclass (although there can be non-test methods too, of course). Although you could write all of the tests for your entire application into a single PRG, that wouldn't be very granular. I prefer one PRG for a single "thing" (module, class, or whatever) I want to test, and then one or more test methods within the PRG to test the functionality of that "thing".
The idea is to write small tests that each test one aspect of one method. For example, if a method accepts a couple of parameters and does different things based on the parameters passed, there should be tests for each parameter being passed or not, different types of bad values for the parameters, different types of good values that result in different behavior, etc. As a result, you'll have a lot of tests for even the simplest application, but each test is small, easy to understand, easy to maintain, and does just one thing. And since the FoxUnit UI manages all of the tests for you, and gives you options to run a single test, all tests in one PRG/class, or all tests, the management of these tests isn't too bad.
Tests are easy to write. Since it's just code in a VFP class, you can add custom properties if necessary. For example, rather than instantiating the object to be tested in every test method, you could create a property of the test class such as oObjectToTest, and in the Setup method (called just before any test is run), instantiate the object into that property: Here's an example, along with a test to ensure the object actually instantiated properly:
define class MyTest as FxuTestCase of FxuTestCase.prg
oObjectToTest = .NULL.
function Setup
This.oObjectToTest = newobject('MyClass', 'MyLibrary.vcx')
endfunc
function TestObjectWasInstantiated
This.AssertNotNull('The object was not instantiated.", This.oObjectToTest)
endfunc
enddefine
Note the use of one of the test methods, AssertNotNull. There are several similar methods available, including AssertEquals and AssertTrue. These methods test that some condition is the way it's expected to be, and if it isn't, the test fails and displays the failure message specified in the assert call.
In addition to instantiating the object, Setup can be used to perform other tasks needed for every test, such as setting up the environment or the object under test. A similar method, TearDown, can be used to perform common tasks after a test has been run, such as restoring the environment.
When you run a test, it either passes, in which case it's shown in green in the FoxUnit UI, or fails, which is shown in red. Tests that haven't been run are shown in gray. Thus it's easy to visually see the results of test runs.
FoxUnit is one of those things that seems like a good idea until you try it, and once you do, you realize it's a great idea. I'm kicking myself for not trying it out a couple of years ago when I first saw Drew Speedie demonstrate it at DevTeach in Montreal. But now that I've worked with it for a while, I'm a firm believer. So, if you haven't started using FoxUnit, do yourself a favor: take out an hour, download it, and create some simple tests. You'll become a believer too.
Friday, May 05, 2006
Debugging Tips
As I mentioned in my post about GLGDW, I missed the Best Practices for Debugging session. Here are a couple of tips I was going to mention. Sorry if someone else mentioned them; I was too busy setting up Rick's system to do my presentation to listen.
1. Write debugging-friendly code.
I used to write code like this:
llReturn = SomeFunction() and SomeOtherFunction() and YetAnotherFunction()I don't do that for two reasons now: it's harder to understand than:
llReturn = SomeFunction()
llReturn = llReturn and SomeOtherFunction()
llReturn = llReturn and YetAnotherFunction()
But more importantly, it's harder to debug. If you step through the code, execution goes into SomeFunction. If you decide you don't need to trace inside that function and want to jump to the next one, you'd think Step Out would do it. Unfortunately, that steps out all the way back to the next line following the llReturn statement. So, the only ways to trace YetAnotherFunction are to go all the way through SomeFunction and SomeOtherFunction or specifically add a SET STEP ON (or set a breakpoint) for YetAnotherFunction.
Note that I don't typically write code like:
llReturn = SomeFunction()
if llReturn
llReturn = SomeOtherFunction()
if llReturn
llReturn = YetAnotherFunction()
endif
endif
That seems (to me) harder to read than the first example.
Here's another example of debugging-unfriendly code I used to write:
SomeVariable = left(SomeFunction(), 5) + substr(SomeOtherFunction(), 2, 1)
The reason that's not friendly is that you can't see what SomeFunction and SomeOtherFunction return unless you actually trace their code. Instead, I now use code like:
SomeVariable1 = SomeFunction()
SomeVariable2 = SomeOtherFunction()
SomeVariable = left(SomeVariable1, 5) + substr(SomeVariable2, 2, 1)
That way, I can trace this code and see what the functions return without having to trace the functions. If something doesn't look right, I can always Set Next Statement back to one of the statements calling a function and Step Into the function to see what went wrong.
2. Instrument Your Application
I, Rod Paddock, Lisa Slater Nicholls, and others have written articles on instrumenting your applications. The idea is to sprinkle calls to a logging object throughout your application. If logging is turned off, nothing happens. If logging is turned on (for example, oLogger.lPerformLogging is .T.), some message is written to some location (a text file, a table, the Windows Event log, etc.) indicating what the application is doing.
I've found this extremely valuable in tracking down problems. While error logs are great, they only give you a snapshot of how things were at the time the error occurred. Sometimes, you need to know how you got there. Also, sometimes the problem the user is experiencing doesn't cause an error but incorrect behavior. By perusing a diagnostic log, you can see all of the steps (the ones you've instrumented, anyway) that lead to the behavior. Yes, you can use SET COVERAGE in your application, but that generates a ton of information, likely a lot more than you need unless you have a really ugly problem that you have no clue about the cause.
Lisa's article is the most recent one I've read, so it's a good starting place. Rod's article is in the September 1997 issue of FoxTalk and mine is in the October 2003 issue of FoxTalk (both articles require you to be subscribers).
