Share via


Data Type Conversion Control

When you move data between a remote server and Visual FoxPro, you might encounter differences in data types available on your server or in Visual FoxPro. There is rarely a one-to-one correlation between data types available on a remote data source and those available in Visual FoxPro. To handle these differences, Visual FoxPro uses ODBC or ADO data types to map remote data types to local Visual FoxPro data types. By understanding how data types are mapped between ODBC or ADO and Visual FoxPro, you can predict how the server's remote data is handled in your Visual FoxPro application.

You can also adjust the data types used on the server or in your application. You can override the default Visual FoxPro field data type by creating a view for the remote data set and then setting the DataType view field property in the database. The DataType property is a character property indicating the desired data type for each field of a remote view. For more information on the DataType property, see DBSETPROP( ).

Data Type Conversion between Remote Views and Visual FoxPro Cursors

When you retrieve data from a remote ODBC or ADO data source, Visual FoxPro converts the data type of each ODBC or ADO field into an equivalent Visual FoxPro data type in the resulting cursor.

Remote Data Types to Visual FoxPro Data Types

The following table lists the default data type mapping between remote field data types and Visual FoxPro data types for cursors.

ODBC/ADO data type Default Visual FoxPro cursor data types

SQL_BINARY / adBinary SQL_VARBINARY / adVarbinary

Memo/Blob1 or Varbinary

Note

Default type is affected by the MapBinary property. For more information, see CURSORSETPROP( ) Function.

SQL_LONGVARBINARY

General/Blob

Note

Default type is affected by the MapBinary property. For more information, see CURSORSETPROP( ) Function.

adLongVarbinary

Memo or Character

SQL_CHAR / adChar SQL_WCHAR / adWChar

Memo1 or Character

SQL_LONGVARCHAR / adLongVarChar SQL_WLONGVARCHAR / adLongVarWChar

Memo

SQL_VARCHAR / adVarChar SQL_WVARCHAR / adVarWChar

Memo1 or Character/Varchar

Note

Default type is affected by the MapVarchar property. For more information, see CURSORSETPROP( ) Function.

SQL_DECIMAL / adNumeric SQL_NUMERIC / adNumeric

Currency2

SQL_BIT / adBoolean

Logical

SQL_TINYINT / adVarbinary SQL_SMALLINT / adSmallInt SQL_INTEGER / adInteger

Integer

SQL_BIGINT / adBigInt

Character

SQL_REAL / adSingle SQL_FLOAT / adDouble SQL_DOUBLE / adSingle

Double

Note

For Double, the number of decimal places is the value of SET DECIMAL in Visual FoxPro.

SQL_DATE / adDBTimeStamp

Date

SQL_TIME / adDBTTimeStamp

DateTime4

SQL_TIMESTAMP / adBinary

DateTime3

1 If the ODBC/ADO field width is less than the value of the cursor property UseMemoSize, it becomes a Character/Varchar/Varbinary field in the Visual FoxPro cursor; otherwise, it becomes a Memo/Blob field.

2 If the server field is a money data type, it becomes a Currency data type in Visual FoxPro.

3 If the value in the SQL_TIMESTAMP field contains fractions of seconds, the fractions are truncated when the value is converted to a Visual FoxPro DateTime data type.

4 The day defaults to 1/1/1900.

The following table lists the data types available when using remote views or CursorAdapter objects along with ODBC or ADO data sources and the equivalent Visual FoxPro data types. For more information about specifying cursor schema, see the CursorAdapter CursorFill Method.

ODBC/ADO data type Visual FoxPro cursor acceptable data types

SQL_BINARY / adBinary SQL_VARBINARY / adVarbinary SQL_LONGVARBINARY / adLongVarbinary SQL_CHAR / adChar SQL_VARCHAR / adVarChar SQL_LONGVARCHAR / adLongVarChar

Blob, Varbinary, Character, Varchar, General, Memo5

SQL_WCHAR / adWChar SQL_WVARCHAR / adVarWChar SQL_WLONGVARCHAR / adLongVarWChar

Character, Varchar, Memo

SQL_BIT / adBoolean

Character, Varchar, Logical

SQL_REAL / adSingle SQL_FLOAT / adDouble SQL_DOUBLE / adSingle SQL_DECIMAL / adNumeric SQL_NUMERIC / adNumeric SQL_TINYINT / adVarbinary SQL_SMALLINT / adSmallInt SQL_INTEGER / adInteger SQL_BIGINT / adBigInt

Character, Varchar, Integer, Numeric, Float, Double, or Currency6

Note

For Double, the number of decimal places is the value of SET DECIMAL in Visual FoxPro.

