Varbinary Data Type

  • Article

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:

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

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