Chapter 9
New Data and Index Types
It’s been a while since VFP had any new data or index types. VFP 9 adds three new data types and a new index type. The new data types make it easier to work with other database engines that already support these data types. The new binary index improves Rushmore optimization for logical conditions such as DELETED().
VFP 3 added several new data types to the product—Double, Currency, Integer, and DateTime—and two new index types, Candidate and Primary. However, since then, the only change to data types was the addition of the auto-incrementing Integer field in VFP 8, which isn’t really a new data type.
Because some other database engines, such as SQL Server, support a wider range of data types, VFP has to map from those data types to its own smaller set. Sometimes, the mapping isn’t perfect. VFP 9 helps with this problem by adding three new data types.
Varchar
The first new data type is Varchar. Varchar isn’t really a new data type; it’s essentially Character but not padded with spaces. The single character abbreviation for Varchar is “V.”
Because the DBF structure hasn’t changed significantly (although the new data types have some impact on the structure; see the “How the new data types affect DBF files” section later in this chapter), Varchar fields are actually stored as fixed length fields. However, when you access a Varchar field, the value appears to be trimmed rather than padded with spaces to the length of the field.
Here’s an example, taken from TestVarchar.PRG, which creates a cursor with a Varchar field and shows how it differs from Character:
create cursor Test (CField C(20), VField V(20))
insert into Test values ('Fox Rocks', 'Fox Rocks')
insert into Test values ('Fox Rocks ', 'Fox Rocks ')
go top
? len(CField), len(VField) && displays 20 and 9
skip
? len(CField), len(VField) && displays 20 and 12
Notice that the second record includes the three spaces specified in the Varchar field, since spaces specifically included in the value stored in the field are considered significant.
Varchar was added to VFP 9 to provide better support for other databases, including SQL Server, that support this data type. For example, in VFP 8, if you create a remote view from a SQL Server table containing Varchar fields, and then use TABLEUPDATE() to write changes back to SQL Server, the Varchar fields in the updated records are padded with spaces because those spaces exist in the VFP 8 view. Doing the same thing in VFP 9 gives the desired results: the Varchar fields are not padded with spaces.
TestVarcharWithSQLServer.PRG demonstrates this. It opens a view in the Test database based on the Customers table in the SQL Server Northwind sample database. The CompanyName field is defined in that table as nvarchar, but the view defines it as Character. Updating the table forces the spaces padding the field into the database. Changing the data type of that field in the view to Varchar and updating the table again removes the extra spaces.
* Connect to the SQL Server Northwind database. Change the connection string as
* necessary.
lnHandle = sqlstringconnect('driver=SQL Server;server=(local);' + ;
'Database=Northwind;trusted_connection=yes;')
if lnHandle < 1
return
endif lnHandle < 1
* Open the sample database.
open database Test
* Ensure the CompanyName field in the CustomerView view is Character, then open
* the view and display the contents of CompanyName in the first record.
dbsetprop('CustomerView.CompanyName', 'Field', 'DataType', 'C(40)')
use CustomerView connstring (lnHandle)
messagebox('CompanyName in the first record is ' + CompanyName + ;
', which is ' + transform(len(CompanyName)) + ' characters.')
* Update the record and save it.
replace CompanyName with trim(CompanyName)
tableupdate()
use
* Now do it again with CompanyName defined as Varchar. Show that the first
* record has extra spaces because of the change we just saved.
dbsetprop('CustomerView.CompanyName', 'Field', 'DataType', 'V(40)')
use CustomerView connstring (lnHandle)
messagebox('CompanyName in the first record is ' + CompanyName + ;
', which is ' + transform(len(CompanyName)) + ' characters.')
* Update the record and save it.
replace CompanyName with trim(CompanyName)
tableupdate()
requery()
messagebox('CompanyName in the first record is ' + CompanyName + ;
', which is ' + transform(len(CompanyName)) + ' characters.')
* Clean up and exit.
sqldisconnect(lnHandle)
close databases all
Of course, just because Varchar was added to support other databases doesn’t mean you can’t use it in native VFP tables. However, note that using Varchar makes a table inaccessible in earlier versions of VFP; see the “How the new data types affect DBF files” section later in this chapter for details.
Here are some details about Varchar:
· Varchar can be used the same way Character can: you can index on it (the index keys are padded with spaces to the length of the field, because index keys must be a fixed length), it accepts null values, and Varchar fields have a limit of 254 characters in a table or cursor.
· Like Character fields, you can define a field in the Table Designer as Varchar (Binary), which is the same thing as specifying NOCPTRANS in a CREATE TABLE/CURSOR command: no code page translation is done on that field.
· TYPE() and VARTYPE() return “C” for Varchar fields and Varchar values stored to memory variables. This is because Varchar isn’t really a different data type but just a different way of dealing with character values. DISPLAY/LIST STRUCTURE and AFIELDS() do indicate “Varchar” and “V,” respectively, for Varchar fields.
· Varchar has priority over Character when they are mixed. For example:
select CField + VField as NewField from Test into cursor Test2
display structure && NewField is Varchar
· LIKE expressions with trailing spaces in the value are only partially Rushmore optimizable:
index on VField tag VField
index on deleted() tag Deleted
sys(3054, 12)
select * from Test where VField like 'Fox Rocks%' into cursor Test2
&& displays full optimization
? _tally && displays 2
select * from Test where VField like 'Fox Rocks %' into cursor Test2
&& displays partial optimization
? _tally && displays 1
· By default, text boxes pad values with spaces to the maximum width of the field. To suppress that behavior, add “F” to the Format property of the Textbox. You should also set MaxLength to the width of the field to ensure the user can enter the maximum number of characters if necessary. To see an example of this, run TestVarcharInTextbox.SCX, type something like “test” in both text boxes, and click on the Results button. Because the second text box has “F” in its Format property, its content is trimmed while the content of the first one is not.
The Developer Download files for this chapter, available at www.hentzenwerke.com, include
TestVarchar.PRG, TestVarcharWithSQLServer.PRG, Test.DBC, and
TestVarcharInTextbox.SCX. Change the connection string in the
first line of code of TestVarcharWithSQLServer.PRG as necessary
to connect to the Northwind database on your SQL Server.
Varbinary
Varbinary really is a new data type: it contains binary values. Like Varchar, Varbinary fields are not padded to the maximum size of the field. Microsoft added Varbinary to VFP 9 for the same reason it added Varchar: better support of other databases. However, they can also be used for binary values such as timestamps or GUIDs in native VFP tables. The single character abbreviation for Varbinary is “Q.”
Values are stored into Varbinary fields using a new notation: 0h (a zero followed by the letter “h”) followed by a series of hexadecimal bytes. The value is not enclosed in quotes. Here’s an example, taken from TestVarbinary.PRG, which shows the use of Varbinary:
create cursor Test (Field1 Q(20))
insert into Test values (0h466F7820526F636B73)
&& the binary representation of the string "Fox Rocks"
? vartype(Field1) && displays "Q"
? len(Field1) && displays 9
? Field1 && displays 0h466F7820526F636B73
Here are some details about Varbinary:
· Varbinary has similar features to Varchar: you can index on it (the index keys are padded with binary zeros to the length of the field, because index keys must be a fixed length), it accepts null values, and Varbinary fields have a limit of 254 characters in a table or cursor.
· One difference between Varbinary and Varchar is MACHINE is the only collate sequence allowed for indexes on Varbinary fields; using any other sequence results in an “invalid collation sequence” error.
· Another difference is the SQL LIKE operator and the LIKE(), LIKEC(), BINTOC(), and CTOBIN() functions aren’t permitted on Varbinary values.
· You can SEEK() on the binary or character representation of a Varbinary field. For example, SEEK 0h466F7820526F636B73 and SEEK ‘Fox Rocks’ will both find the same record.
· No code page translation is performed on Varbinary fields, so there is no Varbinary (Binary) choice in the Table Designer and NOCPTRANS isn’t required in a CREATE TABLE/CURSOR command.
Although their names are similar, Varchar (Binary) is not the same as Varbinary. The former holds non-padded character values that are not code page translated while the latter holds binary data.
· TYPE() and VARTYPE() return “Q” for Varbinary fields and Varbinary values stored to memory variables.
· The data type of the result of concatenating Varbinary and Character (or Varchar) values is the first value. For example, continuing with the previous sample code:
? "It's true that " + Field1 && displays "It's true that Fox Rocks"
? Field1 + " tonight" && displays 0h466F7820526F636B7320746F6E69676874
You can use this behavior to convert between Character and Varbinary. The first line of the following code converts Varbinary to Character while the second converts Character to Varbinary.
? '' + 0h466F7820526F636B73 && displays "Fox Rocks"
? substr(0h00 + "Fox Rocks", 2) && displays 0h466F7820526F636B73
Of course, you can also use the new CAST() function discussed in Chapter 14, “Language Improvements,” to convert between Character and Varbinary.
· As with Character, comparing Varbinary values is sensitive to the setting of EXACT. SET EXACT ON means the values are compared byte-for-byte, the shorter of the two expressions is zero-padded to the length of the longer, and trailing zeros are ignored. SET EXACT OFF means the comparison stops at the end of the expression on the right side. Use == for an exact comparison, including binary zeros.
· Comparison between Varbinary and Character or Varchar values depends on the order of the values. If the Varbinary value is on the left side of the comparison operator, trailing binary zeros are ignored in the Varbinary value but trailing spaces in the Character value are significant. If the Varbinary value is on the right, trailing spaces are ignored in the Character value, but trailing binary zeros in the Varbinary value are significant. For example, continuing with the previous sample code:
? Field1 = 'Fox Rocks' && displays .T.
? Field1 + 0h00 = 'Fox Rocks' && displays .T.
? Field1 = 'Fox Rocks ' && displays .F.
? 'Fox Rocks' = Field1 && displays .T.
? 'Fox Rocks ' = Field1 && displays .T.
? 'Fox Rocks' = Field1 + 0h00 && displays .F.
· Adding “F” to the Format property of a Textbox bound to a Varbinary field prevents the value entered by the user from being padded with binary zeros. You can also use “H” in InputMask to prevent non-hexadecimal characters from being entered in the specified position.
· The TRIM() functions—TRIM(), RTRIM(), LTRIM(), and ALLTRIM()—remove leading and/or trailing zeros from Varbinary values.
· ALINES() removes trailing binary zeros when .T. or 1 is specified for the third parameter. Also, if the parse characters aren’t specified, ALINES() breaks lines at 0h0A values (line feed in hexadecimal).
· TRANSFORM() returns the character representation of binary values without the 0h. For example, TRANSFORM(0hA0A1A2) returns “A0A1A2.”
· ISBLANK() and EMPTY() return .T. when the field is empty or contains only binary zeros.
· With the exception of BITLSHIFT() and BITRSHIFT(), the BIT functions support Varbinary values. All of the values passed to BITAND(), BITOR(), and BITXOR() must be Varbinary values if any of them are. BITCLEAR(), BITNOT(), and BITSET() have new nStartBit and nBitCount parameters that indicate the range of bits the operation applies to. Leaving these parameters out means the operation applies to all bits, while specifying only nStartBit means the operation applies to the specified bit only.
The Developer Download files for this chapter, available at www.hentzenwerke.com, include TestVarbinary.PRG.
Blob
Blob is like a cross between Memo and Varbinary: it stores binary data in an FPT file. As with the other data types, Microsoft added Blob to VFP 9 to provide better support for other databases. However, as you’ll soon see, they have great uses even with native VFP tables. The single character abbreviation for Blob is “W.”
Here are some details about Blob:
· Blob has similar features to Memo: you can’t index on it, it accepts null values, and Blob fields have a limit of 2 GB of data in a table or cursor (of course, the total size of FPT files is still 2 GB). In a BROWSE window or grid, a Blob field appears as “blob” if it’s empty or “Blob” if not.
· You can use an edit box or MODIFY MEMO to display the contents of a Blob field. The binary data displays as hexadecimal values without the leading 0h. However, the contents are read-only. The values in Blob fields must be modified programmatically, such as with REPLACE or GATHER.
· As with Varbinary, no code page translation is performed on Blob fields.
· TYPE() and VARTYPE() return “Q,” not “W” as you may expect, for Blob fields and Blob values stored to memory variables. DISPLAY/LIST STRUCTURE and AFIELDS() do indicate “Blob” and “W,” respectively, for Blob fields.
· TRIM(), RTRIM(), LTRIM(), ALLTRIM(), ALINES(), TRANSFORM(), ISBLANK(), and EMPTY() work the same as they do with Varbinary fields.
· The SQL LIKE operator, LIKE(), LIKEC(), BINTOC(), CTOBIN(), and APPEND FROM don’t support Blob fields.
· Blob fields can be a replacement for General fields that contain images. General fields suffer from many problems: hard to use, hard to update, huge size, and so forth. Instead of storing images in a General field, which requires a file association to an ActiveX server, store them in a Blob field. They’re easier to update; rather than APPEND GENERAL, you can use something like the following to pull an image into a Blob field:
replace Picture with filetostr('BobJones.gif') in Employees
To display an image stored in a Blob field on a form, simply set the new PictureVal property of an Image control to the contents of the Blob field:
Thisform.imgEmployee.PictureVal = Employees.Picture
To see a demo of this, run BlobDemo.SCX. It provides a simple picture viewer form. Its Load method creates a cursor of images using the following code:
create cursor TEST (FIELD1 Blob)
insert into TEST values (filetostr(home() + 'FOX.BMP'))
insert into TEST values (filetostr(home() + 'WIZARDS\BANDRPT.BMP'))
insert into TEST values (filetostr(home() + 'WIZARDS\WIZFLAX.BMP'))
insert into TEST values (filetostr(home() + 'WIZARDS\WIZSTONE.BMP'))
insert into TEST values (filetostr(home() + 'WIZARDS\FOXQSTRT.BMP'))
go top
The Next and Previous buttons set the PictureVal property of the image on the form to FIELD1 in the cursor.
The Developer Download files for this chapter, available at www.hentzenwerke.com, include BlobDemo.SCX.
Specifying the data type of variable length values
In previous versions of VFP, using an expression in a
SQL SELECT statement that results in variable length data has one of two
consequences: the values are padded with spaces rather than trimmed and/or the
values are truncated to the length of the value in the first record in
the result.
Here’s an example, taken from TestVarcharMapping.PRG, which
shows both of
these issues:
select trim(FirstName) + ' ' + trim(LastName) as FullName ;
from Employees ;
into cursor Test
clear
scan
? FullName, len(FullName)
endscan
use
* Do it using a function to show that the fields are sized to the value for the
* first record.
select GetFullName(FirstName, LastName) as FullName ;
from Employees ;
into cursor Test
scan
? FullName, len(FullName)
endscan
function GetFullName(tcFirstName, tcLastName)
return trim(tcFirstName) + ' ' + trim(tcLastName)
The first loop shows all records in the result set are padded to 31 characters even though the expression in the SQL SELECT statement trimmed the fields. The second loop shows all records are truncated at 13 characters because that’s the length of the result of the expression for the first record; VFP uses this value as the template for the cursor structure.
It’s unlikely you want either of these behaviors. In the first case, although you requested trimmed values, the results are not trimmed. In the second case, data is actually lost because the field isn’t wide enough to contain the proper value for each record.
Fortunately, VFP 9 has an easy solution to the first problem: the new SET VARCHARMAPPING command. The VARCHARMAPPING setting, which is off by default, determines whether the values in a result set are Character or Varchar when expressions returning variable length values are used. The VARCHARMAPPING setting is scoped to the current data session. You can also specify this setting in CONFIG.FPW by adding VARCHARMAPPING = ON or VARCHARMAPPING = OFF.
Running the code shown earlier with SET
select cast(GetFullName(FirstName, LastName) as V(31)) as FullName ;
from Employees ;
into cursor Test
The Developer Download files for this chapter, available at www.hentzenwerke.com, include TestVarcharMapping.PRG.
In CursorAdapter and XMLAdapter objects, you can control the mapping of Varchar results to Varchar fields with their new MapVarchar property. Set this property to .T. to use Varchar (for results less than 255 bytes) or Memo (for results more than 255 bytes) fields or .F. to use Character or Memo. Similarly, setting the new MapBinary property to .T. maps binary values to Varbinary or Blob fields. Both of these properties default to .F. for backward compatibility reasons.
You can also control mapping in SQL passthrough. CURSORSETPROP(‘MapVarchar’) specifies whether Varchar values from ODBC data sources map to Varchar (when the setting is .T.) or Character (the setting is .F.) fields. CURSORSETPROP(‘MapBinary’) controls the mapping of binary values to Varbinary or Blob fields. Note that these settings can only be set at the “session” level; meaning you must pass 0 as the third parameter for CURSORSETPROP(). These settings are read-only for SQL passthrough cursors and invalid for VFP tables. TestMapVarchar.PRG demonstrates the use of the MapVarchar setting.
* Connect to the SQL Server Northwind database. Change the connection string as
* necessary.
lnHandle = sqlstringconnect('driver=SQL Server;server=(local);' + ;
'Database=Northwind;trusted_connection=yes;')
if lnHandle < 1
return
endif lnHandle < 1
* Get data from the Customers table with MapVarchar .F.
cursorsetprop('MapVarchar', .F., 0)
sqlexec(lnHandle, 'select * from Customers')
messagebox('CompanyName in the first record is ' + CompanyName + ;
', which is ' + transform(len(CompanyName)) + ' characters.')
use
* Do it again with MapVarchar .T.
cursorsetprop('MapVarchar', .T., 0)
sqlexec(lnHandle, 'select * from Customers')
messagebox('CompanyName in the first record is ' + CompanyName + ;
', which is ' + transform(len(CompanyName)) + ' characters.')
use
* Clean up and exit.
sqldisconnect(lnHandle)
close databases all
The Developer Download files for this chapter, available at www.hentzenwerke.com, include TestMapVarchar.PRG. Change the connection string in the first line of code as necessary to connect to the Northwind database on your SQL Server.
How the new data types affect DBF files
You may be interested to know how VFP 9 implements these new data types in a table. The DBF structure doesn’t allow for variable length fields, so Varchar and Varbinary fields actually are padded with spaces in the DBF file. Therefore, Microsoft had to come up with a mechanism to track the length of the contents for Varchar and Varbinary fields so the proper trimmed value can be returned when requested. Here’s how it works:
· All versions of VFP add a hidden field called _NullFlags to a DBF if any of the fields can accept null values. This field contains bit values that indicate whether a particular field in a given record contains a null. For example, if the first nullable field in a record contains null, bit 0 is set to 1. If the second nullable field contains a non-null value, bit 1 is 0. Because there are eight bits in a byte, _NullFlags has a width of the number of nullable fields divided by 8.
· In VFP 9, _NullFlags serves double-duty: bits also indicate whether the values in Varchar and Varbinary fields fill the fields. If a bit contains 0, the length of the value in a field equals the field size (the field is full). If the bit contains 1, the length of the value is less than the field size, in which case the field is padded with spaces as necessary and the last byte contains the field size. For example, a 10-byte Varchar field containing “AB” actually contains “AB” followed by seven spaces and CHR(2) (2 represents the size of the value) and the bit for the field in _NullsFlags is 1.
· If a field is both nullable and Varchar or Varbinary, two bits are used to represent a field. The lower bit represents the “full” status and the higher bit represents the null status. For example, a nullable 10-byte Varchar field containing “AB” is represented by 01 in _NullFlags (0 means not null, 1 means not full-size) while a null value in the same field is represented by 11 (null and not full-size).
Here’s an example that shows the various values of _NullFlags for nullable Character (Field3), non-nullable Varchar (Field2), and nullable Varchar fields (Field4). Bit 0 represents the “full” status for Field2, bit 1 contains the null status for Field3, bit 2 contains the “full” status for Field4, and bit 3 contains the null status for Field4. This example uses the HexEdit utility that comes with VFP to show the binary contents of the DBF file. Scroll to address 000001C0 to see the contents of the seven records in the table.
create table TestVarchar (Field1 C(1), Field2 V(1), Field3 C(1) null, ;
Field4 V(1) null)
insert into TestVarchar values ('A', 'A', 'A', 'A')
&& Record 20 41 41 41 41, _NullFlags 00000000 = 00
insert into TestVarchar values ('A', '', 'A', 'A')
&& Record 20 41 00 41 41, _NullFlags 00000001 = 01
insert into TestVarchar values ('A', 'A', 'A', '')
&& Record 20 41 41 41 00, _NullFlags 00000100 = 04
insert into TestVarchar values ('A', 'A', .NULL., 'A')
&& Record 20 41 41 20 41, _NullFlags 00000010 = 02
insert into TestVarchar values ('A', 'A', 'A', .NULL.)
&& Record 20 41 41 41 00, _NullFlags 00001100 = 0C
insert into TestVarchar values ('A', 'A', .NULL., .NULL.)
&& Record 20 41 41 20 00, _NullFlags 00001110 = 0E
insert into TestVarchar values ('A', '', .NULL., .NULL.)
&& Record 20 41 00 20 00, _NullFlags 00001111 = 0F
use
do home() + 'Tools\HexEdit\HexEdit' with 'TestVarchar.dbf'
(In the comments in the code, the initial 20 in the record, a space character, indicates the record is not deleted. 41 is the letter “A,” 00 indicates the length of the value in a Varchar field is 0 bytes because the field is either empty or null, and 20 in a Character field is a space, indicating the field is empty or null.)
The Developer Download files for this chapter, available at www.hentzenwerke.com, include ShowDBFStructure.PRG.
Blob fields don’t affect the DBF structure as they are stored in an FPT file using the same organization as normal Memo fields.
One other DBF change: if a table contains any of the new data types, the first byte, which indicates the type of table, contains 0x32 (50 decimal). (You can retrieve this value using SYS(2029).) As a result, you cannot open the table in earlier versions of VFP or with the VFP ODBC driver.
Binary indexes
VFP developers often create an index on the DELETED() function. This tag helps with Rushmore optimization because VFP doesn’t have to hit the disk to determine whether records are deleted; it simply looks in the index, which is likely cached in memory. (However, under certain conditions, this index can actually slow down VFP. For information, see an article by Chris Probst in the May 1999 issue of FoxPro Advisor. There are also several topics on this issue on the FoxPro Wiki; http://fox.wikis.com)
Because indexes on DELETED() and other logical expressions contain only one of two possible values (.T. or .F.), Microsoft discovered they could change the way such an index is stored in the CDX file, resulting in much smaller and faster indexes. Thus, VFP 9 sports a new index type: binary.
To create a binary index, add the BINARY keyword to the INDEX
command.
For example:
index on DELETED() tag DELETED binary
Here are some details about binary indexes:
· Binary indexes can be an order of magnitude smaller, and therefore significantly faster, than normal indexes. TestBinaryIndex.PRG creates a sample table with normal and binary indexes on DELETED(). The binary index is more than 90% smaller than the normal one.
· The only use for binary indexes is Rushmore optimization. You cannot SEEK on them, nor can you SET ORDER to them.
· The logical expression must never evaluate to a null value, neither when the index is created nor in later use of the table, or you’ll get an error.
· You cannot use the FOR, ASCENDING, DESCENDING, UNIQUE, or CANDIDATE clauses in the INDEX command and cannot create an IDX index file when creating a binary index.
· According to the VFP help (the “Visual FoxPro Index Types” topic), VFP may create the Rushmore optimization bitmap faster or slower for a binary index, depending on whether the number of records returned is more or less than 3% of the total number of records. However, this threshold depends on several factors, including the total number of records. As is often the case, you need to test this under real-world conditions to determine the impact binary indexes will have on your queries. Another VFP help topic, “Indexes Based on Deleted Records,” has additional information about what VFP can optimize under various conditions.
The Developer Download files for this chapter, available at www.hentzenwerke.com, include TestBinaryIndex.PRG.
Summary
The new data types added to VFP 9 make it easier than
ever to work with non-native databases such as SQL Server, but also prove
useful when used in native tables. Blob
fields are especially useful if you currently store images in General fields.
Binary indexes
can improve the performance of your SQL SELECT statements beyond VFP’s already blinding
speed.
Updates and corrections for this chapter can be found on Hentzenwerke’s website, www.hentzenwerke.com. Click “Catalog” and navigate to the page for this book.