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.

¹ 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.

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

³ 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.

⁴ 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.

⁵ 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.

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

⁷ 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.

⁸ The day defaults to 1/1/1900.

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.

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 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.

⁹ 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.

¹⁰ 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.

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