Varbinary Data Type

To store binary values or literals of fixed length without padding by additional zero (0) bytes or truncating trailing 0 bytes, use the Varbinary data type. To store binary data with indeterminate length, use the Blob data type instead. For more information, see Blob Data Type.

Binary literals begin with a prefix, 0h, followed by a string of hexadecimal numbers and are not enclosed with quotation marks (""), unlike character strings. The following list includes examples of binary literals:

• 0h202020

• 0h6ABCDEF

• 0h (empty binary literal)

Binary literals are limited to 255 encoded bytes. Varbinary fields are limited to 254 encoded bytes.

When performing comparison or concatenation operations, such as =, !=, ==, >, >=, <=, +, and -, the results vary depending on the operation and order of types in the operation. When you concatenate two binary values, the result is a binary value. When you concatenate variables of Varbinary (Q) type with Varchar (V) or Character (C) type, the result has the data type of the first item in the concatenation list. For example:

V + Q = V

Q + V = Q

When you compare between Varbinary and Character types, the type on the left side of the comparison expression governs the operation. For example, suppose a comparison expression such as the following is binary-based:

Q = V

Visual FoxPro evaluates these values on a byte for byte basis, and the result is the same as performing CAST(V AS Q(n) = Q.

Suppose the following comparison expression is character-based:

V = Q

Visual FoxPro evaluates these values as if they were character strings, and the result is the same as performing CAST(Q AS V(n) = V.

Index keys based on Varbinary fields or expressions are padded with zeros from the right of the value to the maximum length of the field. For example, the values 0hAA, 0hAA00, and 0hAA000 have the same index key of 0hAA0000. The default collation sequence for index keys based on binary expressions is MACHINE. No other collation sequences are permitted. For more information, see INDEX Command and Index Creation Based on Expressions.

Support for Varbinary Data Type

The Varbinary type is supported for database containers (.dbc), free tables, cursors, and views. For example, you can select this type for a field on the Fields tab in the Table Designer. Tables can contain multiple Varbinary fields. You can specify default and null values for Varbinary fields. Varbinary fields support field validation.

No code page translation is performed for data with Varbinary type.

The following clauses and functions do not support Varbinary data types:

• LIKE in join and filter conditions for SQL statements

• LIKE( ) Function

• LIKEC( ) Function

• BINTOC( ) Function

• BITLSHIFT( ) Function

• BITRSHIFT( ) Function

• CTOBIN( ) Function

The following functions remove leading or trailing zero bytes from binary values:

• ALLTRIM( ) Function

• LTRIM( ) Function

• RTRIM( ) Function

• TRIM( ) Function

The following table lists language that contains functionality affected by the Varbinary data type.

? ?? Command	AFIELDS( ) Function	ALINES( ) Function
ALTER TABLE - SQL Command	APPEND FROM Command	BITAND( ) Function
BITCLEAR( ) Function	BITNOT( ) Function	BITOR( ) Function
BITSET( ) Function	BITTEST( ) Function	BITXOR( ) Function
COPY STRUCTURE EXTENDED Command	COPY TO Command	CREATE CURSOR - SQL Command
CREATE FROM Command	CREATE TABLE - SQL Command	CURSORGETPROP( ) Function
CursorSchema Property	CURSORSETPROP( ) Function	CURSORTOXML( ) Function
DataType Property	EMPTY( ) Function	Format Property
InputMask Property	ISBLANK( ) Function	LIST Commands
MLINE( ) Function	SET ENGINEBEHAVIOR Command	SET ANSI Command
SET EXACT Command	TRANSFORM( ) Function	XMLTOCURSOR( ) Function

Note

Values with Varbinary type are not compatible with the binary expressions produced by the BINTOC( ) function or used by the CTOBIN( ) function. String functions that usually return strings, such as the SUBSTR( ) function, now return binary values when passed binary values.

For more specifications about the Varbinary data type, see Visual FoxPro Data and Field Types.

The following example clears the main Visual FoxPro window using the CLEAR command and creates a cursor with a field named myVarbinaryField with Varbinary type using the SQL CREATE CURSOR command. For each SQL INSERT statement, the INSERT command inserts a row into the cursor containing a binary literal in the myVarbinary field. GO TOP positions the record pointer at the first record. The DO WHILE loop displays the number of characters in the field for each row using the LEN( ) function until the last record in the table is reached.

You can type the example in a program (.prg) file and run it from the Command window using the DO Command.

CLEAR
CREATE CURSOR myCursor (myVarbinaryField Q(10))
INSERT INTO myCursor (myVarbinaryField) VALUES (0h616161202020)
INSERT INTO myCursor (myVarbinaryField) VALUES (0hABCDEF)
INSERT INTO myCursor (myVarbinaryField) VALUES (0h)
GO TOP
DO WHILE !EOF()
   ? "# Varbinary characters: ", LEN(myVarbinaryField)
   ? 
   SKIP
ENDDO

For more information about the commands and functions used in this example, see CLEAR Commands, CREATE CURSOR - SQL Command, INSERT - SQL Command, BROWSE Command, GOGOTO Command, DO WHILE ... ENDDO Command, EOF( ) Function, and SKIP Command.

See Also

Tasks

How to: Choose Data Types

Reference

Fields Tab, Table Designer

String and Value Comparison in Visual FoxPro

TYPE( ) Function

VARTYPE( ) Function

Other Resources

Data and Field Types