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