Wiki Home

VFP Data Types


Namespace: WIN_COM_API
Let's talk about data types here

Will someone finish the table please? I am out of time right now. thanks -- ?CFK

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 typeDescriptionSize (bytes)Range
CharacterAny text1 to 254Any characters
VarcharAny text1 to 254Any characters
VarbinaryBinary data1 to 254Binary data
DateChronological data consisting of month, year, and day 8When using strict date formats, {^0001-01-01}, January 1st, 1 A.D to {^9999-12-31}, December 31st, 9999 A.D.
Date TimeChronological 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.
NumericIntegers or fractions1 to 20- .9999999999E+19 to .9999999999E+20
FloatIntegers or fractions1 to 20- .9999999999E+19 to .9999999999E+20
IntegerIntegers4 -2,147,483,647 to 2,147,483,647
Double 1 to 20- .9999999999E+19 to .9999999999E+20
CurrencyMonetary amounts8 bytes- 922337203685477.5807 to 922337203685477.5807
LogicalBoolean value of true or false1 byteTrue (.T.) or False (.F.)
MemoAny text In the DBF 4 bytes/memo
In the FPT allocated in chuncks based on SET BLOCKSIZE
Any characters
Generaldata and host program In the DBF 4 bytes/memo
In the FPT allocated in chuncks based on SET BLOCKSIZE
OLE Documents
BlobBinary 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 BoothOffsite link to http://www.jamesbooth.com

Fields and Rounding - Ben Creighton
Default Values
The following table should help with understanding default values of fields if no default is otherwise specified.
Data typeDefault ValueEmpty()?IsBlank()?
Double, Integer0 (zero)YesNo
Numeric, Currency, Float0 zero)YesYes
Characterall space characters (ASCII 32)YesYes
Memo, Varchar"" (the empty string)YesYes
Logical.f.YesYes
GeneralNoneYesYes
Date, DatetimeNone, incorrect results (but no errors) if default value is used in date/datetime calculations. Recommended to specify NULL as defaultYesYes
- 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):
ll = fopen("F:\ttt.dbf",12)
fseek(ll,43)
fwrite(ll,'G')
fclose(ll)


And opposite operation:
... ll = fopen("F:\ttt.dbf",12)
fseek(ll,43)
fwrite(ll,'M')
fclose(ll)
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.
See also VFP Version Data Type Concordance
Contributors: CFK, Jim BoothOffsite link to http://www.jamesbooth.com
, wgcs, Vlad Grynchyshyn
Category Data, Category Client / Server, Category ADO
( Topic last updated: 2005.12.29 06:50:27 PM )