VFP Data Types

VFP has two realms that concern datatypes: the DBF table, and the types of variables and .value property. There are 3 differences: objects, numerics, and chars. There is no Object type of DBF field, and DBF has different kinds of numerics: integer, float, currency, etc. as where numeric variables are all stored in floating point. VFP automagically converts back and forth when it moves data between memory and disk. As far as I know this should not concern anyone – it has been this way for years, and no one has ever suggested that there are any pitfalls to be aware of.

Moving memos to memory does the same thing, it ends up as a character variable type. — Mike Helland

Ok, I got bit: Currency’s don’t play nice with Pad L() – you have to use MtoN(). Thanks Bill

DBF DataTypes:
From the VFP help topic Data and Field Types

Data type	Description	Size (bytes)	Range
Character	Any text	1 to 254	Any characters
Varchar	Any text	1 to 254	Any characters
Varbinary	Binary data	1 to 254	Binary data
Date	Chronological data consisting of month, year, and day	8	When using strict date formats, {^0001-01-01}, January 1st, 1 A.D to {^9999-12-31}, December 31st, 9999 A.D.
Date Time	Chronological data consisting of month, year, day, and time	8	{^0001-01-01}, January 1st, 1 A.D to {^9999-12-31}, December 31st, 9999 A.D., plus 00:00:00 a.m. to 11:59:59 p.m.
Numeric	Integers or fractions	1 to 20	– .9999999999E+19 to .9999999999E+20
Float	Integers or fractions	1 to 20	– .9999999999E+19 to .9999999999E+20
Integer	Integers	4	-2,147,483,647 to 2,147,483,647
Double		1 to 20	– .9999999999E+19 to .9999999999E+20
Currency	Monetary amounts	8 bytes	– 922337203685477.5807 to 922337203685477.5807
Logical	Boolean value of true or false	1 byte	True (.T.) or False (.F.)
Memo	Any text	In the DBF 4 bytes/memo In the FPT allocated in chuncks based on SET BLOCKSIZE	Any characters
General	data and host program	In the DBF 4 bytes/memo In the FPT allocated in chuncks based on SET BLOCKSIZE	OLE Documents
Blob	Binary data	In the DBF 4 bytes/memo In the FPT allocated in chuncks based on SET BLOCKSIZE	Binary data

Variant All FoxPro memory variables are basically variant. They can contain any of the Visual FoxPro data types and the null value. Once a value is stored to a variant, the variant assumes the data type of the data it contains.

Nulls

Type() and Var Type() handle NULLs in a different way. Here’s a demonstration (which is quicker than words!):


?Type('m.test')   && Prints: U
?VarType(m.test)  && Prints: U  (and DOESN'T give a var not found error!!!)
m.test = .null.
?Type('m.test')   && Prints: L  (Like an uninitialized parameter)
?VarType(m.test)  && Prints: X
m.test = 1
?Type('m.test')   && Prints: N
?VarType(m.test)  && Prints: N
m.test = .null.
?Type('m.test')   && Prints: N  (It remembers!)
?VarType(m.test)  && Prints: X

create cursor tester ( Fld1 N(5) NULL )
append blank
replace fld1 with .NULL.
?type('tester.fld1')   && Prints: N
?vartype(tester.fld1)  && Prints: X
scatter memvar
?type('m.fld1')        && Prints: N
?vartype(m.fld1)       && Prints: X

The conclusion is, you CAN use Var Type() even if you’re not sure of a variable’s existence, PLUS it protects you from .NULL. variables that had had a value before they were null, but it’s no help in determining the real type of a field containing .NULL.

VarType() is fine for non-existant variables, but it errors when checking the type of a property of a non-existant object. For example;

loObj = NULL
? TYPE("loObj.Name") && Returns U as undefined
? VarType(loObj.Name) && Errors with variable not found

Double Field Type

Use the Double data type instead of Numeric when you need more accuracy, a fixed amount of storage in your table, or true floating-point values. For more information about the specifications for this type, see the tables of Data and Field Types.

In a table, unlike Numeric data, you determine the position of the decimal point when you enter the value.

Numeric and Float data types are identical. The only difference is the spelling of the name of the data type. The Float type was introduced to Fox for compatibility to dBase IV many yearas ago. IN VFP the float type is no different than the numeric type.
Hmmm, I see it is stored in plain ascii text, just like a Numeric (like you said). So it isn’t a floating point at all.
No it is not floating point. It was added so that Fox would not choke on the Float type in a DBF created with dBASE IV. dBASE IV’s Float is a floating point number, but Fox’s is not. – Jim Booth Offsite link to http://www.jamesbooth.com

Fields and Rounding

The Double field type does not round any number stored into it.
The Integer field type takes the integer portion (just like int()) of any value stored in it.
Values stored in Currency, Numeric, and Float fields are rounded to the decimal precision specified when the field was defined.

– Ben Creighton

Default Values
The following table should help with understanding default values of fields if no default is otherwise specified.

Data type	Default Value	Empty()?	IsBlank()?
Double, Integer	0 (zero)	Yes	No
Numeric, Currency, Float	0 zero)	Yes	Yes
Character	all space characters (ASCII 32)	Yes	Yes
Memo, Varchar	“” (the empty string)	Yes	Yes
Logical	.f.	Yes	Yes
General	None	Yes	Yes
Date, Datetime	None, incorrect results (but no errors) if default value is used in date/datetime calculations. Recommended to specify NULL as default	Yes	Yes

