Comparing VFP and SQL Server 7.0 Datatypes

• 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 Technical Education Center. 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 >http://www.unicode.org.