VFP: Not always fixed field structures

Anyone who has worked with VFP data will tell you the DBF is a fixed record length and structures do not have variable length columns, even with the introduction of data types like VarChar. A VarChar is still a fixed length field in the table structure, but internally VFP treats it differently when you work with the data. If you design a numeric field 5,2 you have two significant digits, a decimal place, and two decimal places. Right??

Yes, but only most of the time. {g}

Today I ran into a situation that probably has happened to me only once before in more than 17 years of working with DBF files. I am working on a death march conversion. Part of the conversion does some mathematical calculations with cost. The columns we are fitting data into are N(5,2) so any information 99.99 or less should fit just fine. We ran the conversion and the client was complaining about data being off by 2 cents. We figured there was some rounding problem with the data. My co-worker on the project watched the process in the debugger. The data in the SCATTER NAME object had 78 cents (.78). The next line executed is an INSERT INTO ... NAME and in the column was 80 cents (.80).

I was asked by my co-worker to confirm he was seeing what he thought crazy behavior in VFP. I asked him to BROWSE the table. Sure enough it was true. This is sort of what we saw in a column with two decimal places of precision.

89.54
98.29
05.78
147.8

If it was a rounding problem we would have seen several of the values round to the tenth. What caught my eye was the missing hundredths value in the last and current record. At worse I expected .80, but in this case we saw a three whole digits and one decimal place stuffed into a N(5,2) column.

Argh!

Visual FoxPro is dynamically interpreting the structure a different way. I think it should creat an overflow condition, which immediately sticks out like a sore thumb. Instead, we lost a couple of hours of time tracking this down.

It is not until I enter in a numeric field with more than 5 significant digits that I get "Numeric overflow. Data was lost" (Error 39). Interesting behavior for sure. I will not be reporting this as a bug because it is probably the way DBFs have worked since the beginning of time, but it sure can lead to some expensive debugging sessions if you are not aware of this behavior.

I wrote a short test program to see if this is an issue with a SCATTER NAME object vs. doing a standard APPEND BLANK and a REPLACE, but the behavior is the same.

Hopefully this blog entry will save you some time with this problem down the road.

Where in the world is Rick?

(Somewhat off topic)

So where have I been? Vacationing - yep, a real vacation.

This year I decided I am going to vacation like normal people. You might not know this, but most people actually leave work behind when they go on vacation. So I did this over the last twelve days. Okay, not all twelve days, but for the majority of the twelve days. Heck I was disconnected from the Internet most of every day, and sometimes all day. One day when I reconnected I downloaded over 1000 messages.

Last fall while watching The Amazing Race (television show on CBS here in the USA) we saw some beautiful country when the teams visited Yellowstone. It reminded me how much I have wanted to visit this part of the country. I have wanted to go there ever since seeing my grandfather's slides when I was a young boy. Last month I convinced the family to go. They voted unanimously for this. The other option I put on the table was to visit Louisiana/Mississippi where Katrina hit, and Houston, Texas (for Johnson Space Center). Seriously, I thought seeing the destruction first hand would be something good for us to experience.

At first Therese was a little nervous when I mentioned camping without electricity. Then it hit her, if Rick does not have electricity he has no way to run his computer once his battery runs out. She was sold.

We packed up the family (including my parents and nephew), connected up the camper, and headed west on a 1800 mile journey through Michigan, Indiana, Illinois, Wisconsin, Minnesota, North Dakota, Montana, and Wyoming. We also stopped by Idaho since it was still on my list of states to visit. I am now down to three (Alaska, Louisiana, and Oregon -I plan on visiting two of the three next year).

Yellowstone was spectacular! It surpassed all my expectations except for not experiencing an earthquake. We saw everything from Old Faithful to the Grand Canyon of Yellowstone. We went to the top if a 10, 000+ foot pass to get there, visited each and every entrance, and hiked though geyser infested areas. The Mud Pots were cool. The falls were spectacular. Revisiting the Continental Divide is always fun. The views just kept getting better all week. We saw a ton of Elk, dozens of Bison, and even on Grizzly Bear. The weather was spectacular.

We also visited the bridge where Drew and Brent died last September. It reminded me of how fragile our lives are. Being there also brought more questions to mind than answers on how such a tragedy could happen to these two.

After Yellowstone we visited the Grand Tetons. These are the mountains I have always wanted to visit. I have seen mountains in Colorado, Washington, California, and Tennessee, but these mountains are the best I have seen. Therese said these were the closest mountains she has seen to the Alps.

I took over 400 pictures and an hour of video.

On the way home we headed out the east entrance of Yellowstone and drove to Mt. Rushmore in South Dakota because my nephew has not been there. We also travelled through Iowa, Illinois to get back to Michigan.

We have decided Illinois is the state most unfriendly to travelers with RVs (sorry to all my friends there). They restricted us to 55mph when everyone else can drive 65mph including some trucks, they charge more than triple the tolls over the price of a car, and we had long traffic jams around Chicago. One toll booth was responsible for a 15 mile backup and then they charged us US$8 when cars were charged US$2. Appalling.

In Michigan you can travel on our roads for free. So come to Michigan where vacations are better for drivers of all types.

Wyoming is a fantastic state to visit. I have to rate it as one of my 5 favorite to visit. If you have not visited this part of the country, make sure to add it to the list of places to visit. You will not regret it.

So we drove over 4000 miles, added three new states to our list, stayed in some great hotels to and from Wyoming, and one really crappy Holiday Inn Express in Benton Harbor, Michigan (they stuck us in a smoking room when we reserved non-smoking, and told my parents to come get a plunger to unplug their toilet when they arrived to a plugged toilet). The hotel did not even appear to be near capacity, but they refused to put us in a better room.

Overall - one of the best vacations I have ever had. Now for the fun part, getting back to work. I guess it should not be too hard since I love what I do. Good thing, I have a lot to do! {bg}