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 View Data Types to Visual FoxPro Data Types
The following table lists 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 CursorAdapterCursorFill Method.
| ODBC/ADO data type | Visual FoxPro cursor acceptable data type |
|---|---|
| SQL_CHAR / adChar SQL_VARCHAR / adChar SQL_LONGVARCHAR / adChar SQL_BINARY / adBinary SQL_VARBINARY / adVarbinary SQL_LONGVARBINARY / adVarbinary |
Character, Memo1, or General |
| SQL_WCHAR / adWchar SQL_WVARCHAR / adWchar SQL_WLONGVARCHAR / adWchar |
Character, Memo |
| SQL_BIT / adBoolean | Logical or Character |
| 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, Integer, Numeric, Float, Double (number of decimal places is the value of SET DECIMAL in Visual FoxPro), or Currency2 |
| SQL_DATE / adDBTimeStamp SQL_TIMESTAMP / adBinary |
Character, Date, DateTime3 |
| SQL_TIME / adDBTTimeStamp | Character, DateTime4 |
1 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.
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.
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.
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, Memo, or General |
| Numeric, Float, Currency, Integer, Double | Numeric, Float, Currency, Integer, Double, or Character |
| Logical | Character or Logical |
| Date, DateTime | Character, Date, or 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 | SQL_CHAR or SQL_LONGVARCHAR1 |
| Currency | SQL_DECIMAL |
| Date | SQL_DATE or SQL_TIMESTAMP2 |
| DateTime | SQL_TIMESTAMP |
| Double | SQL_DOUBLE |
| Integer | SQL_INTEGER |
| General | SQL_LONGVARBINARY |
| Logical | SQL_BIT |
| Memo | SQL_LONGVARCHAR |
| Numeric | SQL_DOUBLE |
1 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.
2 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
Processing of Multiple Result Sets | Handling SQL Pass-Through Errors | Working with Remote Data Using SQL Pass-Through | Implementing a Client/Server Application | Designing Client/Server Applications | Upsizing Visual FoxPro Databases | Creating Views