Comparing Visual FoxPro and SQL Server 7.0 Datatypes

  • Article

Updated : August 2, 2001

Michael Levy

The most basic tool a database implementor has for enforcing data integrity is the datatype. The datatype of a column controls what kind of data the column can contain. Actually, when you assign a datatype to a column, you specify up to four attributes:

  • The nature of the data that can be placed into the column, such as character or numeric.

  • The maximum length of the column. For instance, a char(9) column can contain a maximum of nine characters. An int column has a fixed length of four bytes.

  • The precision. The precision of a numeric column specifies the maximum number of digits that a column can contain, not including the decimal point. For instance, a decimal(7,2) column can contain a maximum of seven digits. A tinyint has a range of 0 - 255, so the precision of a tinyint is three. It should be noted that SQL Server doesn't lose a position for the decimal point. Also, for numeric datatypes, you can specify the Scale.

  • The Scale determines the maximum number of positions to the right of the decimal point. The Scale must be within the range 0 _ Scale _ Precision. For the decimal column noted previously, the Scale is defined as two. The same column would be defined as N(8,2) in VFP.

Table 1 lists the more common SQL Server 7.0 datatypes.

Table 1. The more common SQL Server 7.0 datatypes.

Datatype

Description

Binary

Fixed length binary data (8000 bytes max)

Bit

Either 0 or 1 (minimum 1 byte)

Char

Fixed length, non-unicode data (8000 bytes max)

Datetime

Date and time data with an accuracy of 3.33 ms. (8 bytes)

Decimal

Fixed precision data with a range of -10^38 to 10^38 -1 (5-17 bytes depending on the precision)

Float

Approximate precision data with a range of -1.79E+308 to 1.79E+308 (4-8 bytes)

Int

Integer data with a range of -2^31 to 2^31 - 1 (4 bytes)

Image

Variable length binary data (2^31 - 1 bytes max)

Money

Currency values with a fixed scale of four (8 bytes)

nChar

Fixed length, unicode data (4000 bytes max)

nVarchar

Variable length, unicode data (4000 bytes max)

nText

Variable length, unicode data (2^30 -1 bytes max)

Real

Approximate precision data with a range of -3.4E+38 to 3.4E+38 (4 bytes)

Smalldatetime

Date and time data with an accuracy of one minute (4 bytes)

Smallint

Integer data with a range of -2^15 to 2^15 - 1 (2 bytes)

SmallMoney

Currency value with a fixed scale of four (4 bytes)

Text

Variable length, non-unicode data (2^31 - 1 bytes max)

Timestamp

A value unique to the database and updated whenever a column changes (8 bytes)

Tinyint

Integer data with a range of 0 to 255 (1 byte)

Uniqueidentifier

A globally unique identifer

Varbinary

Variable length binary data (8000 bytes max)

Varchar

Variable length, non-unicode data (8000 bytes max)

Fixed vs. variable length

SQL Server 7.0's binary and character datatypes fall into two categories, fixed length and varying, or variable length. A fixed length column has a constant length no matter how much data is stored in the column. A char(9) will always be nine bytes. If you were to insert a value that had a length less than the column's, SQL Server would pad the column with spaces. Trying to insert a value that had a length larger than the columns would cause SQL Server to truncate the value so that it would fit within the column.

When you define a varying or variable length column, you specify the maximum length. For instance, a column defined as varchar(9) can contain a maximum of nine characters. Anything less than nine characters won't be padded, but anything more will be truncated.

Datatype mapping

SQL Server has 23 system datatypes. VFP has 13. Obviously, VFP maps multiple SQL Server datatypes to a single VFP datatype. Finding that mapping is easy.

The following is a SQL Server CREATE TABLE statement that will create a table using almost all of the SQL Server datatypes listed in Table 1:

 CREATE TABLE DataTypes (
   t_binary            binary(5) DEFAULT 0x1, 
   t_bit               bit DEFAULT '',
   t_datetime          datetime DEFAULT GETDATE(),
   t_decimal           decimal(5,2) DEFAULT 1.0,
   t_float             float DEFAULT 1.0,
   t_image             image NULL,
   t_int               int DEFAULT 1,
   t_money             money DEFAULT $1.0,
   t_nchar             nchar(5) DEFAULT '', 
   t_ntext             ntext NULL,
   t_nvarchar          nvarchar(5) DEFAULT '',
   t_real              real DEFAULT 1.0,
   t_smalldatetime     smalldatetime DEFAULT GETDATE(),
   t_smallint          smallint DEFAULT 1.0,
   t_smallmoney        smallmoney DEFAULT $1.0,
   t_text              text NULL,
   t_timestamp         timestamp,
   t_tinyint           tinyint DEFAULT 1,
   t_varbinary         varbinary(5) DEFAULT 0x1,
   t_varchar           varchar(5) DEFAULT '',
   t_uniqueidentifier  uniqueidentifier DEFAULT NEWID())

After the table has been created, you use the VFP SQLCOLUMNS() function to see how VFP maps the SQL Server datatypes. SQLCOLUMNS() is a VFP SQL Pass-Through (SPT) function that creates a VFP cursor containing one row for every column in the DataTypes table. Table 2 lists the results of executing SQLCOLUMNS().

 lcConnectString = "Driver=SQLServer;Server=testsql;" + ;
   "Database=pubs;Trusted_Connection=Yes"
 c= SQLSTRINGCONNECT(lcConnectString)
 r=SQLCOLUMNS(c)