– Ben Creighton

Variables:
Character
Numeric
Logical
Date
Datetime
Object

Expressions (remember, Variables are just a simple expression, so this appies to variables too.) These have their own set of data types, which are a bit different than the set of field data types.

It is especially evident with numerics: fields can be D, F, I, N and Y; expressions are all just N.

“Returning the TYPE() or VARTYPE() of integer, numeric, double and float fields all as “N” is a bug. While all are treated the same way when used as memory variables, the storage and precision of integers and doubles are significantly different than the other two. A work-around is to use AFIELDS() to determine which field type you’re working with, and reserve the use of TYPE() for memory variables.” — Hackers Guide

I came across this on the MSDN Library Oct 99 in C:\Program Files\Microsoft Visual Studio\MSDN\99OCT\1033\foxhelp.chm :: /html/condata_and_field_types.htm

What is it talking about a Variant data type in VFP for? I understood all memory variables to be “variant” in that they could contain any data type, but never saw the word “Variant” used in VFP before. Does it have a significance? It seems to have been a lapse of a documentation author who came from VB and was mistaken that “variant” had a meaning of its own. – wgcs

VFP controls can be bound to not only char things, but numerics, dates… (see http://fox.wikis.com/wc.dll?Wiki~VFPDataTypes~VFP for all of them). The .text prop is the char version of .value, regardless of what type .value is. One of the ‘features’ of VFP is loosely typed variables – they assume the type of what ever is being stored to them.

lxVar = “hello world!”
? lxVar
lxVar = 1.5
? lxVar

This includes the .value of an object:

[2001.02.23]
General and Memo Fields

For those who know how hard is to manage general fields, especially writing them to the SQL Server image field or into the ADO object binary data field, following are some tips. Before tips itself I posted here also some problems description so you will understand better how they can help and where to use them.

Memo and General fields in VFP table stores by the same way – 4-byte pointer in the DFB table that follows to the chunk in the fpt file. Both memo field and general field allows any binary data stored there, include chr(0) and many other characters that are not allowed by otehr database systems that manage memo fields (SQL Server text field type tends to corrupt binary data stored in it).

The problem is that VFP allows to work free with memo fields providing a wide set of the string functions (see String Handling), but don’t allows to store memo field content in the image field of the SQL Server using SPT because it mapped to general field in VFP. You can do this however, using mapping field type in the remote view. What about ADO? Another problem is that there are no any command in VFP that allows to store content of general field into the file or get it as a binary string. For example assume we have SQL Server database that stores some binary data in the image field (images). When we run a query from VFP for such field, it will return as a general field. Now, how to save a content of general field to file (image) and show image on form? Again, use of remote views only…

All problems above could be solved very easy. Using the fact that memo and general fields are stored in the VFP table files by the same way, we can easy change a field type… by low-level functions, causing getting content of general field as we use memo field (assumed that genera/memo field is a first field in the table):

Save image to memo field
Filto Str(…) than replace
or append memo … from …

change memo field type to general

ll = fopen(“F:\ttt.dbf”,12)
fseek(ll,43)
fwrite(ll,’G’)
fclose(ll)

now you can save it to image field type

above could be used also to get general field from SQL Server using ADO

And opposite operation:

get data from serevr in general field

…

change general field type to memo

ll = fopen(“F:\ttt.dbf”,12)
fseek(ll,43)
fwrite(ll,’M’)
fclose(ll)

now you can read it as a text!
use this way also to store general field on the server using ADO

There is also assumption that working with general fields should be avoided at all cost. Its very wrong assumption. General field in VFP provides such functionality that is unique – it does not exists in any other database systems. Well, you can assume it is dead, but look to following example.
Say, you require to print a MS Graph chart. Well, its easy to put a chart to the form control and populate it and design it using its objects model and properties. Now, what if you need to include it into VFP report? Well, the solution here is only to use General field. However, little of programmers use that. Why? The simple reason is that you cannot use control’s object model to edit chart in the general field. Well, you cannot do this directly. However, you can make an invisible form, put OLEBoundControl on it, bind it to your general field with chart and using “MyForm.OleBoundControl1.Object…” syntax you can edit enything you want in that chart! The greatest thing is that changes by such way are really stored in the general field and could be used in report. So now we can make an empty chart in the general field, change it as we want by useing OLEBoundControl on the form that is outside of visible portion of VFP main window, than show it in report… Pretty COOL! Now, using this approach, you can prepare for printing any OLE Object by this way and print it! For eample, this way RTF content could be easy printed in the VFP report using Word OLE object in general field. Just replicate it for all records and change each general field to store RTF content for each record.

The same applies to all other OLE controls. You can use Object property of the OLE Bound control to edit properties of any object in it across all objects tree of OLE object. You can do this for MS Word documents, for example. And all changes are stored in the general field where you can use it again and again.