Defining the data types for table columns Professional

The data types of table columns are defined when the table is created. The usual approach is to define the table structure in an array with four columns and passing it to the DbCreate() function. Each element of the array defines the field name, data type, field length and number of decimal places for one column in the table. For example:

aStructure := {              ; 
   { "CHAR"  , "C", 20, 0 }, ; 
   { "NUMBER", "N",  8, 2 }  ; 
} 

DbCreate( "Test", aStructure ) 

The data types of the columns are defined in the second array column with a single character representing the Xbase++ DDL-type for a field variable. This procedure does not differ from DBF file creation. However, since the table is created on the server, the DDL-type is mapped to an equivalent DBMS-type supported by the server (refer to Data type mappingfor an explanation of DDL-types and DBMS-types).

The single character for the DDL-type is not always sufficient to define the DBMS-type unambiguously. An example is the character "I" which defines fields holding numeric integer values. There is only one DDL-type "I", but the DBMS may support SQL_TINYINT, SQL_SMALLINT, SQL_INTEGER and SQL_BIGINT, i.e. Xbase++ must be able to create tables storing four different integer data types.

An exact definition of the DBMS-type is achieved by using a specific combination of DDL-type, field length and decimal places. This is different to DBF table creation and may also differ between two DBMS. Therefore, the required combination to define a specific DBMS-type can be retrieved from a DacSession object once a connection to the server is established. The code for retrieving this information is listed in the next example:

cConnect := "<... the connection string ...>" 

oSession := DacSession():new( cConnect ) 

aMapping := oSession:setProperty( ODBCSSN_XPP_TO_ODBC ) 

ASort( aMapping,,, {|a1,a2| a1[1] < a2[1] } ) 

? "DDL-type  FLen  FDec  DBMS-type" 
? 
AEval( aMapping, {|a| QOut( a ) } ) 

Passing the #define constant ODBCSSN_XPP_TO_ODBC to the :setProperty()method results in an array having four columns. They contain the information required for defining the DBMS-type of table columns when a table is created on the server by an Xbase++ application. The contents of the array is now discussed using an example output of this code. This output was obtained by connecting to MS SQL Server:

DDL-type  FLen  FDec  DBMS-type 

{C,          0,    0, char} 
{C,         36,    1, uniqueidentifier} 
{D,          0,    0, datetime} 
{F,          0,    0, float} 
{F,         16,    7, real} 
{I,          0,    0, int} 
{I,          4,    0, tinyint} 
{I,          6,    0, smallint} 
{L,          0,    0, bit} 
{M,          0,    0, text} 
{M,        max,    0, varchar} 
{N,          0,    0, numeric} 
{Q,          0,    0, ntext} 
{Q,        max,    0, nvarchar} 
{V,          0,    0, image} 
{V,        max,    0, varbinary} 
{W,          0,    0, nchar} 
{X,          0,    0, binary} 

The first column of the array contains the letters for the DDL-type of a table column. The second and third column contain the values for field length and decimal places that must be used to define a table column of the DBMS-type listed in the fourth column of the array. The word "max" in the second column is a placeholder for the maximum length of data that can be stored in a table column having a variable-length data type with a fixed maximum length. The following code demonstrates how this information can be used:

aStructure := {                       ; 
   { "UID"          , "C",   36, 1 }, ;  // unique identifier 
   { "PARTNAME"     , "C",   20, 0 }, ;  // char, max. length 20 
   { "DESCRIPTION"  , "M", 1024, 0 }, ;  // varchar, max. length 1024 
   { "JPGIMAGE"     , "V",    0, 0 }, ;  // image 
   { "COST"         , "F",   16, 7 }  ;  // real 
} 

DbCreate( "Test", aStructure ) 

When this code is executed in a Xbase++ application (client side), a table with five columns is created on the server, having the DDL-types CCMVF. The MS SQL Server, however, would treat data stored in the table as uniqueidentifier, char, varchar, image and real.