Table 2. VFP's translation of SQL Server's datatypes.

Field_name

Field_type

Field_len

Field_dec

T_BINARY

M

4

0

T_BIT

L

1

0

T_DATETIME

T

8

0

T_DECIMAL

N

7

2

T_FLOAT

B

8

2

T_IMAGE

G

4

0

T_INT

I

4

0

T_MONEY

Y

8

4

T_NCHAR

C

5

0

T_NTEXT

C

255

0

T_NVARCHAR

C

5

0

T_REAL

B

8

2

T_SMALLDATETIME

T

8

0

T_SMALLINT

I

4

0

T_SMALLMONEY

Y

8

4

T_TEXT

M

4

0

T_TIMESTAMP

M

4

0

T_TINYINT

I

4

0

T_VARBINARY

M

4

0

T_VARCHAR

C

5

0

T_UNIQUEIDENTIFIER

C

36

0

VFP correctly maps bit, datetime, decimal, float, image, int, money, real, and text datatypes. It also correctly handles smalldatetime, smallint, and smallmoney by promoting them to the full-size VFP datatypes. Pay attention to the way that SQL Server's varchar datatype is handled. Since VFP can't support a variable length row, the easiest thing for VFP to do is use the maximum length that the column could be.

There are some surprises here. The first is how VFP handles the conversion of SQL Server's binary and varbinary datatypes. Both get converted to VFP's memo datatype. VFP does have a character (binary) datatype, but it's really a character with the no codepage translation (NOCPTRANS) option set.

The biggest surprise is the way that VFP handles SQL Server's ntext datatype. VFP maps the ntext datatype to a character(255)! But isn't the maximum length of a VFP character column 254? This seems to be a problem with VFP. I used Visual Basic's Data View window to examine the DataTypes table using both the ODBC driver and the OLE DB provider for SQL Server, and both reported the column as ntext.

Another surprise

Creating a remote view against the DataTypes table brought another surprise. The pure act of creating the view worked correctly:

 CREATE SQL VIEW vdatatypes REMOTE ;
   CONNECTION pubs_conn ;
 AS SELECT * FROM datatypes

Trying to open the vdatatypes view generated an error 1544 -- "DataType property for field `T_ntext' is invalid." Using the DBGETPROP() function to query the datatype of the vdatatypes.t_ntext column returned character(255). But opening the view in the view designer and examining the properties of the t_ntext column with the View Fields Properties dialog box revealed the data mapping to be character(20). By changing the length to 254 (or anything smaller), I was able to open the view.

And another . . .

Now that I could get the view open, I wanted to see how VFP handled the data stored within the Unicode datatypes (see the sidebar "The Unicode Standard" for more on Unicode). I populated the DataTypes table with several rows and retrieved the data using the VFP view. Did you ever notice that no matter how much data is in a row, your eyes always find the funky control characters? Well, terminating both the t_nchar and t_nvarchar fields was one of those funky control characters, and it turned out to be a 0x00 character. Even when I filled the column completely -- using a value like "12345" -- the data was truncated after the fourth position, and the last position was filled with a 0x00.

The t_ntext column didn't exhibit this problem, but I wonder if the 0x00 character is hidden in the missing character in the 255th position.

The UseMemoSize cursor property

This property will affect the datatype mappings. If the SQL Server character column has a length that's greater than the value specified by UseMemoSize, VFP will convert the column to a memo datatype when it retrieves it. You can use the CURSORSETPROP() function to change the UseMemoSize property from its default value of 255.

The disclaimer

I used the following products and versions for this article. I'm listing them because our industry and tools change so fast that the behavior of a product can change from version to version:

  • Visual FoxPro (6.00.8167)

  • SQL Server 7.0 Beta 3 (7.00.517)

  • SQL Server ODBC Driver (3.70.517)

The end

Hopefully, I've provided additional insight into the datatypes that both SQL Server and VFP offer. Having a clear-cut example of how the datatypes of the two systems interact should make it easier to design and predict the data-transformation behavior of your applications.

Michael Levy is a consultant with ISResearch, Inc., a Microsoft Solution Provider and Certified Partner for Learning Solutions. He's a Microsoft Certified Solution Developer and Trainer. Michael specializes in using Visual Studio and SQL Server to solve business problems. mlevy@isresearch.com.

Sidebar: The Unicode Standard

The problem with using a single byte to encode a character is that only 256 characters can be represented. In order to support multiple alphabets, different encoding schemes (code pages) must be created. The Unicode standard was developed with the primary goal of providing one character-encoding scheme for all alphabetic characters, ideographic characters, and symbols. Using a 16-bit encoding scheme enables a total of over 65,000 characters to be encoded. Currently, version 2 of the Unicode standard contains more than 38,000 characters and symbols from the world's languages and mathematics.

SQL Server 7.0 includes three new datatypes for storing Unicode data: nchar, nvarchar, and ntext (the prefix n comes from the SQL 92 standard for National datatypes). The Unicode datatypes are identical to their non-Unicode cousins, except they take up more space and can only store half as much data (4000 bytes vs. 8000 bytes).

For more information about the Unicode Standard, visit the Unicode Consortium Web site at https://www.unicode.org.