SQL_DATE / adDBTimeStamp SQL_TIMESTAMP / adBinary

Character, Varchar, Date, DateTime7

SQL_TIME / adDBTTimeStamp

Character, Varchar, DateTime8

5 If the ODBC field width is less than the value of the cursor property UseMemoSize, it becomes a Character field in the Visual FoxPro cursor; otherwise, it becomes a Memo field.

6 If the server field is a money data type, it becomes a Currency data type in Visual FoxPro.

7 If the value in the SQL_TIMESTAMP field contains fractions of seconds, the fractions are truncated when the value is converted to a Visual FoxPro DateTime data type.

8 The day defaults to 1/1/1900.

Note

Null values in ODBC data source fields become null values in the Visual FoxPro cursor, regardless of the SET NULL setting in Visual FoxPro at the time your application retrieves remote data.

In Visual FoxPro 9.0, certain ODBC and ADO data types can be mapped to a logical data type in remote views and the CursorAdapter object.

If the backend value is zero, the data type is mapped to a logical False (.F.); all other values are mapped to True (.T.). When updates are performed, the mapping from a logical data type to an integer is backend specific. For ODBC, False (.F.) is usually mapped to 0 and True (.T.) is usually mapped to 1. For ADO, False (.F.) is usually mapped to zero and .T. is usually mapped to 1 or -1, depending on the command being executed. The CursorAdapter ConversionFunc Property can be used to enforce the mapping.

For remote views and the CursorAdapter object, the ODBC SQL_DECIMAL and SQL_NUMERIC data types can only be mapped to a logical value if the Scale is set to zero.

For the CursorAdapter object, the ADO adDecimal and adNumeric data types can only be mapped to a logical value if the Scale is set to zero.

The following table lists the ODBC and ADO data types that can be mapped to a logical data type.

ODBC data type ADO data type

SQL_TINYINT

SQL_SMALLINT

SQL_INTEGER

SQL_BIGINT

SQL_DECIMAL

SQL_NUMERIC

adSingle

adTinyInt

adSmallInt

adInteger

adBigInt

adUnsignedTinyInt

adUnsignedSmallInt

adDecimal

adNumeric

The following table lists Visual FoxPro data types available when using remote views with CursorAdapter objects and native Visual FoxPro data and their equivalent Visual FoxPro data types.

Visual FoxPro native data type Visual FoxPro cursor acceptable data type

Character, Memo, General

Character, Varchar, Memo, General

Numeric, Float, Currency, Integer, Double

Numeric, Float, Currency, Integer, Double, Character, Varchar

Logical

Logical, Character, Varchar

Date, DateTime

Character, Varchar, Date, DateTime

Visual FoxPro Parameter Values to Remote View Data Types

If Visual FoxPro data exists in a cursor that originated from remote data, the data reverts to its original ODBC or ADO data type when sending data to the remote server. If you send data that originated in Visual FoxPro to the remote server using SQL pass-through, the table describes the conversions that apply.

Visual FoxPro data type ODBC data type

Character, Varchar, Memo

SQL_CHAR or SQL_LONGVARCHAR9

Blob, Varbinary

SQL_BINARY or SQL_LONGVARBINARY

Currency

SQL_DECIMAL

Date

SQL_DATE or SQL_TIMESTAMP10

DateTime

SQL_TIMESTAMP

Double

SQL_DOUBLE

Integer

SQL_INTEGER

General

SQL_LONGVARBINARY

Logical

SQL_BIT

Numeric

SQL_DOUBLE

9 If the Visual FoxPro variable that maps to a parameter creates an expression whose width is less than 255, it becomes a SQL_CHAR type in the ODBC data source; otherwise, it becomes a SQL_LONGVARCHAR type.

10 Visual FoxPro Date data is converted to SQL_DATE for all ODBC data sources except SQL Server, where it becomes SQL_TIMESTAMP.

Visual FoxPro Parameter Values to Remote Data Type Mapping

You can map a Visual FoxPro parameter value to a particular remote data type by formatting the parameter as a character expression that uses the syntax for the desired remote data type. For example, if your server provides a DateTime data type, you can create your Visual FoxPro parameter as a character expression in the format used by your server to represent DateTime data. When your server receives the parameter value, it attempts to map the formatted data to the DateTime data type.

Note

When you send a parameter to the remote server, be sure the data type in the WHERE clause matches the data type that's used for the parameter expression.

See Also

Concepts

Processing Multiple Result Sets
Handling SQL Pass-Through Errors
Working with Remote Data Using SQL Pass-Through

Other Resources

Enhancing Applications Using SQL Pass-Through Technology
Planning Client/Server Applications
Upsizing Visual FoxPro Databases
Creating Views