Data type mapping Professional

When Xbase++ is used as the data definition language (DDL) and data manipulation language (DML) for non-Xbase databases, an application programmer must take into account the differences that may exist between an Xbase database and a foreign database. This applies mainly to the data types supported by a DBMS which requires to match different data types when the DBMS supports other or more data types than are available in Xbase++.

An SQL DBMS, for example, supports different numeric data types - such as integer, float or real - while there is only one numeric data type in Xbase++. This requires the ODBCDBE to match data types and to convert data when data is read from or written to an SQL table by an Xbase++ application. This is done automatically and is referred to as Data Type Mapping. All numeric data types supported by a DBMS are mapped to one numeric data type in an Xbase++ program. Likewise, the data type Logical of an Xbase++ program is mapped to the data type Bit in an SQL table, because Logical is a data type of Xbase languages and does not exist in SQL databases. Logical, however, is equivalent to Bit.

Xbase++ data types are identified by a single letter, equivalent to the return value of the ValType() function. In contrast, data types of non-Xbase databases are usually identified by symbolic names. This requires a programmer to know what data types are present in an SQL table and what data type a particular value stored in the table will have when it is assigned to a memory variable in the Xbase++ program. The latter raises another issue: some data types cannot exist in memory but only in tables. An example is variable-length text stored in a memo field of a DBF table. Assume a memo field having the name NOTES:

? Valtype( FIELD->NOTES )  // result: M 

cText := FIELD->NOTES      // assign contents of field to variable 

? Valtype( cText )         // result: C 

These three lines of code demonstrate that data type mapping exists already in Xbase++ (and other Xbase languages). The data type of the field value is "M" (Memo), and it becomes "C" (Character) when the field value is assigned to a memory variable. Since Xbase++ can be used as data definition language (DDL) and data manipulation language (DML), it is important to distinguish data type mapping for data definition and data manipulation. Data definition means: creation of tables (CREATE FROM, DbCreate()), and data manipulation covers reading/writing of values stored in fields, or table columns (REPLACE, FieldGet(), FieldPut()). For example, it makes a difference if two fields are defined having datatypes "C" and "M", but it makes no difference when the value of either field is manipulated.

The distinction between DML and DDL is of minor importance as long as data is stored in DBF tables. However, it becomes important when data sources are created or accessed via the ODBCDBE. In this case, data type mapping may be different for data definition and data manipulation. This depends on the data types supported by the data source. For example, the ODBCDBE is able to create an SQL table having columns of different numeric data types. Xbase++, used as the DDL, supports different numeric data types for the data definition. In contrast, Xbase++, used as the DML, makes no difference between numeric data types when data is manipulated in an application program.

Xbase programmers are usually not aware of the difference between DDL and DML since the same programming language is used for both, data definition and data manipulation. In addition, all Xbase programming languages hide data type mapping from a programmer, and so does Xbase++/ODBCDBE (the value of a memo field has type "M" and is mapped to type "C" when it is assigned to a memory variable). However, data type mapping is an important issue when non-Xbase databases are accessed. There is a data type mapping between DDL data types and DML data types, and a mapping between Xbase data types and non-Xbase data types. To be able to distinguish the data types from their origin, the following terms are used:

DDL-type

Data type for the data definition with Xbase++. A DDL-type is represented by a single letter used to define the data type of a database field. DDL-types are listed in the second column of the DbStruct() array.

DML-type

Data type for the data manipulation with Xbase++. A DML-type is represented by a single letter equivalent to the return value of the Valtype() function.

ODBC-type

Data type exposed by the data source accessed via ODBCDBE.

DBMS-type

Native data type of the data source. It is usually a symbolic name for non-Xbase databases.

Since ODBC-types are data source specific, the following documentation uses ODBC data types for explanations.

Data definition (DDL)

When a table is created by an Xbase++ application, the table structure is defined in an array having four columns. The second column of the array contains letters representing the DDL-types for the table columns. The resulting ODBC-types are listed in the following table:

Data manipulation (DML)

When a table is opened by the ODBCDBE, the ODBC-types are mapped to DML-types if a value of a field is assigned to a variable in memory. The DDL-types are listed in the DbStruct() array: