Data Type Conversion Control
When you move data between a remote server and Visual FoxPro, you may encounter differences in the richness of data types available either on your server or in Visual FoxPro, because 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 data types to map remote data types to local Visual FoxPro data types. By understanding how data types are mapped between ODBC and Visual FoxPro, you can predict how your server's remote data will be handled in your Visual FoxPro application.
If you need to, you can also adjust the data types used on your server or in your application. The default Visual FoxPro field data type can be overridden 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( ).
Downloading and Uploading Remote View Data
When you retrieve data from a remote ODBC data source, Visual FoxPro converts the data type of each ODBC field into an equivalent Visual FoxPro data type in the result set cursor. The following table lists the data types available on ODBC data sources and their Visual FoxPro equivalents.
| ODBC data type of remote field | Field data type in Visual FoxPro cursor |
|---|---|
| SQL_CHAR SQL_VARCHAR SQL_LONGVARCHAR |
Character or Memo1 |
| SQL_BINARY SQL_VARBINARY SQL_LONGVARBINARY |
Memo |
| SQL_DECIMAL SQL_NUMERIC |
Numeric or Currency2 |
| SQL_BIT | Logical |
| SQL_TINYINT SQL_SMALLINT SQL_INTEGER |
Integer |
| SQL_BIGINT | Character |
| SQL_REAL SQL_FLOAT SQL_DOUBLE |
Double; the number of decimal places is the value of SET DECIMAL in Visual FoxPro |
| SQL_DATE | Date |
| SQL_TIME | DateTime3 |
| SQL_TIMESTAMP | 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 The day defaults to 1/1/1900.
4 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.
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.
Converting Visual FoxPro Parameters to Remote View Data Types
If Visual FoxPro data exists in a cursor that originated from remote data, the data goes back to its original ODBC type when sent to the remote server. If you send data that originated in Visual FoxPro to the remote server via SQL pass-through, the following conversions 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.
Mapping a Visual FoxPro Parameter Into a Remote Data Type
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